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