Re: Bug #4284

Tom Lane Wrote:
> "David Rowley" <dgrowley(at)gmail(dot)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.