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 1uERfN-008Cwj-KH for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2025 11:49:54 +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 1uERfK-00FUNK-Qq for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2025 11:49:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uERfK-00FUNB-8Z for pgsql-performance@lists.postgresql.org; Mon, 12 May 2025 11:49:50 +0000 Received: from mail-vs1-xe2b.google.com ([2607:f8b0:4864:20::e2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uERfH-001Q6t-1L for pgsql-performance@lists.postgresql.org; Mon, 12 May 2025 11:49:49 +0000 Received: by mail-vs1-xe2b.google.com with SMTP id ada2fe7eead31-4def2870995so2200804137.1 for ; Mon, 12 May 2025 04:49:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1747050587; x=1747655387; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=RpN/hN1bXNWcSSrI2JGQle8Y6si6+1F0BNn5gKWbLMY=; b=nGK1yx4wBCXNeKsPga98GSvE/PpW/V1xEDS8RFirQo+wOvmGcF+yKspOAyrNk0eBGN QJxV/GZoIH7EhQCzSjHlxI0uAQcntjkOZ1+jiyxhCq2o2Hs+pP1zqwYs8h8dRtZggrdm n+4wsQIOPjincnHnWrmOAfZ5D9GhV/leiChY0m8HnS1vQx6c6BskyFRaMJXfB9SfhKTU 2eOqw+MS40n9kTbV1V15JFaf4EPEL6yNozMuf2EWtbefsPeAad9TLikeK3vxZXYHFAsK tFlsf0R8645dqtsxqTxEJzT8pZlgfRPZvXqW2zqlS4r58oN6jEic51pq30I0/OZwrRFv OiUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1747050587; x=1747655387; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=RpN/hN1bXNWcSSrI2JGQle8Y6si6+1F0BNn5gKWbLMY=; b=GeKSyyQS7HpgFGXIAcFfoCn27zQuPkuzPHH8KLnjJaHqo67QZQ8AdG+qvvM3swgZ24 jkeD0P5xTBSCMeOJj3zZZsloVhSU2SK/cFq+pstQE3l0HkldEJJJCZefep5p+6CrVN0Z hKfIcd0OLvIzDoqx4VG+zBohqiEVLVMs+tXlC68qTYBaPDQPnftZSQ80bvACahyKk4x7 aoViEdQJbIub+wGqB/1fIlt+xtiP1qDQ9I7ZkRyo6zg4deWXsn97eg0kri+Ubrohywod XmktWTgnbpVzZORkA41pJJSWGTB+QmioWDVy7rsHDnz9bnuWBmAPEGwTcuF0lTPFneER TY2Q== X-Gm-Message-State: AOJu0YxmqkkqKtSnaW39T2TpiocomJ3NN7GQxhUV6udmNTEkPfdBtUQ6 WvurV13P6EyIKUPO+EDpQDR/+bMnw5FT4DsdAR6Ceg2lVsRUgIWGLLxpq8/wmCliRLHFGF0TqDW mdjjNPD24OXDLaFIXfqrvK/aQvEZYIwgJnulLsA== X-Gm-Gg: ASbGncupSelDebzW6IGMnmj/13JsJfQzUR575UQb25LLhHucaTm7unAk5w95dYtnbiu 2f/Nr/UiF9bR9WNJb27HWo3rmffr7NiAYiYlD6RPJRq5mDI9KQrslRlcpDgAZ1wUWd9jzop+oyb YoOOGqL+4pxrMw7h9DM1tvJ9qgy+bUSmc= X-Google-Smtp-Source: AGHT+IEDX6VGk8oEn31e3Eal7ceeUd3jE0rEUjH8KzZRxVMEWklRQOg43j+dYFkkm5EuPlmXpUBqiGzSiCrPo/HoYL0= X-Received: by 2002:a05:6102:150e:b0:4bb:baa0:370b with SMTP id ada2fe7eead31-4deed30a25emr8213707137.7.1747050586973; Mon, 12 May 2025 04:49:46 -0700 (PDT) MIME-Version: 1.0 From: Maxim Boguk Date: Mon, 12 May 2025 14:49:10 +0300 X-Gm-Features: AX0GCFtOlX0OVhO3dlhHpYbNuFHmZeRVFmrJ_i7q2gx3ZQ7usLdZeR2qEZmWRgw Message-ID: Subject: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) To: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a42b890634eee966" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a42b890634eee966 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, I found a case where plan cache all time switching to custom plans forces query replan each call (and thus slows down the whole query for 10x or more). What makes the situation intriguing - that both custom and generic plans are the same. job_stats_master - partitioned table with 24 partitions (per month last 2 year). Problem query: prepare qqq(timestamp, timestamp) AS SELECT * FROM "job_stats_master" WHERE "job_stats_master"."created_at" BETWEEN $1 AND $2 AND "job_stats_master"."job_reference" =3D '******' AND "job_stats_master"."job_board_id" =3D 27068 ORDER BY "created_at" DESC LIMIT 1; plan (after 6th execution): explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp without time zone, '2025-05-12 09:22:00.203'::timestamp without time zone); QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------- Limit (cost=3D1.14..1.29 rows=3D1 width=3D384) (actual time=3D0.026..0.02= 6 rows=3D1 loops=3D1) -> Append (cost=3D1.14..9.10 rows=3D50 width=3D384) (actual time=3D0.025..0.026 rows=3D1 loops=3D1) -> Index Scan Backward using job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on job_stats_new_2025_05 job_stats_master_2 (cost=3D0.56..3.28 rows=3D18 width=3D368) (actual time=3D0.025..0.025 rows=3D1 loops=3D1) Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D '******'::text) AND (created_at >=3D '2025-04-11 09:22:00.193'::timestamp without time zone) AND (created_at <=3D '2025-05-1= 2 09:22:00.203'::timestamp without time zone)) -> Index Scan Backward using job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on job_stats_new_2025_04 job_stats_master_1 (cost=3D0.57..5.32 rows=3D32 width=3D394) (never executed) Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D '******'::text) AND (created_at >=3D '2025-04-11 09:22:00.193'::timestamp without time zone) AND (created_at <=3D '2025-05-1= 2 09:22:00.203'::timestamp without time zone)) Planning Time: 0.611 ms Execution Time: 0.057 ms (8 rows) plan with set plan_cache_mode to force_generic_plan ; explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp without time zone, '2025-05-12 09:22:00.203'::timestamp without time zone); QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------------------- Limit (cost=3D19.06..19.32 rows=3D1 width=3D407) (actual time=3D0.030..0.= 030 rows=3D1 loops=3D1) -> Append (cost=3D19.06..26.74 rows=3D29 width=3D407) (actual time=3D0.029..0.030 rows=3D1 loops=3D1) Subplans Removed: 27 -> Index Scan Backward using job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on job_stats_new_2025_05 job_stats_master_2 (cost=3D0.56..0.82 rows=3D1 width=3D368) (actual time=3D0.029..0.029 rows=3D1 loops=3D1) Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D '*******'::text) AND (created_at >=3D $1) AND (created_at <=3D $2)) -> Index Scan Backward using job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on job_stats_new_2025_04 job_stats_master_1 (cost=3D0.57..0.83 rows=3D1 width=3D394) (never executed) Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D '*******'::text) AND (created_at >=3D $1) AND (created_at <=3D $2)) Planning Time: 0.033 ms Execution Time: 0.086 ms Plan "de facto" the same, performance almost the same but with custom plans there is 20x more time spent on planning. With over 1M RPS - it's become quite an issue even for the best available servers. No playing with cost parameters provides any changes in selection custom plan over generic. As I understand there is an issue with costing model - generic plan thinks it will visit all 24 partitions but custom plan does prune partitions during planning thus custom plan always wins in this case "by cost" and in the same time huge loss in performance (but actual plans are the same in both cases). I suspect this situation should be quite common with queries over partitioned tables (where planning time is usually quite a high). Any suggestions what could be done there outside of using force_generic_plan for a particular db user (which will kill performance in other queries for sure)? --=20 Maxim Boguk Senior Postgresql DBA Phone UA: +380 99 143 0000 Phone AU: +61 45 218 5678 --000000000000a42b890634eee966 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,
I found a case where= plan cache all time switching to custom plans forces query replan each cal= l (and thus slows down the whole query for 10x or more).
What makes the situation intriguing - that both custom and generic plan= s are the same.

job_stats_master - p= artitioned table with 24 partitions (per month last 2 year).

Problem query:
prepare qqq(timest= amp, timestamp) AS
SELECT *
FROM "job_stats_master"
WHE= RE
"job_stats_master"."created_at" BETWEEN $1 AND $= 2 AND "job_stats_master"."job_reference" =3D '*****= *' AND "job_stats_master"."job_board_id" =3D 27068<= br>ORDER BY "created_at" DESC LIMIT 1;

plan (after 6th execution):
explain analyze e= xecute qqq('2025-04-11 09:22:00.193'::timestamp without time zone, = '2025-05-12 09:22:00.203'::timestamp without time zone);
=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 =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 =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 =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=A0QUERY PLAN =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 =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 = =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 =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=A0Limit =C2=A0(cost=3D1.14..1.29 rows=3D1 wi= dth=3D384) (actual time=3D0.026..0.026 rows=3D1 loops=3D1)
=C2=A0 =C2=A0= -> =C2=A0Append =C2=A0(cost=3D1.14..9.10 rows=3D50 width=3D384) (actual = time=3D0.025..0.026 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Index Scan Backward using job_stats_new_2025_05_job_board_id= _job_reference_created_at_idx on job_stats_new_2025_05 job_stats_master_2 = =C2=A0(cost=3D0.56..3.28 rows=3D18 width=3D368) (actual time=3D0.025..0.025= rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::text =3D = '******'::text) AND (created_at >=3D '2025-04-11 09:22:00.19= 3'::timestamp without time zone) AND (created_at <=3D '2025-05-1= 2 09:22:00.203'::timestamp without time zone))
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0-> =C2=A0Index Scan Backward using job_stats_new_2025_04_jo= b_board_id_job_reference_created_at_idx on job_stats_new_2025_04 job_stats_= master_1 =C2=A0(cost=3D0.57..5.32 rows=3D32 width=3D394) (never executed)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((job_= board_id =3D 27068) AND ((job_reference)::text =3D '******'::text) = AND (created_at >=3D '2025-04-11 09:22:00.193'::timestamp withou= t time zone) AND (created_at <=3D '2025-05-12 09:22:00.203'::tim= estamp without time zone))
=C2=A0Planning Time: 0.611 ms
=C2=A0Execut= ion Time: 0.057 ms
(8 rows)

plan with set plan_= cache_mode to force_generic_plan ;

explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp= without time zone, '2025-05-12 09:22:00.203'::timestamp without ti= me zone);
=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 =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 =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 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0QUERY PLAN =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 =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 =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 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------
=C2=A0Limit =C2=A0(cost=3D= 19.06..19.32 rows=3D1 width=3D407) (actual time=3D0.030..0.030 rows=3D1 loo= ps=3D1)
=C2=A0 =C2=A0-> =C2=A0Append =C2=A0(cost=3D19.06..26.74 rows= =3D29 width=3D407) (actual time=3D0.029..0.030 rows=3D1 loops=3D1)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Subplans Removed: 27
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0-> =C2=A0Index Scan Backward using job_stats_new_2025_05_j= ob_board_id_job_reference_created_at_idx on job_stats_new_2025_05 job_stats= _master_2 =C2=A0(cost=3D0.56..0.82 rows=3D1 width=3D368) (actual time=3D0.0= 29..0.029 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Index Cond: ((job_board_id =3D 27068) AND ((job_reference)::te= xt =3D '*******'::text) AND (created_at >=3D $1) AND (created_at= <=3D $2))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan B= ackward using job_stats_new_2025_04_job_board_id_job_reference_created_at_i= dx on job_stats_new_2025_04 job_stats_master_1 =C2=A0(cost=3D0.57..0.83 row= s=3D1 width=3D394) (never executed)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Index Cond: ((job_board_id =3D 27068) AND ((job_referen= ce)::text =3D '*******'::text) AND (created_at >=3D $1) AND (cre= ated_at <=3D $2))
=C2=A0Planning Time: 0.033 ms
=C2=A0Execution Ti= me: 0.086 ms

Plan=C2=A0"de facto" the sa= me, performance almost the same but with custom plans there is 20x more tim= e spent on planning.
With over 1M RPS - it's become qu= ite an issue even for the best available servers.

No playing with cost parameters provides any changes in selec= tion custom plan over generic.
As I understand there is an= issue with costing model - generic plan thinks it will visit all 24 partit= ions but custom plan does prune partitions during planning thus custom plan= =C2=A0always wins in this case "by cost" and in the same time hug= e loss in performance (but actual plans are the same in both cases).
<= div style=3D"font-family:monospace,monospace;font-size:large" class=3D"gmai= l_default">
I suspect this situation should be quite c= ommon with queries over partitioned tables (where planning time is usually = quite a high).

Any suggestions what = could be done there outside of using force_generic_plan for a particular db= user (which will kill performance in other queries for sure)?
<= div>



--
=
Maxim Boguk
Senior Postgresql DBA

P= hone UA: +380 99 143 0000
Phone AU: +61=C2=A0 45 218 5678

=
--000000000000a42b890634eee966--