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 8DC718BA315 for ; Thu, 17 Feb 2005 23:28:12 +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 80593-03 for ; Thu, 17 Feb 2005 23:28:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0341B8BA30E for ; Thu, 17 Feb 2005 23:28:08 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1HNS5X0011927; Thu, 17 Feb 2005 18:28:06 -0500 (EST) To: werner fraga Cc: John Arbash Meinel , pgsql-performance@postgresql.org Subject: Re: VACUUM ANALYZE slows down query In-reply-to: <42151CED.7050309@arbash-meinel.com> References: <20050217222428.84111.qmail@web41408.mail.yahoo.com> <42151CED.7050309@arbash-meinel.com> Comments: In-reply-to John Arbash Meinel message dated "Thu, 17 Feb 2005 16:38:37 -0600" Date: Thu, 17 Feb 2005 18:28:05 -0500 Message-ID: <11926.1108682885@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/253 X-Sequence-Number: 10582 John Arbash Meinel writes: > So the big issue is why does the planner think that a nested loop is > going to be more expensive than a merge join. That I don't really know. Well, with the increased (and much more accurate) rowcount estimate, the estimated cost of the nestloop naturally went up a lot: it's proportional to the number of rows involved. It appears that the estimated cost of the mergejoin actually went *down* quite a bit (else it'd have been selected the first time too). That seems odd to me. AFAIR the only reason that would happen is that given stats about the distributions of the two join keys, the planner can recognize that one side of the merge may not need to be run to completion --- for example if one column ranges from 1..100 and the other only from 1..40, you never need to look at the values 41..100 in the first table. You can see in the explain output that this is indeed happening to some extent: -> Sort (cost=3200.13..3267.24 rows=26844 width=4) (actual time=352.324..453.352 rows=24746 loops=1) Sort Key: tool.id -> Seq Scan on tool (cost=0.00..1225.44 rows=26844 width=4) (actual time=0.024..126.826 rows=26844 loops=1) Only 24746 of the 26844 tool rows ever got read from the sort node (and even that is probably overstating matters; if there are duplicate toolid values in the lefthand input, as seems likely, then the same rows will be pulled from the sort node multiple times). However, when both sides of the merge are being explicitly sorted, as is happening here, then not running one side to completion does not save you much at all (since you had to do the sort anyway). The early-out trick only really wins when you can quit early on a more incremental subplan, such as an indexscan. So I'm pretty surprised that the planner made this pair of choices. The estimated cost of the mergejoin shouldn't have changed much with the addition of statistics, and so ISTM it should have been picked the first time too. Walt, is there anything proprietary about the contents of these tables? If you'd be willing to send me a dump off-list, I'd like to dig through what the planner is doing here. There may be a bug somewhere in the cost estimation code. regards, tom lane