Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8E0D96849EA for ; Wed, 19 May 2010 13:53:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96421-08 for ; Wed, 19 May 2010 16:53:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 10C0C6849EC for ; Wed, 19 May 2010 13:53:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o4JGr3af021498; Wed, 19 May 2010 12:53:03 -0400 (EDT) To: Matthew Wakeling cc: Scott Marlowe , pgsql-performance@postgresql.org Subject: Re: merge join killing performance In-reply-to: References: Comments: In-reply-to Matthew Wakeling message dated "Tue, 18 May 2010 23:00:18 -0400" Date: Wed, 19 May 2010 12:53:03 -0400 Message-ID: <21497.1274287983@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/99 X-Sequence-Number: 38683 Matthew Wakeling writes: > On Tue, 18 May 2010, Scott Marlowe wrote: >> Aggregate (cost=902.41..902.42 rows=1 width=4) >> -> Merge Join (cost=869.97..902.40 rows=1 width=4) >> Merge Cond: (f.eid = ev.eid) >> -> Index Scan using files_eid_idx on files f >> (cost=0.00..157830.39 rows=3769434 width=8) > Okay, that's weird. How is the cost of the merge join only 902, when the > cost of one of the branches 157830, when there is no LIMIT? It's apparently estimating (wrongly) that the merge join won't have to scan very much of "files" before it can stop because it finds an eid value larger than any eid in the other table. So the issue here is an inexact stats value for the max eid. regards, tom lane