Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uF0I9-00GCaF-5k for pgsql-performance@arkaria.postgresql.org; Wed, 14 May 2025 00:48:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uF0I7-007cBv-5M for pgsql-performance@arkaria.postgresql.org; Wed, 14 May 2025 00:48:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uF0I6-007cBm-Q6 for pgsql-performance@lists.postgresql.org; Wed, 14 May 2025 00:48:10 +0000 Received: from mail-vs1-xe2a.google.com ([2607:f8b0:4864:20::e2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uF0I4-001lo3-0B for pgsql-performance@lists.postgresql.org; Wed, 14 May 2025 00:48:10 +0000 Received: by mail-vs1-xe2a.google.com with SMTP id ada2fe7eead31-4def49bf2bdso3234895137.0 for ; Tue, 13 May 2025 17:48:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747183687; x=1747788487; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=lzImfF6ddkybhf2G3Fs4DHIe9MisUza1upbsayhL4FM=; b=HcOGtZ8E5wDoyuOTJoONItzjYp2wmFFqiTo44slAc/U8qcJpiRsPS4ecJI/FX5tmjd yIX35eRyU/DYnKletd7sNkCkJdn9ENSmiaFBpywdWmCP6/G2UFlJZpcqqMZ5aE0ydzB0 sg8c/yeCLKUU4Tj4gyco/WQKgOGCei2nHngG4TJE9fKAFlzNrQuNAHKkTLucwRy4krB1 lasuJqBBiimzer9WiOiCKuPBkAo9Oy31zCVfJ1VdhVOIp48d+LxFa9Hx7tv5y1+5/D+z m/E2ia+tJhTWmjuXJAci+ukkXiM6m2qNut4W163ByCV2on0A9ilrAHUm8JQ5LcyCzJ7C 8lgA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747183687; x=1747788487; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=lzImfF6ddkybhf2G3Fs4DHIe9MisUza1upbsayhL4FM=; b=ClZX9TbRw7Pa/CqwMLRw4WoszJDzatcCfNwX0WJ9ipKGm0ML0U3+jP5ZNJYqB8Hlp3 xSKVlla95TMU2Wy9viwoxZeSh0coe2Cct21aDtrOGvb99ATIkPFeO8KxGWkQMl++BKjH 94O3mDU5hATa1JCbTNsb9kKDMFYcmPs/ujOC9DH0qClUTBlSkRSoc9EZnsaaDzGboHBZ afYPFEkyFJE72kWMWekrrP4oQ4LV71pqVFRC45QcUkiHdynbrhb9U0rq+tcKHUcR3gq+ XRQj35J9ds/UxjMkkM4htxbwc8Y4AzXVguF4E2j3nPDgT/QXGOmiUJTGoKIq0R1gUcRp sI3Q== X-Forwarded-Encrypted: i=1; AJvYcCWdFAiE2Hs2lIhszrOCRciG7GKVfeJoaDI+VLuwwZBaJvwiVDdedwHL7UIiTsWVqIAg6hHQtYBozIvM/woesB8i/A==@lists.postgresql.org X-Gm-Message-State: AOJu0Yzb9w8D587qFZyG2njiRovAvqy1uaoF12FrsiazoDuKdKa31et5 ljAtHH1N9HNejxiZFNrcXVaSZec6jFdEluDFkn6hbZ39gD/n5FjA7dXj3SqJECWQYhZxbUDPrGT t8PX0ECBK6i6/bceZvWTm9TVuHvw= X-Gm-Gg: ASbGncu0yckfTBnQA7tzySjFts/w4kHyTR/qrhvbPIkhs1w+pPj+TlkTEYbZGNB354Q 8V/I/T/P2Lwz6XRwTWdFupadS98E0clwDtMM/okTChvNMi3f3uwoo9QgaiWQJNVK7KGFdLMCUBf FFI8osmueLNMK8VRC9osQZhNCpKiv+BZU= X-Google-Smtp-Source: AGHT+IHOmqb03R+LMdzy9y5xQmwl6/Av1wlj1lZvc4LNvUB6nEEKTOLmZ04XFdKo/w2p4JZSw0dZbjrEJOWaoyT+Ekg= X-Received: by 2002:a05:6102:4b10:b0:4c1:a15c:ab5c with SMTP id ada2fe7eead31-4df7dd8e742mr1525188137.20.1747183686819; Tue, 13 May 2025 17:48:06 -0700 (PDT) MIME-Version: 1.0 References: <38201.1747063982@sss.pgh.pa.us> <38869.1747073274@sss.pgh.pa.us> In-Reply-To: <38869.1747073274@sss.pgh.pa.us> From: Maxim Boguk Date: Wed, 14 May 2025 03:47:30 +0300 X-Gm-Features: AX0GCFsD-D6rARpw3WhfU496KuKFH9Rdulhd5OeVLu6coMwznmzDtaaLISzGs9U Message-ID: Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) To: Tom Lane Cc: David Rowley , Andrei Lepikhov , Pgsql Performance Content-Type: multipart/alternative; boundary="00000000000002a47706350de743" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000002a47706350de743 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, May 12, 2025 at 9:07=E2=80=AFPM Tom Lane wrote: > Maxim Boguk writes: > > Reading the code - probably the lowest hanging fruit is to make > > 'The current multiplier of 1000 * cpu_operator_cost' configurable in th= e > > 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 ***=3D> begin; BEGIN ****=3D*> 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) ***=3D*> 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 ***=3D> begin; BEGIN ***=3D*> 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) ***=3D*> 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 --=20 Maxim Boguk Senior Postgresql DBA Phone UA: +380 99 143 0000 Phone AU: +61 45 218 5678 --00000000000002a47706350de743 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, May 12, 2025 at 9:07=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Maxim Boguk <maxim.boguk@gmail.com> writes: > Reading the code - probably the lowest hanging fruit is to make
> 'The current multiplier of 1000 * cpu_operator_cost' configura= ble in the
> future versions.

Is the 10= 0x 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_ca= che_mode to force_custom_plan ;
reconnect pgbouncers/wait 5 min/check sa= mple

***=3D> begin;
BEGIN
****=3D*> select count(*), cou= nt(*) filter (where generic_plans>0) as generic_plans, count(*) filter (= where custom_plans>0) as custom_plans from pg_prepared_statements ;
= =C2=A0count | generic_plans | custom_plans
-------+---------------+----= ----------
=C2=A0 =C2=A0177 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = 3 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0174
(1 row)

***=3D*> sel= ect 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_byte= s, =C2=A0sum(free_chunks) as free_chunks, pg_size_pretty(sum(used_bytes)) a= s used_bytes from pg_backend_memory_contexts group by 1,2,3 having sum(tota= l_bytes)>128*1024 order by 3, sum(total_bytes) desc;
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 parent =C2=A0 =C2=A0 =C2=A0 | level | count | =C2=A0bytes =C2=A0= | nblocks | free_bytes | free_chunks | used_bytes
---------------------= ----+--------------------+-------+-------+---------+---------+------------+= -------------+------------
=C2=A0TopMemoryContext =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| =C2=A0 =C2=A0 0 | =C2=A0 =C2=A0 1 | 769 kB =C2=A0| =C2=A0 =C2=A0 =C2= =A015 | 236 kB =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 574 | 532 kB
= =C2=A0CacheMemoryContext =C2=A0 =C2=A0 =C2=A0| TopMemoryContext =C2=A0 | = =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 1 | 9856 kB | =C2=A0 =C2=A0 125 | 223 kB = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2 | 9633 kB
=C2=A0Cac= hedPlanSource =C2=A0 =C2=A0 =C2=A0 =C2=A0| CacheMemoryContext | =C2=A0 =C2= =A0 2 | =C2=A0 264 | 5228 kB | =C2=A0 =C2=A01142 | 2142 kB =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 456 | 3086 kB
=C2=A0index info =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| CacheMemoryContext | =C2=A0 =C2=A0 2 |= =C2=A0 776 | 1612 kB | =C2=A0 =C2=A01483 | 575 kB =C2=A0 =C2=A0 | =C2=A0 = =C2=A0 =C2=A0 =C2=A0 908 | 1037 kB
=C2=A0CachedPlan =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0| CacheMemoryContext | =C2=A0 =C2=A0 2 | =C2=A0= =C2=A062 | 154 kB =C2=A0| =C2=A0 =C2=A0 137 | 41 kB =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A031 | 113 kB
=C2=A0CachedPlanQuery =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | CachedPlanSource =C2=A0 | =C2=A0 =C2=A0 3 | =C2= =A0 264 | 4777 kB | =C2=A0 =C2=A01147 | 1628 kB =C2=A0 =C2=A0| =C2=A0 =C2= =A0 =C2=A0 =C2=A0 133 | 3149 kB


