Thread

1. BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions

   PG Bug reporting form <noreply@postgresql.org> — 2025-05-07T21:18:00Z

   The following bug has been logged on the website:
   
   Bug reference:      18916
   Logged by:          Maxim Boguk
   Email address:      maxim.boguk@gmail.com
   PostgreSQL version: 17.4
   Operating system:   Ubuntu
   Description:        
   
   Hi,
   I don't know it's missing feature or actual bug in case of native
   partitioning
   alter table partition_head alter somefield set (n_distinct=...)
   and/or alter table individual_paritition alter somefield set
   (n_distinct=...)
   simple have no effect in distinct somefield estimations, thus leading to
   very bad plans.
   In my particular case (job_stats_master partitioned by timestamp to 24
   monthly partitions):
   somedb=# explain analyze  select distinct ip_matching_id from
   job_stats_master where  ((created_at >= '2025-04-01'::date) AND (created_at
   < '2025-05-01'::date));
   QUERY PLAN
   -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Unique  (cost=7645580.53..8024432.51 rows=638235 width=8) (actual
   time=23453.318..49782.682 rows=32248908 loops=1)
   ...
                        ->  Parallel Seq Scan on job_stats_new_2025_04
   job_stats_master  (cost=0.00..2056130.41 rows=43756912 width=8) (actual
   time=162.721..14384.324 rows=38893470 loops=9)
                              Filter: ((created_at >= '2025-04-01'::date) AND
   (created_at < '2025-05-01'::date))
   almost 2 order of magnitude error
   Now try set n_distinct to fix estimates:
   --trying n_distinct on head (-1 to simplify checks)
   alter table  job_stats_master alter ip_matching_id set (n_distinct=-1);
   analyze job_stats_master;
   analyze job_stats_new_2025_04;
   explain select distinct ip_matching_id from job_stats_master where
   ((created_at >= '2025-04-01'::date) AND (created_at < '2025-05-01'::date));
    Unique  (cost=4058566.04..4210370.73 rows=599315 width=8)
   --no effect on estimates
   --trying n_distinct on individual partition
   alter table  job_stats_master alter ip_matching_id reset (n_distinct);
   alter table job_stats_new_2025_04  alter ip_matching_id set (n_distinct=-1);
   analyze job_stats_master;
   analyze job_stats_new_2025_04;
   explain select distinct ip_matching_id from job_stats_master where
   ((created_at >= '2025-04-01'::date) AND (created_at < '2025-05-01'::date));
    Unique  (cost=4061580.04..4226718.54 rows=651956 width=8)
   --again no effect on estimates
   --trying both
   alter table  job_stats_master alter ip_matching_id set (n_distinct=-1);
   alter table job_stats_new_2025_04  alter ip_matching_id set (n_distinct=-1);
   analyze job_stats_master;
   analyze job_stats_new_2025_04;
   explain select distinct ip_matching_id from job_stats_master where
   ((created_at >= '2025-04-01'::date) AND (created_at < '2025-05-01'::date));
    Unique  (cost=4058537.12..4213542.47 rows=611951 width=8)
   --unfortunately no effect as well
   Kind Regards,
   Maxim
   
   

2. Re: BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions

   David Rowley <dgrowleyml@gmail.com> — 2025-05-07T22:49:24Z

   On Thu, 8 May 2025 at 10:40, PG Bug reporting form
   <noreply@postgresql.org> wrote:
   > I don't know it's missing feature or actual bug in case of native
   > partitioning
   > alter table partition_head alter somefield set (n_distinct=...)
   > and/or alter table individual_paritition alter somefield set
   > (n_distinct=...)
   > simple have no effect in distinct somefield estimations, thus leading to
   > very bad plans.
   
   You'll need to use n_distinct_inherited since partitioned tables use
   the inheritance statistics.
   
   The documentation in [1] does not seem to highlight this very well.
   
   David
   
   [1] https://www.postgresql.org/docs/17/sql-altertable.html