pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables
3+ messages / 3 participants
[nested] [flat]
* BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables
@ 2023-04-30 03:20 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2023-04-30 03:20 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: maxim.boguk@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables
@ 2023-05-01 04:40 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2023-05-01 04:40 UTC (permalink / raw)
To: maxim.boguk@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17913: alter column set (n_distinct=...) on partition head doesn't work for declarative partitioned tables
@ 2023-05-01 04:46 Maxim Boguk <maxim.boguk@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Maxim Boguk @ 2023-05-01 04:46 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-05-01 04:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-30 03:20 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-05-01 04:40 ` David Rowley <dgrowleyml@gmail.com>
2023-05-01 04:46 ` Maxim Boguk <maxim.boguk@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox