agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19563: Planner does not eliminate redundant outer DISTINCT over UNION
2+ messages / 2 participants
[nested] [flat]
* BUG #19563: Planner does not eliminate redundant outer DISTINCT over UNION
@ 2026-07-21 10:46 PG Bug reporting form <noreply@postgresql.org>
2026-07-21 14:28 ` Re: BUG #19563: Planner does not eliminate redundant outer DISTINCT over UNION Vik Fearing <vik@postgresfriends.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-07-21 10:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 2320415112@qq.com
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;
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19563: Planner does not eliminate redundant outer DISTINCT over UNION
2026-07-21 10:46 BUG #19563: Planner does not eliminate redundant outer DISTINCT over UNION PG Bug reporting form <noreply@postgresql.org>
@ 2026-07-21 14:28 ` Vik Fearing <vik@postgresfriends.org>
0 siblings, 0 replies; 2+ messages in thread
From: Vik Fearing @ 2026-07-21 14:28 UTC (permalink / raw)
To: 2320415112@qq.com; pgsql-bugs@lists.postgresql.org
On 21/07/2026 12:46, PG Bug reporting form wrote:
> 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.
Funny this should come up, I have recently been working on improvements
for DISTINCT tracking. Nothing ready to publish yet, but at least one
other person noticed this missed optimization, and others like it.
--
Vik Fearing
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-21 14:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-21 10:46 BUG #19563: Planner does not eliminate redundant outer DISTINCT over UNION PG Bug reporting form <noreply@postgresql.org>
2026-07-21 14:28 ` Vik Fearing <vik@postgresfriends.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox