X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E78C73A2B1B for ; Wed, 3 Nov 2004 19:18:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59399-01 for ; Wed, 3 Nov 2004 19:17:54 +0000 (GMT) Received: from neomail03.traderonline.com (email.traderonline.com [65.213.231.10]) by svr1.postgresql.org (Postfix) with ESMTP id BC01A3A1D9E for ; Wed, 3 Nov 2004 19:17:54 +0000 (GMT) Received: from [192.168.102.100] (24.229.184.154.res-cmts.nbh.ptd.net [24.229.184.154] (may be forged)) by neomail03.traderonline.com (8.12.10/8.12.8) with ESMTP id iA3IYu63027766; Wed, 3 Nov 2004 13:34:56 -0500 Message-ID: <41892EE0.3090703@ptd.net> Date: Wed, 03 Nov 2004 14:17:52 -0500 From: Doug Y User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: patrick ~ Cc: pgsql-performance@postgresql.org Subject: Re: vacuum analyze slows sql query References: <20041103185647.87443.qmail@web52105.mail.yahoo.com> In-Reply-To: <20041103185647.87443.qmail@web52105.mail.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/20 X-Sequence-Number: 8974 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 >