public inbox for [email protected]
help / color / mirror / Atom feedFrom: Maxim Boguk <[email protected]>
To: [email protected]
Subject: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
Date: Mon, 12 May 2025 14:49:10 +0300
Message-ID: <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@mail.gmail.com> (raw)
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" = '******' AND
"job_stats_master"."job_board_id" = 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=1.14..1.29 rows=1 width=384) (actual time=0.026..0.026 rows=1
loops=1)
-> Append (cost=1.14..9.10 rows=50 width=384) (actual
time=0.025..0.026 rows=1 loops=1)
-> 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=0.56..3.28 rows=18
width=368) (actual time=0.025..0.025 rows=1 loops=1)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
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=0.57..5.32 rows=32
width=394) (never executed)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
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=19.06..19.32 rows=1 width=407) (actual time=0.030..0.030
rows=1 loops=1)
-> Append (cost=19.06..26.74 rows=29 width=407) (actual
time=0.029..0.030 rows=1 loops=1)
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=0.56..0.82 rows=1
width=368) (actual time=0.029..0.029 rows=1 loops=1)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= $1) AND
(created_at <= $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=0.57..0.83 rows=1
width=394) (never executed)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= $1) AND
(created_at <= $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)?
--
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]
Subject: Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
In-Reply-To: <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@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