MIME-Version: 1.0
From: Justin Blank <justin.blank@gmail.com>
Date: Sat, 15 Mar 2025 17:41:53 +0000
Message-ID: <CADJ=z8vD-HsM_qrNRLn9Lne-EkZtg=xmHjJ3RqksOoakzcuOyg@mail.gmail.com>
Subject: The logic behind comparing generic vs. custom plan costs
To: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CADJ%3Dz8vD-HsM_qrNRLn9Lne-EkZtg%3DxmHjJ3RqksOoakzcuOyg%40mail.gmail.com>
Precedence: bulk

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