Обсуждение: BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables
BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables
От
PG Bug reporting form
Дата:
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
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
Senior Postgresql DBA
https://dataegret.com/
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678