> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 10 February 2009 22:30
> To: David Rowley
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Bug #4284
>
> "David Rowley" <dgrowley@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.
At work I've been (unwillingly) working with MS SQL Server 2008. I notice
that when I request a query plan it's an "Estimated" query plan. Perhaps
this is to get around problems with bad row estimates. In theory at least it
should be possible to revert back to another plan after the inner nested
queries have processed and before the join takes place... I know that's a
major change, I'm just theorising.
Apart from that, do you think that this could only be fixed with stats that
span multiple columns? I know this was talked about not so long ago.
David.