BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches

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