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

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

> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)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