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 #19570: redundant double negation prevents IN-subquery pull-up and causes a slower SubPlan
Date: Wed, 22 Jul 2026 06:57:56 +0000
Message-ID: <19570-ce74757b8bb2eb9c@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19570
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 the identity `NOT NOT P = P`, which is valid under SQL
three-valued logic because double negation preserves TRUE, FALSE, and NULL.
When `P` is an `IN` subquery, PostgreSQL chooses different plans for the
equivalent forms.

### Expected Behaviour

PostgreSQL should remove double negation before subquery planning and
produce the same semijoin plan as the unwrapped `IN` predicate.

### Actual Behaviour

The plain predicate is pulled up into a `Hash Semi Join`. The double-negated
form remains a hashed `SubPlan` evaluated by an outer sequential scan. In
the standalone case, execution time increases from 7.143 ms to 12.321 ms,
approximately 1.72x.

Generated pair 3137 exhibits a larger order-stable instance: median
execution time increases from 2.255 ms to 404.978 ms, approximately 178.6x.

## 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 double-negated form NOT NOT P.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, TIMING OFF)
SELECT COUNT(*)
FROM identity_outer AS o
WHERE NOT NOT (
    o.v IN (SELECT i.v FROM identity_inner AS i)
);
```

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

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

NOT NOT P:
  Seq Scan on identity_outer
    Filter: ANY (... hashed SubPlan 1 ...)
  Execution Time: 12.321 ms
```

## Assessment

Message-ID: <19570-ce74757b8bb2eb9c@postgresql.org>
Permalink:  ../19570-ce74757b8bb2eb9c@postgresql.org/
Also on:    postgresql.org/message-id/19570-ce74757b8bb2eb9c@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 #19570: redundant double negation prevents IN-subquery pull-up and causes a slower SubPlan
  In-Reply-To: <19570-ce74757b8bb2eb9c@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