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
