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

On Thu, 23 Jul 2026 at 18:05, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19575
> Logged by: cl hl
> Email address: 2320415112(at)qq(dot)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