=
And with:
alter role app_server set pla= n_cache_mode to force_generic_plan ;
reconnect pgbouncers/wait 5 min/che= ck sample

***=3D> begin;
BEGIN
***=3D*> select count(*),= count(*) filter (where generic_plans>0) as generic_plans, count(*) filt= er (where custom_plans>0) as custom_plans from pg_prepared_statements ;<= br>=C2=A0count | generic_plans | custom_plans
-------+---------------+-= -------------
=C2=A0 =C2=A0165 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 165 = | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
(1 row)

***=3D*> = select name,parent,level,count(*), pg_size_pretty(sum(total_bytes)) as byte= s, sum(total_nblocks) as nblocks, pg_size_pretty(sum(free_bytes)) as free_b= ytes, =C2=A0sum(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(t= otal_bytes)>128*1024 order by 3, sum(total_bytes) desc;
=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2= =A0 =C2=A0 parent =C2=A0 =C2=A0 =C2=A0 | level | count | =C2=A0bytes =C2=A0= | nblocks | free_bytes | free_chunks | used_bytes
---------------------= ----+--------------------+-------+-------+---------+---------+------------+= -------------+------------
=C2=A0TopMemoryContext =C2=A0 =C2=A0 =C2=A0 = =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| =C2=A0 =C2=A0 0 | =C2=A0 =C2=A0 1 | 809 kB =C2=A0| =C2=A0 =C2=A0 =C2= =A016 | 236 kB =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 712 | 573 kB
= =C2=A0CacheMemoryContext =C2=A0 =C2=A0 =C2=A0| TopMemoryContext =C2=A0 | = =C2=A0 =C2=A0 1 | =C2=A0 =C2=A0 1 | 18 MB =C2=A0 | =C2=A0 =C2=A0 126 | 8137= kB =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 3 | 9910 kB
=C2=A0= CachedPlan =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| CacheMemoryCon= text | =C2=A0 =C2=A0 2 | =C2=A0 252 | 73 MB =C2=A0 | =C2=A0 =C2=A01490 | 29= MB =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 127 | 43 MB
=C2=A0= CachedPlanSource =C2=A0 =C2=A0 =C2=A0 =C2=A0| CacheMemoryContext | =C2=A0 = =C2=A0 2 | =C2=A0 252 | 4942 kB | =C2=A0 =C2=A01095 | 1926 kB =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 381 | 3016 kB
=C2=A0index info =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| CacheMemoryContext | =C2=A0 =C2=A0 = 2 | =C2=A0 794 | 1655 kB | =C2=A0 =C2=A01516 | 579 kB =C2=A0 =C2=A0 | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 926 | 1076 kB
=C2=A0CachedPlanQuery =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | CachedPlanSource =C2=A0 | =C2=A0 =C2=A0 3 | =C2=A0 252 = | 4502 kB | =C2=A0 =C2=A01096 | 1460 kB =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0= =C2=A0 134 | 3041 kB

=C2=A0
In t= he first case 2.5Kb per CachedPlan
in the second case 300K= b per CachedPlan

Problem with fo= rce_generic_plan that backends quickly eat up 1GB per backend exhausting av= ailable server memory.
Postgresql version 17.4 and no comp= licated query in this workload (1-2-3 tables per query, sometimes two table= s could be partitioned to 24 partitions each, third table always monolitic)= .

Regards,
Maxi= m


--
Maxim Boguk
Senior Postgresql DBA

= Phone UA: +380 99 143 0000
Phone AU: +61=C2=A0 45 218 5678
--00000000000002a47706350de743--