Thread: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)

Maxim Boguk <maxim.boguk@gmail.com> writes:
> And the problem is that the cost of a custom plan ignores the cost of
> planning itself (which is like 2x orders of magnitude worse than the cost
> of real time partition pruning of a generic plan).

False.  The estimate is evidently pretty wrong, but it's not that
there is no consideration at all.  See around line 1370 in
src/backend/utils/cache/plancache.c.

            regards, tom lane

Maxim Boguk <maxim.boguk@gmail.com> writes:
> Reading the code - probably the lowest hanging fruit is to make
> 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> future versions.

I'm wondering whether we should try to make the planner not expend
the effort in the first place, but leave partition pruning to the
executor, at least in cases where it can determine that that will be
possible.

            regards, tom lane

On Tue, 13 May 2025 at 03:19, Maxim Boguk <maxim.boguk@gmail.com> wrote:
> On Mon, May 12, 2025 at 6:01 PM David Rowley <dgrowleyml@gmail.com> wrote:
>> This is just an artifact of the fact that runtime pruning is not factored into the costs. Note the cost of the
genericplan. The plan_cache_mode GUC is about the only way to overrule the choice to use the custom plan. 
>
> Situation quite the opposite - I need to force a generic plan because it has the same execution time as a custom plan
butperforms 20-50x faster (because in custom plan case - 95-98% time spent in planning not in execution). 

You misunderstood. The choice the planner (or choose_custom_plan) made
to use the custom plan can be overridden with SET plan_cache_mode =
force_generic_plan;, which seems to be what performs better for you,
per your example EXPLAIN ANALYZE outputs.

> And the problem is that the cost of a custom plan ignores the cost of planning itself (which is like 2x orders of
magnitudeworse than the cost of real time partition pruning of a generic plan). I started thinking of something like
cost_plannerGUC to help with similar issues (where planning cost calculated as cost_planned*(some heuristic function
withamount involved in query tables). 
>
> In my case the high cost of planning itself should force the database to use generic plan.

Certainly the cost estimate for planning there is quite crude. I doubt
you'll find anyone arguing that it's not. It is however designed to be
low-overhead.  The estimated planning cost isn't the issue here. It's
(as I mentioned) related to no cost consideration being given to
run-time pruning.  We could certainly adjust things so that is
accounted for, and we (I think Robert and I) have talked about it in
the past.  The problem is that doing that is a wild stab in the dark,
especially so for your range partitioned case where the amount of
actual partitions pruned during executor startup could range from 0 to
all of them.  Unfortunately when we tag those costs onto the plan,
we've no idea what the parameter values are going to be when the plan
is executed. I think Robert suggested multiplying the Append cost by
DEFAULT_INEQ_SEL for this bounded range type pruning. Whether that
will help you or not depends on how many partitions you have and how
evenly populated they are.

In order words, it's a tricky problem with no one-size-fits-all solution.

David