Tom Lane Wrote: > "David Rowley" <dgrow...@gmail.com> writes: > > My report contained a full re-creation script to reproduce the problem > and > > tonight I'm having the same problem with CVS Head. To my untrained eye > it > > looks like the planner is not properly pushing down the row count. > > It looks more like a multicolumn selectivity issue to me. The planner > is supposing that the join condition > > ON t1.productiondate = t2.productiondate AND t1.lineid = t2.lineid > AND t1.partcode = t2.partcode > > is going to eliminate some fair-size fraction of t1 rows, whereas in > fact the construction of t2 is such that it won't eliminate any of them. > This is less obviously true for the join to t4, but I imagine from the > rowcounts that it's also true there. So you get an unreasonably small > rowcount for whichever join gets done first, and then the nestloop plan > looks like a good idea for the second join.
I thought about this after sending my reply to this last night. I remembered when I created my test case I had to add the other tables to get the nest loop behaviour. I'm not sure your guess about the multicolumn selectivity issue is correct. I re-tested with the following query. EXPLAIN ANALYZE SELECT t1.productiondate, t1.partcode, t1.batchcode, t1.bestbefore FROM batches t1 LEFT OUTER JOIN (SELECT productiondate, lineid, partcode, SUM(quantity) AS quantity FROM production GROUP BY productiondate,partcode,lineid ) t4 ON t1.productiondate = t4.productiondate AND t1.lineid = t4.lineid AND t1.partcode = t4.partcode; The top line of the explain analyze is: Merge Left Join (cost=464.51..510.72 rows=4200 width=21) (actual time=107.872..157.882 rows=4200 loops=1) Perfect row estimate! It seems to be something to do with having those other tables in there. David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers