BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION

On Wed, 22 Jul 2026 at 11:04, PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19567
Logged by: cl hl
Email address: 2320415112@qq.com
PostgreSQL version: 17.10
Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
Description:

## Description

This issue concerns an outer `DISTINCT` applied to a non-`ALL` `UNION`.
`UNION` already returns duplicate-free rows, so the outer operation cannot
alter the result. PostgreSQL nevertheless executes a second full aggregation
over the union output.

### Expected Behaviour

PostgreSQL should propagate the uniqueness guarantee from `UNION` and remove
the outer `DISTINCT`. Both SQL forms should require only the deduplication
performed by `UNION` itself.

### Actual Behaviour

The outer `DISTINCT` adds a second `HashAggregate`. With two 500,000-row
inputs and 750,000 union rows, five-run median execution time increased from
236.234 ms to 420.470 ms. The redundant form is approximately 78.0% slower,
or 1.78x the execution time.

## How to repeat

```sql
DROP TABLE IF EXISTS union_distinct_lhs;
DROP TABLE IF EXISTS union_distinct_rhs;

CREATE TABLE union_distinct_lhs (v INTEGER NOT NULL);
CREATE TABLE union_distinct_rhs (v INTEGER NOT NULL);

INSERT INTO union_distinct_lhs
SELECT g FROM generate_series(1, 500000) AS g;

INSERT INTO union_distinct_rhs
SELECT g FROM generate_series(250001, 750000) AS g;

ANALYZE union_distinct_lhs;
ANALYZE union_distinct_rhs;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT DISTINCT v
FROM (
SELECT v FROM union_distinct_lhs
UNION
SELECT v FROM union_distinct_rhs
) AS set_result;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT v
FROM (
SELECT v FROM union_distinct_lhs
UNION
SELECT v FROM union_distinct_rhs
) AS set_result;
```

Both queries return the same 750,000 rows. The characteristic plans are:

```text
with DISTINCT: HashAggregate -> HashAggregate -> Append
without: HashAggregate -> Append
```

This doesn't look like a bug, but rather an enhancement request. This
is probably related to the UniqueKeys work previously discussed in the
community: /messages/by-id/CAKU4AWrwZMAL=uaFUDMf4WGOVkEL3ONbatqju9nSXTUucpp_pw@mail.gmail.com

Thom

Thom Brown <thom@linux.com> writes:

This doesn't look like a bug, but rather an enhancement request.

Yeah, not one of these are bugs. Some of them might be missed
optimization opportunities. But you'd need to show that the extra
planning cost of detecting such cases is worthwhile, ie there's
a reasonably large fraction of real-world queries that would
benefit materially.

regards, tom lane