PostgreSQL 'Not In' Queries: Why They Stall and How to Fix Them

Q: Can I use NOT IN with a join?

Yes. `WHERE id NOT IN (SELECT user_id FROM banned_users WHERE ...)` with a filtered subquery. The team that uses this has the join conditions in the subquery, keeping the outer query clean.

PostgreSQL NOT IN queries stall when the subquery returns NULLs or when the subquery is large. The fix: use NOT EXISTS or LEFT JOIN with IS NULL. Both are NULL-safe and often faster.

The NULL problem
The performance problem
The LEFT JOIN with IS NULL alternative
When NOT IN is fine
Reading the query plan
What usually breaks
The execution plan patterns
The optimization techniques
FAQ

The NULL problem

The classic gotcha: NOT IN with NULLs returns no rows.

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

If banned_users.user_id has any NULL values, the entire result is empty. That’s because SQL’s three-valued logic treats NULL as “unknown”, and NOT IN with an unknown returns unknown.

The fix: use NOT EXISTS:

SELECT * FROM users u WHERE NOT EXISTS (
    SELECT 1 FROM banned_users b WHERE b.user_id = u.id
);

NOT EXISTS is NULL-safe and uses index lookups efficiently.

The performance problem

NOT IN with a large subquery is slow because PostgreSQL has to:

Run the subquery.
Build a hash table of the results.
For each row in the outer query, check if it’s in the hash table.

The alternative with NOT EXISTS:

For each row in the outer query, run the subquery with a correlated WHERE.

With an index on banned_users.user_id, NOT EXISTS uses the index and is much faster. NOT IN may or may not use the index, depending on the planner’s choice.

The team that switches from NOT IN to NOT EXISTS on a large table gets a 10-100x performance improvement.

The LEFT JOIN with IS NULL alternative

The third pattern: LEFT JOIN with IS NULL.

SELECT u.* FROM users u LEFT JOIN banned_users b ON b.user_id = u.id WHERE b.user_id IS NULL;

This finds users that have no match in banned_users. It’s NULL-safe (the LEFT JOIN keeps unmatched rows) and often the fastest of the three patterns.

The team that has a complex NOT IN with multiple conditions sometimes finds that the LEFT JOIN is clearer than the NOT EXISTS.

When NOT IN is fine

NOT IN is fine when:

The subquery is small and has no NULLs. A static list of values: WHERE id NOT IN (1, 2, 3).
The subquery returns a known set without NULLs. The team that uses SELECT id FROM users WHERE status = 'active' AND id IS NOT NULL can use NOT IN safely.

The team that uses NOT IN with a nullable column has a NULL trap waiting.

Reading the query plan

The right tool for understanding query performance:

EXPLAIN ANALYZE SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);

The output shows:

Seq Scan on banned_users. The planner is doing a full scan, not an index lookup.
Hash Anti Join. The planner is using a hash-based NOT IN.
Nested Loop Anti Join. The planner is using NOT EXISTS (which uses the index).

The team that runs EXPLAIN ANALYZE on slow queries has the data to optimize them. The team that doesn’t has a guessing game.

What usually breaks

The four pitfalls:

NULLs in the subquery. The team that uses NOT IN with a nullable column has the silent-zero-rows bug.
No index on the subquery column. The team that has 100K rows in banned_users without an index gets a sequential scan.
The subquery returns duplicates. The team that uses SELECT DISTINCT to deduplicate has a slower query; the planner can dedupe automatically for NOT IN.
The outer query has many rows. The team that runs NOT IN against a billion-row table has a multi-hour query.

The execution plan patterns

The execution plan patterns for NOT IN vs NOT EXISTS:

NOT IN execution plan. Postgres usually transforms NOT IN into an anti-join. For WHERE id NOT IN (SELECT user_id FROM banned_users):

Hash Anti Join
  Hash Cond: (users.id = banned_users.user_id)
  ->  Seq Scan on users
  ->  Hash
        ->  Seq Scan on banned_users

This is fast if banned_users is small. For large banned_users, the hash becomes large and memory-consuming.

NOT EXISTS execution plan. For WHERE NOT EXISTS (SELECT 1 FROM banned_users WHERE banned_users.user_id = users.id):

Nested Loop Anti Join
  ->  Seq Scan on users
  ->  Index Only Scan using idx_banned_users_user_id on banned_users
        Index Cond: (user_id = users.id)

This uses the index on banned_users.user_id. Fast for both small and large banned_users.

LEFT JOIN with IS NULL execution plan. Similar to NOT EXISTS; uses the same index lookup.

The team that runs EXPLAIN ANALYZE on slow queries sees the difference and picks the right pattern.

The optimization techniques

Beyond switching to NOT EXISTS:

Add an index. CREATE INDEX idx_banned_users_user_id ON banned_users(user_id). This makes NOT EXISTS and LEFT JOIN with IS NULL fast.

Use a CTE or subquery for the IN list. For small static lists, IN with a constant list is faster than a subquery. WHERE id NOT IN (1, 2, 3) is optimized to a constant check.

Avoid correlated subqueries when not needed. NOT EXISTS with a correlated subquery runs the subquery for each outer row. The team that uses a JOIN instead may get better performance.

Materialize the subquery. For complex subqueries, SELECT * FROM (SELECT ... FROM ...) AS sub materializes once. The right tool for repeated subqueries.

FAQ

Why does my NOT IN query return no rows?

Because the subquery has NULL values. SQL’s three-valued logic with NOT IN and NULL returns unknown. The fix: use NOT EXISTS.

Is NOT EXISTS faster than NOT IN?

Usually yes, when the subquery is large or has NULLs. NOT EXISTS uses the index efficiently; NOT IN may not. The team that switches gets a 10-100x speedup on large datasets.

What’s the difference between NOT EXISTS and LEFT JOIN with IS NULL?

Both are NULL-safe and often faster than NOT IN. The team that finds LEFT JOIN with IS NULL clearer uses that; the team that prefers the NOT EXISTS pattern uses that. The performance is similar.

Should I avoid NOT IN entirely?

Not entirely. NOT IN is fine for small, non-NULL lists. The team that uses NOT IN with nullable columns has a NULL trap waiting; switch to NOT EXISTS.

Does NOT IN use an index?

Sometimes. The Postgres planner may use an index for NOT IN if the column is indexed and the conditions are right. The team that runs EXPLAIN ANALYZE verifies whether the index is used.

What’s faster, NOT IN or NOT EXISTS?

NOT EXISTS is usually faster for large subqueries. NOT IN may be faster for small subqueries (the planner can hash them in memory). The team that runs both EXPLAIN ANALYZE on the actual data picks the right one.

Can I use NOT IN with a join?

Yes. WHERE id NOT IN (SELECT user_id FROM banned_users WHERE ...) with a filtered subquery. The team that uses this has the join conditions in the subquery, keeping the outer query clean.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references: