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

On Tue, 13 May 2025 at 03:19, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
> On Mon, May 12, 2025 at 6:01 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> This is just an artifact of the fact that runtime pruning is not factored into the costs. Note the cost of the generic plan. 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 but performs 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 magnitude worse than the cost of real time partition pruning of a generic plan). I started thinking of something like cost_planner GUC to help with similar issues (where planning cost calculated as cost_planned*(some heuristic function with amount 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