Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19575: Enhancement for Redundant DISTINCT in set-operation
 branches
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 2320415112@qq.com
Reply-To: 2320415112@qq.com, pgsql-bugs@lists.postgresql.org
Date: Thu, 23 Jul 2026 12:54:38 +0000
Message-ID: <19575-7ce4e703a249ba27@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19575-7ce4e703a249ba27%40postgresql.org>
Precedence: bulk

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:       =20

## 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;
```