Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19563: Planner does not eliminate redundant outer DISTINCT over
 UNION
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: Tue, 21 Jul 2026 10:46:44 +0000
Message-ID: <19563-444007e4ab7c1292@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19563-444007e4ab7c1292%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19563
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:       =20

PostgreSQL keeps an extra duplicate-removal operator for a redundant outer
`DISTINCT` over a `UNION` query.

`UNION` already returns a distinct set of rows. Therefore, the following
transformation is semantically redundant:

```sql
SELECT a FROM t WHERE ...
UNION
SELECT a FROM t WHERE ...
```

to:

```sql
SELECT DISTINCT *
FROM (
  SELECT a FROM t WHERE ...
  UNION
  SELECT a FROM t WHERE ...
) AS u;
```

However, PostgreSQL plans the mutated query with two `HashAggregate` nodes:
one for the `UNION` duplicate elimination and another for the outer
`DISTINCT`. The outer `HashAggregate` should be removable because its input
is already distinct on the same output columns.

How to repeat


```sql
DROP TABLE IF EXISTS outer_distinct_union_t;

CREATE TABLE outer_distinct_union_t (
  a INT PRIMARY KEY,
  b INT
);

INSERT INTO outer_distinct_union_t
SELECT i, i % 10
FROM generate_series(1, 1000) AS g(i);

ANALYZE outer_distinct_union_t;

-- Original query.
-- UNION already removes duplicates.
EXPLAIN
SELECT a
FROM outer_distinct_union_t
WHERE a <=3D 800
UNION
SELECT a
FROM outer_distinct_union_t
WHERE a >=3D 200;

-- Mutated query.
-- The outer DISTINCT is redundant.
EXPLAIN
SELECT DISTINCT *
FROM (
  SELECT a
  FROM outer_distinct_union_t
  WHERE a <=3D 800
  UNION
  SELECT a
  FROM outer_distinct_union_t
  WHERE a >=3D 200
) AS u;
```