redundant double negation prevents IN-subquery pull-up and causes a slower SubPlan
## 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
```
On Mon, 17 Aug 2026 at 20:16, 陈列行 <2320415112@qq.com> wrote:
### Expected Behaviour
PostgreSQL should remove double negation before subquery planning and
produce the same semijoin plan as the unwrapped `IN` predicate.
This seems to be a duplicate of what you reported in bug #19570.
Just to make you aware, we don't claim to have implemented every
possible optimisation in the query planner. If you want to work on any
of the optimisations you've mentioned in the set of emails you just
sent, please feel free. For this particular optimisation, I really
doubt we'd ever do anything for that one case unless it happened to
become optimised as a side-effect of something larger and more useful
project. So, I don't suggest you work on such a narrow optimisation
with the expectation that it would be accepted in that narrow form.
I'd probably classify this one as more useful to keep so that someone
can prevent the planner from choosing a Semi Join plan. It seems akin
to the "col + 0" trick to prevent index or statistics usage on col.
David
David Rowley <dgrowleyml@gmail.com> writes:
Just to make you aware, we don't claim to have implemented every
possible optimisation in the query planner. If you want to work on any
of the optimisations you've mentioned in the set of emails you just
sent, please feel free. For this particular optimisation, I really
doubt we'd ever do anything for that one case unless it happened to
become optimised as a side-effect of something larger and more useful
project.
To expand on what David said: every time we think about adding a new
optimization rule to the planner, we have to weigh the potential
benefit against the cost of making the checks (not to mention the
engineering effort to create and then maintain the code). The benefit
will accrue to some subset of queries, whereas the cost will be paid
by a larger set wherein we make some tests and find the change doesn't
apply. So it's going to be hard to convince us to take a patch if the
benefit only applies to queries that are visibly badly written.
I'd probably classify this one as more useful to keep so that someone
can prevent the planner from choosing a Semi Join plan. It seems akin
to the "col + 0" trick to prevent index or statistics usage on col.
Yeah, there's also that. Somebody could be deliberately using a
pattern like this to prevent optimization, for reasons that seem
good to them.
regards, tom lane