Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19571: Semantically redundant OR FALSE prevents IN-subquery
 pull-up and causes a slower 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: Wed, 22 Jul 2026 06:59:35 +0000
Message-ID: <19571-335e2c79294a2252@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19571-335e2c79294a2252%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19571
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 the Boolean identity `P OR FALSE =3D P`. The equivalence
remains valid under SQL three-valued logic, including when `P` evaluates to
NULL. When `P` is an `IN` subquery, PostgreSQL nevertheless optimizes the
two forms differently.

### Expected Behaviour

PostgreSQL should simplify `P OR FALSE` early enough that both forms are
planned identically. In particular, the redundant constant should not
prevent an `IN` subquery from being pulled up into a semijoin.

### Actual Behaviour

The plain `IN` predicate becomes a `Hash Semi Join`. Wrapping the same
predicate in `OR FALSE` leaves it as a hashed `SubPlan` attached to an outer
sequential scan. In the standalone case, execution time increases from 7.143
ms to 13.810 ms, approximately 1.93x.

The generated pair 837 shows a much larger order-stable instance: median
time increases from 2.750 ms to 668.339 ms, approximately 243.9x.

## How to repeat

```sql
DROP TABLE IF EXISTS identity_outer;
DROP TABLE IF EXISTS identity_inner;

CREATE TABLE identity_outer (v INTEGER NOT NULL);
CREATE TABLE identity_inner (v INTEGER NOT NULL);

INSERT INTO identity_outer
SELECT g FROM generate_series(90001, 91000) AS g;

INSERT INTO identity_inner
SELECT g FROM generate_series(1, 100000) AS g;

ANALYZE identity_outer;
ANALYZE identity_inner;

-- Original form P.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM identity_outer AS o
WHERE o.v IN (
    SELECT i.v FROM identity_inner AS i
);

-- Equivalent form P OR FALSE.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM identity_outer AS o
WHERE (
    o.v IN (SELECT i.v FROM identity_inner AS i)
) OR FALSE;
```

Both queries return 1,000. Characteristic plans and measured times are:

```text
P:
  Hash Semi Join
  Execution Time: 7.143 ms

P OR FALSE:
  Seq Scan on identity_outer
    Filter: ANY (... hashed SubPlan 1 ...)
  Execution Time: 13.810 ms
```