The logic behind comparing generic vs. custom plan costs

From: Justin Blank <justin(dot)blank(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: The logic behind comparing generic vs. custom plan costs
Date: 2025-03-15 17:41:53
Message-ID: CADJ=z8vD-HsM_qrNRLn9Lne-EkZtg=xmHjJ3RqksOoakzcuOyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've been looking into the way that postgres decides whether to use a
custom or generic plan. I believe I understand how, but I have
confused myself about the why.

In plancache.c, the code always executes a custom plan for the first
five iterations, and afterwards, it compares the average cost of the
custom plan (including the planning cost) to the cost of a generic
plan (not including planning), and choose the option with the lower
cost.

My idea had been that even if the custom plans average higher cost
than the generic plan, it is still worth considering custom plans. If
1 time in 5, the custom plan is 10% of the cost of the generic plan,
it doesn't matter if the average custom plan is worse, you should at
least determine the cost of the custom plan before deciding whether to
use it or the generic plan. Even if 80% of the time, you don't use the
custom plan, the payoff in the good case may be worth it.

However, after starting to implement that idea, I became confused, and
the underlying logic of comparing custom and generic plans stopped
making sense to me.

I can think of two different reasons the cost estimates for the
generic plan and custom plan can differ:

1. The two plans substantively differ: they use a different join
order, different join strategy, or different indexes.
2. They do not fundamentally differ, but the custom plan makes
different estimates of the selectivity of predicates because it
knows their precise values.

For the sake of deciding between custom and generic plans, it seems
like only the first type of difference matters.

So my first question is whether there's any value in comparing the
cost estimates for custom plans that have the same access plan as the
generic plan.

Continuing to cases where the plans differ meaningfully, I'm not sure
that comparison makes sense either.

If the custom plan beats the generic plan, it seems reasonable to take
that as a real improvement.

However, if the custom plan has a higher cost than the generic plan,
this may or may not count against the custom plan. The planner used
the bind parameters of the specific query. So it may be that the
custom plan really is worse (the planner has made a mistake) but it
may also be that it considered the access plan of the generic plan,
and it was worse for those particular bind parameters. In this case,
the custom plan appears worse than the generic plan, but would ideally
be preferred.

Does my reasoning make sense? Or have I misunderstood something about
this process and how to reason about these cost estimates? I'm very
unsure, and worrying I've completely misunderstood, as if I'm right,
the logic in plancache.c doesn't make much sense.

Justin Blank

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2025-03-15 18:06:11 Re: The logic behind comparing generic vs. custom plan costs
Previous Message Fujii Masao 2025-03-15 13:59:59 Re: Disabling vacuum truncate for autovacuum