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