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 #19564: Semantically redundant DISTINCT in an IN subquery changes the join strategy and improves execution t
Date: Tue, 21 Jul 2026 14:30:47 +0000
Message-ID: <19564-f9f1c827f56c1261@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19564
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 two queries that differ only by an explicit `DISTINCT`
inside an `IN` subquery. Duplicate values from an `IN` subquery cannot
change membership semantics, so the two forms are logically equivalent.
Despite this, PostgreSQL selects substantially different execution plans,
and the form containing the redundant `DISTINCT` is approximately 20 times
faster.

### Expected Behaviour

PostgreSQL should recognize the duplicate-insensitive semantics of `IN` and
consider the same efficient deduplicated or hashed semijoin strategies
whether or not `DISTINCT` is written explicitly. The redundant keyword
should not be required to obtain the better plan, and the two equivalent
forms should have comparable execution time.

### Actual Behaviour

The query without `DISTINCT` uses a `Nested Loop Semi Join`. Adding
`DISTINCT` introduces `Sort -> Unique -> Hash` on the subquery result and
changes the top-level operation to a `Hash Join`. This plan change reduces
median execution time from 30.720 ms to 1.529 ms, a 20.0874x improvement.

The difference remains present in both execution orders:

| Measurement | Without DISTINCT | With DISTINCT |
|---|---:|---:|
| Average | 30.669 ms | 1.427 ms |
| Median | 30.720 ms | 1.529 ms |
| Original executed first | 18.5066x slower | baseline |
| DISTINCT executed first | 31.9321x slower | baseline |

The benchmark classified the result as order-stable. This is a query-plan
quality/performance issue, not a result-correctness issue.

## How to repeat

Run the following standalone SQL in a new PostgreSQL session. It creates all
required objects and data, refreshes statistics, and executes both
equivalent queries with runtime instrumentation.

```sql
DROP TABLE IF EXISTS distinct_mre_outer;
DROP TABLE IF EXISTS distinct_mre_inner;

CREATE TABLE distinct_mre_outer (
    v INTEGER NOT NULL
);

CREATE TABLE distinct_mre_inner (
    a INTEGER NOT NULL,
    b INTEGER NOT NULL,
    v INTEGER NOT NULL
);

-- None of these values occur in the inner relation. This makes the semijoin
-- inspect its complete inner input for every outer row.
INSERT INTO distinct_mre_outer (v)
SELECT 1000000 + g
FROM generate_series(1, 1000) AS g;

-- a and b are perfectly correlated. The predicate a=1 AND b=1 returns 1,000
-- rows, although single-column statistics estimate approximately one row.
INSERT INTO distinct_mre_inner (a, b, v)
SELECT g % 1000, g % 1000, g
FROM generate_series(1, 1000000) AS g;

ANALYZE distinct_mre_outer;
ANALYZE distinct_mre_inner;

-- Original form: DISTINCT is absent because IN is duplicate-insensitive.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM distinct_mre_outer AS o
WHERE o.v IN (
    SELECT i.v
    FROM distinct_mre_inner AS i
    WHERE i.a = 1 AND i.b = 1
);

-- Semantically equivalent form with redundant DISTINCT.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM distinct_mre_outer AS o
WHERE o.v IN (
    SELECT DISTINCT i.v
    FROM distinct_mre_inner AS i
    WHERE i.a = 1 AND i.b = 1
);
```

Both queries return `0`. On PostgreSQL 17.10 in the tested container, the
first query produced:

```text
Nested Loop Semi Join
Rows Removed by Join Filter: 1000000
Execution Time: 40.369 ms
```

The query containing redundant `DISTINCT` produced:

```text
Hash Join
  -> Hash
       -> Unique
Execution Time: 10.849 ms
```

The minimized case is therefore approximately 3.72x faster with the
redundant `DISTINCT`. Exact timings vary by host, but the plan difference is
deterministic with the tested version and statistics.

Message-ID: <19564-f9f1c827f56c1261@postgresql.org>
Permalink:  ../19564-f9f1c827f56c1261@postgresql.org/
Also on:    postgresql.org/message-id/19564-f9f1c827f56c1261@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 #19564: Semantically redundant DISTINCT in an IN subquery changes the join strategy and improves execution t
  In-Reply-To: <19564-f9f1c827f56c1261@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