Re: disfavoring unparameterized nested loops - Mailing list pgsql-hackers

From Peter Geoghegan
Subject Re: disfavoring unparameterized nested loops
Date
Msg-id CAH2-Wzn0Zy=bV1V8XdHn0gObMvgWoo2vuEFvkJ7Noo38_stFOw@mail.gmail.com
Whole thread Raw
In response to Re: disfavoring unparameterized nested loops  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: disfavoring unparameterized nested loops
List pgsql-hackers
On Tue, Jun 15, 2021 at 5:00 PM David Rowley <dgrowleyml@gmail.com> wrote:
> Most of the time when I see that happen it's down to either the
> selectivity of some correlated base-quals being multiplied down to a
> number low enough that we clamp the estimate to be 1 row.   The other
> case is similar, but with join quals.
>
> It seems to me that each time we multiply 2 selectivities together
> that the risk of the resulting selectivity being out increases.  The
> risk is likely lower when we have some extended statistics which
> allows us to do fewer selectivity multiplications.

It seems important to distinguish between risk and uncertainty --
they're rather different things. The short version is that you can
model risk but you cannot model uncertainty. This seems like a problem
of uncertainty to me.

The example from the paper that Robert cited isn't interesting to me
because it hints at a way of managing the uncertainty, exactly. It's
interesting because it seems to emphasize the user's exposure to the
problem, which is what really matters. Even if it was extremely
unlikely that the user would have a problem, the downside of being
wrong is still absurdly high, and the upside of being right is low
(possibly even indistinguishable from zero). It's just not worth
thinking about. Besides, we all know that selectivity estimates are
very often quite wrong without the user ever noticing. It's amazing
that database optimizers work as well as they do, really.

-- 
Peter Geoghegan



pgsql-hackers by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: Duplicate history file?
Next
From: "houzj.fnst@fujitsu.com"
Date:
Subject: RE: [bug?] Missed parallel safety checks, and wrong parallel safety