agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION
3+ messages / 3 participants
[nested] [flat]
* BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION
@ 2026-07-22 06:49 PG Bug reporting form <noreply@postgresql.org>
2026-07-22 10:38 ` Re: BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION Thom Brown <thom@linux.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-07-22 06:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 2320415112@qq.com
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
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION
2026-07-22 06:49 BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION PG Bug reporting form <noreply@postgresql.org>
@ 2026-07-22 10:38 ` Thom Brown <thom@linux.com>
2026-07-22 11:34 ` Re: BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Thom Brown @ 2026-07-22 10:38 UTC (permalink / raw)
To: 2320415112@qq.com; pgsql-bugs@lists.postgresql.org
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: https://www.postgresql.org/message-id/CAKU4AWrwZMAL=uaFUDMf4WGOVkEL3ONbatqju9nSXTUucpp_pw@mail.gmail...
Thom
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION
2026-07-22 06:49 BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION PG Bug reporting form <noreply@postgresql.org>
2026-07-22 10:38 ` Re: BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION Thom Brown <thom@linux.com>
@ 2026-07-22 11:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-07-22 11:34 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: 2320415112@qq.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-07-22 11:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-22 06:49 BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION PG Bug reporting form <noreply@postgresql.org>
2026-07-22 10:38 ` Thom Brown <thom@linux.com>
2026-07-22 11:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox