Given that the plan doesn't change after an analyze, my guess would be that the first query is hitting cached data, then
you vacuum and that chews though all the cache with its own data pushing the good data out of the cache so it has to
be re-fetched from disk.
If you run the select a 2nd time after the vacuum, what is the time?
Not sure what your pkk_offer_has_pending_purch function does, that might be something to look at as well.
I could be wrong, but thats the only thing that makes sense to me. ARC is supposed to help with that type of behavior in 8.0
patrick ~ wrote:
Greetings pgsql-performance :)
Yesterday I posted to the pgsql-sql list about an issue with VACUUM
while trying to track-down an issue with performance of a SQL SELECT
statement invovling a stored function. It was suggested that I bring
the discussion over to -performance.
Instread of reposting the message here is a link to the original
message followed by a brief summary:
http://marc.theaimsgroup.com/?l=postgresql-sql&m=109945118928530&w=2
Summary:
Our customer complains about web/php-based UI sluggishness accessing
the data in db. I created a "stripped down" version of the tables
in question to be able to post to the pgsql-sql list asking for hints
as to how I can improve the SQL query. While doing this I noticed
that if I 'createdb' and populate it with the "sanatized" data the
query in question is quite fast; 618 rows returned in 864.522 ms.
This was puzzling. Next I noticed that after a VACUUM the very same
query would slow down to a crawl; 618 rows returned in 1080688.921 ms).
This was reproduced on PostgreSQL 7.4.2 running on a Intel PIII 700Mhz,
512mb. This system is my /personal/ test system/sandbox. i.e., it
isn't being stressed by any other processes.
Thanks for reading,
--patrick
