## 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## 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. ``` 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.
