On Mon, May 12, 2025 at 3:08 PM Andrei Lepikhov <lepi...@gmail.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

Reply via email to