Re: 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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19567
> Logged by: cl hl
> Email address: 2320415112(at)qq(dot)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.com

Thom