I am having a serious problem with my application and I hope someone can
help me out.
This could not happen at a worse time as a consulting firm is at my
clients to recommend a new financial system and the inventory
system(which I developed) keeps locking up.
I have a dynamically built query that will periodically(2 times a day
and becoming more frequent) make my server totally unresponsive.
The query that hangs the system is requesting a count(*)
based on some parameters the users selects.
Since I set my timeout to 2 minutes(the only way I have been able to
deal with this so far) I see the offending query in the log.
I took the query from the logs and pasted it into pgAdmin and ran it a
few times.
Sometime is takes 700-900 ms, but others it takes 60-100 seconds.
Other times it never stops(I waited 10 minutes).
If I run 10 times I get
8 less that 1 sec
2 5-10 sec
And maybe 1 in 20 will not complete.
Dev Server specs
1 CPU Xeon 5472 Quad core 3.0 GHz 1600MHz FSB
2x10k Raptor Raid 1
DB 50 mb with a lot of table joins
These queries are being run with nothing else running on the server.
My guess it that the planner is picking an inefficient plan sometimes.
I have not looked into the detail of the explain, and I do see visually
that very different plans are being chosen.
How can I see the plan that was chosen when the time it very high?
Is there a way to print the query plan chosen when the query times out
as well?