Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0A9829FB358 for ; Wed, 6 Jun 2007 21:14:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40270-07 for ; Wed, 6 Jun 2007 21:14:33 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mtao04.charter.net (mtao04.charter.net [209.225.8.178]) by postgresql.org (Postfix) with ESMTP id 967CA9FB33D for ; Wed, 6 Jun 2007 21:14:32 -0300 (ADT) Received: from aa04.charter.net ([10.20.200.156]) by mtao04.charter.net (InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP id <20070607001426.IELL1527.mtao04.charter.net@aa04.charter.net>; Wed, 6 Jun 2007 20:14:26 -0400 Received: from [10.0.1.3] (really [66.188.164.45]) by aa04.charter.net with ESMTP id <20070607001426.SLHR26958.aa04.charter.net@[10.0.1.3]>; Wed, 6 Jun 2007 20:14:26 -0400 In-Reply-To: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com> References: <371D1936-E96B-4865-AEEF-C82AC41C2982@hotdogrecords.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <7229E474-9550-4419-A665-429D3881A1CB@seespotcode.net> Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: Weird 8.2.4 performance Date: Wed, 6 Jun 2007 19:14:17 -0500 To: Kurt Overberg X-Mailer: Apple Mail (2.752.2) X-Chzlrs: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/116 X-Sequence-Number: 25149 On Jun 6, 2007, at 18:27 , Kurt Overberg wrote: > select id from taskinstance where taskid in (select id from task > where campaignid = 75); > > Now, I know this could (and should) be rewritten to not use the > WHERE x IN () style, but this is actually a sub-query to a larger > query. Granted, it won't explain why this particular query is slower in 8.2, but it shouldn't be to hard to drop in something like SELECT id FROM taskinstance NATURAL JOIN ( SELECT id AS taskid, campaignid FROM tasks) t WHERE campaignid = 75 AIUI, the planner can sometimes rewrite IN as a join, but I don't know whether or not that's is happening in this case. I'm guessing not as I see nested loops in the plans. (I'm a novice at reading plans, so take this with at least a teaspoon of salt. :) ) > if I run the query again, it gets successively faster (50,000ms- > >6000ms->27ms). Is this normal? If I change the campaignid from > 75 to another number, it jumps back to 50,000ms, which leads me to > believe that postgresql is somehow caching the results of the query > and not figuring out a better way to run the query. As the query is repeated, the associated rows are probably already in memory, leading to the speedups you're seeing. > -- 8.2 > Recheck Cond: (taskinstance.taskid = task.id) > -> Bitmap Index Scan on taskid_taskinstance_key > (cost=0.00..20.57 rows=556 width=0) (actual time=54.709..54.709 > rows=196 loops=9) > Index Cond: (taskinstance.taskid = task.id) > -- 8.0 > -> Index Scan using taskid_taskinstance_key on taskinstance > (cost=0.00..2152.28 rows=563 width=8) (actual time=0.012..0.832 > rows=145 loops=11) > Index Cond: (taskinstance.taskid = "outer".id) I see that the row estimates in both of the query plans are off a little. Perhaps increasing the statistics would help? Also, you can see that 8.2 is using bitmap scans, which aren't available in 8.0. Perhaps try setting enable_bitmapscan off and running the query again to see if there's a performance difference. > The weird thing is that on 8.2, I don't see any sequential scans > taking place, it seems to be properly using the indexes. As an aside, whether the planner decides to use a sequential scan or an index has more to do with the particular query: indexes are not a guaranteed performance win. Hope this helps a bit. Michael Glaesemann grzm seespotcode net