Re: Planner making bad choice in alternative subplan decision

On Tue, 29 Sep 2020 at 12:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > Any opinions on this?
>
> This patch scares the heck out of me. It's a pretty much unprincipled
> change in a fundamental selectivity estimator, which is going to affect
> all sorts of queries not only the particular case you have in mind.
> There's no reason to think that the outcome will be positive for other
> cases, either.
>
> The idea I'd had was to adjust make_subplan and cost_subplan to estimate
> EXIST cases on the basis of either 50% retrieval (same as ANY/ALL) or
> maybe full retrieval if you want to be pessimistic. I've not had time
> to try it out though.

Here's a patch which does that. There are no regression test failures.

I'm trying to anticipate areas that this could cause a regression. I
think generally a hashed subplan should win in about all cases where
we lookup all of the items in the table. The places where it could
cause regression are, where we have to load way more into the hash
table than we're going to lookup. Assuming roughly the same costs for
the subplan for hashing and the non-hashed subplan, then the crossover
point will be about 2 lookups (2 x 50%). I do wonder if 50% is a bit
too big a number. We did ask the planner for a fast startup plan, so
there is perhaps some reasoning to make the cost multiplier somewhere
between 50% and 1 / nrows. I'm just not sure what that should be.
There's very little to go on cost-wise, or even heuristically. So
consider the patch still a discussion level patch.

David