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
Date: Thu, 06 May 2021 08:11:54 -0400
Message-ID: <4148397.1620303114@sss.pgh.pa.us> (raw)
In-Reply-To: <CAApHDvqvQ2mQZ36Gkz+qbC8e98ShVscF0aiB6TXHN=5uiXWOQg@mail.gmail.com>
References: <16993-1298e8dc9c41ab97@postgresql.org>
<4035793.1620236861@sss.pgh.pa.us>
<CAApHDvp89MJGczOeNNypYFJZ2WCRdR7LN=amDzCpeSZk=N1Qtg@mail.gmail.com>
<4113744.1620271715@sss.pgh.pa.us>
<CAApHDvqvQ2mQZ36Gkz+qbC8e98ShVscF0aiB6TXHN=5uiXWOQg@mail.gmail.com>
David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 6 May 2021 at 15:28, Tom Lane <tgl@sss.pgh.pa.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, 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: <4148397.1620303114@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox