public inbox for [email protected]
help / color / mirror / Atom feedFrom: Maxim Boguk <[email protected]>
To: Tom Lane <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: Pgsql Performance <[email protected]>
Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
Date: Wed, 14 May 2025 03:47:30 +0300
Message-ID: <CAK-MWwSPGus_rd+kHtVOknVcXFYCtajzWOTrssf-8HNEYxO2SA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@mail.gmail.com>
<[email protected]>
<CAApHDvrduvaF6nN9ZQSfOdE0Na9SwPOkwYKn=Rb2a=Q9B72MAQ@mail.gmail.com>
<CAK-MWwTL=_NjJc9v241VQdavOC4deWgPjoqDxLbvm=wUFKq=ug@mail.gmail.com>
<[email protected]>
<CAK-MWwQWMK=d_Uzt63hstuYArAuB6Grv9sjYHMah1JXhgkxG3A@mail.gmail.com>
<[email protected]>
On Mon, May 12, 2025 at 9:07 PM Tom Lane <[email protected]> wrote:
> Maxim Boguk <[email protected]> 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
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
In-Reply-To: <CAK-MWwSPGus_rd+kHtVOknVcXFYCtajzWOTrssf-8HNEYxO2SA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox