Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 94EA72E3349 for ; Thu, 6 Dec 2007 14:34:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 48596-01-2 for ; Thu, 6 Dec 2007 14:34:48 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 6DBD72E334E for ; Thu, 6 Dec 2007 14:34:50 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lB6IYlP6018105; Thu, 6 Dec 2007 13:34:47 -0500 (EST) To: Matthew cc: pgsql-performance@postgresql.org Subject: Re: TB-sized databases In-reply-to: References: <4544e0330711260904g45d893a8ub1731e245aa8fc84@mail.gmail.com> <13851.1196351131@sss.pgh.pa.us> <1196352866.4246.1304.camel@ebony.site> <200712051507.22745.xzilla@users.sourceforge.net> <1196933896.4255.340.camel@ebony.site> <20071206154212.GP5294@mathom.us> <15753.1196957598@sss.pgh.pa.us> <17456.1196963738@sss.pgh.pa.us> Comments: In-reply-to Matthew message dated "Thu, 06 Dec 2007 18:03:09 +0000" Date: Thu, 06 Dec 2007 13:34:47 -0500 Message-ID: <18104.1196966087@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/85 X-Sequence-Number: 27901 Matthew writes: > On Thu, 6 Dec 2007, Tom Lane wrote: >> Hmm. IIRC, there are smarts in there about whether a mergejoin can >> terminate early because of disparate ranges of the two join variables. > Very cool. Would that be a planner cost estimate fix (so it avoids the > merge join), or a query execution fix (so it does the merge join on the > table subset)? Cost estimate fix. Basically what I'm thinking is that the startup cost attributed to a mergejoin ought to account for any rows that have to be skipped over before we reach the first join pair. In general this is hard to estimate, but for mergejoin it can be estimated using the same type of logic we already use at the other end. After looking at the code a bit, I'm realizing that there's actually a bug in there as of 8.3: mergejoinscansel() is expected to be able to derive numbers for either direction of scan, but if it's asked to compute numbers for a DESC-order scan, it looks for a pg_stats entry sorted with '>', which isn't gonna be there. It needs to know to look for an '<' histogram and switch the min/max. So the lack of symmetry here is causing an actual bug in logic that already exists. That makes the case for fixing this now a bit stronger ... regards, tom lane