pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions
2+ messages / 2 participants
[nested] [flat]
* BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions
@ 2025-05-07 21:18 PG Bug reporting form <noreply@postgresql.org>
2025-05-07 22:49 ` Re: BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-05-07 21:18 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: 18916
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 17.4
Operating system: Ubuntu
Description:
Hi,
I don't know it's missing feature or actual bug in case of native
partitioning
alter table partition_head alter somefield set (n_distinct=...)
and/or alter table individual_paritition alter somefield set
(n_distinct=...)
simple have no effect in distinct somefield estimations, thus leading to
very bad plans.
In my particular case (job_stats_master partitioned by timestamp to 24
monthly partitions):
somedb=# explain analyze select distinct ip_matching_id from
job_stats_master where ((created_at >= '2025-04-01'::date) AND (created_at
< '2025-05-01'::date));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=7645580.53..8024432.51 rows=638235 width=8) (actual
time=23453.318..49782.682 rows=32248908 loops=1)
...
-> Parallel Seq Scan on job_stats_new_2025_04
job_stats_master (cost=0.00..2056130.41 rows=43756912 width=8) (actual
time=162.721..14384.324 rows=38893470 loops=9)
Filter: ((created_at >= '2025-04-01'::date) AND
(created_at < '2025-05-01'::date))
almost 2 order of magnitude error
Now try set n_distinct to fix estimates:
--trying n_distinct on head (-1 to simplify checks)
alter table job_stats_master alter ip_matching_id set (n_distinct=-1);
analyze job_stats_master;
analyze job_stats_new_2025_04;
explain select distinct ip_matching_id from job_stats_master where
((created_at >= '2025-04-01'::date) AND (created_at < '2025-05-01'::date));
Unique (cost=4058566.04..4210370.73 rows=599315 width=8)
--no effect on estimates
--trying n_distinct on individual partition
alter table job_stats_master alter ip_matching_id reset (n_distinct);
alter table job_stats_new_2025_04 alter ip_matching_id set (n_distinct=-1);
analyze job_stats_master;
analyze job_stats_new_2025_04;
explain select distinct ip_matching_id from job_stats_master where
((created_at >= '2025-04-01'::date) AND (created_at < '2025-05-01'::date));
Unique (cost=4061580.04..4226718.54 rows=651956 width=8)
--again no effect on estimates
--trying both
alter table job_stats_master alter ip_matching_id set (n_distinct=-1);
alter table job_stats_new_2025_04 alter ip_matching_id set (n_distinct=-1);
analyze job_stats_master;
analyze job_stats_new_2025_04;
explain select distinct ip_matching_id from job_stats_master where
((created_at >= '2025-04-01'::date) AND (created_at < '2025-05-01'::date));
Unique (cost=4058537.12..4213542.47 rows=611951 width=8)
--unfortunately no effect as well
Kind Regards,
Maxim
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions
2025-05-07 21:18 BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions PG Bug reporting form <noreply@postgresql.org>
@ 2025-05-07 22:49 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2025-05-07 22:49 UTC (permalink / raw)
To: maxim.boguk@gmail.com; pgsql-bugs@lists.postgresql.org
On Thu, 8 May 2025 at 10:40, PG Bug reporting form
<noreply@postgresql.org> wrote:
> I don't know it's missing feature or actual bug in case of native
> partitioning
> alter table partition_head alter somefield set (n_distinct=...)
> and/or alter table individual_paritition alter somefield set
> (n_distinct=...)
> simple have no effect in distinct somefield estimations, thus leading to
> very bad plans.
You'll need to use n_distinct_inherited since partitioned tables use
the inheritance statistics.
The documentation in [1] does not seem to highlight this very well.
David
[1] https://www.postgresql.org/docs/17/sql-altertable.html
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-05-07 22:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-07 21:18 BUG #18916: alter table ... set (n_distinct=) completely ignored on partition head and/or individual partitions PG Bug reporting form <noreply@postgresql.org>
2025-05-07 22:49 ` David Rowley <dgrowleyml@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