Re: BUG #16324: bad cost estimates for generic query plans

On 3/27/20, 12:36 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
It's not "ignoring" the cost. What it is doing, since this is an EXISTS
subplan, is assuming that it will fetch the first tuple and stop, the same
as if there'd been a LIMIT 1 in the subquery. Since the estimated number
of rows is (wrongly) very high, that results in a low estimated cost to
obtain the EXISTS result, specifically 6796877.67 / 14532272 or about
0.47. Then in reality there are *no* tuples in the result, so that the
seqscan has to run to completion to find that out. Ooops.

So in general this is just an instance of the well-known difficulty of
estimating costs with small LIMIT accurately. On the other hand, since
we know the context is EXISTS, maybe we could do better? There's an
argument that the user wouldn't be bothering to test EXISTS if there
weren't a chance of a false result, hence we ought to assume that the
subquery might need to run to completion; which would lead to taking the
cost as being the full run cost not the estimated-time-to-first-tuple.
On the other hand that seems like it would discourage use of fast-start
plans for this purpose, which is probably a net loss. On the third hand,
it looks like we have already settled on the subplan's plan by this
point so maybe that objection is bogus. If you want to experiment you
could try changing this bit in cost_subplan:

if (subplan->subLinkType == EXISTS_SUBLINK)
{
/* we only need to fetch 1 tuple; clamp to avoid zero divide */
sp_cost.per_tuple += plan_run_cost / clamp_row_est(plan->plan_rows);
}

Thanks for looking at this. I will experiment with that and report back.

As you predicted, changing the query to
SELECT 1 FROM audit_event WHERE id > $1 AND event_name IN ($2,$3,$4,$5,$6,$7,$8,$9,$10) limit 1
results in the same behavior.

-- todd

The comment above that suggests that this logic needs to match
make_subplan, but I'm thinking we would want to intentionally make them
not match, since the other code is what drives picking a fast-start plan
for the subplan.

You could make an argument for charging full run cost, or maybe just
half of that as we do for ALL/ANY cases, depending on whether you
think we should be taking worst-case estimates or not.

regards, tom lane