Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19565: Duplicating an equivalent ANY predicate changes a
 semijoin into a slower per-row SubPlan
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 2320415112@qq.com
Reply-To: 2320415112@qq.com, pgsql-bugs@lists.postgresql.org
Date: Tue, 21 Jul 2026 14:41:10 +0000
Message-ID: <19565-6010d3d795be6e38@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19565-6010d3d795be6e38%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19565
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:       =20

## Description

This issue concerns an idempotent Boolean rewrite such as `P OR P`, which is
logically equivalent to `P`. PostgreSQL simplifies the displayed filter to a
single predicate, but the redundant SQL form can still receive a different
and slower execution strategy. Three independently generated cases exhibited
the same order-stable slowdown.

### Expected Behaviour

PostgreSQL should canonicalize an idempotent Boolean expression before
selecting the access and join strategy. `P` and `P OR P` should therefore
produce equivalent plans and comparable execution times, particularly when
`P` contains an uncorrelated `ANY` subquery.

### Actual Behaviour

In the standalone case below, the original predicate uses a `Nested Loop
Semi Join`. After the same predicate is duplicated with `OR`, PostgreSQL
uses a sequential scan with a per-row materialized `SubPlan`. Although the
final plan displays only one copy of the predicate, execution time increases
from 3,577.467 ms to 4,157.191 ms, approximately 16.2%.


All three remained slower in both execution orders (`order_stable: True`).

## How to repeat

Run the following complete SQL in a new PostgreSQL session:

```sql
DROP TABLE IF EXISTS duplicate_operand_outer;
DROP TABLE IF EXISTS duplicate_operand_inner;

CREATE TABLE duplicate_operand_outer (
    v INTEGER NOT NULL
);

CREATE TABLE duplicate_operand_inner (
    v INTEGER NOT NULL
);

INSERT INTO duplicate_operand_outer (v)
SELECT 1
FROM generate_series(1, 1000);

INSERT INTO duplicate_operand_inner (v)
SELECT 1
FROM generate_series(1, 100000);

ANALYZE duplicate_operand_outer;
ANALYZE duplicate_operand_inner;

-- Original predicate P. The result is 0.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM duplicate_operand_outer AS o
WHERE o.v <> ANY (
    SELECT i.v
    FROM duplicate_operand_inner AS i
);

-- Idempotent rewrite P OR P. The result is also 0.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM duplicate_operand_outer AS o
WHERE o.v <> ANY (
    SELECT i.v
    FROM duplicate_operand_inner AS i
)
OR o.v <> ANY (
    SELECT i.v
    FROM duplicate_operand_inner AS i
);
```

On the tested version, the characteristic plans and timings are:

```text
P:
  Nested Loop Semi Join
  Execution Time: 3577.467 ms

P OR P:
  Seq Scan on duplicate_operand_outer
  Filter: ANY (... SubPlan 1 ...)
  Execution Time: 4157.191 ms
```