This document details 5 real-world production outages and performance incidents caused by subquery antipatterns in high-concurrency relational database systems. Each incident report follows standard Site Reliability Engineering (SRE) post-mortem structure: Symptoms, Diagnosis, Execution Plan Breakdown, Root Cause, Fix Procedure, Verification, Regression Test, and Lessons Learned.
504 Gateway Timeout (30s statement timeout).FATAL: remaining connection slots are reserved for non-replication superuser connections).DBA team inspected pg_stat_activity and identified 450 concurrent instances of the financial view query blocked in active state:
SELECT query, state, age(clock_timestamp(), query_start)
FROM pg_stat_activity
WHERE state != 'idle';
Seq Scan on transactions t (cost=0.00..850420.00 rows=500000 width=48) (actual time=0.080..32100.450 rows=500000 loops=1)
SubPlan 1
-> Aggregate (cost=1.70..1.71 rows=1 width=8) (actual time=0.060..0.060 rows=1 loops=5000000)
-> Index Scan using idx_tx_account on transactions sub (loops=5000000)
The production reporting view contained a projected scalar subquery ((SELECT COUNT(*) FROM transactions sub WHERE sub.account_id = t.account_id)). Because the inner subquery was correlated on account_id, the database parser generated a SubPlan node that executed 5,000,000 index scans per dashboard request, scaling to over $2,250,000,000$ index operations per minute under concurrent traffic.
Refactored the view projection to use a SQL Window Function (COUNT(*) OVER (PARTITION BY account_id)):
-- ✅ PRODUCTION REVISED QUERY (Execution Time: 110ms)
CREATE OR REPLACE VIEW v_executive_dashboard AS
SELECT
t.transaction_id,
t.account_id,
t.amount,
t.created_at,
COUNT(*) OVER (PARTITION BY t.account_id) AS total_account_txs
FROM transactions t
WHERE t.created_at >= CURRENT_DATE - INTERVAL '7 days';
pg_query AST parser to reject any SQL view PR containing SubLink nodes inside projection target lists.Inspecting the batch campaign query revealed it returned 0 rows:
-- ❌ SEV-2 BUG: Returns 0 rows because inner target column contains NULLs
SELECT email
FROM users
WHERE user_id NOT IN (
SELECT customer_id
FROM active_subscriptions -- Contains 1 NULL row!
);
Under ANSI SQL 3-valued logic, user_id NOT IN (1, 2, NULL) expands to (user_id <> 1) AND (user_id <> 2) AND (user_id <> NULL). Because any comparison against NULL yields UNKNOWN, the entire WHERE clause evaluated to UNKNOWN, silently discarding all 450,000 valid customer target records without throwing a syntax or runtime error.
Refactored the query to use NOT EXISTS (2-valued Anti-Join logic):
-- ✅ SAFE FIX: Refactored to NOT EXISTS Anti-Join
SELECT u.email
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM active_subscriptions s
WHERE s.customer_id = u.user_id
);
NOT IN: Mandated complete elimination of NOT IN subqueries targeting nullable columns in favor of NOT EXISTS.pg_stat_activity showed dozens of authorization queries stuck in active state well past their expected sub-50ms runtime, all executing against the same transactions table:
SELECT pid, state, wait_event_type, query_start
FROM pg_stat_activity
WHERE query ILIKE '%fraud_score%' AND state = 'active';
Seq Scan on transactions t (cost=0.00..1920840.00 rows=1 width=24) (actual time=45.200..11890.310 rows=1 loops=1)
Filter: (card_id = $1)
SubPlan 1
-> Aggregate (cost=3.20..3.21 rows=1 width=8) (actual time=118.400..118.400 rows=1 loops=1)
-> Seq Scan on transactions sub (cost=0.00..1920820.00 rows=50000000 width=0) (actual time=0.020..115.900 rows=1240 loops=1)
Filter: (card_id = t.card_id AND created_at >= (now() - '01:00:00'::interval))
The fraud-scoring query’s correlated subquery — counting a card’s transactions in the trailing hour — had no supporting index on (card_id, created_at). Under normal traffic this full scan of the 50M-row transactions table was slow but tolerable; under Black Friday’s 8x transaction volume, concurrent full scans exhausted shared buffer cache, forcing physical disk reads that serialized behind I/O contention and cascaded into the observed latency spike.
Added a composite covering index and rewrote the correlated count as an unnestable, indexable predicate:
-- ✅ Composite index supporting the correlation + time-window filter
CREATE INDEX CONCURRENTLY idx_tx_card_recent
ON transactions (card_id, created_at);
-- ✅ PRODUCTION REVISED QUERY (P99: 8ms, down from 12,000ms)
SELECT
t.transaction_id,
t.card_id,
(SELECT COUNT(*)
FROM transactions sub
WHERE sub.card_id = t.card_id
AND sub.created_at >= now() - INTERVAL '1 hour') AS recent_tx_count
FROM transactions t
WHERE t.card_id = $1;
EXPLAIN (ANALYZE, BUFFERS) confirmed the subquery now resolves via Index Only Scan on idx_tx_card_recent instead of Seq Scan.ERROR: canceling statement due to statement timeout after 15 minutes.Comparing row counts against the prior month revealed the employes table had grown from 8,000 to roughly 50,000 rows following a company acquisition — the query’s cost had scaled with headcount, not with a code change.
HashAggregate (cost=1850200.00..1850200.05 rows=1 width=40) (actual time=901200.100..901200.110 rows=1 loops=1)
Filter: (SubPlan 1 > 100000.00)
-> Seq Scan on departments d (cost=0.00..1850150.00 rows=50 width=40) (actual time=18.000..900980.500 rows=50 loops=1)
SubPlan 1
-> Aggregate (cost=37000.00..37000.01 rows=1 width=8) (actual time=18019.610..18019.610 rows=1 loops=50)
-> Seq Scan on employes e (cost=0.00..36990.00 rows=1000000 width=8) (actual time=0.030..17980.200 rows=50000 loops=50)
Filter: (dept_id = d.dept_id)
An un-indexed correlated subquery inside a HAVING clause (summing salaries per department to flag departments over a budget threshold) re-scanned the full, now-6x-larger employes table once per department — 50 departments × ~1M row scan each ≈ 50,000,000 row evaluations, versus roughly 8M the prior month. The query had no functional bug; it simply crossed a scale threshold where an already-inefficient plan became untenable.
Replaced the correlated HAVING subquery with a pre-aggregated derived table joined once, backed by an index on the join key:
CREATE INDEX CONCURRENTLY idx_employes_dept ON employes (dept_id);
-- ✅ PRODUCTION REVISED QUERY (Execution Time: 640ms, down from >900,000ms)
SELECT d.dept_id, d.dept_name, dept_totals.total_salary
FROM departments d
JOIN (
SELECT dept_id, SUM(salary) AS total_salary
FROM employes
GROUP BY dept_id
) dept_totals ON dept_totals.dept_id = d.dept_id
WHERE dept_totals.total_salary > 100000.00;
HAVING are just as susceptible to the per-row re-execution problem as those in WHERE, but are audited less often in practice.A recent PR, intended to “optimize” a slow EXISTS filter, had replaced it with an INNER JOIN against the same inner table without adding a DISTINCT or GROUP BY. Reviewing the diff:
-- ❌ SEV-1 BUG: JOIN duplicates outer rows when warehouse_inventory has multiple matching rows per item
SELECT o.order_id, o.item_id, o.qty_requested
FROM orders o
JOIN warehouse_inventory wi ON wi.item_id = o.item_id AND wi.qty_available > 0;
Hash Join (cost=1200.00..8400.00 rows=15400 width=24) (actual time=2.100..44.900 rows=15400 loops=1)
Hash Cond: (o.item_id = wi.item_id)
-> Seq Scan on orders o (rows=8200 loops=1)
-> Hash (rows=15400 loops=1)
-> Seq Scan on warehouse_inventory wi (rows=15400 loops=1)
Filter: (qty_available > 0)
The plan itself is fast and “correct” relative to the SQL as written — the row multiplication happened at the semantic level (each order joined against every warehouse holding stock of that item), not from any execution inefficiency, which is exactly why no error or alert fired.
The original EXISTS query correctly expressed “does any warehouse have this item in stock” — a Semi-Join, returning at most one row per order regardless of how many warehouses matched. The INNER JOIN rewrite instead returned one row per matching warehouse, so an item stocked in 3 warehouses caused its order to be processed 3 times by the downstream allocation logic, each time deducting inventory as if it were a distinct order.
Reverted to the EXISTS Semi-Join, and added an explicit code comment documenting why:
-- ✅ RESTORED: EXISTS preserves one-row-per-order Semi-Join semantics
SELECT o.order_id, o.item_id, o.qty_requested
FROM orders o
WHERE EXISTS (
SELECT 1 FROM warehouse_inventory wi
WHERE wi.item_id = o.item_id AND wi.qty_available > 0
);
-- NOTE: Do not replace with INNER JOIN — warehouse_inventory has a
-- many-to-one relationship with items, and a JOIN will duplicate orders
-- once per matching warehouse row. See Incident 5 postmortem.
orders row count for the same date range — a cheap, general-purpose guard against future cardinality-changing rewrites.EXISTS/IN to JOIN must be reviewed for the inner table’s cardinality relative to the join key, not just benchmarked for speed.