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 #19563: Planner does not eliminate redundant outer DISTINCT over UNION
Date: Tue, 21 Jul 2026 10:46:44 +0000
Message-ID: <19563-444007e4ab7c1292@postgresql.org> (raw)

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:        

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 <= 800
UNION
SELECT a
FROM outer_distinct_union_t
WHERE a >= 200;

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

view thread (2+ messages)  latest in thread

Message-ID: <19563-444007e4ab7c1292@postgresql.org>
Permalink:  ../19563-444007e4ab7c1292@postgresql.org/
Also on:    postgresql.org/message-id/19563-444007e4ab7c1292@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 #19563: Planner does not eliminate redundant outer DISTINCT over UNION
  In-Reply-To: <19563-444007e4ab7c1292@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