On Mon, May 12, 2025 at 3:08 PM Andrei Lepikhov <lepihov@xxxxxxxxx> wrote:
On 5/12/25 13:49, Maxim Boguk wrote:
> 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)?
Thanks for this puzzle!
I suppose, in case generic planning is much faster than custom one,
there are two candidates exist:
1. Touching the index during planning causes too much overhead - see
get_actual_variable_range
2. You have a massive default_statistics_target for a table involved.
So, to clarify the problem, may you provide EXPLAIN (without analyze)
with BUFFERS ON ?
Also, could you provide extra information on the statistics involved?
For each column (I think created_at is the most important one), show the
size of MCV and histogram arrays.
--
regards, Andrei Lepikhov
clickcast=# explain (buffers) 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=385)
-> Append (cost=1.14..9.10 rows=50 width=385)
-> 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=371)
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)
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:
Buffers: shared hit=16
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.14..1.29 rows=1 width=385)
-> Append (cost=1.14..9.10 rows=50 width=385)
-> 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=371)
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)
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:
Buffers: shared hit=16
16 buffers - most times, sometimes 12k Buffers: shared hit=12511 (like 5% cases) - I have no idea why.
show default_statistics_target ;
default_statistics_target
---------------------------
100
default_statistics_target
---------------------------
100
No custom statistic targets on this table or partitions.
select tablename,attname,inherited,null_frac,n_distinct,array_length(most_common_vals,1) mcv, array_length(histogram_bounds,1) hist from pg_stats where tablename IN ('job_stats_master', 'job_stats_new_2025_04', 'job_stats_new_2025_05') and attname in ('created_at', 'job_board_id', 'job_reference') order by tablename, attname;
tablename | attname | inherited | null_frac | n_distinct | mcv | hist
-----------------------+---------------+-----------+------------+--------------+-----+------
job_stats_master | created_at | t | 0 | 1.066586e+06 | 15 | 101
job_stats_master | job_board_id | t | 0.52743334 | 1716 | 100 | 101
job_stats_master | job_reference | t | 0 | -0.1 | 39 | 101
job_stats_new_2025_04 | created_at | f | 0 | 832508 | 39 | 101
job_stats_new_2025_04 | job_board_id | f | 0.47096667 | 1096 | 100 | 101
job_stats_new_2025_04 | job_reference | f | 0 | -0.1 | 93 | 101
job_stats_new_2025_05 | created_at | f | 0 | 709166 | 42 | 101
job_stats_new_2025_05 | job_board_id | f | 0.4703 | 1142 | 100 | 101
job_stats_new_2025_05 | job_reference | f | 0 | -0.1 | 100 | 101
tablename | attname | inherited | null_frac | n_distinct | mcv | hist
-----------------------+---------------+-----------+------------+--------------+-----+------
job_stats_master | created_at | t | 0 | 1.066586e+06 | 15 | 101
job_stats_master | job_board_id | t | 0.52743334 | 1716 | 100 | 101
job_stats_master | job_reference | t | 0 | -0.1 | 39 | 101
job_stats_new_2025_04 | created_at | f | 0 | 832508 | 39 | 101
job_stats_new_2025_04 | job_board_id | f | 0.47096667 | 1096 | 100 | 101
job_stats_new_2025_04 | job_reference | f | 0 | -0.1 | 93 | 101
job_stats_new_2025_05 | created_at | f | 0 | 709166 | 42 | 101
job_stats_new_2025_05 | job_board_id | f | 0.4703 | 1142 | 100 | 101
job_stats_new_2025_05 | job_reference | f | 0 | -0.1 | 100 | 101
PS: problem not with difference between custom and generic planning time but with prepared statements
generic plan plans only once, but custom plan plan every call (and plan time cost 95% on total query runtime).
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
