Postgres 19 makes NOT IN 10,000x faster... or 2,000x slower

Which one you get depends on a NOT NULL in your schema, and on how long the list is.

Summary for the Impatient

PostgreSQL 19 rewrites x NOT IN (SELECT ...) into the same anti join that NOT EXISTS gets, as long as both columns are declared NOT NULL. A query for a tenant's first 10 subscribed contacts, skipping anyone emailed in the past year with that year's sends as the NOT IN list, went from about 1.1 seconds on PG18 to 0.06 ms on PG19. The condition lives in the declaration, not the data: a nullable column with zero NULLs in it keeps the old plan, and IS NOT NULL inside the subquery brings the rewrite back, though the same filter on the outer column doesn't. When PG19 can't rewrite a NOT IN, it stops hashing the list at 154,199 rows, where PG17 and PG18 keep hashing up to 262,144. At that size, a NOT IN over a list of banned customers ran about 2,300 times slower on PG19 than on PG18. The cause is a separate PG19 change to how the planner estimates hash table sizes, Tom Lane's 1ea5bdb00bf, and the release notes don't mention it. Every result was correct on all three versions, for the int columns, built-in = and uncast subqueries that were tested. Measured on 17.11, 18.6 and 19rc2 (19rc1 agrees), on one laptop at default settings, with ExoBench running locally and an AI agent designing the measurements.

Does this hit you?

  • You write NOT IN against a subquery.
  • A column on either side is declared nullable, even if it never holds a NULL. If your DDL is generated from JPA mappings, @JoinColumn defaults to nullable = true.
  • The planner expects the subquery to return between 154,199 and 262,144 rows. That's for an int key at the default work_mem; a bigint works out the same, and more hash memory moves both edges.

If all three are true, PG19 runs your NOT IN on the plan PG17 and PG18 save for longer lists. How much slower that is depends on how many rows have to reread the list: about 2,300 times in the banned-customers test below and more than five times on the contacts query, both against PG18.

My "Contribution" to Relational Theory

Back in my junior days, when MSSQL 2012 or thereabouts was fresh off the presses, I had a NOT EXISTS query that took 20 seconds. I considered myself far too clever a developer to put up with that, so I banged my head on it for a while and came up with something the guy before me hadn't thought of: LEFT JOIN the other table, and keep the rows where the joined column comes back NULL.

It worked. I had, in my own estimation, invented a groundbreaking, paradigm-shifting query optimization technique.

Turns out it's called an anti join, and thousands of people had invented it before me. It has a symbol in relational algebra, a canonically defined operator, and enough Stack Overflow answers to have saved me the headache, had I read any of them.

The people who build databases have spent years trying to make it automatic, so nobody would have to invent it again. Postgres has planned NOT EXISTS as an anti join on its own since 2009, and PostgreSQL developers have been posting patches to do the same for NOT IN since at least 2014. Postgres 19 is the first version that finally figured out all the kinks.

Don't Ever use NOT IN

It turns out that NOT IN is even worse than NOT EXISTS. The PostgreSQL wiki in fact has a whole section in its Don't Do This devoted to this. Two reasons are given. NULLs make it return surprising results, and "the planner can't transform it into an anti-join", so it falls apart as the data grows.

What's the advice? Use NOT EXISTS instead... I find that ironic!

Fast forwarding to the PostgreSQL 19 release notes and you get this wonderful tidbit:

Allow NOT IN clauses to be converted to more efficient ANTI JOINs when NULLs are not present (Richard Guo)

So I wanted to know what "when NULLs are not present" means in practice, whether the conversion is correct, and what happens on the queries it doesn't convert. I gave those questions to an AI agent with ExoBench as its only way to touch a database, three Postgres versions side by side (17.11, 18.6 and 19rc2), and let it design the measurements.

One Banned Customer Too Many

Before it got anywhere near PG19, the task made the agent check the folklore, which says PG17 and PG18 never turn NOT IN into a join. They keep it as a filter on the outer table, with the subquery as a SubPlan: hashed, when the planner expects the list to fit in hash memory, and otherwise a plain SubPlan, which keeps a copy of the list and reads through it again for every outer row.

The test was dull on purpose, a table of orders and a growing list of banned customers:

CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;

SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned);

Ten thousand orders, one customer each, every multiple of 3 banned, and the list growing from 100,000 ids to 500,000. Even the shortest list covers every banned order, so the answer never changes: 6,667 orders kept at every size.

Hash memory is work_mem times hash_mem_multiplier, and the agent checked both on all three servers before it timed anything: 4MB and 2, the defaults, so 8 MiB (the settings calls are in the method appendix). Then it looked up the planner's sizing rule through web search, worked out that PG17 and PG18 should stop hashing after 262,144 ids, and put its sizes one row either side of that.

262,144 banned ids: under a tenth of a second.

262,145 banned ids: nearly three minutes.

One more banned customer. Here are PG18's two plans, trimmed to the lines that matter:

--- 262,144 banned ids, 75.690 ms ---
Aggregate
  ->  Seq Scan on orders
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        SubPlan 1
          ->  Seq Scan on banned
--- 262,145 banned ids, 165,878.249 ms ---
Aggregate
  ->  Seq Scan on orders
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        SubPlan 1
          ->  Materialize  (... loops=10000)
                ->  Seq Scan on banned

The two filter lines differ by the word hashed. With it, PG18 builds a hash table from the list once and looks each order up in it. Without it, PG18 keeps the list in a Materialize node, on disk in temp files here, and walks it again for each order; the Materialize's loops=10000 counts the walks. PG17 runs the same two plans.

That one row is a cliff, a jump of about 2,200 times on PG18, and PG17 drops off at exactly the same row, about 1,800 times (PG17's call is in the ban-list appendix). PG18's calls, verbatim:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_181.31s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K24.1 ms
At 100,000 banned ids PG18 runs the NOT IN as a hashed SubPlan, not an anti join, even with both columns declared NOT NULL. One execution, 24.079 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=26
Planning Time: 0.132 ms
Execution Time: 24.079 ms
0.24s
10K / 154.2K#142.7 ms
At 154,198 banned ids PG18 still hashes the list from one Seq Scan of banned. One execution, 42.746 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.125 ms
Execution Time: 42.746 ms
0.33s
10K / 154.2K#243.3 ms
At 154,199 banned ids the plan does not change on PG18: the same hashed SubPlan. One execution, 43.270 ms.
QUERY PLAN
Aggregate  (cost=2815.49..2815.50 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.49..2790.49 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.107 ms
Execution Time: 43.270 ms
0.31s
10K / 262.1K75.7 ms
At 262,144 banned ids, one row short of its cliff, PG18 still hashes, reading 1,215 shared buffers. One execution, 75.690 ms.
QUERY PLAN
Aggregate  (cost=4641.80..4641.81 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Seq Scan on orders  (cost=4436.80..4616.80 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
                Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.128 ms
Execution Time: 75.690 ms
0.44s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_18507.58s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 262.1K165.9s
At 262,145 banned ids, one more than the largest list it hashes, PG18 switches to a plain SubPlan over a Materialize stored on disk and rescanned 10,000 times. One execution, 165,878.249 ms.
QUERY PLAN
Aggregate  (cost=33862892.50..33862892.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215, temp read=3003672 written=449
  ->  Seq Scan on orders  (cost=0.00..33862867.50 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=3003672 written=449
        SubPlan 1
          ->  Materialize  (cost=0.00..6117.18 rows=262145 width=4) (actual rows=175327.68 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=1160, temp read=3003672 written=449
                ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145.00 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.114 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 165878.249 ms
166.23s
10K / 500K340.7s
At 500,000 banned ids the plain SubPlan rescans 333,905.61 stored ids per pass on average from its disk-backed Materialize. One execution, 340,746.169 ms.
QUERY PLAN
Aggregate  (cost=64585205.00..64585205.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=2268, temp read=5710068 written=855
  ->  Seq Scan on orders  (cost=0.00..64585180.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=2268, temp read=5710068 written=855
        SubPlan 1
          ->  Materialize  (cost=0.00..11667.00 rows=500000 width=4) (actual rows=333905.61 loops=10000)
                Storage: Disk  Maximum Storage: 6836kB
                Buffers: shared hit=2213, temp read=5710068 written=855
                ->  Seq Scan on banned  (cost=0.00..7213.00 rows=500000 width=4) (actual rows=500000.00 loops=1)
                      Buffers: shared hit=2213
Planning:
  Buffers: shared hit=26
Planning Time: 0.167 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 340746.169 ms
341.35s

Score one for the folklore: it was right to the row. PG17 and PG18 charge a flat 32 bytes for every int in the list, 262,144 of them come to exactly 8 MiB, and one more puts the estimate over (the code is in the hash-size appendix).

NOT EXISTS, by the way, has no such row. Written that way, PG18 planned the same query as an anti join and finished the 262,145-id list in 26.699 ms, and PG17 did the same (both cards are in the ban-list appendix too).

Postgres 19 Does the Rewrite for You

The wiki's advice for NOT IN was to write NOT EXISTS instead. PG19 now does that rewrite for you, but only when it can prove that neither side of the comparison can be NULL. It's Richard Guo's work, and the idea is at least as old as David Rowley's patch from 2014.

On the ban list with both columns NOT NULL, PG19 planned the NOT IN as a Hash Right Anti Join at every size from 100,000 ids to 500,000, the plan NOT EXISTS gets. It hashes the 10,000 orders instead of the list and streams the list past them, so the length of the list no longer decides the plan. Over 10 runs at two sizes, NOT IN and NOT EXISTS had the same plan costs and the same buffers, and their times were within a few percent of each other (the ban-list appendix has the cards).

The other test was a campaign-preview query, in the shape a jOOQ app writes it: the first 10 subscribed contacts of a tenant that haven't been emailed in the last year, against 2,000,000 generated sends. The NOT IN part:

  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')

For tenant 1, it took about 1.1 seconds on PG18.

On PG19 it took 0.06 ms.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1825.84s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M1.108 ±0.057 s
PG18 runs the same plan shape as PG17: a plain SubPlan over a Materialize of the 659,439 matching sends rows, stored on disk at 9,016 kB and rescanned 12 times per run on average. Mean Execution Time over 10 runs is 1,108 ms, stddev 57.16 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.85..374566.94 avg_rows=10 avg_width=26) (actual avg_time=280.724..1106.140 stddev_time=51.887..57.159 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=3256, temp avg_read=10148 avg_written=1127
  Disabled: false
  ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.85..333551105.44 avg_rows=8905 avg_width=26) (actual avg_time=275.674..1101.083 stddev_time=51.511..56.872 avg_rows=10 avg_loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan 1).col1))))
        Rows Removed by Filter: avg=3
        Buffers: shared avg_hit=3256, temp avg_read=10148 avg_written=1127
        Disabled: false
        Index Searches: avg=1
        SubPlan 1
          ->  Materialize  (avg_cost=0.43..32020.13 avg_rows=657268 avg_width=4) (actual avg_time=0.006..57.908 stddev_time=0.001..3.794 avg_rows=550586.92 avg_loops=12)
                Storage: Disk  Maximum Storage: avg=9016kB
                Buffers: shared avg_hit=3252, temp avg_read=10148 avg_written=1127
                Disabled: false
                ->  Index Only Scan using sends_tenant_sent on sends s  (avg_cost=0.43..26165.79 avg_rows=657268 avg_width=4) (actual avg_time=0.031..87.993 stddev_time=0.010..13.204 avg_rows=659439 avg_loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
                      Heap Fetches: avg=0
                      Buffers: shared avg_hit=3252
                      Disabled: false
                      Index Searches: avg=1
Planning Time: avg=0.14 ms stddev=0.02 n=10 samples=0.16,0.11,0.13,0.12,0.12,0.15,0.15,0.11,0.16,0.19
JIT:
  Functions: avg=12
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation avg=0.000 ms, Inlining avg=0.000 ms, Optimization avg=0.261 ms stddev=0.050 ms, Emission avg=4.818 ms stddev=0.466 ms, Total avg=5.702 ms stddev=0.561 ms
Execution Time: avg=1108 ms stddev=57.16 n=10 samples=1245.9,1048.48,1123.52,1115.45,1063.89,1095.79,1085.38,1142.59,1055.79,1103.25
25.84s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1911.69s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M0.06 ±0.02 ms
19rc2 rewrites the NOT IN into a Nested Loop Anti Join: it walks tenant 1's subscribed contacts in id order and probes the sends_contact index once per contact, 12 probes per run on average, until the LIMIT 10 is met. It reads 42 shared buffers per run, and the mean Execution Time over 10 runs is 0.06 ms, stddev 0.02 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.84..9.46 avg_rows=10 avg_width=26) (actual avg_time=0.026..0.044 stddev_time=0.007..0.016 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=42
  Disabled: false
  ->  Nested Loop Anti Join  (avg_cost=0.84..13470.56 avg_rows=15630 avg_width=26) (actual avg_time=0.026..0.043 stddev_time=0.007..0.016 avg_rows=10 avg_loops=1)
        Buffers: shared avg_hit=42
        Disabled: false
        ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.42..2530.02 avg_rows=18255 avg_width=26) (actual avg_time=0.017..0.020 stddev_time=0.005..0.006 avg_rows=12 avg_loops=1)
              Index Cond: (tenant_id = 1)
              Filter: (status = 'subscribed'::text)
              Rows Removed by Filter: avg=1
              Buffers: shared avg_hit=4
              Disabled: false
              Index Searches: avg=1
        ->  Index Scan using sends_contact on sends s  (avg_cost=0.43..6.67 avg_rows=42 avg_width=4) (actual avg_time=0.002..0.002 stddev_time=0.001..0.001 avg_rows=0.17 avg_loops=12)
              Index Cond: (contact_id = c.id)
              Filter: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
              Buffers: shared avg_hit=38
              Disabled: false
              Index Searches: avg=12
Planning:
  Buffers: shared avg_hit=14
Planning Time: avg=0.2 ms stddev=0.04 n=10 samples=0.22,0.24,0.16,0.16,0.24,0.2,0.19,0.17,0.28,0.17
Execution Time: avg=0.06 ms stddev=0.02 n=10 samples=0.1,0.06,0.05,0.05,0.08,0.05,0.07,0.05,0.08,0.05
11.69s

Both are 10-run means under EXPLAIN (ANALYZE, BUFFERS), which times every plan node. Dividing one by the other makes PG19 about 18,000 times faster but per-node timing slows PG18's plan down to 10,000x so the latter number gets the red-carpet treatment. (If you're curious, the confessions at the end of the article explain it further). For now, here are the two plans, trimmed:

--- Postgres 18.6, avg 1,108 ms ---
Limit
  ->  Index Scan on contacts c
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan 1).col1))))
        SubPlan 1
          ->  Materialize  (... avg_loops=12)
                ->  Index Only Scan on sends s  (... avg_rows=659439)
--- Postgres 19rc2, avg 0.06 ms ---
Limit
  ->  Nested Loop Anti Join
        ->  Index Scan on contacts c
        ->  Index Scan using sends_contact on sends s  (... avg_loops=12)
              Index Cond: (contact_id = c.id)

PG18 copies the contact id of every send that tenant 1 made that year into a Materialize, then walks tenant 1's contacts in id order and runs down that copy for each one it considers, until it has 10 that aren't on it. PG19 walks the same contacts and asks the sends_contact index about each one, a single probe where PG18 makes a pass over the whole year. PG17 runs the same latter plan, at about 1.3 seconds. Written as NOT EXISTS, the query was already fast before PG19: 0.09 ms on PG18 and 0.08 ms on PG17. All three runs are in the campaign-query appendix, with the schema and the full query.

This is really neat: the wiki's workaround, applied by the planner, to SQL behind the scenes.

At 1,000,000 orders against 300,000 ids, PG19 ran the NOT IN in 145ms, while PG17 and PG18, rereading the list once per order, would need about two and a quarter hours each for every query... that's a long time to wait for an API call!

NOT EXISTS had no trouble on either: at the same size PG17 ran it in 146.988 ms and PG18 in 143.636 ms, with the same Parallel Hash Anti Join.

Where PG17 and PG18 still hash, the gain is smaller. At a million orders and 262,144 ids PG19's anti join averaged 140.46 ms over 10 runs. Their hashed SubPlan averaged 230.75 ms on PG17 and 217.72 ms on PG18. The million-order appendix has the runs and the cancellations.

Nullability means in the schema... mostly

