| From: | Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> |
|---|---|
| To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
| Cc: | Andrei Lepikhov <lepihov(at)gmail(dot)com>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) |
| Date: | 2025-05-12 15:18:43 |
| Message-ID: | CAK-MWwTL=_NjJc9v241VQdavOC4deWgPjoqDxLbvm=wUFKq=ug@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Mon, May 12, 2025 at 6:01 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <lepihov(at)gmail(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2025-05-12 15:33:02 | Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) |
| Previous Message | David Rowley | 2025-05-12 15:01:03 | Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) |