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

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

Reply via email to