Delete the NOT NULL from the campaign schema, so contact_id INT NOT NULL becomes contact_id INT, and PG19 stops rewriting. Not one of the 2,000,000 sends has a NULL contact, and the planner's own statistics put the NULL fraction at zero (the data check is quoted in the campaign-query appendix). PG19 runs the old plain SubPlan anyway, at 801.19 ms.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1920.48s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M801 ±24 ms
With sends.contact_id declared nullable, 19rc2 does not rewrite the NOT IN and runs the old plain SubPlan: a Materialize of the 659,439 matching sends rows, stored on disk at 9,016 kB and rescanned 12 times per run on average. Mean Execution Time over 10 runs is 801.19 ms, stddev 24.21 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.85..373695.44 avg_rows=10 avg_width=26) (actual avg_time=206.355..799.837 stddev_time=9.592..24.220 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=3256, temp avg_read=10148 avg_written=1127
  Disabled: false
  ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.85..337147258.00 avg_rows=9022 avg_width=26) (actual avg_time=206.354..799.830 stddev_time=9.592..24.219 avg_rows=10 avg_loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan any_1).col1))))
        Rows Removed by Filter: avg=3
        Buffers: shared avg_hit=3256, temp avg_read=10148 avg_written=1127
        Disabled: false
        Index Searches: avg=1
        SubPlan any_1
          ->  Materialize  (avg_cost=0.43..31982.78 avg_rows=656534 avg_width=4) (actual avg_time=0.005..42.216 stddev_time=0.001..1.228 avg_rows=550586.92 avg_loops=12)
                Storage: Disk  Maximum Storage: avg=9016kB
                Buffers: shared avg_hit=3252, temp avg_read=10148 avg_written=1127
                Disabled: false
                ->  Index Only Scan using sends_tenant_sent on sends s  (avg_cost=0.43..26135.11 avg_rows=656534 avg_width=4) (actual avg_time=0.021..70.287 stddev_time=0.005..2.313 avg_rows=659439 avg_loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
                      Heap Fetches: avg=0
                      Buffers: shared avg_hit=3252
                      Disabled: false
                      Index Searches: avg=1
Planning Time: avg=0.13 ms stddev=0.02 n=10 samples=0.12,0.12,0.12,0.12,0.12,0.12,0.18,0.12,0.12,0.12
Execution Time: avg=801.19 ms stddev=24.21 n=10 samples=797.9,790.28,840.42,785.73,829.86,770.15,779.52,813.04,780.54,824.5
20.48s

A nullable column is a perfectly reasonable hedge. Declaring one costs nothing, JPA's schema generation writes one for every @JoinColumn unless the mapping marks it NOT NULL, and it leaves room for the send with no contact, which in 2,000,000 rows never once turned up.

PG19 plans from the declaration. On the subquery's side a declared NOT NULL counts, and so does a condition in its own WHERE that rules NULL out. Adding AND s.contact_id IS NOT NULL as the subquery's last condition brought the anti join back (its card is in the campaign-query appendix). PG19 even folded the filter into its index probe. Both plans, trimmed:

--- PG19 sends.contact_id nullable, avg 801.19 ms ---
Limit
  ->  Index Scan on contacts c
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan any_1).col1))))
        SubPlan any_1
          ->  Materialize
--- PG19 sends.contact_id nullable, IS NOT NULL inside the subquery, avg 0.06 ms ---
Limit
  ->  Nested Loop Anti Join
        ->  Index Scan on contacts c
        ->  Index Scan using sends_contact on sends s
              Index Cond: ((contact_id = c.id) AND (contact_id IS NOT NULL))

PG17 and PG18 never rewrite a NOT IN, so the filter changes nothing there. On the ban list it went the same way: the nullable list with IS NOT NULL inside the subquery was an anti join at every size (cards in the ban-list appendix).

The outer column is stricter. With orders.customer_id declared nullable, PG19 never rewrites the ban-list NOT IN, at any list size (ban-list appendix). The agent even tried a not-null filter on that side. No dice!

--- orders.customer_id nullable, IS NOT NULL in the outer WHERE, 262,144 banned ids, plan only ---
Aggregate
  ->  Seq Scan on orders
        Filter: ((customer_id IS NOT NULL) AND (NOT (ANY (customer_id = (SubPlan any_1).col1))))
        SubPlan any_1
          ->  Materialize

The IS NOT NULL sits right next to a SubPlan that wasn't rewritten. For the outer side, PG19 accepts only the column's declared constraint as proof, and its source says that checking the query's other conditions as well "doesn't seem worth the extra complication", for the moment (the code is in the rewrite-rules appendix).

The Cliff that Moved

Back to the ban list, now with banned.customer_id declared nullable, so PG19 keeps the NOT IN as a SubPlan the way PG17 and PG18 do. These are PG19's plans one row apart:

--- PG19 154,198 banned ids, 39.764 ms ---
Aggregate
  ->  Seq Scan on orders
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        SubPlan any_1
          ->  Seq Scan on banned
--- PG19 154,199 banned ids, 101,734.255 ms 😱 ---
Aggregate
  ->  Seq Scan on orders
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        SubPlan any_1
          ->  Materialize  (... loops=10000)
                ->  Seq Scan on banned

Take a close look at that again!

That's PG18's cliff, the missing hashed and the rescan for every order, more than a hundred thousand ids early. Here's PG19 on the nullable list, and then PG18 on the same list:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_19296.59s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K24.3 ms
At 100,000 banned ids 19rc2 leaves the NOT IN as it is, since the list column is nullable, and runs the old hashed SubPlan over one Seq Scan of banned. One execution, 24.285 ms, keeping 6,667 of the orders.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan any_1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=32
Planning Time: 0.152 ms
Execution Time: 24.285 ms
0.22s
10K / 154.2K#139.8 ms
At 154,198 banned ids, the largest list 19rc2 hashes here, the Filter still reads hashed SubPlan any_1. One execution, 39.764 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan any_1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=32
Planning Time: 0.118 ms
Execution Time: 39.764 ms
0.29s
10K / 154.2K#2101.7s
One more banned id, 154,199, and 19rc2 stops hashing: the SubPlan reads a Materialize stored on disk, rescanned 10,000 times, once per order. The one execution takes 101,734.255 ms.
QUERY PLAN
Aggregate  (cost=19922617.50..19922617.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738, temp read=1770462 written=264
  ->  Seq Scan on orders  (cost=0.00..19922592.50 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738, temp read=1770462 written=264
        SubPlan any_1
          ->  Materialize  (cost=0.00..3598.98 rows=154199 width=4) (actual rows=103360.08 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=683, temp read=1770462 written=264
                ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
                      Buffers: shared hit=683
Planning:
  Buffers: shared hit=33
Planning Time: 0.200 ms
Execution Time: 101734.255 ms
101.71s
10K / 262.1K194.6s
At 262,144 banned ids, a list PG17 and PG18 still hash, 19rc2 runs the same plain SubPlan over a Materialize on disk, with 2,997,006 temp blocks read. One execution, 194,564.214 ms.
QUERY PLAN
Aggregate  (cost=33857805.00..33857805.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215, temp read=2997006 written=448
  ->  Seq Scan on orders  (cost=0.00..33857780.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=2997006 written=448
        SubPlan any_1
          ->  Materialize  (cost=0.00..6116.16 rows=262144 width=4) (actual rows=175327.02 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=1160, temp read=2997006 written=448
                ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=32
Planning Time: 0.210 ms
Execution Time: 194564.214 ms
194.37s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_181.40s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K36.5 ms
At 100,000 banned ids PG18 hashes the nullable list in a hashed SubPlan over one Seq Scan of banned. One execution, 36.507 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=26
Planning Time: 0.158 ms
Execution Time: 36.507 ms
0.35s
10K / 154.2K#144.1 ms
At 154,198 banned ids PG18 still hashes the list. One execution, 44.124 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.108 ms
Execution Time: 44.124 ms
0.33s
10K / 154.2K#244.1 ms
At 154,199 banned ids, where 19rc2 stops hashing this same list, PG18 keeps the hashed SubPlan. One execution, 44.066 ms, for the same 6,667 orders.
QUERY PLAN
Aggregate  (cost=2815.49..2815.50 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.49..2790.49 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.152 ms
Execution Time: 44.066 ms
0.29s
10K / 262.1K79.5 ms
At 262,144 banned ids, the largest list PG18 hashes, the hashed SubPlan reads 1,215 shared buffers. One execution, 79.451 ms.
QUERY PLAN
Aggregate  (cost=4641.80..4641.81 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Seq Scan on orders  (cost=4436.80..4616.80 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
                Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.139 ms
Execution Time: 79.451 ms
0.42s

At 154,199 ids, PG18 still hashes the list, in 44.066 ms, and so does PG17 (PG17's card is in the ban-list appendix). There, the same NOT IN ran about 2,300 times slower on PG19 than on PG18, and about 2,000 times slower than on PG17. Here's the whole sweep. On one linear axis everything that hashes or anti-joins lies flat along the bottom, so it gets a panel of its own, in milliseconds:

NOT IN execution time as the banned list grows, 10,000 orders, one execution per point with EXPLAIN (ANALYZE, TIMING OFF, BUFFERS): PostgreSQL 17.11, 18.6 and 19rc2 with banned.customer_id declared without NOT NULL, and 19rc2 with both columns NOT NULL.
Both panels are linear and share one x-axis drawn to scale: runs that reread the list above, in seconds, and runs that hash the list or anti-join below, in milliseconds. A dotted line is a jump from one panel to the other.
0 s100 s200 s300 s0 ms25 ms50 ms75 ms100 ms100,000154,199262,145500,000Banned ids in the NOT IN list, to scale (10,000 orders)Rereads the list for every order (seconds)Hashes the list, or anti-joins (milliseconds)+1 id: 40 ms → 102 sPG19 nullable 345 sPG17 nullable 315 sPG18 nullable 309 sPG19 NOT NULL 47 ms
With a nullable list, 19rc2 jumps to the seconds panel at 154,199 banned ids while PG17 and PG18 keep hashing through 262,144, and 19rc2's anti join on NOT NULL columns never leaves the milliseconds panel.
Execution time per point, one execution each, 10,000 orders. PG17, nullable list: 28.683 ms at 100,000 banned ids, 52.754 ms at 154,198, 51.777 ms at 154,199 and 80.391 ms at 262,144 on the hashed SubPlan, then 165,261.775 ms at 262,145 and 314,769.094 ms at 500,000 on the plain SubPlan. PG18, nullable list: 36.507 ms, 44.124 ms, 44.066 ms and 79.451 ms hashed at those four sizes, then 162,743.662 ms at 262,145 and 309,423.976 ms at 500,000 on the plain SubPlan. PG19, nullable list: 24.285 ms at 100,000 and 39.764 ms at 154,198 hashed, then 101,734.255 ms at 154,199, 194,564.214 ms at 262,144, 182,753.052 ms at 262,145 and 345,100.858 ms at 500,000 on the plain SubPlan. PG19, both columns NOT NULL, a Hash Right Anti Join at every size: 10.729 ms, 15.605 ms, 16.598 ms, 27.021 ms, 25.135 ms and 46.841 ms.

Call the stretch from 154,199 to 262,144 banned ids the gap. In it, a NOT IN that PG19 won't rewrite rereads the list for every order, and PG17 and PG18 still hash the same list. That's for an int key at default memory.

The same cliff turned up twice more. With the orders column nullable instead of the list, PG19 hashed at 154,198 ids and switched at 154,199 (ban-list appendix). The first release candidate drew it at the same row too, about 2,300 times slower than PG18 there (rc1 appendix).

The rewrite didn't move the cliff, but a different PG19 commit did: Tom Lane's "Improve planner's estimates of tuple hash table sizes" (1ea5bdb00bf), reported by Jeff Janes and reviewed by David Rowley. PG17 and PG18 estimate a hashed list at a flat cost per row. PG19's estimate is lighter on each stored row, but it adds two things PG17 and PG18 never counted: the hash table's bucket array and, for a NOT IN, a second table for NULLs. The limit is still 8 MiB, and for 154,199 ints the new estimate comes out ten bytes over. The check is pass or fail, so ten bytes over gets the plan that rereads the list.

The new estimate is the more honest one. The commit message calls the old one "pretty far off, especially for small data widths", because it left out the hash table's own overhead. The commit just isn't in the release notes, which list the NOT IN rewrite and say nothing about this. The code and the arithmetic, to the byte, are in the hash-size appendix.

Users will trip over this

Importantly, I didn't go looking around to find this gap, my agent happened to walk into it. The first time was, on 19rc1 on Nov 12, ran the 90-day version with contact_id nullable.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1915.55s computewarm ×3×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 90 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M248 ±44 ms
19rc1 does not rewrite this NOT IN, since the column is nullable, and does not hash it either: the planner estimates 160,692 rows for the 90-day subquery, and the SubPlan reads a Materialize stored on disk, rescanned 12 times per run on average. The subquery returns 162,209 rows, and the mean Execution Time over 10 runs is 248.15 ms, stddev 43.88 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.85..91253.47 avg_rows=10 avg_width=26) (actual avg_time=62.012..247.777 stddev_time=9.859..43.866 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=807, temp avg_read=2502 avg_written=278
  Disabled: false
  ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.85..83012509.22 avg_rows=9097 avg_width=26) (actual avg_time=62.011..247.769 stddev_time=9.859..43.868 avg_rows=10 avg_loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan any_1).col1))))
        Rows Removed by Filter: avg=3
        Buffers: shared avg_hit=807, temp avg_read=2502 avg_written=278
        Disabled: false
        Index Searches: avg=1
        SubPlan any_1
          ->  Materialize  (avg_cost=0.43..7829.73 avg_rows=160692 avg_width=4) (actual avg_time=0.005..12.765 stddev_time=0.001..2.049 avg_rows=135247 avg_loops=12)
                Storage: Disk  Maximum Storage: avg=4096kB
                Buffers: shared avg_hit=803, temp avg_read=2502 avg_written=278
                Disabled: false
                ->  Index Only Scan using sends_tenant_sent on sends s  (avg_cost=0.43..6398.27 avg_rows=160692 avg_width=4) (actual avg_time=0.021..20.379 stddev_time=0.007..2.386 avg_rows=162209 avg_loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '90 days'::interval)))
                      Heap Fetches: avg=0
                      Buffers: shared avg_hit=803
                      Disabled: false
                      Index Searches: avg=1
Planning Time: avg=0.17 ms stddev=0.04 n=10 samples=0.19,0.18,0.12,0.24,0.17,0.22,0.15,0.17,0.17,0.12
Execution Time: avg=248.15 ms stddev=43.88 n=10 samples=201.53,200.18,198.43,313.82,281.41,213.56,228.21,285.18,276.31,282.9
15.55s

248.15 ms on 19rc1, where PG18 hashed the same list in 43.83 ms, more than five times faster (PG18's card is in the rc1 appendix). There's very little of this documented online so after burning an appropriate number of tokens, my agent found Tom Lane's commit itself with the following fun notes: "Predicted PG19 switch for int NOT IN: ~154,199 rows." That happened to match my ban-list query's 154,198 number quite readily!

Raising work_mem to 8MB brought the hash back on 19rc1, at 35.6 ms (rc1 appendix). Hash memory moves both cliffs: at that setting, by the same arithmetic, PG17 and PG18 hash up to 524,288 rows and PG19 up to 308,400. That's computed from the code, not measured, and so is the effect of key width: bigint moves neither cliff, a uuid moves both (hash-size appendix).

Confessions of Performance Engineering

Release candidates, not 19.0. Every PG19 number here is 19rc2 unless it says 19rc1, and 19rc1 drew the same plans wherever it ran the same test. 19.0 itself wasn't measured. The project said RC2 "will be mostly identical to the initial release", and the hash table estimate is the same code in both release candidates.

One laptop. An Intel i7 with 4 cores, a few unrelated containers running beside the three databases, and ExoBench as a local build in front of them. The plan shapes and the rows where they change carry over to any 64-bit machine at the same settings; the milliseconds don't.

Mostly single runs. Most ban-list measurements ran once, because the slow ones take minutes. The differences this post leans on are a thousandfold.

Per-node timing. The PG18 and PG19 campaign cards ran EXPLAIN (ANALYZE, BUFFERS), which times every plan node, and that timing slows PG18's plan down: it averaged 1,108 ms over 10 runs with timing on, and took 870.716 ms in one run with timing off. PG19's anti join on that query was only measured with timing on, at 0.06 ms. So the title claims 10,000x, which holds against either PG18 number: 1,108 ms is about 18,000 times 0.06 ms, and 870.716 ms about 14,500 times.

int keys at default memory. Every cliff here is for an int key, work_mem 4MB and hash_mem_multiplier 2. Other key widths and memory settings move them the way the hash-size appendix computes, and none of that was measured.

JIT ran at each server's default, on in PG17 and PG18 and off in PG19, which changed its default this release. The rc1 ban-list timings set it off on all three.

The million-order NOT IN on PG17 and PG18 is "more than 120 seconds", a lower bound, not a time.

Who chose what. The agent sessions designed and ran their own measurements. The million-order calls at 100,000 and 262,144 ids were mine, scripted through the same ExoBench setup and logged the same way. How the agent was set up, what it could touch and what each session cost are in the method appendix.

So, Don't Ever Use NOT IN?

On PG19, NOT IN is fine to write when both columns are declared NOT NULL, or when the outer one is and the subquery filters its own column with IS NOT NULL. Then PG19 plans it the way it plans NOT EXISTS.

Everywhere else the wiki's advice holds, and PG19 gives it one more reason: a NOT IN it can't rewrite gives up hashing at a shorter list than it used to.

In the order I'd try them:

  1. Declare the columns NOT NULL if they never hold a NULL. With JPA-generated DDL, that's nullable = false on the @JoinColumn.
  2. If the schema can't promise that for the subquery's column, add WHERE col IS NOT NULL inside the subquery.
  3. Or write NOT EXISTS, which Postgres has planned as an anti join since PostgreSQL 8.4, in 2009.

If none of those is possible, look at the planner's row estimate for the subquery in EXPLAIN. For an int at the default work_mem, PG19 hashes up to 154,198 rows and PG17 and PG18 up to 262,144, so an estimate between the two is a NOT IN that gets slower when you upgrade. The hash-size appendix works out both numbers for other memory settings.

Postgres finally invents the anti join for you, as long as you promise it there are no NULLs. Make the promise in the schema.

Appendix: The ban-list test, call by call

Every call here ran the ban-list tables from the body with 10,000 orders and seed 4242, in the agent's rc2 ban-list session, one execution per size under EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) unless the caption says otherwise.

A card's scale column rounds the list sizes, so 154,198 and 154,199 both show as 154.2K. In the same way, 262,144 and 262,145 both show as 262.1K. A card that has both sizes of a pair marks them #1 and #2, and the captions name the exact sizes.

PG19, both columns NOT NULL. An anti join at every size, the chart's flat line:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_191.52s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K10.7 ms
At 100,000 banned ids 19rc2 rewrites the NOT IN into a Hash Right Anti Join: it hashes the orders, 558 kB in one batch, and streams the banned list past the hash. One execution, 10.729 ms.
QUERY PLAN
Aggregate  (cost=2198.01..2198.02 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Hash Right Anti Join  (cost=280.00..2198.00 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=498
        ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
              Buffers: shared hit=443
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=88
Planning Time: 0.310 ms
Execution Time: 10.729 ms
0.24s
10K / 154.2K#115.6 ms
At 154,198 banned ids the plan is the same Hash Right Anti Join, and since the hash holds the orders rather than the list, the list's length does not decide whether the hash fits. One execution, 15.605 ms.
QUERY PLAN
Aggregate  (cost=3183.23..3183.24 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.22 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=89
Planning Time: 0.270 ms
Execution Time: 15.605 ms
0.27s
10K / 154.2K#216.6 ms
At 154,199 banned ids, where the nullable list drops to a plain SubPlan on 19rc2, this NOT NULL list stays a Hash Right Anti Join. One execution, 16.598 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=3183.24..3183.25 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.24 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=89
Planning Time: 0.300 ms
Execution Time: 16.598 ms
0.27s
10K / 262.1K#127 ms
At 262,144 banned ids it is still the Hash Right Anti Join over the same 558 kB hash of the orders, with the longer list only streaming past it. One execution, 27.021 ms.
QUERY PLAN
Aggregate  (cost=5144.49..5144.50 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.48 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=88
Planning Time: 0.253 ms
Execution Time: 27.021 ms
0.38s
10K / 262.1K#225.1 ms
At 262,145 banned ids, where PG17 and PG18 start rescanning the list once per order, 19rc2 keeps the same anti join. One execution, 25.135 ms, keeping the same 6,667 orders.
QUERY PLAN
Aggregate  (cost=5144.50..5144.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.49 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145.00 loops=1)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=89
Planning Time: 0.205 ms
Execution Time: 25.135 ms
0.37s

The same NOT IN against NOT EXISTS, 10 runs each, with per-node timing on. Apart from the table aliases and the planning buffers, the two plans are the same, cost for cost and buffer for buffer:

--- NOT IN, 154,199 banned ids, avg 17.66 ms ---
Aggregate
  ->  Hash Right Anti Join  (avg_cost=280.00..3183.24 ...)
        Hash Cond: (banned.customer_id = orders.customer_id)
--- NOT EXISTS, 154,199 banned ids, avg 18.28 ms ---
Aggregate
  ->  Hash Right Anti Join  (avg_cost=280.00..3183.24 ...)
        Hash Cond: (b.customer_id = o.customer_id)
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_191.54s compute×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 154.2K17.66 ±0.54 ms
Over 10 runs at 154,199 banned ids, 19rc2 plans the NOT IN as a Hash Right Anti Join with the same cost and the same shared buffers per run as NOT EXISTS on this list. Mean Execution Time 17.66 ms, stddev 0.54 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Aggregate  (avg_cost=3183.24..3183.25 avg_rows=1 avg_width=40) (actual avg_time=17.629..17.630 stddev_time=0.538..0.538 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=738
  Disabled: false
  ->  Hash Right Anti Join  (avg_cost=280.00..3183.24 avg_rows=1 avg_width=6) (actual avg_time=16.589..17.094 stddev_time=0.561..0.542 avg_rows=6667 avg_loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared avg_hit=738
        Disabled: false
        ->  Seq Scan on banned  (avg_cost=0.00..2224.99 avg_rows=154199 avg_width=4) (actual avg_time=0.005..4.956 stddev_time=0.004..0.164 avg_rows=154199 avg_loops=1)
              Buffers: shared avg_hit=683
              Disabled: false
        ->  Hash  (avg_cost=155.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=1.773..1.774 stddev_time=0.485..0.486 avg_rows=10000 avg_loops=1)
              Buckets: avg=16384  Batches: avg=1  Memory Usage: avg=558kB
              Buffers: shared avg_hit=55
              Disabled: false
              ->  Seq Scan on orders  (avg_cost=0.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=0.007..0.693 stddev_time=0.001..0.216 avg_rows=10000 avg_loops=1)
                    Buffers: shared avg_hit=55
                    Disabled: false
Planning:
  Buffers: shared avg_hit=9.1 stddev_hit=28.8
Planning Time: avg=0.12 ms stddev=0.08 n=10 samples=0.33,0.09,0.08,0.09,0.09,0.09,0.1,0.15,0.09,0.08
Execution Time: avg=17.66 ms stddev=0.54 n=10 samples=18.1,17.1,17.08,17.56,18.24,18.01,17.43,18.58,17.36,17.12
0.43s
10K / 500K52.3 ±3.4 ms
At 500,000 banned ids the anti join's cost and buffer count again match NOT EXISTS on the same list. Mean Execution Time over 10 runs is 52.29 ms, stddev 3.37 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Aggregate  (avg_cost=9468.01..9468.02 avg_rows=1 avg_width=40) (actual avg_time=52.262..52.264 stddev_time=3.372..3.372 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=2268
  Disabled: false
  ->  Hash Right Anti Join  (avg_cost=280.00..9468.00 avg_rows=1 avg_width=6) (actual avg_time=51.205..51.720 stddev_time=3.373..3.371 avg_rows=6667 avg_loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared avg_hit=2268
        Disabled: false
        ->  Seq Scan on banned  (avg_cost=0.00..7213.00 avg_rows=500000 avg_width=4) (actual avg_time=0.004..16.685 stddev_time=0.001..1.211 avg_rows=500000 avg_loops=1)
              Buffers: shared avg_hit=2213
              Disabled: false
        ->  Hash  (avg_cost=155.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=1.577..1.577 stddev_time=0.164..0.164 avg_rows=10000 avg_loops=1)
              Buckets: avg=16384  Batches: avg=1  Memory Usage: avg=558kB
              Buffers: shared avg_hit=55
              Disabled: false
              ->  Seq Scan on orders  (avg_cost=0.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=0.007..0.599 stddev_time=0.001..0.078 avg_rows=10000 avg_loops=1)
                    Buffers: shared avg_hit=55
                    Disabled: false
Planning:
  Buffers: shared avg_hit=8.9 stddev_hit=28.1
Planning Time: avg=0.12 ms stddev=0.06 n=10 samples=0.29,0.1,0.09,0.11,0.09,0.09,0.09,0.09,0.09,0.17
Execution Time: avg=52.29 ms stddev=3.37 n=10 samples=51.1,57.94,49.4,51.64,49.75,50.68,58.78,52.75,51.5,49.39
1.11s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_191.55s compute×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*), sum(total) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.customer_id = o.customer_id)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 154.2K18.3 ±1.2 ms
At 154,199 banned ids NOT EXISTS gets the Hash Right Anti Join 19rc2 builds for the rewritten NOT IN, at the same cost and the same shared buffers per run. Mean Execution Time over 10 runs is 18.28 ms, stddev 1.19 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Aggregate  (avg_cost=3183.24..3183.25 avg_rows=1 avg_width=40) (actual avg_time=18.244..18.248 stddev_time=1.189..1.188 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=738
  Disabled: false
  ->  Hash Right Anti Join  (avg_cost=280.00..3183.24 avg_rows=1 avg_width=6) (actual avg_time=17.120..17.673 stddev_time=1.133..1.149 avg_rows=6667 avg_loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared avg_hit=738
        Disabled: false
        ->  Seq Scan on banned b  (avg_cost=0.00..2224.99 avg_rows=154199 avg_width=4) (actual avg_time=0.007..5.063 stddev_time=0.005..0.198 avg_rows=154199 avg_loops=1)
              Buffers: shared avg_hit=683
              Disabled: false
        ->  Hash  (avg_cost=155.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=1.754..1.755 stddev_time=0.349..0.349 avg_rows=10000 avg_loops=1)
              Buckets: avg=16384  Batches: avg=1  Memory Usage: avg=558kB
              Buffers: shared avg_hit=55
              Disabled: false
              ->  Seq Scan on orders o  (avg_cost=0.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=0.009..0.688 stddev_time=0.007..0.158 avg_rows=10000 avg_loops=1)
                    Buffers: shared avg_hit=55
                    Disabled: false
Planning:
  Buffers: shared avg_hit=8.9 stddev_hit=28.1
Planning Time: avg=0.11 ms stddev=0.04 n=10 samples=0.22,0.08,0.09,0.08,0.13,0.08,0.08,0.1,0.11,0.08
Execution Time: avg=18.28 ms stddev=1.19 n=10 samples=17.82,18.73,18.12,21.04,19.4,16.96,17.6,17.97,17.32,17.81
0.48s
10K / 500K49.1 ±1.6 ms
At 500,000 banned ids NOT EXISTS and the rewritten NOT IN again share one plan, cost and buffer count. Mean Execution Time over 10 runs is 49.15 ms, stddev 1.63 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Aggregate  (avg_cost=9468.01..9468.02 avg_rows=1 avg_width=40) (actual avg_time=49.124..49.126 stddev_time=1.624..1.624 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=2268
  Disabled: false
  ->  Hash Right Anti Join  (avg_cost=280.00..9468.00 avg_rows=1 avg_width=6) (actual avg_time=48.063..48.570 stddev_time=1.582..1.591 avg_rows=6667 avg_loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared avg_hit=2268
        Disabled: false
        ->  Seq Scan on banned b  (avg_cost=0.00..7213.00 avg_rows=500000 avg_width=4) (actual avg_time=0.005..15.618 stddev_time=0.005..0.566 avg_rows=500000 avg_loops=1)
              Buffers: shared avg_hit=2213
              Disabled: false
        ->  Hash  (avg_cost=155.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=1.546..1.547 stddev_time=0.124..0.124 avg_rows=10000 avg_loops=1)
              Buckets: avg=16384  Batches: avg=1  Memory Usage: avg=558kB
              Buffers: shared avg_hit=55
              Disabled: false
              ->  Seq Scan on orders o  (avg_cost=0.00..155.00 avg_rows=10000 avg_width=10) (actual avg_time=0.009..0.593 stddev_time=0.007..0.062 avg_rows=10000 avg_loops=1)
                    Buffers: shared avg_hit=55
                    Disabled: false
Planning:
  Buffers: shared avg_hit=8.8 stddev_hit=27.8
Planning Time: avg=0.11 ms stddev=0.05 n=10 samples=0.24,0.08,0.12,0.09,0.11,0.09,0.08,0.08,0.08,0.08
Execution Time: avg=49.15 ms stddev=1.63 n=10 samples=52.91,49.09,48.25,48.06,48.48,48.48,51.27,47.97,48.28,48.74
1.06s

The chart's other points. PG19 with the nullable list at 262,145 ids:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_19182.60s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 262.1K182.8s
At 262,145 banned ids, the size where PG17 and PG18 stop hashing too, 19rc2 runs the plain SubPlan over a Materialize on disk, rescanned 10,000 times, once per order. The one execution takes 182,753.052 ms.
QUERY PLAN
Aggregate  (cost=33862892.50..33862892.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215, temp read=3003672 written=449
  ->  Seq Scan on orders  (cost=0.00..33862867.50 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=3003672 written=449
        SubPlan any_1
          ->  Materialize  (cost=0.00..6117.18 rows=262145 width=4) (actual rows=175327.68 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=1160, temp read=3003672 written=449
                ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145.00 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=32
Planning Time: 0.122 ms
Execution Time: 182753.052 ms
182.60s

PG17 hashing the nullable list through 262,144 ids:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres1.27s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K28.7 ms
At 100,000 banned ids PG17 runs the NOT IN as a hashed SubPlan over one Seq Scan of banned. One execution, 28.683 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=26
Planning Time: 0.099 ms
Execution Time: 28.683 ms
0.23s
10K / 154.2K#152.8 ms
At 154,198 banned ids PG17 still hashes the list. One execution, 52.754 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.122 ms
Execution Time: 52.754 ms
0.30s
10K / 154.2K#251.8 ms
At 154,199 banned ids, the size where 19rc2 stops hashing this same list, PG17 keeps the hashed SubPlan. One execution, 51.777 ms, for the same 6,667 orders.
QUERY PLAN
Aggregate  (cost=2815.49..2815.50 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.49..2790.49 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.104 ms
Execution Time: 51.777 ms
0.31s
10K / 262.1K80.4 ms
At 262,144 banned ids, the largest list PG17 hashes, the hashed SubPlan reads 1,215 shared buffers. One execution, 80.391 ms.
QUERY PLAN
Aggregate  (cost=4641.80..4641.81 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215
  ->  Seq Scan on orders  (cost=4436.80..4616.80 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144 loops=1)
                Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.097 ms
Execution Time: 80.391 ms
0.43s

PG17 and PG18 with the nullable list at 262,145 and 500,000 ids, on the plain SubPlan:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres480.98s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 262.1K165.3s
At 262,145 banned ids PG17 stops hashing the nullable list as well: the SubPlan reads a Materialize from temp files on disk, rescanned 10,000 times, once per order. The one execution takes 165,261.775 ms.
QUERY PLAN
Aggregate  (cost=33862892.50..33862892.51 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215, temp read=3003672 written=449
  ->  Seq Scan on orders  (cost=0.00..33862867.50 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=3003672 written=449
        SubPlan 1
          ->  Materialize  (cost=0.00..6117.18 rows=262145 width=4) (actual rows=175328 loops=10000)
                Buffers: shared hit=1160, temp read=3003672 written=449
                ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.107 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 165261.775 ms
165.64s
10K / 500K314.8s
At 500,000 banned ids the same plain SubPlan rescans a longer list, 333,906 stored ids per pass on average, all of it read back from temp files. The one execution takes 314,769.094 ms.
QUERY PLAN
Aggregate  (cost=64585205.00..64585205.01 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=2268, temp read=5710068 written=855
  ->  Seq Scan on orders  (cost=0.00..64585180.00 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=2268, temp read=5710068 written=855
        SubPlan 1
          ->  Materialize  (cost=0.00..11667.00 rows=500000 width=4) (actual rows=333906 loops=10000)
                Buffers: shared hit=2213, temp read=5710068 written=855
                ->  Seq Scan on banned  (cost=0.00..7213.00 rows=500000 width=4) (actual rows=500000 loops=1)
                      Buffers: shared hit=2213
Planning:
  Buffers: shared hit=26
Planning Time: 0.096 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 314769.094 ms
315.34s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_18473.14s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 262.1K162.7s
At 262,145 banned ids PG18 stops hashing the nullable list: the SubPlan reads a Materialize stored on disk and rescanned 10,000 times, once per order. The one execution takes 162,743.662 ms.
QUERY PLAN
Aggregate  (cost=33862892.50..33862892.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215, temp read=3003672 written=449
  ->  Seq Scan on orders  (cost=0.00..33862867.50 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=3003672 written=449
        SubPlan 1
          ->  Materialize  (cost=0.00..6117.18 rows=262145 width=4) (actual rows=175327.68 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=1160, temp read=3003672 written=449
                ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145.00 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.108 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 162743.662 ms
163.14s
10K / 500K309.4s
At 500,000 banned ids the Materialize still sits on disk, and each rescan reads 333,905.61 rows on average. The one execution takes 309,423.976 ms.
QUERY PLAN
Aggregate  (cost=64585205.00..64585205.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=2268, temp read=5710068 written=855
  ->  Seq Scan on orders  (cost=0.00..64585180.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=2268, temp read=5710068 written=855
        SubPlan 1
          ->  Materialize  (cost=0.00..11667.00 rows=500000 width=4) (actual rows=333905.61 loops=10000)
                Storage: Disk  Maximum Storage: 6836kB
                Buffers: shared hit=2213, temp read=5710068 written=855
                ->  Seq Scan on banned  (cost=0.00..7213.00 rows=500000 width=4) (actual rows=500000.00 loops=1)
                      Buffers: shared hit=2213
Planning:
  Buffers: shared hit=26
Planning Time: 0.103 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 309423.976 ms
310.00s

PG19's two points at 500,000 ids have no card: the nullable list ran the plain SubPlan in 345,100.858 ms (seq 28), and both columns NOT NULL ran the anti join in 46.841 ms (seq 17).

PG17's cliff. Both columns NOT NULL, hashed at 262,144 ids, then the plain SubPlan at 262,145, the same row as PG18:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres169.92s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 262.1K#194.5 ms
At 262,144 banned ids PG17 runs the NOT IN as a hashed SubPlan: one Seq Scan of banned fills a hash table, and each order is checked against it with one hash lookup. One execution, 94.516 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=4641.80..4641.81 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215
  ->  Seq Scan on orders  (cost=4436.80..4616.80 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144 loops=1)
                Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.103 ms
Execution Time: 94.516 ms
0.52s
10K / 262.1K#2169s
One more banned id, 262,145, and the word hashed disappears from the Filter: the SubPlan now reads a Materialize from temp files on disk, rescanned 10,000 times, once per order. The one execution takes 169,016.541 ms.
QUERY PLAN
Aggregate  (cost=33862892.50..33862892.51 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215, temp read=3003672 written=449
  ->  Seq Scan on orders  (cost=0.00..33862867.50 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=3003672 written=449
        SubPlan 1
          ->  Materialize  (cost=0.00..6117.18 rows=262145 width=4) (actual rows=175328 loops=10000)
                Buffers: shared hit=1160, temp read=3003672 written=449
                ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.143 ms
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true
Execution Time: 169016.541 ms
169.40s

NOT EXISTS on PG18 and PG17. A Hash Right Anti Join at every size, 262,145 included:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_181.55s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.customer_id = o.customer_id)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K12.5 ms
At 100,000 banned ids PG18 plans NOT EXISTS as a Hash Right Anti Join: the orders are hashed and the banned list streams past them. One execution, 12.489 ms.
QUERY PLAN
Aggregate  (cost=2198.01..2198.02 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Hash Right Anti Join  (cost=280.00..2198.00 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=498
        ->  Seq Scan on banned b  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
              Buffers: shared hit=443
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=86
Planning Time: 0.245 ms
Execution Time: 12.489 ms
0.21s
10K / 154.2K#118 ms
At 154,198 banned ids the NOT EXISTS plan is unchanged, the same Hash Right Anti Join. One execution, 18.024 ms.
QUERY PLAN
Aggregate  (cost=3183.23..3183.24 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.22 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned b  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=86
Planning Time: 0.202 ms
Execution Time: 18.024 ms
0.30s
10K / 154.2K#217 ms
At 154,199 banned ids PG18's NOT EXISTS is still that anti join. One execution, 17.029 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=3183.24..3183.25 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.24 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned b  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=86
Planning Time: 0.202 ms
Execution Time: 17.029 ms
0.28s
10K / 262.1K#129.5 ms
At 262,144 banned ids the NOT EXISTS anti join is unchanged. One execution, 29.547 ms.
QUERY PLAN
Aggregate  (cost=5144.49..5144.50 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.48 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned b  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=86
Planning Time: 0.207 ms
Execution Time: 29.547 ms
0.39s
10K / 262.1K#226.7 ms
At 262,145 banned ids, where PG18's NOT IN falls to rescanning the list once per order, NOT EXISTS stays a Hash Right Anti Join. One execution, 26.699 ms.
QUERY PLAN
Aggregate  (cost=5144.50..5144.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.49 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned b  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145.00 loops=1)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=86
Planning Time: 0.267 ms
Execution Time: 26.699 ms
0.38s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres1.60s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.customer_id = o.customer_id)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K14.7 ms
At 100,000 banned ids PG17 already plans NOT EXISTS as a Hash Right Anti Join: the orders go into a 558 kB hash and the banned list streams past it. One execution, 14.730 ms.
QUERY PLAN
Aggregate  (cost=2198.01..2198.02 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=498
  ->  Hash Right Anti Join  (cost=280.00..2198.00 rows=1 width=6) (actual rows=6667 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=498
        ->  Seq Scan on banned b  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000 loops=1)
              Buffers: shared hit=443
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=78
Planning Time: 0.228 ms
Execution Time: 14.730 ms
0.25s
10K / 154.2K#120.2 ms
At 154,198 banned ids the NOT EXISTS plan is unchanged, the same Hash Right Anti Join. One execution, 20.228 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=3183.23..3183.24 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.22 rows=1 width=6) (actual rows=6667 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned b  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198 loops=1)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=78
Planning Time: 0.251 ms
Execution Time: 20.228 ms
0.27s
10K / 154.2K#221.8 ms
At 154,199 banned ids PG17's NOT EXISTS is still that anti join. One execution, 21.754 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=3183.24..3183.25 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.24 rows=1 width=6) (actual rows=6667 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned b  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199 loops=1)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=78
Planning Time: 0.174 ms
Execution Time: 21.754 ms
0.30s
10K / 262.1K#132.1 ms
At 262,144 banned ids the NOT EXISTS anti join reads 1,215 shared buffers. One execution, 32.076 ms.
QUERY PLAN
Aggregate  (cost=5144.49..5144.50 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.48 rows=1 width=6) (actual rows=6667 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned b  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144 loops=1)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=78
Planning Time: 0.184 ms
Execution Time: 32.076 ms
0.38s
10K / 262.1K#240.3 ms
At 262,145 banned ids, where PG17's NOT IN falls to rescanning the list once per order, NOT EXISTS stays a Hash Right Anti Join. One execution, 40.291 ms.
QUERY PLAN
Aggregate  (cost=5144.50..5144.51 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.49 rows=1 width=6) (actual rows=6667 loops=1)
        Hash Cond: (b.customer_id = o.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned b  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145 loops=1)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders o  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=78
Planning Time: 0.226 ms
Execution Time: 40.291 ms
0.41s

The rewrite's conditions. The nullable list with WHERE customer_id IS NOT NULL inside the subquery, an anti join at every size:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_191.59s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned WHERE customer_id IS NOT NULL)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K12.5 ms
At 100,000 banned ids the IS NOT NULL inside the subquery is enough for 19rc2 to rewrite the NOT IN into a Hash Right Anti Join, with the filter applied in the Seq Scan on banned. One execution, 12.497 ms, keeping 6,667 orders.
QUERY PLAN
Aggregate  (cost=2198.01..2198.02 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Hash Right Anti Join  (cost=280.00..2198.00 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=498
        ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
              Filter: (customer_id IS NOT NULL)
              Buffers: shared hit=443
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=89
Planning Time: 0.224 ms
Execution Time: 12.497 ms
0.22s
10K / 154.2K#120.9 ms
At 154,198 banned ids the filtered list is still an anti join against a hash of the orders. One execution, 20.894 ms.
QUERY PLAN
Aggregate  (cost=3183.23..3183.24 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.22 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
              Filter: (customer_id IS NOT NULL)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=87
Planning Time: 0.272 ms
Execution Time: 20.894 ms
0.27s
10K / 154.2K#220.3 ms
At 154,199 banned ids, where the same nullable list without the filter falls to a plain SubPlan on 19rc2, the filtered one stays a Hash Right Anti Join. One execution, 20.332 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=3183.24..3183.25 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Hash Right Anti Join  (cost=280.00..3183.24 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=738
        ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
              Filter: (customer_id IS NOT NULL)
              Buffers: shared hit=683
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=87
Planning Time: 0.235 ms
Execution Time: 20.332 ms
0.35s
10K / 262.1K#127.8 ms
At 262,144 banned ids the filtered NOT IN is still the Hash Right Anti Join. One execution, 27.767 ms, 1,215 shared buffer hits.
QUERY PLAN
Aggregate  (cost=5144.49..5144.50 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.48 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
              Filter: (customer_id IS NOT NULL)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=88
Planning Time: 0.352 ms
Execution Time: 27.767 ms
0.40s
10K / 262.1K#227.8 ms
At 262,145 banned ids the plan does not change either: a Hash Right Anti Join with Filter (customer_id IS NOT NULL) on the banned scan. One execution, 27.848 ms.
QUERY PLAN
Aggregate  (cost=5144.50..5144.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Hash Right Anti Join  (cost=280.00..5144.49 rows=1 width=6) (actual rows=6667.00 loops=1)
        Hash Cond: (banned.customer_id = orders.customer_id)
        Buffers: shared hit=1215
        ->  Seq Scan on banned  (cost=0.00..3781.45 rows=262145 width=4) (actual rows=262145.00 loops=1)
              Filter: (customer_id IS NOT NULL)
              Buffers: shared hit=1160
        ->  Hash  (cost=155.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 558kB
              Buffers: shared hit=55
              ->  Seq Scan on orders  (cost=0.00..155.00 rows=10000 width=10) (actual rows=10000.00 loops=1)
                    Buffers: shared hit=55
Planning:
  Buffers: shared hit=88
Planning Time: 0.231 ms
Execution Time: 27.848 ms
0.36s

The orders column nullable instead, never rewritten, and switching to the plain SubPlan at the same 154,199 ids:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_19282.56s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K25.9 ms
With the orders column nullable, 19rc2 does not rewrite the NOT IN either: at 100,000 banned ids it runs a hashed SubPlan over one Seq Scan of banned. One execution, 25.934 ms.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan any_1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=33
Planning Time: 0.185 ms
Execution Time: 25.934 ms
0.26s
10K / 154.2K#143.8 ms
At 154,198 banned ids the NOT IN with a nullable orders column is still hashed on 19rc2. One execution, 43.766 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan any_1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=32
Planning Time: 0.274 ms
Execution Time: 43.766 ms
0.35s
10K / 154.2K#2116.4s
At 154,199 banned ids it stops hashing at the same row as the nullable ban list: a plain SubPlan over a Materialize on disk, rescanned 10,000 times. One execution, 116,371.893 ms.
QUERY PLAN
Aggregate  (cost=19922617.50..19922617.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738, temp read=1770462 written=264
  ->  Seq Scan on orders  (cost=0.00..19922592.50 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738, temp read=1770462 written=264
        SubPlan any_1
          ->  Materialize  (cost=0.00..3598.98 rows=154199 width=4) (actual rows=103360.08 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=683, temp read=1770462 written=264
                ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
                      Buffers: shared hit=683
Planning:
  Buffers: shared hit=33
Planning Time: 0.146 ms
Execution Time: 116371.893 ms
116.33s
10K / 262.1K165.7s
At 262,144 banned ids the plain SubPlan rescans 175,327.02 stored ids per pass on average from disk. The one execution takes 165,691.856 ms.
QUERY PLAN
Aggregate  (cost=33857805.00..33857805.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215, temp read=2997006 written=448
  ->  Seq Scan on orders  (cost=0.00..33857780.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215, temp read=2997006 written=448
        SubPlan any_1
          ->  Materialize  (cost=0.00..6116.16 rows=262144 width=4) (actual rows=175327.02 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=1160, temp read=2997006 written=448
                ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
                      Buffers: shared hit=1160
Planning:
  Buffers: shared hit=34
Planning Time: 0.207 ms
Execution Time: 165691.856 ms
165.61s

The orders column nullable, with customer_id IS NOT NULL in the outer WHERE, as a plan only:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_190.38s compute
EXPLAIN SELECT count(*), sum(total) FROM orders WHERE customer_id IS NOT NULL AND customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 262.1K1 ms
Plan only, so no timing: with customer_id IS NOT NULL in the outer WHERE, 19rc2 still does not rewrite the NOT IN. The Seq Scan on orders checks the IS NOT NULL and then a SubPlan over a Materialize of the 262,144 banned ids, the plain form, with no anti join anywhere in the plan.
QUERY PLAN
Aggregate  (cost=33857805.00..33857805.01 rows=1 width=40)
  ->  Seq Scan on orders  (cost=0.00..33857780.00 rows=5000 width=6)
        Filter: ((customer_id IS NOT NULL) AND (NOT (ANY (customer_id = (SubPlan any_1).col1))))
        SubPlan any_1
          ->  Materialize  (cost=0.00..6116.16 rows=262144 width=4)
                ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4)
0.38s

Source: the rc2 ban-list session, run 20261113_025021_phase2-3-cliff-agent-rc2, seq 14, 15, 16, 17, 20, 24, 27, 28, 29, 35, 38, 41, 86, 87 and 88.

Appendix: The campaign-preview query

The data and the query, as the agent's task gave them. Every call used this schema with the scale point {"TENANTS": 2000, "SENDS": 2000000} and seed 4242. The nullable variant changes contact_id INT NOT NULL in sends to contact_id INT, and nothing else.

-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;

The query. The application inlines the tenant and the cap as literals; the cards ran tenant 1, with caps of 365 days on rc2 and 90 days on rc1:

SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = :tenant_id
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = :tenant_id
                     AND s.sent_at >= now() - :cap_days * interval '1 day')
ORDER BY c.id
LIMIT 10

The same query with NOT EXISTS:

SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = :tenant_id
  AND c.status = 'subscribed'
  AND NOT EXISTS (SELECT 1
                  FROM sends s
                  WHERE s.contact_id = c.id
                    AND s.tenant_id = :tenant_id
                    AND s.sent_at >= now() - :cap_days * interval '1 day')
ORDER BY c.id
LIMIT 10

PG17 runs the same plan as PG18, both columns NOT NULL:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres28.30s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M1.33 ±0.21 s
PG17 keeps the NOT IN as a plain SubPlan: it stores the 659,439 matching sends rows in a Materialize and rescans it for each subscribed contact it checks, 12 times per run on average, with 10,148 temp blocks read. Mean Execution Time over 10 runs is 1,331.79 ms, stddev 212.33 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.85..378662.57 avg_rows=10 avg_width=26) (actual avg_time=302.953..1329.724 stddev_time=61.511..212.136 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=3256, temp avg_read=10148 avg_written=1127
  ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.85..346551203.46 avg_rows=9152 avg_width=26) (actual avg_time=297.417..1324.180 stddev_time=60.365..211.416 avg_rows=10 avg_loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan 1).col1))))
        Rows Removed by Filter: avg=3
        Buffers: shared avg_hit=3256, temp avg_read=10148 avg_written=1127
        SubPlan 1
          ->  Materialize  (avg_cost=0.43..32334.78 avg_rows=663734 avg_width=4) (actual avg_time=0.006..69.390 stddev_time=0.001..11.207 avg_rows=550587 avg_loops=12)
                Buffers: shared avg_hit=3252, temp avg_read=10148 avg_written=1127
                ->  Index Only Scan using sends_tenant_sent on sends s  (avg_cost=0.43..26423.11 avg_rows=663734 avg_width=4) (actual avg_time=0.029..100.858 stddev_time=0.007..17.816 avg_rows=659439 avg_loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
                      Heap Fetches: avg=0
                      Buffers: shared avg_hit=3252
Planning Time: avg=0.13 ms stddev=0.03 n=10 samples=0.14,0.14,0.16,0.11,0.18,0.11,0.11,0.11,0.11,0.11
JIT:
  Functions: avg=12
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation avg=0.000 ms, Inlining avg=0.000 ms, Optimization avg=0.299 ms stddev=0.065 ms, Emission avg=5.262 ms stddev=1.237 ms, Total avg=6.162 ms stddev=1.385 ms
Execution Time: avg=1331.79 ms stddev=212.33 n=10 samples=1446.46,1643.01,1363.66,1601.3,1419.8,1410.05,1069.81,1130.23,1173.79,1059.77
28.30s

PG19 with the column nullable and AND s.contact_id IS NOT NULL inside the subquery, back on the anti join:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1911.67s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day'
                     AND s.contact_id IS NOT NULL)
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M0.06 ±0.02 ms
The IS NOT NULL inside the subquery is enough for 19rc2 to rewrite the NOT IN into a Nested Loop Anti Join after all, and the filter shows up in the probe of sends_contact as Index Cond ((contact_id = c.id) AND (contact_id IS NOT NULL)), 12 probes per run on average. It reads 42 shared buffers per run, and the mean Execution Time over 10 runs is 0.06 ms, stddev 0.02 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.84..9.63 avg_rows=10 avg_width=26) (actual avg_time=0.029..0.048 stddev_time=0.014..0.017 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=42
  Disabled: false
  ->  Nested Loop Anti Join  (avg_cost=0.84..13605.19 avg_rows=15494 avg_width=26) (actual avg_time=0.029..0.046 stddev_time=0.014..0.016 avg_rows=10 avg_loops=1)
        Buffers: shared avg_hit=42
        Disabled: false
        ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.42..2520.02 avg_rows=18132 avg_width=26) (actual avg_time=0.021..0.025 stddev_time=0.012..0.014 avg_rows=12 avg_loops=1)
              Index Cond: (tenant_id = 1)
              Filter: (status = 'subscribed'::text)
              Rows Removed by Filter: avg=1
              Buffers: shared avg_hit=4
              Disabled: false
              Index Searches: avg=1
        ->  Index Scan using sends_contact on sends s  (avg_cost=0.43..6.97 avg_rows=41 avg_width=4) (actual avg_time=0.001..0.001 avg_rows=0.17 avg_loops=12)
              Index Cond: ((contact_id = c.id) AND (contact_id IS NOT NULL))
              Filter: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
              Buffers: shared avg_hit=38
              Disabled: false
              Index Searches: avg=12
Planning:
  Buffers: shared avg_hit=14
Planning Time: avg=0.21 ms stddev=0.04 n=10 samples=0.16,0.31,0.18,0.21,0.23,0.24,0.17,0.22,0.2,0.17
Execution Time: avg=0.06 ms stddev=0.02 n=10 samples=0.05,0.08,0.05,0.09,0.05,0.06,0.08,0.08,0.05,0.05
11.67s

NOT EXISTS on PG18 and PG17, already an anti join:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1812.75s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND NOT EXISTS (SELECT 1
                  FROM sends s
                  WHERE s.contact_id = c.id
                    AND s.tenant_id = 1
                    AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M0.09 ±0.03 ms
Written as NOT EXISTS, the query is already a Nested Loop Anti Join on PG18: it probes the sends_contact index once per subscribed contact, 12 probes per run on average, and reads 42 shared buffers. Mean Execution Time over 10 runs is 0.09 ms, stddev 0.03 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.84..9.47 avg_rows=10 avg_width=26) (actual avg_time=0.036..0.067 stddev_time=0.009..0.018 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=42
  Disabled: false
  ->  Nested Loop Anti Join  (avg_cost=0.84..13421.75 avg_rows=15552 avg_width=26) (actual avg_time=0.035..0.065 stddev_time=0.009..0.018 avg_rows=10 avg_loops=1)
        Buffers: shared avg_hit=42
        Disabled: false
        ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.42..2525.92 avg_rows=18169 avg_width=26) (actual avg_time=0.025..0.031 stddev_time=0.008..0.009 avg_rows=12 avg_loops=1)
              Index Cond: (tenant_id = 1)
              Filter: (status = 'subscribed'::text)
              Rows Removed by Filter: avg=1
              Buffers: shared avg_hit=4
              Disabled: false
              Index Searches: avg=1
        ->  Index Scan using sends_contact on sends s  (avg_cost=0.43..6.67 avg_rows=42 avg_width=4) (actual avg_time=0.002..0.002 stddev_time=0.001..0.001 avg_rows=0.17 avg_loops=12)
              Index Cond: (contact_id = c.id)
              Filter: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
              Buffers: shared avg_hit=38
              Disabled: false
              Index Searches: avg=12
Planning:
  Buffers: shared avg_hit=14
Planning Time: avg=0.26 ms stddev=0.07 n=10 samples=0.26,0.23,0.27,0.31,0.39,0.3,0.3,0.19,0.22,0.15
Execution Time: avg=0.09 ms stddev=0.03 n=10 samples=0.09,0.08,0.08,0.12,0.12,0.1,0.14,0.06,0.08,0.05
12.75s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres11.58s computewarm ×2×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND NOT EXISTS (SELECT 1
                  FROM sends s
                  WHERE s.contact_id = c.id
                    AND s.tenant_id = 1
                    AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M0.08 ±0.02 ms
Written as NOT EXISTS, the query is already a Nested Loop Anti Join on PG17: it probes the sends_contact index once per subscribed contact, 12 probes per run on average, and reads 42 shared buffers. Mean Execution Time over 10 runs is 0.08 ms, stddev 0.02 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=0.84..9.50 avg_rows=10 avg_width=26) (actual avg_time=0.036..0.059 stddev_time=0.014..0.020 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=42
  ->  Nested Loop Anti Join  (avg_cost=0.84..13304.76 avg_rows=15380 avg_width=26) (actual avg_time=0.035..0.057 stddev_time=0.014..0.020 avg_rows=10 avg_loops=1)
        Buffers: shared avg_hit=42
        ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=0.42..2514.53 avg_rows=17975 avg_width=26) (actual avg_time=0.023..0.029 stddev_time=0.013..0.013 avg_rows=12 avg_loops=1)
              Index Cond: (tenant_id = 1)
              Filter: (status = 'subscribed'::text)
              Rows Removed by Filter: avg=1
              Buffers: shared avg_hit=4
        ->  Index Scan using sends_contact on sends s  (avg_cost=0.43..6.69 avg_rows=42 avg_width=4) (actual avg_time=0.002..0.002 stddev_time=0.001..0.001 avg_rows=0 avg_loops=12)
              Index Cond: (contact_id = c.id)
              Filter: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
              Buffers: shared avg_hit=38
Planning:
  Buffers: shared avg_hit=14
Planning Time: avg=0.26 ms stddev=0.06 n=10 samples=0.3,0.28,0.26,0.15,0.17,0.27,0.36,0.28,0.25,0.29
Execution Time: avg=0.08 ms stddev=0.02 n=10 samples=0.08,0.07,0.05,0.05,0.05,0.1,0.1,0.1,0.11,0.09
11.58s

The plain SubPlan with per-node timing off, one timed execution after two warm-ups on each version, with both columns NOT NULL on PG17 and PG18 and the column nullable on PG19:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres14.98s computewarm ×2
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M814.5 ms
With per-node timing off, PG17 runs the same plain SubPlan over a Materialize of the 659,439 matching sends rows, rescanned 12 times, with 10,148 temp blocks read. The one timed execution takes 814.469 ms, with JIT compiling 12 functions.
QUERY PLAN
Limit  (cost=0.85..380120.65 rows=10 width=26) (actual rows=10 loops=1)
  Buffers: shared hit=3256, temp read=10148 written=1127
  ->  Index Scan using contacts_tenant_id on contacts c  (cost=0.85..338876800.29 rows=8915 width=26) (actual rows=10 loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan 1).col1))))
        Rows Removed by Filter: 3
        Buffers: shared hit=3256, temp read=10148 written=1127
        SubPlan 1
          ->  Materialize  (cost=0.43..32477.43 rows=666600 width=4) (actual rows=550587 loops=12)
                Buffers: shared hit=3252, temp read=10148 written=1127
                ->  Index Only Scan using sends_tenant_sent on sends s  (cost=0.43..26540.43 rows=666600 width=4) (actual rows=659439 loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
                      Heap Fetches: 0
                      Buffers: shared hit=3252
Planning Time: 0.123 ms
JIT:
  Functions: 12
  Options: Inlining false, Optimization false, Expressions true, Deforming true
Execution Time: 814.469 ms
14.98s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1814.39s computewarm ×2
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT NOT NULL, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M870.7 ms
With per-node timing off, PG18 runs the same plain SubPlan over a Materialize stored on disk at 9,016 kB, rescanned 12 times, with 10,148 temp blocks read. The one timed execution takes 870.716 ms, with JIT compiling 12 functions.
QUERY PLAN
Limit  (cost=0.85..374888.64 rows=10 width=26) (actual rows=10.00 loops=1)
  Buffers: shared hit=3256, temp read=10148 written=1127
  ->  Index Scan using contacts_tenant_id on contacts c  (cost=0.85..335861971.01 rows=8959 width=26) (actual rows=10.00 loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan 1).col1))))
        Rows Removed by Filter: 3
        Index Searches: 1
        Buffers: shared hit=3256, temp read=10148 written=1127
        SubPlan 1
          ->  Materialize  (cost=0.43..32123.78 rows=659334 width=4) (actual rows=550586.92 loops=12)
                Storage: Disk  Maximum Storage: 9016kB
                Buffers: shared hit=3252, temp read=10148 written=1127
                ->  Index Only Scan using sends_tenant_sent on sends s  (cost=0.43..26251.11 rows=659334 width=4) (actual rows=659439.00 loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
                      Heap Fetches: 0
                      Index Searches: 1
                      Buffers: shared hit=3252
Planning Time: 0.117 ms
JIT:
  Functions: 12
  Options: Inlining false, Optimization false, Expressions true, Deforming true
Execution Time: 870.716 ms
14.39s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1913.35s computewarm ×2
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 365 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M765.5 ms
With per-node timing off and the column nullable, 19rc2 runs the plain SubPlan that PG17 and PG18 run here, over a Materialize stored on disk at 9,016 kB, rescanned 12 times, with the same 10,148 temp blocks read. The one timed execution takes 765.528 ms, and the plan shows no JIT, which is off by default in 19rc2.
QUERY PLAN
Limit  (cost=0.85..374509.47 rows=10 width=26) (actual rows=10.00 loops=1)
  Buffers: shared hit=3256, temp read=10148 written=1127
  ->  Index Scan using contacts_tenant_id on contacts c  (cost=0.85..337994027.32 rows=9025 width=26) (actual rows=10.00 loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((status = 'subscribed'::text) AND (NOT (ANY (id = (SubPlan any_1).col1))))
        Rows Removed by Filter: 3
        Index Searches: 1
        Buffers: shared hit=3256, temp read=10148 written=1127
        SubPlan any_1
          ->  Materialize  (cost=0.43..31942.13 rows=655668 width=4) (actual rows=550586.92 loops=12)
                Storage: Disk  Maximum Storage: 9016kB
                Buffers: shared hit=3252, temp read=10148 written=1127
                ->  Index Only Scan using sends_tenant_sent on sends s  (cost=0.43..26101.79 rows=655668 width=4) (actual rows=659439.00 loops=1)
                      Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '365 days'::interval)))
                      Heap Fetches: 0
                      Index Searches: 1
                      Buffers: shared hit=3252
Planning Time: 0.144 ms
Execution Time: 765.528 ms
13.35s

The data check behind "no NULLs", a row set from 19rc2 with the nullable schema:

CheckResult
sends.contact_id attnotnullfalse
sends rows / rows with contact_id IS NULL2000000 / 0
Tenant 1, 365-day subquery: rows / distinct / NULL659439 / 4000 / 0
pg_stats null_frac of sends.contact_id0

Source: the rc2 campaign session, run 20261113_015648_phase1-s11-agent-rc2, seq 9, 10, 11, 21, 33, 34, 55, 56 and 57 for the cards, and seq 54 for the data check.

Appendix: A million orders

The ban-list tables at 1,000,000 orders against 300,000 banned ids, both columns NOT NULL. PG19 rewrote the NOT IN and ran it as a Parallel Hash Anti Join:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_192.79s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
1M / 300K145.8 ms
At 1,000,000 orders and 300,000 banned ids 19rc2 runs the NOT IN as a Parallel Hash Anti Join with two workers launched. The three processes share one hash of the banned list, built in a single batch, and the one execution takes 145.789 ms.
QUERY PLAN
Finalize Aggregate  (cost=21480.87..21480.88 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=6734
  ->  Gather  (cost=21480.64..21480.85 rows=2 width=40) (actual rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=6734
        ->  Partial Aggregate  (cost=20480.64..20480.65 rows=1 width=40) (actual rows=1.00 loops=3)
              Buffers: shared hit=6734
              ->  Parallel Hash Anti Join  (cost=5298.59..19022.31 rows=291667 width=6) (actual rows=233333.33 loops=3)
                    Hash Cond: (orders.customer_id = banned.customer_id)
                    Buffers: shared hit=6734
                    ->  Parallel Seq Scan on orders  (cost=0.00..9572.67 rows=416667 width=10) (actual rows=333333.33 loops=3)
                          Buffers: shared hit=5406
                    ->  Parallel Hash  (cost=3092.71..3092.71 rows=176471 width=4) (actual rows=100000.00 loops=3)
                          Buckets: 524288  Batches: 1  Memory Usage: 15872kB
                          Buffers: shared hit=1328
                          ->  Parallel Seq Scan on banned  (cost=0.00..3092.71 rows=176471 width=4) (actual rows=100000.00 loops=3)
                                Buffers: shared hit=1328
Planning:
  Buffers: shared hit=98
Planning Time: 0.383 ms
Execution Time: 145.789 ms
2.79s

Past 262,144 ids, PG17 and PG18 reread the list once per order, so the agent ran their NOT IN at this size only under SET statement_timeout = '120s'. Both came back cancelled, ERROR: canceling statement due to statement timeout, with runtimeDurationMs 120017 on PG17 and 120011 on PG18. That's a lower bound, more than 120 seconds, not a time, and a cancelled call has no plan to put on a card.

The two and a quarter hours is arithmetic, not a measurement. The banned ids are the multiples of 3, so each order that isn't banned reads the whole list, and the order for customer 3k stops at the list's k-th id. At this size the 700,000 orders that aren't banned make 210 billion of those reads and the banned ones about 45 billion, 255 billion in all.

The pace comes from PG18's rescan of 262,145 ids, the plan in the body. Its Materialize read 1.75 billion rows, one walk per order, in 165.9 seconds on one process, which is about 95 ns a row.

At a million orders, EXPLAIN without ANALYZE plans both versions with two workers (seq 74 and 75), so three processes share the orders. Split evenly, that's about 8,000 seconds on PG18 and 8,200 on PG17, whose rescan ran at 96 ns a row. It assumes three processes really go three times as fast on a 4-core laptop that's also running the other two databases, so it's the hopeful number.

The NOT EXISTS form finished on both at this size, with the same Parallel Hash Anti Join PG19 used: 146.988 ms on PG17 and 143.636 ms on PG18, one run each.

Before trusting the timeout, the agent proved it on a statement that can't finish early: SELECT pg_sleep(6) under a 3-second statement_timeout was cancelled on PG17, PG18 and PG19, and the same sleep without the timeout ran its full six seconds on PG17.

Where PG17 and PG18 still hash, I scripted the comparison myself after the agent's session: a million orders against 100,000 and 262,144 ids, 10 runs each, through the same harness and gateway. At the longer list, PG19's anti join was about 1.5 to 1.6 times faster than the hashed SubPlan on PG17 and PG18.

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres8.78s compute×10 runs
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
1M / 100K161 ±10 ms
At a million orders and 100,000 banned ids PG17 runs a parallel Seq Scan of orders, and each of the three processes builds its own hashed SubPlan from a full scan of banned. Mean Execution Time over 10 runs is 161.04 ms, stddev 10.01 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Finalize Aggregate  (avg_cost=14349.23..14349.24 avg_rows=1 avg_width=40) (actual avg_time=154.868..160.455 stddev_time=9.973..9.964 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=6735
  ->  Gather  (avg_cost=14349.00..14349.21 avg_rows=2 avg_width=40) (actual avg_time=154.134..160.443 stddev_time=10.016..9.964 avg_rows=3 avg_loops=1)
        Workers Planned: avg=2
        Workers Launched: avg=2
        Buffers: shared avg_hit=6735
        ->  Partial Aggregate  (avg_cost=13349.00..13349.01 avg_rows=1 avg_width=40) (actual avg_time=151.582..151.584 stddev_time=9.926..9.926 avg_rows=1 avg_loops=3)
              Buffers: shared avg_hit=6735
              ->  Parallel Seq Scan on orders  (avg_cost=1693.00..12307.33 avg_rows=208333 avg_width=6) (actual avg_time=34.559..115.013 stddev_time=5.594..8.453 avg_rows=300000 avg_loops=3)
                    Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
                    Rows Removed by Filter: avg=33333
                    Buffers: shared avg_hit=6735
                    SubPlan 1
                      ->  Seq Scan on banned  (avg_cost=0.00..1443.00 avg_rows=100000 avg_width=4) (actual avg_time=0.013..6.328 stddev_time=0.002..1.190 avg_rows=100000 avg_loops=3)
                            Buffers: shared avg_hit=1329
Planning:
  Buffers: shared avg_hit=3.5 stddev_hit=11.1
Planning Time: avg=0.08 ms stddev=0.04 n=10 samples=0.18,0.06,0.08,0.11,0.06,0.06,0.08,0.06,0.07,0.06
Execution Time: avg=161.04 ms stddev=10.01 n=10 samples=156.06,167.84,182.88,151.64,157.79,154.3,158.78,155.86,171.9,153.33
3.94s
1M / 262.1K231 ±38 ms
At 262,144 banned ids each of the three processes still hashes the full list in its own SubPlan. Mean Execution Time over 10 runs is 230.75 ms, stddev 38.05 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Finalize Aggregate  (avg_cost=17093.03..17093.04 avg_rows=1 avg_width=40) (actual avg_time=223.184..229.085 stddev_time=38.046..38.028 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=8886
  ->  Gather  (avg_cost=17092.80..17093.01 avg_rows=2 avg_width=40) (actual avg_time=220.889..229.072 stddev_time=37.911..38.028 avg_rows=3 avg_loops=1)
        Workers Planned: avg=2
        Workers Launched: avg=2
        Buffers: shared avg_hit=8886
        ->  Partial Aggregate  (avg_cost=16092.80..16092.81 avg_rows=1 avg_width=40) (actual avg_time=217.733..217.734 stddev_time=36.197..36.198 avg_rows=1 avg_loops=3)
              Buffers: shared avg_hit=8886
              ->  Parallel Seq Scan on orders  (avg_cost=4436.80..15051.13 avg_rows=208333 avg_width=6) (actual avg_time=96.880..186.851 stddev_time=26.264..33.095 avg_rows=245952 avg_loops=3)
                    Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
                    Rows Removed by Filter: avg=87381
                    Buffers: shared avg_hit=8886
                    SubPlan 1
                      ->  Seq Scan on banned  (avg_cost=0.00..3781.44 avg_rows=262144 avg_width=4) (actual avg_time=0.020..16.630 stddev_time=0.005..5.391 avg_rows=262144 avg_loops=3)
                            Buffers: shared avg_hit=3480
Planning:
  Buffers: shared avg_hit=3.5 stddev_hit=11.1
Planning Time: avg=0.08 ms stddev=0.04 n=10 samples=0.18,0.09,0.06,0.12,0.06,0.08,0.06,0.07,0.06,0.07
Execution Time: avg=230.75 ms stddev=38.05 n=10 samples=236.37,336.94,221.2,217.47,223.37,219.26,211.67,218.53,210.51,212.22
4.83s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_188.97s compute×10 runs
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
1M / 100K148.4 ±6.4 ms
At a million orders and 100,000 banned ids PG18 runs the same parallel plan as PG17, a hashed SubPlan built in each of the three processes. Mean Execution Time over 10 runs is 148.39 ms, stddev 6.38 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Finalize Aggregate  (avg_cost=14349.23..14349.24 avg_rows=1 avg_width=40) (actual avg_time=141.995..148.034 stddev_time=6.045..6.395 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=6735
  Disabled: false
  ->  Gather  (avg_cost=14349.00..14349.21 avg_rows=2 avg_width=40) (actual avg_time=141.344..148.023 stddev_time=6.021..6.395 avg_rows=3 avg_loops=1)
        Workers Planned: avg=2
        Workers Launched: avg=2
        Buffers: shared avg_hit=6735
        Disabled: false
        ->  Partial Aggregate  (avg_cost=13349.00..13349.01 avg_rows=1 avg_width=40) (actual avg_time=138.534..138.535 stddev_time=6.071..6.071 avg_rows=1 avg_loops=3)
              Buffers: shared avg_hit=6735
              Disabled: false
              ->  Parallel Seq Scan on orders  (avg_cost=1693.00..12307.33 avg_rows=208333 avg_width=6) (actual avg_time=30.267..102.349 stddev_time=1.759..4.331 avg_rows=300000 avg_loops=3)
                    Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
                    Rows Removed by Filter: avg=33333
                    Buffers: shared avg_hit=6735
                    Disabled: false
                    SubPlan 1
                      ->  Seq Scan on banned  (avg_cost=0.00..1443.00 avg_rows=100000 avg_width=4) (actual avg_time=0.018..5.439 stddev_time=0.004..0.284 avg_rows=100000 avg_loops=3)
                            Buffers: shared avg_hit=1329
                            Disabled: false
Planning:
  Buffers: shared avg_hit=3.5 stddev_hit=11.1
Planning Time: avg=0.08 ms stddev=0.03 n=10 samples=0.17,0.06,0.06,0.06,0.07,0.08,0.06,0.06,0.07,0.08
Execution Time: avg=148.39 ms stddev=6.38 n=10 samples=148.83,140.96,147.15,155.81,147.58,161.88,148.95,147.75,142.8,142.13
4.06s
1M / 262.1K218 ±13 ms
At 262,144 banned ids PG18 still hashes the full list once per process. Mean Execution Time over 10 runs is 217.72 ms, stddev 12.84 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Finalize Aggregate  (avg_cost=17093.03..17093.04 avg_rows=1 avg_width=40) (actual avg_time=210.844..216.728 stddev_time=12.698..12.740 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=8886
  Disabled: false
  ->  Gather  (avg_cost=17092.80..17093.01 avg_rows=2 avg_width=40) (actual avg_time=209.085..216.717 stddev_time=12.714..12.741 avg_rows=3 avg_loops=1)
        Workers Planned: avg=2
        Workers Launched: avg=2
        Buffers: shared avg_hit=8886
        Disabled: false
        ->  Partial Aggregate  (avg_cost=16092.80..16092.81 avg_rows=1 avg_width=40) (actual avg_time=206.086..206.087 stddev_time=12.726..12.726 avg_rows=1 avg_loops=3)
              Buffers: shared avg_hit=8886
              Disabled: false
              ->  Parallel Seq Scan on orders  (avg_cost=4436.80..15051.13 avg_rows=208333 avg_width=6) (actual avg_time=91.135..175.820 stddev_time=7.250..10.531 avg_rows=245952 avg_loops=3)
                    Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
                    Rows Removed by Filter: avg=87381
                    Buffers: shared avg_hit=8886
                    Disabled: false
                    SubPlan 1
                      ->  Seq Scan on banned  (avg_cost=0.00..3781.44 avg_rows=262144 avg_width=4) (actual avg_time=0.019..15.391 stddev_time=0.004..1.598 avg_rows=262144 avg_loops=3)
                            Buffers: shared avg_hit=3480
                            Disabled: false
Planning:
  Buffers: shared avg_hit=3.5 stddev_hit=11.1
Planning Time: avg=0.09 ms stddev=0.05 n=10 samples=0.23,0.06,0.06,0.06,0.06,0.08,0.09,0.07,0.11,0.06
Execution Time: avg=217.72 ms stddev=12.84 n=10 samples=213.17,224.47,210.47,213.46,208.99,209.68,212.53,228.1,207.49,248.83
4.92s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_197.88s compute×10 runs
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int NOT NULL);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
1M / 100K138 ±13 ms
At a million orders and 100,000 banned ids 19rc2 runs a Hash Anti Join in each of the three processes, each building its own hash of the whole banned list. Mean Execution Time over 10 runs is 137.75 ms, stddev 12.51 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Finalize Aggregate  (avg_cost=20031.52..20031.53 avg_rows=1 avg_width=40) (actual avg_time=131.475..137.313 stddev_time=12.583..12.475 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=6735
  Disabled: false
  ->  Gather  (avg_cost=20031.30..20031.51 avg_rows=2 avg_width=40) (actual avg_time=130.285..137.298 stddev_time=12.568..12.476 avg_rows=3 avg_loops=1)
        Workers Planned: avg=2
        Workers Launched: avg=2
        Buffers: shared avg_hit=6735
        Disabled: false
        ->  Partial Aggregate  (avg_cost=19031.30..19031.31 avg_rows=1 avg_width=40) (actual avg_time=127.353..127.356 stddev_time=12.513..12.514 avg_rows=1 avg_loops=3)
              Buffers: shared avg_hit=6735
              Disabled: false
              ->  Hash Anti Join  (avg_cost=2693.00..17156.29 avg_rows=375000 avg_width=6) (actual avg_time=16.515..100.093 stddev_time=2.125..9.461 avg_rows=300000 avg_loops=3)
                    Hash Cond: (orders.customer_id = banned.customer_id)
                    Buffers: shared avg_hit=6735
                    Disabled: false
                    ->  Parallel Seq Scan on orders  (avg_cost=0.00..9572.67 avg_rows=416667 avg_width=10) (actual avg_time=0.007..13.579 stddev_time=0.001..1.686 avg_rows=333333.33 avg_loops=3)
                          Buffers: shared avg_hit=5406
                          Disabled: false
                    ->  Hash  (avg_cost=1443.00..1443.00 avg_rows=100000 avg_width=4) (actual avg_time=15.938..15.939 stddev_time=2.109..2.110 avg_rows=100000 avg_loops=3)
                          Buckets: avg=131072  Batches: avg=1  Memory Usage: avg=4540kB
                          Buffers: shared avg_hit=1329
                          Disabled: false
                          ->  Seq Scan on banned  (avg_cost=0.00..1443.00 avg_rows=100000 avg_width=4) (actual avg_time=0.021..5.739 stddev_time=0.006..0.425 avg_rows=100000 avg_loops=3)
                                Buffers: shared avg_hit=1329
                                Disabled: false
Planning:
  Buffers: shared avg_hit=9.8 stddev_hit=31.0
Planning Time: avg=0.13 ms stddev=0.06 n=10 samples=0.29,0.09,0.11,0.11,0.15,0.1,0.15,0.1,0.13,0.1
Execution Time: avg=137.75 ms stddev=12.51 n=10 samples=136.59,143.91,134.5,134.65,132.65,126.21,170.76,128.6,135.18,134.5
3.93s
1M / 262.1K140.5 ±9.6 ms
At 262,144 banned ids 19rc2 switches to a Parallel Hash Anti Join, the three processes sharing one hash of the list. Mean Execution Time over 10 runs is 140.46 ms, stddev 9.59 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Finalize Aggregate  (avg_cost=21030.68..21030.69 avg_rows=1 avg_width=40) (actual avg_time=134.254..140.426 stddev_time=9.458..9.589 avg_rows=1 avg_loops=1)
  Buffers: shared avg_hit=6566
  Disabled: false
  ->  Gather  (avg_cost=21030.45..21030.66 avg_rows=2 avg_width=40) (actual avg_time=133.430..140.412 stddev_time=9.364..9.587 avg_rows=3 avg_loops=1)
        Workers Planned: avg=2
        Workers Launched: avg=2
        Buffers: shared avg_hit=6566
        Disabled: false
        ->  Partial Aggregate  (avg_cost=20030.45..20030.46 avg_rows=1 avg_width=40) (actual avg_time=130.463..130.465 stddev_time=9.151..9.151 avg_rows=1 avg_loops=3)
              Buffers: shared avg_hit=6566
              Disabled: false
              ->  Parallel Hash Anti Join  (avg_cost=4629.55..18493.25 avg_rows=307440 avg_width=6) (actual avg_time=19.756..108.000 stddev_time=2.341..7.174 avg_rows=245952 avg_loops=3)
                    Hash Cond: (orders.customer_id = banned.customer_id)
                    Buffers: shared avg_hit=6566
                    Disabled: false
                    ->  Parallel Seq Scan on orders  (avg_cost=0.00..9572.67 avg_rows=416667 avg_width=10) (actual avg_time=0.012..13.396 stddev_time=0.004..1.004 avg_rows=333333.33 avg_loops=3)
                          Buffers: shared avg_hit=5406
                          Disabled: false
                    ->  Parallel Hash  (avg_cost=2702.02..2702.02 avg_rows=154202 avg_width=4) (actual avg_time=19.046..19.047 stddev_time=2.331..2.331 avg_rows=87381.33 avg_loops=3)
                          Buckets: avg=262144  Batches: avg=1  Memory Usage: avg=12352kB stddev=21.333kB
                          Buffers: shared avg_hit=1160
                          Disabled: false
                          ->  Parallel Seq Scan on banned  (avg_cost=0.00..2702.02 avg_rows=154202 avg_width=4) (actual avg_time=0.008..2.910 stddev_time=0.003..0.448 avg_rows=87381.33 avg_loops=3)
                                Buffers: shared avg_hit=1160
                                Disabled: false
Planning:
  Buffers: shared avg_hit=9.7 stddev_hit=30.7
Planning Time: avg=0.12 ms stddev=0.04 n=10 samples=0.22,0.09,0.09,0.13,0.1,0.1,0.13,0.1,0.15,0.1
Execution Time: avg=140.46 ms stddev=9.59 n=10 samples=137.76,137.66,141.51,139.17,133.91,131.71,133.03,157.67,157.84,134.37
3.95s

Source: the rc2 ban-list session, run 20261113_025021_phase2-3-cliff-agent-rc2, seq 78 for the card, seq 76 and 77 for the cancellations, seq 74 and 75 for the plans without ANALYZE, seq 12 and 15 for the rescan pace, seq 80 and 81 for the NOT EXISTS times, seq 68 to 71 and 73 for the cancelled sleeps and seq 72 and 82 for the sleeps without a timeout; the scripted million-order session, run 20261113_161016_1m-equivalent-scripted-rc2, seq 1, 2 and 3.

Appendix: The first release candidate

Both tests ran on 19rc1 before the rc2 runs. The three rc1 ban-list calls here ran with SET jit = off.

19rc1 hashed the nullable list at 154,198 ids and switched to the plain SubPlan at 154,199, the same row as 19rc2:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_190.49s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K21.3 ms
On the first release candidate the nullable list is not rewritten either: at 100,000 banned ids 19rc1 runs a hashed SubPlan, one execution in 21.338 ms with JIT off.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan any_1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=33
Planning Time: 0.164 ms
Execution Time: 21.338 ms
0.21s
10K / 154.2K38.5 ms
At 154,198 banned ids 19rc1 still hashes the list, 738 shared buffer hits. One execution, 38.464 ms.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan any_1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=33
Planning Time: 0.141 ms
Execution Time: 38.464 ms
0.28s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_19217.51s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 154.2K95.7s
At 154,199 banned ids 19rc1 stops hashing, the same row where 19rc2 stops: the SubPlan reads a Materialize on disk, rescanned 10,000 times. One execution, 95,669.969 ms.
QUERY PLAN
Aggregate  (cost=19922617.50..19922617.51 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738, temp read=1770462 written=264
  ->  Seq Scan on orders  (cost=0.00..19922592.50 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738, temp read=1770462 written=264
        SubPlan any_1
          ->  Materialize  (cost=0.00..3598.98 rows=154199 width=4) (actual rows=103360.08 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=683, temp read=1770462 written=264
                ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
                      Buffers: shared hit=683
Planning:
  Buffers: shared hit=32
Planning Time: 0.117 ms
Execution Time: 95669.969 ms
95.66s
10K / 200K121.9s
At 200,000 banned ids 19rc1 stays on the plain SubPlan, with 2,290,410 temp blocks read. One execution, 121,886.209 ms.
QUERY PLAN
Aggregate  (cost=25835205.00..25835205.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=940, temp read=2290410 written=342
  ->  Seq Scan on orders  (cost=0.00..25835180.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (SubPlan any_1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=940, temp read=2290410 written=342
        SubPlan any_1
          ->  Materialize  (cost=0.00..4667.00 rows=200000 width=4) (actual rows=133895.61 loops=10000)
                Storage: Disk  Maximum Storage: 4096kB
                Buffers: shared hit=885, temp read=2290410 written=342
                ->  Seq Scan on banned  (cost=0.00..2885.00 rows=200000 width=4) (actual rows=200000.00 loops=1)
                      Buffers: shared hit=885
Planning:
  Buffers: shared hit=33
Planning Time: 0.195 ms
Execution Time: 121886.209 ms
121.85s

PG18, in the same session, hashed through 262,144 ids. Its 42.129 ms at 154,199 makes 19rc1 about 2,300 times slower there, and PG17's 44.734 ms about 2,100 times:

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_181.60s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K24.4 ms
At 100,000 banned ids PG18 hashes the nullable list, a hashed SubPlan over one Seq Scan of banned. One execution with JIT off, 24.398 ms.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000.00 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=26
Planning Time: 0.130 ms
Execution Time: 24.398 ms
0.25s
10K / 154.2K#145.5 ms
At 154,198 banned ids PG18 still hashes the list, a hashed SubPlan over one Seq Scan of banned. One execution, 45.454 ms.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.117 ms
Execution Time: 45.454 ms
0.30s
10K / 154.2K#242.1 ms
At 154,199 banned ids, where 19rc1 switches to the plain SubPlan, PG18 keeps hashing. One execution, 42.129 ms.
QUERY PLAN
Aggregate  (cost=2815.49..2815.50 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.49..2790.49 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199.00 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.147 ms
Execution Time: 42.129 ms
0.29s
10K / 200K53.2 ms
At 200,000 banned ids PG18 is still on the hashed SubPlan. One execution, 53.194 ms.
QUERY PLAN
Aggregate  (cost=3590.00..3590.01 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=940
  ->  Seq Scan on orders  (cost=3385.00..3565.00 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=940
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2885.00 rows=200000 width=4) (actual rows=200000.00 loops=1)
                Buffers: shared hit=885
Planning:
  Buffers: shared hit=26
Planning Time: 0.138 ms
Execution Time: 53.194 ms
0.34s
10K / 262.1K77 ms
At 262,144 banned ids, the largest list on this card, PG18 still hashes. One execution, 77.042 ms.
QUERY PLAN
Aggregate  (cost=4641.80..4641.81 rows=1 width=40) (actual rows=1.00 loops=1)
  Buffers: shared hit=1215
  ->  Seq Scan on orders  (cost=4436.80..4616.80 rows=5000 width=6) (actual rows=6667.00 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144.00 loops=1)
                Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.192 ms
Execution Time: 77.042 ms
0.42s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres1.70s compute
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS) SELECT count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, total numeric(10,2) NOT NULL);
INSERT INTO orders SELECT i, i, (i % 1000) + 0.99 FROM generate_series(1, ${O}) i;
CREATE TABLE banned (customer_id int);
INSERT INTO banned SELECT i * 3 FROM generate_series(1, ${B}) i;
VACUUM ANALYZE;
ScaleTimePlanCompute
10K / 100K40.7 ms
At 100,000 banned ids PG17 hashes the nullable list here too, a hashed SubPlan over one Seq Scan of banned. One execution with JIT off, 40.655 ms.
QUERY PLAN
Aggregate  (cost=1898.00..1898.01 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=498
  ->  Seq Scan on orders  (cost=1693.00..1873.00 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=498
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..1443.00 rows=100000 width=4) (actual rows=100000 loops=1)
                Buffers: shared hit=443
Planning:
  Buffers: shared hit=26
Planning Time: 0.190 ms
Execution Time: 40.655 ms
0.25s
10K / 154.2K#145.7 ms
At 154,198 banned ids PG17 still hashes the list. One execution, 45.723 ms, 738 shared buffer hits.
QUERY PLAN
Aggregate  (cost=2815.48..2815.49 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.47..2790.47 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.98 rows=154198 width=4) (actual rows=154198 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.100 ms
Execution Time: 45.723 ms
0.29s
10K / 154.2K#244.7 ms
At 154,199 banned ids, where 19rc1 switches to the plain SubPlan, PG17 keeps hashing. One execution, 44.734 ms.
QUERY PLAN
Aggregate  (cost=2815.49..2815.50 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=738
  ->  Seq Scan on orders  (cost=2610.49..2790.49 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=738
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2224.99 rows=154199 width=4) (actual rows=154199 loops=1)
                Buffers: shared hit=683
Planning:
  Buffers: shared hit=26
Planning Time: 0.107 ms
Execution Time: 44.734 ms
0.30s
10K / 200K89.5 ms
At 200,000 banned ids PG17 is still on the hashed SubPlan, 940 shared buffer hits. One execution, 89.468 ms.
QUERY PLAN
Aggregate  (cost=3590.00..3590.01 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=940
  ->  Seq Scan on orders  (cost=3385.00..3565.00 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=940
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..2885.00 rows=200000 width=4) (actual rows=200000 loops=1)
                Buffers: shared hit=885
Planning:
  Buffers: shared hit=26
Planning Time: 0.171 ms
Execution Time: 89.468 ms
0.42s
10K / 262.1K80.8 ms
At 262,144 banned ids, the largest list on this card, PG17 still hashes, reading 1,215 shared buffers. One execution, 80.796 ms.
QUERY PLAN
Aggregate  (cost=4641.80..4641.81 rows=1 width=40) (actual rows=1 loops=1)
  Buffers: shared hit=1215
  ->  Seq Scan on orders  (cost=4436.80..4616.80 rows=5000 width=6) (actual rows=6667 loops=1)
        Filter: (NOT (ANY (customer_id = (hashed SubPlan 1).col1)))
        Rows Removed by Filter: 3333
        Buffers: shared hit=1215
        SubPlan 1
          ->  Seq Scan on banned  (cost=0.00..3781.44 rows=262144 width=4) (actual rows=262144 loops=1)
                Buffers: shared hit=1160
Planning:
  Buffers: shared hit=26
Planning Time: 0.131 ms
Execution Time: 80.796 ms
0.43s

The campaign query over 90 days with contact_id nullable: PG18 hashed the list, and 19rc1 hashed it again after SET work_mem = '8MB':

Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1811.75s computewarm ×3×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 90 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M43.8 ±1.7 ms
PG18 hashes the 90-day subquery: the planner estimates 163,410 rows, the Index Only Scan returns 162,209, and the hashed SubPlan is built once per run with no temp reads. Mean Execution Time over 10 runs is 43.83 ms, stddev 1.74 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=6917.57..6920.42 avg_rows=10 avg_width=26) (actual avg_time=43.802..43.807 stddev_time=1.742..1.743 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=807
  Disabled: false
  ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=6917.57..9487.59 avg_rows=9022 avg_width=26) (actual avg_time=43.800..43.805 stddev_time=1.742..1.743 avg_rows=10 avg_loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((NOT (ANY (id = (hashed SubPlan 1).col1))) AND (status = 'subscribed'::text))
        Rows Removed by Filter: avg=3
        Buffers: shared avg_hit=807
        Disabled: false
        Index Searches: avg=1
        SubPlan 1
          ->  Index Only Scan using sends_tenant_sent on sends s  (avg_cost=0.43..6508.63 avg_rows=163410 avg_width=4) (actual avg_time=0.014..19.965 stddev_time=0.003..0.672 avg_rows=162209 avg_loops=1)
                Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '90 days'::interval)))
                Heap Fetches: avg=0
                Buffers: shared avg_hit=803
                Disabled: false
                Index Searches: avg=1
Planning Time: avg=0.14 ms stddev=0.04 n=10 samples=0.11,0.11,0.11,0.12,0.11,0.13,0.22,0.11,0.2,0.15
Execution Time: avg=43.83 ms stddev=1.74 n=10 samples=43.75,43.97,46.61,46.25,41.04,44.98,42.59,42.18,43.23,43.73
11.75s
Benchmark SQL
benchmarkSql complete
MCP Tool✓
postgres_1911.21s computewarm ×3×10 runs
EXPLAIN (ANALYZE, BUFFERS) SELECT c.id, c.email, c.first_name
FROM contacts c
WHERE c.tenant_id = 1
  AND c.status = 'subscribed'
  AND c.id NOT IN (SELECT s.contact_id
                   FROM sends s
                   WHERE s.tenant_id = 1
                     AND s.sent_at >= now() - 90 * interval '1 day')
ORDER BY c.id
LIMIT 10
-- contact ids come in one block per tenant: tenant 1 has 20,000 contacts, tenant 2 10,000, tenant t about 12,000 / t
CREATE TABLE tenant_sizes AS SELECT t AS tenant_id, CASE WHEN t = 1 THEN 20000 WHEN t = 2 THEN 10000 ELSE greatest(5, 12000 / t) END AS n FROM generate_series(1, ${TENANTS}) t;
CREATE TABLE tenant_blocks AS SELECT tenant_id, n, (sum(n) OVER (ORDER BY tenant_id) - n + 1)::int AS first_id FROM tenant_sizes;
CREATE TABLE contacts (id INT PRIMARY KEY, tenant_id INT NOT NULL, email TEXT NOT NULL, first_name TEXT, status TEXT NOT NULL);
INSERT INTO contacts SELECT b.first_id + k, b.tenant_id, 'c' || (b.first_id + k) || '@example.com', 'Ann',
  CASE WHEN random() < 0.9 THEN 'subscribed' ELSE 'unsubscribed' END
FROM tenant_blocks b, generate_series(0, b.n - 1) k;

-- a year of sends: tenant 1 sends a third of them, tenant 2 a sixth, the rest follow a power law;
-- each tenant mails its engaged segment (every fifth contact) over and over
CREATE TABLE sends (id INT PRIMARY KEY, tenant_id INT NOT NULL, contact_id INT, sent_at TIMESTAMPTZ NOT NULL);
INSERT INTO sends SELECT g.i, b.tenant_id, b.first_id + 5 * floor(random() * (b.n / 5))::int, NOW() - random() * interval '365 days'
FROM (SELECT i, CASE WHEN r < 0.33 THEN 1 WHEN r < 0.495 THEN 2 ELSE 3 + floor((${TENANTS} - 2) * power(random(), 2))::int END AS t
      FROM (SELECT i, random() AS r FROM generate_series(1, ${SENDS}) i) a) g
JOIN tenant_blocks b ON b.tenant_id = g.t;
CREATE INDEX contacts_tenant_id ON contacts (tenant_id, id);
CREATE INDEX sends_tenant_sent ON sends (tenant_id, sent_at) INCLUDE (contact_id);
CREATE INDEX sends_contact ON sends (contact_id);
DROP TABLE tenant_blocks;
DROP TABLE tenant_sizes;
VACUUM ANALYZE;
ScaleTimePlanCompute
2K / 2M35.6 ±1.2 ms
Under SET work_mem = '8MB', 19rc1 hashes the 90-day subquery again: the planner estimates 163,834 rows, and the hashed SubPlan is built once per run with no temp reads. Mean Execution Time over 10 runs is 35.6 ms, stddev 1.22 ms.
QUERY PLAN
Statistics over 10 runs: avg = mean, stddev = sample standard deviation, omitted where 0.
Limit  (avg_cost=6935.11..6937.98 avg_rows=10 avg_width=26) (actual avg_time=35.567..35.573 stddev_time=1.222..1.222 avg_rows=10 avg_loops=1)
  Buffers: shared avg_hit=807
  Disabled: false
  ->  Index Scan using contacts_tenant_id on contacts c  (avg_cost=6935.11..9493.21 avg_rows=8927 avg_width=26) (actual avg_time=35.566..35.572 stddev_time=1.222..1.223 avg_rows=10 avg_loops=1)
        Index Cond: (tenant_id = 1)
        Filter: ((NOT (ANY (id = (hashed SubPlan any_1).col1))) AND (status = 'subscribed'::text))
        Rows Removed by Filter: avg=3
        Buffers: shared avg_hit=807
        Disabled: false
        Index Searches: avg=1
        SubPlan any_1
          ->  Index Only Scan using sends_tenant_sent on sends s  (avg_cost=0.43..6525.11 avg_rows=163834 avg_width=4) (actual avg_time=0.015..16.498 stddev_time=0.005..0.326 avg_rows=162209 avg_loops=1)
                Index Cond: ((tenant_id = 1) AND (sent_at >= (now() - '90 days'::interval)))
                Heap Fetches: avg=0
                Buffers: shared avg_hit=803
                Disabled: false
                Index Searches: avg=1
Planning Time: avg=0.13 ms stddev=0.02 n=10 samples=0.11,0.12,0.12,0.16,0.18,0.12,0.12,0.12,0.12,0.12
Execution Time: avg=35.6 ms stddev=1.22 n=10 samples=36.74,35.4,36.38,35.98,34.57,34.97,34.35,37.46,36.55,33.62
11.21s

Source: the rc1 ban-list session, run 20261112_231953_phase2-3-cliff-agent, seq 25, 27 and 57; the rc1 campaign session, run 20261112_224834_phase1-s11-agent, seq 41 and 42, and seq 32 for the card in the body.

Appendix: Where each version stops hashing, in the code

The code is cut from the PostgreSQL source at the tags that were measured, REL_17_11, REL_18_6 and REL_19_RC2; REL_19_RC1 has the same size estimate as REL_19_RC2. Most comments, overflow checks and branches the arithmetic doesn't pass through are cut, ... marks any other cut inside a function, and the first line of each block names the version and the file. The change behind PG19's cliff is Tom Lane's 1ea5bdb00bf, committed 2025-11-02.

The decision. When an uncorrelated NOT IN stays a SubPlan, build_subplan hashes the list if subplan_is_hashable says it fits, and otherwise puts a Material node on top of it for the executor to read again for every outer row (19rc2, lines 535 to 560; PG17 and PG18 call subplan_is_hashable(plan) with no second argument):

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: build_subplan */
		if (subLinkType == ANY_SUBLINK &&
			splan->parParam == NIL &&
			subplan_is_hashable(plan, unknownEqFalse) &&
			testexpr_is_hashable(splan->testexpr, splan->paramIds))
			splan->useHashTable = true;

		else if (splan->parParam == NIL && enable_material &&
				 !ExecMaterializesOutput(nodeTag(plan)))
			plan = materialize_finished_plan(plan);

The test. subplan_is_hashable in 17.11 and 18.6, which are identical, and in 19rc2 (17.11, 18.6, 19rc2):

/* PostgreSQL 17.11 and 18.6, src/backend/optimizer/plan/subselect.c */
static bool
subplan_is_hashable(Plan *plan)
{
	double		subquery_size;

	subquery_size = plan->plan_rows *
		(MAXALIGN(plan->plan_width) + MAXALIGN(SizeofHeapTupleHeader));
	if (subquery_size > get_hash_memory_limit())
		return false;

	return true;
}

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c */
static bool
subplan_is_hashable(Plan *plan, bool unknownEqFalse)
{
	Size		hashtablesize;

	hashtablesize = EstimateSubplanHashTableSpace(plan->plan_rows,
												  plan->plan_width,
												  unknownEqFalse);
	if (hashtablesize >= get_hash_memory_limit())
		return false;

	return true;
}

plan_rows and plan_width are the planner's estimate for the subquery, which the captured plans print on the scan of banned as rows=154199 width=4 and so on, the list's real length at every size here. Two things changed: PG19 asks for the size of an actual hash table, and it rejects on equality, >= where PG17 has >. PG17's own comment on its formula says the heap tuple header it charges "provides some fudge factor for hashtable overhead".

The limit is the same in all three, get_hash_memory_limit (17.11, 19rc2):

/* PostgreSQL 17.11, 18.6 and 19rc2, src/backend/executor/nodeHash.c: get_hash_memory_limit */
	mem_limit = (double) work_mem * hash_mem_multiplier * 1024.0;

work_mem was 4096 kB and hash_mem_multiplier 2 on all three servers, so the limit is 8 MiB. In bytes that's 4096 x 2 x 1024 = 8,388,608.

PG17 and PG18: 32 bytes a row. MAXALIGN rounds up to MAXIMUM_ALIGNOF, which is 8 on these 64-bit builds (configure sets it, so it isn't in the repository), and the heap tuple header is the 23 bytes its struct marks (c.h, htup_details.h):

/* PostgreSQL 17.11, src/include/c.h */
#define MAXALIGN(LEN)			TYPEALIGN(MAXIMUM_ALIGNOF, (LEN))

/* PostgreSQL 17.11, src/include/access/htup_details.h */
struct HeapTupleHeaderData
{
	union
	{
		HeapTupleFields t_heap;
		DatumTupleFields t_datum;
	}			t_choice;

	ItemPointerData t_ctid;		/* current TID of this or newer tuple (or a
								 * speculative insertion token) */
	...
	uint8		t_hoff;			/* sizeof header incl. bitmap, padding */

	/* ^ - 23 bytes - ^ */
	...
};

#define SizeofHeapTupleHeader offsetof(HeapTupleHeaderData, t_bits)

So an int costs MAXALIGN(4) + MAXALIGN(23) = 8 + 24 = 32 bytes. 262,144 rows x 32 = 8,388,608 bytes, which isn't greater than the limit, so that list still hashes. 262,145 rows x 32 = 8,388,640 bytes, which is, so that one gets the plain SubPlan.

PG19: a NOT IN also pays for a NULLs table. make_subplan hands its isTopQual flag to build_subplan, where it becomes unknownEqFalse, and the NOT that a NOT IN puts above its = ANY turns the flag off in process_sublinks_mutator (lines 244 to 248, lines 2427 to 2435):

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: make_subplan */
	/* And convert to SubPlan or InitPlan format. */
	result = build_subplan(root, plan, best_path,
						   subroot, plan_params,
						   subLinkType, subLinkId,
						   testexpr, NIL, isTopQual);

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: process_sublinks_mutator */
	/*
	 * If we recurse down through anything other than an AND or OR node, we
	 * are definitely not at top qual level anymore.
	 */
	locContext.isTopQual = false;

With unknownEqFalse false, the estimate adds a second table, for rows whose comparison comes out NULL, sized at a sixteenth of the rows (EstimateSubplanHashTableSpace):

/* PostgreSQL 19rc2, src/backend/executor/nodeSubplan.c: EstimateSubplanHashTableSpace */
	/* Estimate size of main hashtable */
	tab1space = EstimateTupleHashTableSpace(nentries,
											tupleWidth,
											0 /* no additional data */ );

	/* Done if we don't need a hashnulls table */
	if (unknownEqFalse)
		return tab1space;

	nentries /= 16;
	if (nentries < 1)
		nentries = 1;

	tab2space = EstimateTupleHashTableSpace(nentries,
											tupleWidth,
											0 /* no additional data */ );

	return tab1space + tab2space;

Each table is a bucket array plus the stored rows (EstimateTupleHashTableSpace):

/* PostgreSQL 19rc2, src/backend/executor/execGrouping.c: EstimateTupleHashTableSpace */
	/* First estimate the space needed for the simplehash table */
	sh_space = tuplehash_estimate_space(nentries);

	tuples_space = nentries * (MAXALIGN(SizeofMinimalTupleHeader) +
							   MAXALIGN(tupleWidth) +
							   MAXALIGN(additionalsize));

	return (Size) (sh_space + tuples_space);

The bucket array is the row count divided by the fill factor, rounded up to a power of two, times the size of an entry, plus the table's header (SH_ESTIMATE_SPACE, SH_FILLFACTOR):

/* PostgreSQL 19rc2, src/include/lib/simplehash.h */
#define SH_FILLFACTOR (0.9)

	/* scale request by SH_FILLFACTOR, as SH_CREATE does */
	nentries = nentries / SH_FILLFACTOR;

	/* should be safe to convert to uint64 */
	size = (uint64) nentries;

	/* round up size to the next power of 2, that's how bucketing works */
	size = pg_nextpower2_64(size);

	/* calculate space needed for ->data */
	space = ((uint64) sizeof(SH_ELEMENT_TYPE)) * size;

	return (size_t) space + sizeof(SH_TYPE);

The sizes come from three structs (execnodes.h, simplehash.h, htup_details.h):

/* PostgreSQL 19rc2, src/include/nodes/execnodes.h */
typedef struct TupleHashEntryData
{
	MinimalTuple firstTuple;	/* -> copy of first tuple in this group */
	uint32		status;			/* hash status */
	uint32		hash;			/* hash value (cached) */
} TupleHashEntryData;

/* PostgreSQL 19rc2, src/include/lib/simplehash.h */
typedef struct SH_TYPE
{
	uint64		size;
	uint32		members;
	uint32		sizemask;
	uint32		grow_threshold;
	SH_ELEMENT_TYPE *data;
#ifndef SH_RAW_ALLOCATOR
	MemoryContext ctx;
#endif
	void	   *private_data;
}			SH_TYPE;

/* PostgreSQL 19rc2, src/include/access/htup_details.h */
#define MINIMAL_TUPLE_PADDING \
	((offsetof(HeapTupleHeaderData, t_infomask2) - sizeof(uint32)) % MAXIMUM_ALIGNOF)

struct MinimalTupleData
{
	uint32		t_len;			/* actual length of minimal tuple */

	char		mt_padding[MINIMAL_TUPLE_PADDING];

	uint16		t_infomask2;	/* number of attributes + various flags */
	uint16		t_infomask;		/* various flag bits, see below */
	uint8		t_hoff;			/* sizeof header incl. bitmap, padding */
	...
};

#define SizeofMinimalTupleHeader offsetof(MinimalTupleData, t_bits)

On a 64-bit build, an entry is a pointer and two uint32s, 16 bytes. The table's header is 8 + 4 + 4 + 4 bytes, padded to 24, then three pointers, 48 bytes. A stored row is a MinimalTuple, whose header is 15 bytes, t_len, then 6 bytes of padding, then the 5 bytes of fields it shares with the heap header; MAXALIGN makes that 16, plus 8 for the int.

That's 24 bytes a stored row, against the 32 that PG17 and PG18 charge, but PG19 adds both bucket arrays on top. For the last size PG19 hashes and the first it doesn't:

154,198 rows154,199 rows
Main table buckets: rows / 0.9, next power of 2262,144262,144
Main table bucket array: 262,144 x 16 + 484,194,352 B4,194,352 B
Main table rows: rows x (16 + 8)3,700,752 B3,700,776 B
NULLs table entries: rows / 169,637.3759,637.4375
NULLs table buckets: entries / 0.9, next power of 216,38416,384
NULLs table bucket array: 16,384 x 16 + 48262,192 B262,192 B
NULLs table rows: entries x 24, truncated with its table231,297 B231,298.5 B
Estimate8,388,593 B8,388,618 B
Against the 8,388,608 B limit15 under: hashed10 over: plain

Both cliffs, PG17's and PG19's, reproduce to the row from these excerpts and the row estimates in the captured plans, on rc2 and on rc1.

Two consequences of the same code, neither of them measured. First, the bucket array is about half the budget. At 154,199 rows the main table rounds up to 262,144 buckets, which take 4,194,352 bytes. At 262,144 rows, PG17's last hashed size, it would need twice as many: 524,288 x 16 + 48 = 8,388,656 bytes, over the limit before a single row is stored. That's the overhead the commit message says the old formula left out.

Second, in the estimate, the NULLs table is what tips 154,199 over. The main table alone is 7,895,128 bytes, 493,480 under the limit, and the NULLs table adds 493,490. The estimator's comment says it skips "the special case for a single hash column" because "this table has relatively little impact on the total estimate anyway", and when the executor builds that table for a one-column NOT IN, it sizes it for a single entry (buildSubPlanHash):

/* PostgreSQL 19rc2, src/backend/executor/nodeSubplan.c: buildSubPlanHash */
	if (!subplan->unknownEqFalse)
	{
		if (ncols == 1)
			nentries = 1;		/* there can only be one entry */

Without the NULLs-table term, the same arithmetic would hash up to 174,760 rows. That's a reading of the code, not a measurement of the executor's real memory use, and the comment names the simplification itself.

Other key widths and memory. The same code puts the cliffs at these sizes, computed and not measured, with hash_mem_multiplier 2:

Key and work_memPG17 and PG18 hash up toPG19 hashes up to
int or bigint, 4MB262,144154,198
uuid, 4MB209,715117,979
int, 8MB524,288308,400
int, 16MB1,048,576616,805
int, 64MB4,194,3042,467,233

bigint lands on the same rows as int because MAXALIGN rounds 4 bytes and 8 bytes to the same 8. A uuid is 16 bytes, so it moves both.

The campaign query, predicted. Its plans print their own estimates for the 90-day list. On 19rc1 at the default that's 160,692 rows, past 154,198, so plain. On PG18 it's 163,410, under 262,144, so hashed. On 19rc1 with work_mem at 8MB it's 163,834, under 308,400, so hashed again. All three match what ran.

Appendix: When PG19 rewrites a NOT IN, in the code

Cut from REL_17_11, REL_18_6 and REL_19_RC2 the same way as the hash-size appendix. The rewrite is Richard Guo's 383eb21eb, "Convert NOT IN sublinks to anti-joins when safe", committed 2026-03-12.

Where it starts. pull_up_sublinks_qual_recurse looks under a NOT for a subquery it can turn into a join. PG17 and PG18 only try EXISTS. PG19 also tries ANY, which is what x NOT IN (SELECT ...) becomes under the NOT (17.11, 18.6, 19rc2):

/* PostgreSQL 17.11 and 18.6, src/backend/optimizer/prep/prepjointree.c */
	if (is_notclause(node))
	{
		/* If the immediate argument of NOT is EXISTS, try to convert */
		...
			if (sublink->subLinkType == EXISTS_SUBLINK)

/* PostgreSQL 19rc2, src/backend/optimizer/prep/prepjointree.c */
	if (is_notclause(node))
	{
		/* If the immediate argument of NOT is ANY or EXISTS, try to convert */
		...
			if (sublink->subLinkType == ANY_SUBLINK)
			{
				if ((j = convert_ANY_sublink_to_join(root, sublink, true,
													 available_rels1)) != NULL)

The gate. convert_ANY_sublink_to_join gives up on a NOT IN unless both sides are provably non-null (lines 1375 to 1386):

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: convert_ANY_sublink_to_join */
	/*
	 * Per SQL spec, NOT IN is not ordinarily equivalent to an anti-join, so
	 * that by default we have to fail when under_not.  However, if we can
	 * prove that neither the outer query's expressions nor the sub-select's
	 * output columns can be NULL, and further that the operator itself cannot
	 * return NULL for non-null inputs, then the logic is identical and it's
	 * safe to convert NOT IN to an anti-join.
	 */
	if (under_not &&
		(!sublink_testexpr_is_not_nullable(root, sublink) ||
		 !query_outputs_are_not_nullable(subselect)))
		return NULL;

The operator has to belong to a B-tree or hash operator family, as the built-in int = int does (lines 1515 to 1534):

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: sublink_testexpr_is_not_nullable */
		/*
		 * We rely on membership in a B-tree or Hash operator family as a
		 * guarantee that the operator acts as a proper boolean comparison and
		 * does not yield NULL for valid non-null inputs.
		 */
		if (!op_is_safe_index_member(opexpr->opno))
			return false;

The outer side has to be non-null by its declaration, and the function says outright that it doesn't look at the outer query's conditions (lines 1589 to 1619):

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: sublink_testexpr_is_not_nullable */
	/* Check that every outer expression is non-nullable */
	foreach_ptr(Expr, expr, outer_exprs)
	{
		/*
		 * We have already collected relation-level not-null constraints for
		 * the outer query, so we can consult the global hash table for
		 * nullability information.
		 */
		if (!expr_is_nonnullable(root, expr, NOTNULL_SOURCE_HASHTABLE))
			return false;

		/*
		 * Note: It is possible to further prove non-nullability by examining
		 * the qual clauses available at or below the jointree node where this
		 * NOT IN clause is evaluated, but for the moment it doesn't seem
		 * worth the extra complication.
		 */
	}

That's why customer_id IS NOT NULL in the outer WHERE left the ban-list plan alone. Richard Guo's proposal on pgsql-hackers said as much about outer-query conditions: "This patch does not attempt to do so." For a bare column the declaration is the only way in. expr_is_nonnullable also accepts a few expressions that can't be NULL, such as a COALESCE with a non-null argument, but writing the outer side that way changes what the query means, and none of it was measured.

The subquery's side can be proved non-null by its declaration, or by a strict condition in the subquery's own WHERE or ON (query_outputs_are_not_nullable, lines 2235 to 2268):

/* PostgreSQL 19rc2, src/backend/optimizer/util/clauses.c: query_outputs_are_not_nullable */
		if (expr_is_nonnullable(&subroot, expr, NOTNULL_SOURCE_CATALOG))
			continue;

		/* Note we can only prove things about this query's own Vars */
		if (IsA(expr, Var) && ((Var *) expr)->varlevelsup == 0)
		{
			Var		   *var = (Var *) expr;

			/*
			 * For a plain Var, even if that didn't work, we can conclude that
			 * the Var is not nullable if find_nonnullable_vars can find a
			 * "var IS NOT NULL" or similarly strict condition among the quals
			 * on non-outerjoined-rels.  Compute the list of Vars having such
			 * quals if we didn't already.
			 */
			if (!computed_nonnullable_vars)
			{
				find_subquery_safe_quals((Node *) query->jointree, &safe_quals);
				safe_quals = (List *)
					flatten_join_alias_vars(NULL, query, (Node *) safe_quals);
				nonnullable_vars = find_nonnullable_vars((Node *) safe_quals);
				computed_nonnullable_vars = true;
			}

			if (!mbms_is_member(var->varno,
								var->varattno - FirstLowInvalidHeapAttributeNumber,
								nonnullable_vars))
				return false;	/* we failed to prove the Var non-null */
		}

That's why IS NOT NULL inside the subquery brings the anti join back, on both tests.

A declared NOT NULL doesn't count if an outer join can null the column, or if the constraint isn't valid (lines 4806 to 4825, lines 4888 to 4925):

/* PostgreSQL 19rc2, src/backend/optimizer/util/clauses.c: var_is_nonnullable */
	/* could the Var be nulled by any outer joins or grouping sets? */
	if (!bms_is_empty(var->varnullingrels))
		return false;
	...
		case NOTNULL_SOURCE_CATALOG:
				/*
				 * We check the attnullability field in the tuple descriptor.
				 * This is necessary rather than checking the attnotnull field
				 * from the attribute relation, because attnotnull is also set
				 * for invalid (NOT VALID) NOT NULL constraints, which do not
				 * guarantee the absence of NULLs.
				 */
				...
				result = (attr->attnullability == ATTNULLABLE_VALID);

That covers the LEFT JOIN checks in the correctness appendix: PG19 kept those NOT INs as SubPlans and returned the right rows.

Between rc1 and rc2 the subquery's operands also have to be bare parameters, because a cast on the subquery's output can return NULL for a non-null input. This is Bhuvan Chilukuti's b7eee09eab7, committed by Richard Guo on Nov 4 (19rc2, lines 1578 to 1587):

/* PostgreSQL 19rc2, src/backend/optimizer/plan/subselect.c: sublink_testexpr_is_not_nullable, added for rc2 */
	/* Check that every inner operand is a possibly-relabeled sublink Param */
	foreach_ptr(Expr, expr, inner_exprs)
	{
		while (IsA(expr, RelabelType))
			expr = ((RelabelType *) expr)->arg;

		if (!IsA(expr, Param) ||
			((Param *) expr)->paramkind != PARAM_SUBLINK)
			return false;
	}

No measured query had a cast, and rc1 and rc2 agree on every plan they share.

ConditionWhat 19rc2 didMeasured
Both columns declared NOT NULLanti joinyes, both tests
Subquery column nullable, no NULLs in itSubPlanyes, both tests
The same, with IS NOT NULL inside the subqueryanti joinyes, both tests
Outer column nullableSubPlanyes, ban list
Outer column nullable, IS NOT NULL in the outer WHERESubPlanplan only, ban list
NULLs from a LEFT JOIN over NOT NULL columnsSubPlan, correct rowsyes, ban list
A cast on the subquery's outputSubPlan, since rc2no, code only
A user-defined = in a hash or B-tree operator class that can return NULL for non-null inputrewritten, with wrong results, per the BUG #19852 threadno
Appendix: The correctness checks

These are row sets, plain queries rather than benchmarks, so they're quoted here rather than shown as cards.

The ban-list checks. 10,000 orders against 100,000 banned ids, seed 4242, the same statement on each server:

SELECT 'not_in' AS form, count(*) AS n, sum(total) AS s FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned)
UNION ALL SELECT 'not_in_filtered', count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM banned WHERE customer_id IS NOT NULL)
UNION ALL SELECT 'not_exists', count(*), sum(total) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.customer_id = o.customer_id)
UNION ALL SELECT 'oj_outer_not_in', count(*), sum(o.total) FROM generate_series(1,3) g LEFT JOIN orders o ON o.id = g + 100000 WHERE o.customer_id NOT IN (SELECT customer_id FROM banned)
UNION ALL SELECT 'oj_outer_not_exists', count(*), sum(o.total) FROM generate_series(1,3) g LEFT JOIN orders o ON o.id = g + 100000 WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.customer_id = o.customer_id)
UNION ALL SELECT 'oj_inner_not_in', count(*), sum(total) FROM orders WHERE customer_id NOT IN (SELECT b.customer_id FROM generate_series(1,3) g LEFT JOIN banned b ON b.customer_id = g)

The oj_ forms make NULLs out of columns declared NOT NULL: the outer one joins three order ids that don't exist, and the inner one joins banned to 1, 2 and 3, of which only 3 is banned. Rows returned, identical on PG17, PG18 and PG19:

Schema and datanot_innot_in_filterednot_existsoj_outer_not_inoj_outer_not_existsoj_inner_not_in
Both columns NOT NULL666766676667030
banned.customer_id nullable, a NULL added066676667030
orders.customer_id nullable, an order with a NULL customer added666766676668030

The sums of total match wherever the counts do: 3336267.33 for the 6667 orders. The 6668 count, NOT EXISTS keeping the order with a NULL customer, sums to 3336269.32. Source: the rc2 ban-list session, run 20261113_025021_phase2-3-cliff-agent-rc2, seq 44, 46 and 47, then 51, 53 and 54, then 56, 59 and 61, each trio on 19rc2, PG17 and PG18. On PG19 the plan of the same statement (seq 45, EXPLAIN (COSTS OFF)) keeps oj_outer_not_in and oj_inner_not_in as hashed SubPlans.

On the plain SubPlan path. The same NULL checks with 1,000 orders and a list long enough that the unfiltered NOT IN runs as a plain SubPlan on every version, 154,199 ids on PG19 and 262,145 on PG17 and PG18, with rows read from EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF):

Datanot_innot_in_filterednot_exists
A NULL added to the list0667667
An order with a NULL customer667667668

Identical on all three. Source: the same session, seq 62 and 63 on 19rc2, 64 and 65 on PG17, 66 and 67 on PG18.

A million orders. 1,000,000 orders against 300,000 banned ids, both columns NOT NULL:

ServerNOT INNOT EXISTS
19rc2700000, sum 350343000.00700000, sum 350343000.00
17.11cancelled at 120 s, see the million-order appendix700000, sum 350343000.00
18.6cancelled at 120 s, see the million-order appendix700000, sum 350343000.00

Source: the same session, seq 83, 84 and 85.

The campaign query, full result. NOT IN against NOT EXISTS for tenant 1 over 365 days, without the LIMIT, compared with EXCEPT ALL both ways and an md5 of the ordered rows, under SET statement_timeout = '120s':

Serverwork_memNOT IN rowsNOT EXISTS rowsRows in only onemd5 of both
19rc2default14375143750 and 0d10a17e5a533b38a54039f80cf1d380b
17.11defaultcancelled at 120 s
18.6defaultcancelled at 120 s
17.1164MB14375143750 and 0d10a17e5a533b38a54039f80cf1d380b
18.664MB14375143750 and 0d10a17e5a533b38a54039f80cf1d380b
19rc264MB14375143750 and 0d10a17e5a533b38a54039f80cf1d380b

At 64MB, by the arithmetic in the hash-size appendix, PG17 and PG18 hash the list, so the correctness of their default plain SubPlan on this query rests on the ban-list checks above, not on this one. Source: the rc2 campaign session, run 20261113_015648_phase1-s11-agent-rc2, seq 44, then 45 and 47 (cancelled, runtimeDurationMs 120002 and 120003), then 49, 50 and 51.

Appendix: How the measurements were made

The experimenter. The stock OpenHands agent (openhands-sdk 1.41.0) running Claude Opus 5.5 at effort xhigh, sealed in a Docker container by a harness. Inside the box it had a shell and files, a local ExoBench as an MCP server, and web search. It had no other internet access, no access to the host and no database ports, so ExoBench was its only way to a database.

The tasks. Each session got a task file with the data, the questions and "How you measure is up to you." The ban-list task asked the agent to check the usual account of NOT IN on PG17 and PG18 first, then whether PG19 rewrites the NOT IN with both columns NOT NULL and with either one nullable, where PG19 stops hashing for the forms it doesn't rewrite, whether every result is correct including the NULL cases, and what happens at 1,000,000 orders against 300,000 ids under a 120-second timeout proven on a short call first. The campaign task asked how PG17, PG18 and PG19 run the NOT IN at caps of 365, 180 and 90 days, whether PG19 depends on contact_id being NOT NULL, and whether NOT IN and NOT EXISTS return the same rows.

Predicted, then measured. On rc2 the agent's web searches ran from 02:51:50 to 02:53:40 UTC on Nov 13, and its first ExoBench call at 154,198 and 154,199 ids came at 02:55:26 (seq 6). On rc1 the searches ran from 23:23:32 to 23:25:57 UTC on Nov 12, and the first call at those sizes came at 23:27:40 (seq 5).

The log. A separate gateway container logged every ExoBench call verbatim, arguments and response. Every card in this post is cut from that log, byte for byte, and the post's data file tags the sessions S1, G1, S2, G2 and M2, in the order of the table below.

The stack. ExoBench 1.4.26, built from tag v1.4.26, running locally in front of three Docker databases: postgres:17 (17.11), postgres:18 (18.6) and postgres:19rc2 (19rc2), with postgres:19rc1 in its place for the first round. All are 64-bit x86_64 builds. Before timing anything the agent read the settings on each server:

Serverwork_memhash_mem_multiplierjitshared_buffersmax_parallel_workers_per_gather
17.114096 kB2on16384 x 8kB2
18.64096 kB2on16384 x 8kB2
19rc24096 kB2off16384 x 8kB2

Source: the rc2 ban-list session, seq 3, 4 and 5. JIT is off in PG19 because PG19 changed the default (7f8c88c2b, credited to Jelte Fennema-Nio in the release notes).

The host. One laptop, an Intel Core i7-7700HQ at 2.80GHz with 4 cores, 8 threads and 62 GB of RAM, with a few unrelated containers (LibreChat, MongoDB, Meilisearch, pgvector) up at the same time.

The sessions.

SessionPG19 buildRunModel costExoBench callsWeb searchesWall time
Campaign queryrc120261112_224834_phase1-s11-agent$6.3352530.4 min
Ban listrc120261112_231953_phase2-3-cliff-agent$10.59, stopped at its $10 budget631680.2 min
Campaign queryrc220261113_015648_phase1-s11-agent-rc2$13.2257253.2 min
Ban listrc220261113_025021_phase2-3-cliff-agent-rc2$9.87889125.9 min
Million orders, scripted by merc220261113_161016_1m-equivalent-scripted-rc2none, no model calls6off

The campaign data. On PG19 the same seed gives the sends rows different ids within each tenant. The agent compared PG17 and PG19 on everything the query reads: the same per-tenant counts, the same sum(contact_id) and the same fingerprint of each tenant's contacts in send-time order. Only fingerprints over sends.id differ, and the query never reads it (rc2 campaign session, seq 5 to 8). PG18 matched PG17 on every fingerprint, sends.id included (seq 2 and 3).

The cast check. Every NOT IN in the 173 captured calls that use one compares int columns with the built-in =, and no subquery casts its output, so neither the rc2 cast fix nor BUG #19852 was exercised.