On Mon, May 12, 2025 at 6:01 PM David Rowley <dgrowle...@gmail.com> wrote:

> On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <lepi...@gmail.com> wrote:
>
>> Thanks for this puzzle!
>> I suppose, in case generic planning is much faster than custom one,
>> there are two candidates exist:
>> 1. Touching the index during planning causes too much overhead - see
>> get_actual_variable_range
>> 2. You have a massive default_statistics_target for a table involved.
>>
>
> 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).

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.

-- 
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

Reply via email to