Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 45CC42E02D7 for ; Tue, 29 Jan 2008 18:21:14 -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 66521-03 for ; Tue, 29 Jan 2008 18:21:03 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 1B3AC2E2D8F for ; Tue, 29 Jan 2008 17:59:46 -0400 (AST) 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 m0TLxiT2026417; Tue, 29 Jan 2008 16:59:44 -0500 (EST) To: "Dmitry Potapov" cc: "Guillaume Smet" , pgsql-performance@postgresql.org Subject: Re: planner chooses unoptimal plan on joins with complex key In-reply-to: <878c83960801290725h6b5346d2h896f45e984da2dcd@mail.gmail.com> References: <878c83960801230548i6bee9353m19bcee7335220c9b@mail.gmail.com> <1d4e0c10801230558i1e56a9dcgaa9e6c2fac6d0448@mail.gmail.com> <22362.1201108676@sss.pgh.pa.us> <878c83960801240803i3d94efc5n34160559bbd91aad@mail.gmail.com> <21624.1201228021@sss.pgh.pa.us> <878c83960801290725h6b5346d2h896f45e984da2dcd@mail.gmail.com> Comments: In-reply-to "Dmitry Potapov" message dated "Tue, 29 Jan 2008 18:25:55 +0300" Date: Tue, 29 Jan 2008 16:59:44 -0500 Message-ID: <26416.1201643984@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/300 X-Sequence-Number: 28539 "Dmitry Potapov" writes: > 2008/1/25, Tom Lane : >> It's hard to tell whether the planner is just being overoptimistic >> about the results of ANDing the three join conditions, or if one or >> more of the basic condition selectivities were misestimated. > I wonder if it will ever finish the work :( So, for now, the only > thing I can show is: > =# explain select 1 from t1, t2 where t1.m1=t2.m1 ; > QUERY PLAN > -------------------------------------------------------------------------------- > Nested Loop (cost=0.00..162742993234.25 rows=57462242912003 width=0) > -> Seq Scan on t2 (cost=0.00..690784.54 rows=30820054 width=4) > -> Index Scan using i_t1_m1 on t1 (cost=0.00..3080.74 rows=175973 width=4) > Index Cond: (t1.m1 = t2.m1) > (4 rows) So this is estimating the join selectivity for m1 alone as regression=# select 57462242912003 / (30820054.0 * 21292688); ?column? ------------------------ 0.08756260792962293676 (1 row) which would suggest that there are only 11 or so distinct values for m1; is that about right? There's something a bit funny here --- looking at the plan components, the output row estimate should be about 30820054 * 175973, but it's ten times that. I'm not sure if this is just a presentation artifact or if it points to a real bug. Could you show us the pg_stats rows for t1.m1 and t2.m1? The m2 estimate seems nearly dead on, and the m3 estimate is within 10% or so which is certainly close enough. Barring the possibility that there's something wrong underneath that m1 estimate, the products of the selectivities do indeed work out to give us about 1 row out of the join with all three conditions. So my conclusion is that these three values are not independent, but are highly correlated (in fact, it seems that m2 alone provides most of the join selectivity). Do you actually need to compare all three to make the join, or is one field perhaps dependent on the other two? Can you refactor the data to make it so? regards, tom lane