On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <lepihov@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.
