Re: BUG #16993: Query Planner does not use index for EXISTS-query on a large table with all equal foreign key values

David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> On Thu, 6 May 2021 at 15:28, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Yeah, I agree that it doesn't seem great to let var_eq_non_const return
>> 1.0 when it has no idea what the comparison value is. However, that
>> doesn't translate to having much confidence in any other value either.
>> The actual number-of-rows-fetched, given that we know the column
>> contents are all the same value, is either zero or the whole table.
>> It's hard to do much with that; and biasing it towards believing the
>> optimistic value over the pessimistic value seems dangerous.

> Maybe we should be also looking at var_eq_non_const()'s 'other' field
> and seeing if that's a Var with stats and dividing the results by the
> number of distinct values in that, or at least doing something closer
> to what eqjoinsel_inner() does when it's prompted with the same
> problem.

Yeah, as I mentioned upthread, it's tempting to think about applying
join selectivity rules when dealing with a parameterized path's
restriction-qual-that's-really-a-join-qual. I'm not sure if that
selectivity is expressed the right way as things stand (i.e.,
fraction of the Cartesian product size isn't the same thing as
fraction of the inner relation). In any case that seems like a bit
of a research problem.

regards, tom lane