X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9E9A9D1C7F1 for ; Tue, 12 Aug 2003 16:28:46 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 80360-05 for ; Tue, 12 Aug 2003 13:27:12 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 8D3D5D1C4BA for ; Tue, 12 Aug 2003 13:28:35 -0300 (ADT) Received: from melina.szuwary.org (136-bem-3.acn.waw.pl [62.121.82.136]) by www.postgresql.com (Postfix) with ESMTP id 6F419CF77E0 for ; Tue, 12 Aug 2003 06:52:27 -0300 (ADT) Received: by melina.szuwary.org (Postfix, from userid 1000) id 38178DF22; Tue, 12 Aug 2003 11:57:08 +0200 (CEST) Date: Tue, 12 Aug 2003 11:57:07 +0200 From: Jacek Rembisz To: pgsql-performance@postgresql.org Subject: Re: Analyze makes queries slow... Message-ID: <20030812095707.GA22395@melina.szuwary.org> References: <20030811155841.483ffd93.svb@ucs.co.za> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-2 Content-Disposition: inline In-Reply-To: <20030811155841.483ffd93.svb@ucs.co.za> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/206 X-Sequence-Number: 3023 On Mon, Aug 11, 2003 at 03:58:41PM +0200, Stef wrote: > I have attached an sql statement that normally runs under 3 minutes. > That is, until I vacuum analyze the database (or just the tables in the query), > then the same query runs longer than 12 hours, and I have to kill it. Hmm, I have noticed similar problem with a query with order by ... limit clause.Although it runs only 10 times slower after analyze :) The query joins one big table (20 000 rows) with several small tables (200-4000 rows) than order by "primary key of big table" limit 20 Without this order by ... limit clause the query is 5 times faster after analyze. Looking into explain analyze outputs: 1. Before vacuum analyze a planer chooses nested loop, the deepest is: -> Nested Loop (cost=0.00..116866.54 rows=19286 width=96) (actual time=0.14..1.39 rows=21 loops=1) -> Index Scan Backward using big_table_pkey on big_table k (cost=0.00..1461.15 rows=19286 width=52) (actual time=0.07..0.47 rows=21 loops=1) -> Index Scan using 4000rows_table_pkey on 4000rows_table zs (cost=0.00..5.97 rows=1 width=44) (actual time=0.02..0.02 rows=0 loops=21) 2. After analyze uses hashjoins When I remove this order by limit clause the query after analyze takes the same time and the query before analyze is much more slower. I won't blame the planer. How he could learn that he should first take those 20 rows and than perform joins? There is a where clause with complex exists(subquery) condition regarding one of big_table fields, but removing this condition does not change the query plan. Pure joining without any additional conditions and only primary key of big table in select clause runs 4 times slower then whole query before vacuuum analyze :) Does in all the planer take in the consideration the limit clause? Probably I'm missing something. I don't know much about the planer. Finaly I have redesigned the query. Regards, Jacek