Postgres Slow Subquery Performance: Replacing IN With EXISTS or JOIN

A slow query using a subquery inside IN can often be sped up significantly by rewriting it as EXISTS or a JOIN — while modern Postgres's planner is often smart enough to optimize simple IN subqueries equivalently, certain patterns, especially with correlated conditions or large subquery result sets, still benefit meaningfully from an explicit rewrite.

The Problem

A query filtering based on a subquery's results runs slower than expected, particularly as the subquery's underlying data grows:

SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

Checking EXPLAIN ANALYZE shows the subquery's result set being fully materialized before the outer comparison happens, which can become genuinely expensive as the subquery's result set grows large.

Why It Happens

Postgres's query planner generally does a good job optimizing straightforward IN subqueries into an efficient plan on its own, often producing performance equivalent to a manually written EXISTS or JOIN version — but certain patterns still benefit from an explicit rewrite:

• A large subquery result set combined with a query pattern the planner doesn't optimize as effectively into a semi-join style execution, causing it to materialize the full subquery result before proceeding, rather than short-circuiting as soon as a match is confirmed
• Correlated conditions that are more naturally and clearly expressed with EXISTS, where the subquery genuinely depends on the outer query's current row, a pattern that doesn't map as cleanly onto a simple IN at all
• Complex queries where the planner's cost estimation, based on potentially imprecise statistics, leads it to choose a less efficient execution strategy than a differently-phrased but logically equivalent query would prompt it to choose instead

The Fix

Rewrite using EXISTS, which allows the planner to stop searching as soon as it finds one matching row, rather than needing to materialize the complete subquery result set upfront:

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.total > 1000
);

This is a correlated subquery — it references the outer query's c.id directly within the subquery — and this form often maps more naturally onto an efficient semi-join execution plan than the equivalent IN phrasing, especially as the underlying data volume grows.

Alternatively, rewrite as an explicit JOIN with DISTINCT (or, more precisely, using a join specifically structured to avoid row duplication issues), which can also be effective, though it requires being more careful about not accidentally duplicating outer rows if a customer has multiple matching orders:

SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

Be cautious with this JOIN-based rewrite specifically: without DISTINCT (or an equivalent way of ensuring each customer only appears once), a customer with multiple qualifying orders would appear multiple times in the result, which is a genuine behavioral difference from both the original IN query and the EXISTS rewrite — this is a common, easy-to-overlook mistake when converting IN/EXISTS patterns to explicit joins, so verify the result set's row count matches expectations after rewriting.

Always verify with EXPLAIN ANALYZE that your specific rewrite genuinely improves the execution plan for your actual data and Postgres version, rather than assuming any particular rewrite is universally faster — the planner's behavior and effectiveness at optimizing each form varies across Postgres versions and can depend significantly on your specific data distribution and statistics:

EXPLAIN ANALYZE
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000);

Compare the actual execution time and plan shape (particularly whether a full materialization step appears) against the original IN version's own EXPLAIN ANALYZE output, confirming the rewrite genuinely helps for your specific case before committing to it as the standard pattern across your codebase.

For a NOT IN variant specifically, be aware of an additional, important behavioral subtlety beyond just performance: NOT IN with a subquery that could return any NULL values behaves in a genuinely surprising way (the entire NOT IN condition can evaluate to no rows matching at all, due to NULL's three-valued logic interacting with the negation), which NOT EXISTS doesn't share — this makes NOT EXISTS generally the safer, more predictable default choice for negated subquery conditions, independent of any pure performance consideration:

-- Prefer this over "NOT IN (subquery)" whenever the subquery could return NULLs
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Still Not Working?

If neither EXISTS nor an explicit JOIN rewrite improves performance meaningfully, the bottleneck may not be the subquery pattern itself at all, but a missing index on the join/correlation column — confirm an index exists on orders.customer_id specifically, since without it, every rewrite variant still ultimately requires an expensive full scan of the orders table regardless of how the query itself is phrased:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);