Hello,
I've noticed some posts on hanging queries but haven't seen any
solutions yet so far.
Our problem is that about a week and a half ago we started to get some
queries that would (seemingly) never return (e.g., normally run in a
couple minutes, but after 2.5 hours, they were still running, the
process pushing the processor up to 99.9% active).
We are running Postgres 8.1.1 on Redhat 7.3 using Dell poweredge quad
processor boxes with 4 GB of memory. We have a main database that is
replicated via Sloney to a identical system.
Things we've tried so far:
We've stopped and restarted postgres and that didn't seem to help, we've
rebuilt all the indexes and that didn't seem to help either. We've
stopped replication between the boxes and that didn't do anything.
We've tried the queries on both the production and the replicated box,
and there is no difference in the queries (or query plans)
We do have another identical system that is a backup box (same type of
box, Postgres 8.1.1, Redhat 7.3, etc), and there, the query does
complete executing in a short time. We loaded up a current copy of the
production database and it still responded quickly.
Generally these queries, although not complicated, are on the more
complex side of our application. Second, they have been running up
until a few weeks ago.
Attached are an example query plan: Query.sql
The query plan from our production sever: QueryPlanBroke.txt
The working query plan from our backup server: QueryPlanWork.txt
What we found that has worked so far is to remove all the outer joins,
put the results into a temp table and then left join from the temp table
to get our results. Certainly this isn't a solution, but rather
something we have resorted to in a place or to as we limp along.
Any help would be greatly appreciated.
Thanks,
Chris Beecroft