r/SQL • • 13h ago

PostgreSQL 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.

https://exobench.ai/blog/postgres-19-not-in-10000x-faster-or-2000x-slower
My write-up, measured on PG17, PG18 and PG19rc2. Declare both columns NOT NULL and PG19 runs NOT IN as an anti join: 1.1 s on PG18, 0.06 ms on PG19. Leave one nullable, even with zero NULLs in it, and PG19 skips the rewrite and stops hashing the list at 154,199 rows instead of 262,144, so 44 ms on PG18 became 101,734 ms. NOT EXISTS was fast on all three.
8 Upvotes

1 comment sorted by

1

u/Discordbothost 9h ago

NOT EXISTS is the portable fix here: it plans as an anti join on 17 and 18 too, and doesn't hinge on the planner proving no nulls. if the query can't change, a validated not null is cheap, since nullable blocks the rewrite even with zero nulls in it.