From: Arne Roland <A.Roland@index.de>
To: David Rowley <dgrowleyml@gmail.com>, "pg@bowt.ie" <pg@bowt.ie>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Pg Bugs <pgsql-bugs@postgresql.org>, "James
 Coleman" <jtc331@gmail.com>
Subject: Re: Less selective index chosen unexpectedly
Thread-Topic: Less selective index chosen unexpectedly
Thread-Index: AQHXTCWtfiWEMvopoUaGTnzv2xVJ/qrpoU0AgAEihgCAAKsXgIAAt2tA
Date: Thu, 20 May 2021 12:04:07 +0000
Message-ID: <6172fc42175b46a88eb3c9e08c253f1a@index.de>
References: 
 <CAAaqYe-a_CP3sjtT9P1WEVPeaZfWnhW8uGA-NvQg1dcwToU4FQ@mail.gmail.com>,<CAApHDvp8TYccf9mN_7ZAogh2jZYeNA-8UPCQZLBhuef1z=rhkg@mail.gmail.com>
In-Reply-To: 
 <CAApHDvp8TYccf9mN_7ZAogh2jZYeNA-8UPCQZLBhuef1z=rhkg@mail.gmail.com>
Accept-Language: en-US, de-DE
Content-Language: en-US
Content-Type: multipart/alternative;
	boundary="_000_6172fc42175b46a88eb3c9e08c253f1aindexde_"
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/6172fc42175b46a88eb3c9e08c253f1a%40index.de>
Precedence: bulk

--_000_6172fc42175b46a88eb3c9e08c253f1aindexde_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

David Rowley <dgrowleyml@gmail.com> wrote:
> The planner does often take plenty of other risks. The situation you
> describe here is just one of many.  Another example is that if we do a
> join on say 3 or more conditions that those conditions could be
> correlated. We currently always assume they're independent and just
> multiply individual selectivities. We could end up thinking that the
> join will produce far fewer rows than it actually will.  Problems can
> arise in subsequent joins. We may end up doing a subsequent join using
> a nested loop thinking that only 1 row will be produced from the first
> join.  That is likely to turn bad if the innermost join instead
> produces millions of rows.

The n_distinct case is another especially hard one. I go around and set tho=
se values by hand all the time. Or how about the gazillion of fallback case=
s where the planner gives up estimating any selectivity and just pulls up a=
nother constant?
We are still living in a world where even the simplest function could cause=
 one of these "I don't know the selectivity...let's say X0%"-cases.
And I am still surprised how well these broad estimates tend to work.
While we progressed a lot on that front, and I am grateful for all these fo=
lks who worked on the planner, the general problem is way to hard.
Getting to a point where I as a human cannot easily find a case where I can=
 say "Ugh, that cliff is dangerous, don't go ther..." seems impossible to m=
e.

The underlying problem here is more severe. We make systematic errors and t=
he more complex the query gets the more likely it is that we will find a cl=
iff to jump.
It's not just that we stumble upon one, no we actively search for it. Think=
ing about predicate pushing: That stuff can alter the estimated rows of a s=
ubquery.