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 A122455431 for ; Wed, 9 Mar 2005 22:08:50 +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 65927-07 for ; Wed, 9 Mar 2005 22:08:41 +0000 (GMT) Received: from pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 132B854D6F for ; Wed, 9 Mar 2005 22:08:38 +0000 (GMT) Received: (qmail 27111 invoked from network); 9 Mar 2005 22:08:33 -0000 Received: from unknown (HELO wizzard.pws.com.au) (russell@pws.com.au@138.217.54.70) by mail.pws.com.au with SMTP; 9 Mar 2005 22:08:33 -0000 From: Russell Smith To: Hugo Ferreira Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer Date: Thu, 10 Mar 2005 09:08:32 +1100 User-Agent: KMail/1.7 Cc: pgsql-performance@postgresql.org References: <20050304002724.650A95AF649@svr4.postgresql.org> <3276.1110218527@sss.pgh.pa.us> <4e8efcf5050309040836b06cc5@mail.gmail.com> In-Reply-To: <4e8efcf5050309040836b06cc5@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503100908.32467.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/173 X-Sequence-Number: 10878 On Wed, 9 Mar 2005 11:08 pm, Hugo Ferreira wrote: > For example... I run the query, it takes 122seconds. Then I delete the > target tables, vacuum the database, re-run it again: 9s. But if I run > vacuum several times, and then run, it takes again 122seconds. If I > stop this 122seconds query, say, at second 3 and then run it again, it > will only take 9s. It simply doesn't make sense. Also, explain analyse > will give me diferent plans each time I run it... Unfortunately, this > is rendering PostgreSQL unusable for our goals. Any ideas? > The explain analyze is still be best information if you want assistance with what postgresql is doing, and how to stop it. If you could attach explain analyzes for both the fast (9s), and slow (122s) runs, that would help people get an idea of how the query is running. At the moment we don't know how postgresql is actually executing the query. Regards Russell Smith.