Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)

On Mon, May 12, 2025 at 3:08 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> 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
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
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

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