Re: vacuum analyze slows sql query

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
>