agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19575: Enhancement for Redundant DISTINCT in set-operation branches
2+ messages / 2 participants
[nested] [flat]
* BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches
@ 2026-07-23 12:54 PG Bug reporting form <noreply@postgresql.org>
2026-07-23 19:03 ` Re: BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches Thom Brown <thom@linux.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-07-23 12:54 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 2320415112@qq.com
The following bug has been logged on the website:
Bug reference: 19575
Logged by: cl hl
Email address: 2320415112@qq.com
PostgreSQL version: 17.10
Operating system: ubuntu 22-04
Description:
## Description
PostgreSQL retains a `HashAggregate` in each input branch when `DISTINCT` is
added below `UNION`, `INTERSECT`, or `EXCEPT`, even though the outer set
operation already removes duplicates.
### Expected behaviour
The planner should remove branch-level `DISTINCT` and let `HashAggregate` or
`HashSetOp` at the set-operation level handle duplicate elimination.
### Actual behaviour
For `UNION`, two branch `HashAggregate` nodes feed another set-level
`HashAggregate`. For `INTERSECT` and `EXCEPT`, two branch `HashAggregate`
nodes
feed `HashSetOp`.
| Operation | Without branch DISTINCT | With branch DISTINCT | Slowdown |
|---|---:|---:|---:|
| UNION | 32.48 ms | 62.13 ms | 1.91x |
| INTERSECT | 24.49 ms | 50.26 ms | 2.05x |
| EXCEPT | 22.90 ms | 49.67 ms | 2.17x |
## How to repeat
```sql
DROP SCHEMA IF EXISTS pg_set_branch_distinct CASCADE;
CREATE SCHEMA pg_set_branch_distinct;
SET search_path TO pg_set_branch_distinct;
CREATE TABLE lhs(id INT, v INT);
CREATE TABLE rhs(id INT, v INT);
INSERT INTO lhs SELECT n,n%1000 FROM generate_series(1,100000) AS n;
INSERT INTO rhs SELECT n+50000,n%1000 FROM generate_series(1,100000) AS n;
VACUUM ANALYZE;
EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) UNION (SELECT DISTINCT id FROM rhs)) s;
EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) INTERSECT (SELECT DISTINCT id FROM rhs)) s;
EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) EXCEPT (SELECT DISTINCT id FROM rhs)) s;
SELECT COUNT(*) FROM ((SELECT id FROM lhs) UNION (SELECT id FROM rhs)) s;
SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) UNION (SELECT DISTINCT id FROM rhs)) s;
SELECT COUNT(*) FROM ((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs))
s;
SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) INTERSECT (SELECT DISTINCT id FROM rhs)) s;
SELECT COUNT(*) FROM ((SELECT id FROM lhs) EXCEPT (SELECT id FROM rhs)) s;
SELECT COUNT(*) FROM
((SELECT DISTINCT id FROM lhs) EXCEPT (SELECT DISTINCT id FROM rhs)) s;
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches
2026-07-23 12:54 BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches PG Bug reporting form <noreply@postgresql.org>
@ 2026-07-23 19:03 ` Thom Brown <thom@linux.com>
0 siblings, 0 replies; 2+ messages in thread
From: Thom Brown @ 2026-07-23 19:03 UTC (permalink / raw)
To: 2320415112@qq.com; pgsql-bugs@lists.postgresql.org
On Thu, 23 Jul 2026 at 18:05, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19575
> Logged by: cl hl
> Email address: 2320415112@qq.com
> PostgreSQL version: 17.10
> Operating system: ubuntu 22-04
> Description:
>
> ## Description
>
> PostgreSQL retains a `HashAggregate` in each input branch when `DISTINCT` is
> added below `UNION`, `INTERSECT`, or `EXCEPT`, even though the outer set
> operation already removes duplicates.
>
> ### Expected behaviour
>
> The planner should remove branch-level `DISTINCT` and let `HashAggregate` or
> `HashSetOp` at the set-operation level handle duplicate elimination.
>
> ### Actual behaviour
>
> For `UNION`, two branch `HashAggregate` nodes feed another set-level
> `HashAggregate`. For `INTERSECT` and `EXCEPT`, two branch `HashAggregate`
> nodes
> feed `HashSetOp`.
>
> | Operation | Without branch DISTINCT | With branch DISTINCT | Slowdown |
> |---|---:|---:|---:|
> | UNION | 32.48 ms | 62.13 ms | 1.91x |
> | INTERSECT | 24.49 ms | 50.26 ms | 2.05x |
> | EXCEPT | 22.90 ms | 49.67 ms | 2.17x |
>
> ## How to repeat
>
> ```sql
> DROP SCHEMA IF EXISTS pg_set_branch_distinct CASCADE;
> CREATE SCHEMA pg_set_branch_distinct;
> SET search_path TO pg_set_branch_distinct;
>
> CREATE TABLE lhs(id INT, v INT);
> CREATE TABLE rhs(id INT, v INT);
> INSERT INTO lhs SELECT n,n%1000 FROM generate_series(1,100000) AS n;
> INSERT INTO rhs SELECT n+50000,n%1000 FROM generate_series(1,100000) AS n;
> VACUUM ANALYZE;
>
> EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
> ((SELECT DISTINCT id FROM lhs) UNION (SELECT DISTINCT id FROM rhs)) s;
> EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
> ((SELECT DISTINCT id FROM lhs) INTERSECT (SELECT DISTINCT id FROM rhs)) s;
> EXPLAIN (ANALYZE, COSTS OFF) SELECT COUNT(*) FROM
> ((SELECT DISTINCT id FROM lhs) EXCEPT (SELECT DISTINCT id FROM rhs)) s;
>
> SELECT COUNT(*) FROM ((SELECT id FROM lhs) UNION (SELECT id FROM rhs)) s;
> SELECT COUNT(*) FROM
> ((SELECT DISTINCT id FROM lhs) UNION (SELECT DISTINCT id FROM rhs)) s;
> SELECT COUNT(*) FROM ((SELECT id FROM lhs) INTERSECT (SELECT id FROM rhs))
> s;
> SELECT COUNT(*) FROM
> ((SELECT DISTINCT id FROM lhs) INTERSECT (SELECT DISTINCT id FROM rhs)) s;
> SELECT COUNT(*) FROM ((SELECT id FROM lhs) EXCEPT (SELECT id FROM rhs)) s;
> SELECT COUNT(*) FROM
> ((SELECT DISTINCT id FROM lhs) EXCEPT (SELECT DISTINCT id FROM rhs)) s;
> ```
I suggest you send any enhancement proposals to the pgsql-hackers
mailing list as the -bugs list is only for bugs that need fixing. That
said, I did have Claude have a go at solving this, had it reviewed and
re-reviewed, so I'll submit that to the -hackers list in case it's any
use in future.
Thom
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-23 19:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-23 12:54 BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches PG Bug reporting form <noreply@postgresql.org>
2026-07-23 19:03 ` Thom Brown <thom@linux.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox