David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 29 Sep 2020 at 12:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
(1) IMO, make_subplan should be adjusted in the same way; or at least,
if it isn't, the comments therein need to be changed to explain why
it's okay for it to be out of sync with cost_subplan.
(2) Does this fix the plan choice problem you started with?
> 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.
One way to get some data is to see what the threshold multiplier is
to change the plan choice in an EXISTS that is currently planned
wrongly.
regards, tom lane