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.