Re: Bogus nestloop rows estimate in 8.4.7

On Tue, May 29, 2012 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Hmm, but isn't this a case of the left hand not knowing what the right
>> hand is doing?  I mean, somehow we have enough information to estimate
>> that the index scans on b{1,2,3} are going to produce 2 rows per
>> execution, but having figured that out (correctly) we then proceed to
>> ignore it.
>
> Well, if you wanted to do that, what it would amount to is saying that
> the estimated size of the join relation is going to change depending on
> which implementation path you consider for it, which would be a mess,
> not to mention mathematically silly.

Well, I do not understand this code in depth (can you tell?) but it
seems to me that we are effectively computing the size of the inner
relation in two different ways in two different parts of the code.
Whatever logic the index-scan-path is using to estimate how many rows
are going to pop out is obviously much more accurate - in this
instance - than the join selectivity estimator. I can't help wishing
the more accurate logic were somehow being used here, without having a
very good idea what it would take to make that happen.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company