Re: Bogus nestloop join estimate, ignores WHERE clause

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> On Wed, Aug 31, 2011 at 23:59, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Could we see the pg_stats rows for the two join columns?

> Sure, but I don't want to send this out to the public list since
> [ it's private data ]

Thanks for the data. I set up a comparable test case and duplicated
your problem. It looks like it is a variant of the same brain fade
being discussed over in pgsql-performance,
http://archives.postgresql.org/pgsql-performance/2011-08/msg00327.php

In your case, we are running through the branch of eqjoinsel_semi
that does have MCVs to play with, and that code path is effectively
not taking any account at all of restrictions applied to the inner
relation. We need to have it clamp nd2 (and not nd1) along the same
lines as should be happening in the no-MCV-list code path. This is
exactly the case I was thinking needed to be covered when I was
responding to Mark, and now I've got an example to prove it.

In this particular case, the estimate is probably still not going to be
that good, because you have so many empty-string keys that that one
value dominates the result. The only way for the planner to get a real
quality estimate would be for it to know whether or not the specific
value of client_id mapped to an empty-string id_code, which would
require cross-column stats that we haven't got. Things would get better
if you were willing to replace the empty strings with nulls, which the
planner would know couldn't match. But I'm not sure if that is the
semantics you need. In any case, the eqjoinsel_semi logic is broken;
will fix.

regards, tom lane