pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Doug Y <dylists@ptd.net>
To: patrick ~ <sidsrr@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Wed, 03 Nov 2004 14:17:52 -0500
Message-ID: <41892EE0.3090703@ptd.net> (raw)
In-Reply-To: <20041103185647.87443.qmail@web52105.mail.yahoo.com>
References: <20041103185647.87443.qmail@web52105.mail.yahoo.com>

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
> 




view thread (17+ messages)  latest in thread

Message-ID: <41892EE0.3090703@ptd.net>
Permalink:  ../41892EE0.3090703@ptd.net/
Also on:    postgresql.org/message-id/41892EE0.3090703@ptd.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dylists@ptd.net, sidsrr@yahoo.com
  Subject: Re: vacuum analyze slows sql query
  In-Reply-To: <41892EE0.3090703@ptd.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox