Horton
Menu

Thread

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-12T11:49:10Z

    Hi,
I found a case where plan cache all time switching to custom plans forces
query replan each call (and thus slows down the whole query for 10x or
more).
What makes the situation intriguing - that both custom and generic plans
are the same.

job_stats_master - partitioned table with 24 partitions (per month last 2
year).

Problem query:
prepare qqq(timestamp, timestamp) AS
SELECT *
FROM "job_stats_master"
WHERE
"job_stats_master"."created_at" BETWEEN $1 AND $2 AND
"job_stats_master"."job_reference" = '******' AND
"job_stats_master"."job_board_id" = 27068
ORDER BY "created_at" DESC LIMIT 1;

plan (after 6th execution):
explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp without
time zone, '2025-05-12 09:22:00.203'::timestamp without time zone);

                                               QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.14..1.29 rows=1 width=384) (actual time=0.026..0.026 rows=1
loops=1)
   ->  Append  (cost=1.14..9.10 rows=50 width=384) (actual
time=0.025..0.026 rows=1 loops=1)
         ->  Index Scan Backward using
job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_05 job_stats_master_2  (cost=0.56..3.28 rows=18
width=368) (actual time=0.025..0.025 rows=1 loops=1)
               Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
         ->  Index Scan Backward using
job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_04 job_stats_master_1  (cost=0.57..5.32 rows=32
width=394) (never executed)
               Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
 Planning Time: 0.611 ms
 Execution Time: 0.057 ms
(8 rows)

plan with set plan_cache_mode to force_generic_plan ;

explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp without
time zone, '2025-05-12 09:22:00.203'::timestamp without time zone);

                               QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=19.06..19.32 rows=1 width=407) (actual time=0.030..0.030
rows=1 loops=1)
   ->  Append  (cost=19.06..26.74 rows=29 width=407) (actual
time=0.029..0.030 rows=1 loops=1)
         Subplans Removed: 27
         ->  Index Scan Backward using
job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_05 job_stats_master_2  (cost=0.56..0.82 rows=1
width=368) (actual time=0.029..0.029 rows=1 loops=1)
               Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= $1) AND
(created_at <= $2))
         ->  Index Scan Backward using
job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_04 job_stats_master_1  (cost=0.57..0.83 rows=1
width=394) (never executed)
               Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= $1) AND
(created_at <= $2))
 Planning Time: 0.033 ms
 Execution Time: 0.086 ms

Plan "de facto" the same, performance almost the same but with custom plans
there is 20x more time spent on planning.
With over 1M RPS - it's become quite an issue even for the best available
servers.

No playing with cost parameters provides any changes in selection custom
plan over generic.
As I understand there is an issue with costing model - generic plan thinks
it will visit all 24 partitions but custom plan does prune partitions
during planning thus custom plan always wins in this case "by cost" and in
the same time huge loss in performance (but actual plans are the same in
both cases).

I suspect this situation should be quite common with queries over
partitioned tables (where planning time is usually quite a high).

Any suggestions what could be done there outside of using
force_generic_plan for a particular db user (which will kill performance in
other queries for sure)?




-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    Andrei Lepikhov <lepihov@gmail.com> — 2025-05-12T12:08:21Z

    On 5/12/25 13:49, Maxim Boguk wrote:
> I suspect this situation should be quite common with queries over 
> partitioned tables (where planning time is usually quite a high).
> 
> Any suggestions what could be done there outside of using 
> force_generic_plan for a particular db user (which will kill performance 
> in other queries for sure)?
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.

So, to clarify the problem, may you provide EXPLAIN (without analyze) 
with BUFFERS ON ?
Also, could you provide extra information on the statistics involved? 
For each column (I think created_at is the most important one), show the 
size of MCV and histogram arrays.

-- 
regards, Andrei Lepikhov

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-12T13:08:09Z

    On Mon, May 12, 2025 at 3:08 PM Andrei Lepikhov <lepihov@gmail.com> wrote:

> On 5/12/25 13:49, Maxim Boguk wrote:
> > I suspect this situation should be quite common with queries over
> > partitioned tables (where planning time is usually quite a high).
> >
> > Any suggestions what could be done there outside of using
> > force_generic_plan for a particular db user (which will kill performance
> > in other queries for sure)?
> 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.
>
> So, to clarify the problem, may you provide EXPLAIN (without analyze)
> with BUFFERS ON ?
> Also, could you provide extra information on the statistics involved?
> For each column (I think created_at is the most important one), show the
> size of MCV and histogram arrays.
>
> --
> regards, Andrei Lepikhov
>


clickcast=# explain (buffers) execute qqq('2025-04-11
09:22:00.193'::timestamp without time zone, '2025-05-12
09:22:00.203'::timestamp without time zone);

                                               QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.14..1.29 rows=1 width=385)
   ->  Append  (cost=1.14..9.10 rows=50 width=385)
         ->  Index Scan Backward using
job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_05 job_stats_master_2  (cost=0.56..3.28 rows=18
width=371)
               Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*****'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
         ->  Index Scan Backward using
job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_04 job_stats_master_1  (cost=0.57..5.32 rows=32
width=394)
               Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
 Planning:
   Buffers: shared hit=16
16 buffers - most times, sometimes 12k   Buffers: shared hit=12511 (like 5%
cases) - I have no idea why.

show default_statistics_target ;
 default_statistics_target
---------------------------
 100
No custom statistic targets on this table or partitions.

select
tablename,attname,inherited,null_frac,n_distinct,array_length(most_common_vals,1)
mcv, array_length(histogram_bounds,1) hist from pg_stats where tablename IN
('job_stats_master', 'job_stats_new_2025_04', 'job_stats_new_2025_05') and
attname in ('created_at', 'job_board_id', 'job_reference') order by
tablename, attname;
       tablename       |    attname    | inherited | null_frac  |
 n_distinct  | mcv | hist
-----------------------+---------------+-----------+------------+--------------+-----+------
 job_stats_master      | created_at    | t         |          0 |
1.066586e+06 |  15 |  101
 job_stats_master      | job_board_id  | t         | 0.52743334 |
1716 | 100 |  101
 job_stats_master      | job_reference | t         |          0 |
-0.1 |  39 |  101
 job_stats_new_2025_04 | created_at    | f         |          0 |
832508 |  39 |  101
 job_stats_new_2025_04 | job_board_id  | f         | 0.47096667 |
1096 | 100 |  101
 job_stats_new_2025_04 | job_reference | f         |          0 |
-0.1 |  93 |  101
 job_stats_new_2025_05 | created_at    | f         |          0 |
709166 |  42 |  101
 job_stats_new_2025_05 | job_board_id  | f         |     0.4703 |
1142 | 100 |  101
 job_stats_new_2025_05 | job_reference | f         |          0 |
-0.1 | 100 |  101


PS: problem not with difference between custom and generic planning time
but with prepared statements
generic plan plans only once, but custom plan plan every call (and plan
time cost 95% on total query runtime).


-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    Andrei Lepikhov <lepihov@gmail.com> — 2025-05-12T13:48:24Z

    On 5/12/25 15:08, Maxim Boguk wrote:
> PS: problem not with difference between custom and generic planning time 
> but with prepared statements
> generic plan plans only once, but custom plan plan every call (and plan 
> time cost 95% on total query runtime).
Ah, now I got it.
I'm aware of this problem from at least two sources of regular complaints.
What can you do here? Let's imagine a palliative solution:
Having pg_stat_statements data and the list of prepared statements (see 
pg_prepared_statement) and queryId enabled, there is a way to force a 
custom or generic plan in specific cases only: look up into min/max 
query execution time. If no big difference exists and planning time is 
sufficient, setting force_generic_plan for this plan makes sense. In 
another case, if the planning time is too short or the generic plan is 
unstable - switch to force_custom_plan.

It is not hard to write such a tiny extension. As I see, the only extra 
stored "C" procedure is needed to set up force-plan-type flag employing 
FetchPreparedStatement(). The rest of the code - querying 
pg_stat_statements and switching between plan types may be written in 
plpgsql.

If I'm not mistaken, it will work with all PG versions that are 
currently in support. What do you think?

-- 
regards, Andrei Lepikhov

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-12T14:04:45Z

    On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepihov@gmail.com> wrote:

> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95% on total query runtime).
> Ah, now I got it.
> I'm aware of this problem from at least two sources of regular complaints.
> What can you do here? Let's imagine a palliative solution:
> Having pg_stat_statements data and the list of prepared statements (see
> pg_prepared_statement) and queryId enabled, there is a way to force a
> custom or generic plan in specific cases only: look up into min/max
> query execution time. If no big difference exists and planning time is
> sufficient, setting force_generic_plan for this plan makes sense. In
> another case, if the planning time is too short or the generic plan is
> unstable - switch to force_custom_plan.
>
> It is not hard to write such a tiny extension. As I see, the only extra
> stored "C" procedure is needed to set up force-plan-type flag employing
> FetchPreparedStatement(). The rest of the code - querying
> pg_stat_statements and switching between plan types may be written in
> plpgsql.
>
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?


Such extension would be very useful (and in general - the solution based on
the actual execution data - seems more stable/predictable than the plan
cost based selection which is currently used by postgresql).


-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-12T14:21:09Z

    On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepihov@gmail.com> wrote:

> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95% on total query runtime).
> Ah, now I got it.
> I'm aware of this problem from at least two sources of regular complaints.
> What can you do here? Let's imagine a palliative solution:
> Having pg_stat_statements data and the list of prepared statements (see
> pg_prepared_statement) and queryId enabled, there is a way to force a
> custom or generic plan in specific cases only: look up into min/max
> query execution time. If no big difference exists and planning time is
> sufficient, setting force_generic_plan for this plan makes sense. In
> another case, if the planning time is too short or the generic plan is
> unstable - switch to force_custom_plan.
>
> It is not hard to write such a tiny extension. As I see, the only extra
> stored "C" procedure is needed to set up force-plan-type flag employing
> FetchPreparedStatement(). The rest of the code - querying
> pg_stat_statements and switching between plan types may be written in
> plpgsql.
>
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?


But a more general question - this exact issue will affect every prepared
query logic which selects only a subset of partitions.
In this case - current logic will always select custom plan over generic
plan (even in case the both plans are actually the same).
E.g. If a fast/cheap query over a partitioned table has conditions that
allow use of only a few partitions - custom plan always wins whatever
database settings is (outside of force_custom_plan hammer).
Seems there could be something done about the cost calculation of generic
plan.



-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    David Rowley <dgrowleyml@gmail.com> — 2025-05-12T15:01:03Z

    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.

David

>

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-12T15:18:43Z

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

> 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.

-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-12T15:33:02Z

    Maxim Boguk <maxim.boguk@gmail.com> writes:
> 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).

False.  The estimate is evidently pretty wrong, but it's not that
there is no consideration at all.  See around line 1370 in
src/backend/utils/cache/plancache.c.

			regards, tom lane

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

    Andrei Lepikhov <lepihov@gmail.com> — 2025-05-12T17:22:05Z

    On 12/5/2025 16:04, Maxim Boguk wrote:
> On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepihov@gmail.com 
>     It is not hard to write such a tiny extension. As I see, the only extra
>     stored "C" procedure is needed to set up force-plan-type flag employing
>     FetchPreparedStatement(). The rest of the code - querying
>     pg_stat_statements and switching between plan types may be written in
>     plpgsql.
> 
>     If I'm not mistaken, it will work with all PG versions that are
>     currently in support. What do you think?
> 
> 
> Such extension would be very useful (and in general - the solution based 
> on the actual execution data - seems more stable/predictable than the 
> plan cost based selection which is currently used by postgresql).
Okay, as far as I can see now, it costs a couple of weeks to develop. It 
would be more profitable in terms of speed and usage in older versions 
than any core patch.
What's more, if, as you predict, it will work, it may provide a 
rationale for opening the entire plan cache for extensions and allow a 
wide audience to impact the extended protocol (and query plans in stored 
procedures) in many curious ways.
As I may envision, a dummy routine providing a link to the 
saved_plan_list will spend a few lines of code. A subscription to cached 
statements may cost more time and effort but seems even more profitable.

-- 
regards, Andrei Lepikhov

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-12T17:55:07Z

    On Mon, May 12, 2025 at 6:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Maxim Boguk <maxim.boguk@gmail.com> writes:
> > 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).
>
> False.  The estimate is evidently pretty wrong, but it's not that
> there is no consideration at all.  See around line 1370 in
> src/backend/utils/cache/plancache.c.
>
>                         regards, tom lane
>

Thank you.
Reading the code - probably the lowest hanging fruit is to make
'The current multiplier of 1000 * cpu_operator_cost' configurable in the
future versions.

PS: it's always nice to see when my ad-hoc idea (about N*nrelations as cost
planner estimate) is already implemented.


-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-12T18:07:54Z

    Maxim Boguk <maxim.boguk@gmail.com> writes:
> Reading the code - probably the lowest hanging fruit is to make
> 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> future versions.

I'm wondering whether we should try to make the planner not expend
the effort in the first place, but leave partition pruning to the
executor, at least in cases where it can determine that that will be
possible.

			regards, tom lane

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

    Andrei Lepikhov <lepihov@gmail.com> — 2025-05-13T04:25:17Z

    On 5/12/25 20:07, Tom Lane wrote:
> Maxim Boguk <maxim.boguk@gmail.com> writes:
>> Reading the code - probably the lowest hanging fruit is to make
>> 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
>> future versions.
> 
> I'm wondering whether we should try to make the planner not expend
> the effort in the first place, but leave partition pruning to the
> executor, at least in cases where it can determine that that will be
> possible.
Significant planning time is a sorting out lots of scan paths, applying 
partition statistics etc. planner-stage partitioning reduces these 
efforts drastically.

-- 
regards, Andrei Lepikhov

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

    David Rowley <dgrowleyml@gmail.com> — 2025-05-13T11:20:21Z

    On Tue, 13 May 2025 at 03:19, Maxim Boguk <maxim.boguk@gmail.com> wrote:
> On Mon, May 12, 2025 at 6:01 PM David Rowley <dgrowleyml@gmail.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

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

    Maxim Boguk <maxim.boguk@gmail.com> — 2025-05-14T00:47:30Z

    On Mon, May 12, 2025 at 9:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Maxim Boguk <maxim.boguk@gmail.com> writes:
> > Reading the code - probably the lowest hanging fruit is to make
> > 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> > future versions.


Is the 100x backend memory usage per cached plan difference expected
between generic and custom plans?

There are sample memory context dump with
alter role app_server set plan_cache_mode to force_custom_plan ;
reconnect pgbouncers/wait 5 min/check sample

***=> begin;
BEGIN
****=*> select count(*), count(*) filter (where generic_plans>0) as
generic_plans, count(*) filter (where custom_plans>0) as custom_plans from
pg_prepared_statements ;
 count | generic_plans | custom_plans
-------+---------------+--------------
   177 |             3 |          174
(1 row)

***=*> select name,parent,level,count(*), pg_size_pretty(sum(total_bytes))
as bytes, sum(total_nblocks) as nblocks, pg_size_pretty(sum(free_bytes)) as
free_bytes,  sum(free_chunks) as free_chunks,
pg_size_pretty(sum(used_bytes)) as used_bytes from
pg_backend_memory_contexts group by 1,2,3 having sum(total_bytes)>128*1024
order by 3, sum(total_bytes) desc;
          name           |       parent       | level | count |  bytes  |
nblocks | free_bytes | free_chunks | used_bytes
-------------------------+--------------------+-------+-------+---------+---------+------------+-------------+------------
 TopMemoryContext        |                    |     0 |     1 | 769 kB  |
   15 | 236 kB     |         574 | 532 kB
 CacheMemoryContext      | TopMemoryContext   |     1 |     1 | 9856 kB |
  125 | 223 kB     |           2 | 9633 kB
 CachedPlanSource        | CacheMemoryContext |     2 |   264 | 5228 kB |
 1142 | 2142 kB    |         456 | 3086 kB
 index info              | CacheMemoryContext |     2 |   776 | 1612 kB |
 1483 | 575 kB     |         908 | 1037 kB
 CachedPlan              | CacheMemoryContext |     2 |    62 | 154 kB  |
  137 | 41 kB      |          31 | 113 kB
 CachedPlanQuery         | CachedPlanSource   |     3 |   264 | 4777 kB |
 1147 | 1628 kB    |         133 | 3149 kB


And with:
alter role app_server set plan_cache_mode to force_generic_plan ;
reconnect pgbouncers/wait 5 min/check sample

***=> begin;
BEGIN
***=*> select count(*), count(*) filter (where generic_plans>0) as
generic_plans, count(*) filter (where custom_plans>0) as custom_plans from
pg_prepared_statements ;
 count | generic_plans | custom_plans
-------+---------------+--------------
   165 |           165 |            0
(1 row)

***=*> select name,parent,level,count(*), pg_size_pretty(sum(total_bytes))
as bytes, sum(total_nblocks) as nblocks, pg_size_pretty(sum(free_bytes)) as
free_bytes,  sum(free_chunks) as free_chunks,
pg_size_pretty(sum(used_bytes)) as used_bytes from
pg_backend_memory_contexts group by 1,2,3 having sum(total_bytes)>128*1024
order by 3, sum(total_bytes) desc;
          name           |       parent       | level | count |  bytes  |
nblocks | free_bytes | free_chunks | used_bytes
-------------------------+--------------------+-------+-------+---------+---------+------------+-------------+------------
 TopMemoryContext        |                    |     0 |     1 | 809 kB  |
   16 | 236 kB     |         712 | 573 kB
 CacheMemoryContext      | TopMemoryContext   |     1 |     1 | 18 MB   |
  126 | 8137 kB    |           3 | 9910 kB
 CachedPlan              | CacheMemoryContext |     2 |   252 | 73 MB   |
 1490 | 29 MB      |         127 | 43 MB
 CachedPlanSource        | CacheMemoryContext |     2 |   252 | 4942 kB |
 1095 | 1926 kB    |         381 | 3016 kB
 index info              | CacheMemoryContext |     2 |   794 | 1655 kB |
 1516 | 579 kB     |         926 | 1076 kB
 CachedPlanQuery         | CachedPlanSource   |     3 |   252 | 4502 kB |
 1096 | 1460 kB    |         134 | 3041 kB


In the first case 2.5Kb per CachedPlan
in the second case 300Kb per CachedPlan

Problem with force_generic_plan that backends quickly eat up 1GB per
backend exhausting available server memory.
Postgresql version 17.4 and no complicated query in this workload (1-2-3
tables per query, sometimes two tables could be partitioned to 24
partitions each, third table always monolitic).

Regards,
Maxim


-- 
Maxim Boguk
Senior Postgresql DBA

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

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

    Andrei Lepikhov <lepihov@gmail.com> — 2025-05-18T16:54:24Z

    On 5/12/25 16:04, Maxim Boguk wrote:
> On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <lepihov@gmail.com 
>     If I'm not mistaken, it will work with all PG versions that are
>     currently in support. What do you think?
> 
> 
> Such extension would be very useful (and in general - the solution based 
> on the actual execution data - seems more stable/predictable than the 
> plan cost based selection which is currently used by postgresql).
I've written a sketch of such an extension; see [1]. A trivial strategy 
is implemented to force prepared statements to use a generic plan if the 
planning time exceeds the maximum execution time.
It is just an example - it is written in plpgsql, and you can implement 
alternative strategies independently.
I would be happy if it covered your use case. Any feedback is welcome.

[1] https://github.com/danolivo/pg_mentor/tree/main

-- 
regards, Andrei Lepikhov