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

[Maxim Boguk <maxim.boguk@xxxxxxxxx>]

On Mon, May 12, 2025 at 9:07 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Maxim Boguk <maxim.boguk@xxxxxxxxx> 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