Thread

1. BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables

   PG Bug reporting form <noreply@postgresql.org> — 2023-04-30T03:20:31Z

   The following bug has been logged on the website:
   
   Bug reference:      17913
   Logged by:          Maxim Boguk
   Email address:      maxim.boguk@gmail.com
   PostgreSQL version: 15.2
   Operating system:   Linux
   Description:        
   
   I found that the
   alter table sometable alter somecolumn set (n_distinct=...);
   Doesn't affect distinct values estimation if sometable - declarative
   partitioned table.
   It work on individual partition level, but setting for an individual
   partitions doesn't affect distinct estimation on whole table too.
   
   Minimal test case:
   CREATE TABLE test (
       id              int not null,
       value           int not null
   ) PARTITION BY RANGE (id);
   CREATE TABLE test_p1 PARTITION OF test
       FOR VALUES FROM (0) TO (1000);
   insert into test select id, random()*100::integer from
   generate_series(1,999) as g(id);
   analyze test;
   
   explain analyze select distinct value from test;
                                                      QUERY PLAN               
                                       
   -----------------------------------------------------------------------------------------------------------------
    HashAggregate  (cost=17.49..18.50 rows=101 width=4) (actual
   time=12.802..13.737 rows=101 loops=1)
      Group Key: test.value
      Batches: 1  Memory Usage: 24kB
      ->  Seq Scan on test_p1 test  (cost=0.00..14.99 rows=999 width=4) (actual
   time=0.016..6.272 rows=999 loops=1)
    Planning Time: 0.232 ms
    Execution Time: 14.659 ms
   
   So far ok, now lets change distinct estimation for value:
   alter table test alter value set (n_distinct=-1);
   analyze test;
   explain select distinct value from test;
                                QUERY PLAN                              
   ---------------------------------------------------------------------
    HashAggregate  (cost=17.49..18.50 rows=101 width=4)
      Group Key: test.value
      ->  Seq Scan on test_p1 test  (cost=0.00..14.99 rows=999 width=4)
   
   No changes in estimated amount of distinct values.
   
   

2. Re: BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables

   David Rowley <dgrowleyml@gmail.com> — 2023-05-01T04:40:52Z

   On Mon, 1 May 2023 at 15:03, PG Bug reporting form
   <noreply@postgresql.org> wrote:
   > I found that the
   > alter table sometable alter somecolumn set (n_distinct=...);
   > Doesn't affect distinct values estimation if sometable - declarative
   > partitioned table.
   
   It might not be that intuitive, but n_distinct_inherited is the option for that.
   
   Maybe the documentation [1] should mention partition partitions as
   well as "inheritance children" is this isn't clear.
   
   David
   
   [1] https://www.postgresql.org/docs/current/sql-altertable.html
   
   
   
   

3. Re: BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables

   Maxim Boguk <maxim.boguk@gmail.com> — 2023-05-01T04:46:02Z

   On Mon, May 1, 2023 at 2:41 PM David Rowley <dgrowleyml@gmail.com> wrote:
   
   > On Mon, 1 May 2023 at 15:03, PG Bug reporting form
   > <noreply@postgresql.org> wrote:
   > > I found that the
   > > alter table sometable alter somecolumn set (n_distinct=...);
   > > Doesn't affect distinct values estimation if sometable - declarative
   > > partitioned table.
   >
   > It might not be that intuitive, but n_distinct_inherited is the option for
   > that.
   >
   > Maybe the documentation [1] should mention partition partitions as
   > well as "inheritance children" is this isn't clear.
   >
   > David
   >
   > [1] https://www.postgresql.org/docs/current/sql-altertable.html
   >
   
   
   Thank you very much, I don't know how I missed this moment in documentation.
   
   
   -- 
   Maxim Boguk
   Senior Postgresql DBA
   https://dataegret.com/
   
   Phone UA: +380 99 143 0000
   Phone AU: +61  45 218 5678