On Mon, May 12, 2025 at 6:01 PM David Rowley <dgrowleyml@xxxxxxxxx> wrote:
On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <lepihov@xxxxxxxxx> 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
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
