It's likely that data is in filesystem (not database) cache the second time you run the query. See if the same thing happens when you stop and restart the postmaster (it likely wont), then do something like this to flush the filesystem cache (read a big file, can't give you a sample cmd because my Treo has no equal sign :-) then run the query again.
- Luke -----Original Message----- From: John Mendenhall [mailto:[EMAIL PROTECTED] Sent: Sat Jul 23 12:54:18 2005 To: pgsql-performance list Subject: [PERFORM] re: performance decrease after reboot pgsql performance gurus, I sent the following message earlier this week. I have continued attempting to find something on the net that would explain this strange change of query plans, but nothing seems to apply. Are there any thoughts, such as possibly tweaking the database somehow to see if I can get this to repeat consistently? Please let me know if any of you have any pointers as to the cause of the different query plans. Thank you very much in advance for any pointers you can provide. JohnM On Tue, 19 Jul 2005, John Mendenhall wrote: > I tuned a query last week to obtain acceptable performance. > Here is my recorded explain analyze results: > > LOG: duration: 826.505 ms statement: explain analyze > [cut for brevity] > > I rebooted the database machine later that night. > Now, when I run the same query, I get the following > results: > > LOG: duration: 6931.701 ms statement: explain analyze > [cut for brevity] I just ran my query again, no changes from yesterday and it is back to normal: LOG: duration: 795.839 ms statement: explain analyze What could have been the problem? The major differences in the query plan are as follows: (1) The one that runs faster uses a Hash Join at the very top of the query plan. It does a Hash Cond on the country and code fields. (2) The one that runs slower uses a Materialize with the subplan, with no Hash items. The Materialize does Seq Scan of the countries table, and above it, a Join Filter is run. (3) The partners_pkey index on the partners table is in a different place in the query. Does anyone know what would cause the query plan to be different like this, for the same server, same query? I run vacuum analyze every night. Is this perhaps the problem? What setting do I need to tweak to make sure the faster plan is always found? Thanks for any pointers in this dilemma. JohnM -- John Mendenhall [EMAIL PROTECTED] surf utopia internet services ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly