From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: a.schnabl@synedra.com,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16993: Query Planner does not use index for EXISTS-query on
 a large table with all equal foreign key values
In-reply-to: 
 <CAApHDvp89MJGczOeNNypYFJZ2WCRdR7LN=amDzCpeSZk=N1Qtg@mail.gmail.com>
References: <16993-1298e8dc9c41ab97@postgresql.org>
 <4035793.1620236861@sss.pgh.pa.us>
 <CAApHDvp89MJGczOeNNypYFJZ2WCRdR7LN=amDzCpeSZk=N1Qtg@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 06 May 2021 14:13:04 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4113741.1620271715.1@sss.pgh.pa.us>
Date: Wed, 05 May 2021 23:28:35 -0400
Message-ID: <4113744.1620271715@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4113744.1620271715%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 6 May 2021 at 05:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> A narrower fix would be to hack var_eq_non_const so that it doesn't
>> assume that the comparison value must be one of the entries in the
>> column.  But it seems like whatever change we made in that line would
>> be a very unprincipled hack, because what are you going to assume
>> instead?

> Yeah, this is the same problem as I was mentioning in [1]
> My solution was to go for that "unprincipled hack" in var_eq_non_const().
> I'm not sure I 100% agree that it's a complete hack, you don't really
> have to change the n_distinct by much to get the good plan.  It's a
> massive risk to assume that the given value will *always* be the
> single distinct value that's indexed.

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.

In any case, I think the real issue here is that we know that *in use*,
the indexscan will fetch either zero or one row, and be pretty quick
either way.  The problem is that that knowledge is being applied
at the join level, which is too late to save the path from losing.
How could we move that knowledge down to the scan path costs?

			regards, tom lane