From: Arne Roland <A.Roland@index.de>
To: David Rowley <dgrowleyml@gmail.com>
To: pg@bowt.ie <pg@bowt.ie>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Bugs <pgsql-bugs@postgresql.org>
Cc: James Coleman <jtc331@gmail.com>
Subject: Re: Less selective index chosen unexpectedly
Date: Thu, 20 May 2021 12:04:07 +0000
Message-ID: <6172fc42175b46a88eb3c9e08c253f1a@index.de> (raw)
In-Reply-To: <CAApHDvp8TYccf9mN_7ZAogh2jZYeNA-8UPCQZLBhuef1z=rhkg@mail.gmail.com>
References: <CAAaqYe-a_CP3sjtT9P1WEVPeaZfWnhW8uGA-NvQg1dcwToU4FQ@mail.gmail.com>
<CAApHDvp8TYccf9mN_7ZAogh2jZYeNA-8UPCQZLBhuef1z=rhkg@mail.gmail.com>
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 those values by hand all the time. Or how about the gazillion of fallback cases where the planner gives up estimating any selectivity and just pulls up another 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 folks 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 me.
The underlying problem here is more severe. We make systematic errors and the more complex the query gets the more likely it is that we will find a cliff to jump.
It's not just that we stumble upon one, no we actively search for it. Thinking about predicate pushing: That stuff can alter the estimated rows of a subquery.
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: A.Roland@index.de, dgrowleyml@gmail.com, pg@bowt.ie, tgl@sss.pgh.pa.us, jtc331@gmail.com
Subject: Re: Less selective index chosen unexpectedly
In-Reply-To: <6172fc42175b46a88eb3c9e08c253f1a@index.de>
* 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