Обсуждение: BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables

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.

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