agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 2320415112@qq.com
Subject: BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches
Date: Thu, 23 Jul 2026 12:54:38 +0000
Message-ID: <19575-7ce4e703a249ba27@postgresql.org> (raw)

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

view thread (2+ messages)  latest in thread

Message-ID: <19575-7ce4e703a249ba27@postgresql.org>
Permalink:  ../19575-7ce4e703a249ba27@postgresql.org/
Also on:    postgresql.org/message-id/19575-7ce4e703a249ba27@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 2320415112@qq.com
  Subject: Re: BUG #19575: Enhancement for Redundant DISTINCT in set-operation branches
  In-Reply-To: <19575-7ce4e703a249ba27@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox