| name | loom-sql-optimization |
| description | Analyzes and optimizes SQL queries for performance. Use for index design, query rewriting, EXPLAIN/EXPLAIN ANALYZE interpretation, PostgreSQL tuning, N+1 prevention, CTE and window function optimization, join strategies, and common SQL anti-patterns. |
| allowed-tools | ["Read","Grep","Glob","Bash"] |
| triggers | ["SQL","query optimization","EXPLAIN","EXPLAIN ANALYZE","index","slow query","execution plan","query plan","join optimization","subquery","CTE","common table expression","window function","partition","N+1","query cache","database performance","sequential scan","index scan","bitmap scan","nested loop","hash join","merge join","PostgreSQL","query tuning","table scan","cardinality","statistics","vacuum","analyze"] |
SQL Optimization
Overview
Analyzing and tuning SQL for performance: reading execution plans, index design, query rewriting, and PostgreSQL-specific behavior. Most notes assume PostgreSQL. The mechanism-level material — reading EXPLAIN, composite-index column order, partial-index limits, statistics, concurrency — is in Expert Practices below; this section is the workflow and the anti-pattern catalogue.
Workflow
- Find the slow query (logs,
pg_stat_statements by total time, not just per-call).
- Explain it:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS). Read plans by estimated-vs-actual row divergence (bad stats → wrong join/scan choice), scan type, join algorithm, and Rows Removed by Filter. See Expert Practices → Reading EXPLAIN.
- Fix in priority order: refresh/extend statistics → add/reshape an index → rewrite the query → denormalize/derive → tune config. Change one thing at a time.
- Validate: re-EXPLAIN on production-like data, confirm the target node changed (Sort gone / Index Scan chosen / Heap Fetches low), verify correctness, monitor post-deploy.
Best Practices
EXPLAIN (ANALYZE, BUFFERS) is the source of truth — never optimize by guessing; actual rows and buffers reveal the real cost.- Select only needed columns (enables index-only scans; avoids TOAST/wide-row I/O).
- Index for WHERE/JOIN/ORDER BY, but mind column order and the write tax (Expert Practices → Index Design).
- Avoid N+1: one JOIN or a batched
WHERE id = ANY($1), not a query per row.
NOT EXISTS, never NOT IN (nullable subquery) — one NULL silently returns zero rows; NOT EXISTS is NULL-safe and hash-anti-join-able. (Inclusion IN/ANY/EXISTS plan identically in modern PG — pick readability.)- No functions/implicit casts on indexed columns (
WHERE lower(x)= needs an expression index; WHERE int_col = '1' may cast and skip the index).
- Keyset pagination, not deep
OFFSET (Expert Practices).
- CTEs are not fences (PG12+): a non-recursive, side-effect-free CTE used once is inlined; used >1× it materializes. Force with
AS MATERIALIZED / AS NOT MATERIALIZED; the OFFSET 0 trick is obsolete.
ANALYZE after bulk loads; monitor query performance over time.
PostgreSQL-Specific Optimizations
Execution Plan Operators
Scan Types:
- Sequential Scan: Full table scan (slow for large tables)
- Index Scan: Walks the index then fetches matching heap rows — best when the predicate matches only a SMALL fraction of the table. When many rows match, the planner switches to a Sequential Scan (or Bitmap Heap Scan for a middling fraction) because the random heap I/O of many index lookups costs more than reading the table sequentially.
- Index Only Scan: Uses covering index (fastest)
- Bitmap Index Scan: Multiple index scans combined (good for OR conditions)
Join Algorithms:
- Nested Loop: Best for small tables or index lookups
- Hash Join: Best for medium-sized tables with equality joins
- Merge Join: Best for large pre-sorted tables
Statistics and Maintenance
ANALYZE table_name;
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY last_analyze NULLS FIRST;
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
n_dead_tup, n_live_tup,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
VACUUM ANALYZE table_name;
Configuration Tuning
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW random_page_cost;
work_mem is the limit for each sort/hash operation and each parallel worker — a single query with several such nodes can use N × work_mem simultaneously, and concurrent sessions multiply that further. Since PG14, hash operations use up to hash_mem_multiplier × work_mem (default 2.0). A large global work_mem on a high-concurrency server is an OOM risk: keep the global value modest and raise it per-session with SET LOCAL work_mem for known heavy reports. Set log_temp_files = 0 and watch for Batches > 1 on Hash nodes in EXPLAIN ANALYZE to detect spills.
Common Anti-Patterns
1. SELECT * in Application Code
SELECT * FROM users WHERE id = 1;
SELECT id, email, name FROM users WHERE id = 1;
2. Implicit Type Conversion
SELECT * FROM users WHERE id = '123';
SELECT * FROM users WHERE id = 123;
3. OR Conditions Without Indexes
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';
SELECT * FROM orders WHERE status IN ('pending', 'processing');
PostgreSQL generally rewrites OR conditions on the SAME column into IN/ANY and can use a plain B-tree index on that column directly. For OR conditions across DIFFERENT columns it combines separate index scans via a BitmapOr (or you can rewrite as UNION ALL). Verify the chosen path with EXPLAIN rather than assuming a partial index helps — a partial index only restricts which rows are indexed; it does not provide an access path for searching by those status values.
4. Correlated Subqueries
SELECT p.name,
(SELECT COUNT(*) FROM order_items WHERE product_id = p.id) AS order_count
FROM products p;
SELECT p.name, COUNT(oi.id) AS order_count
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name;
5. Missing WHERE Clauses
UPDATE products SET updated_at = NOW();
UPDATE products SET updated_at = NOW()
WHERE id IN (SELECT product_id FROM price_changes);
Advanced Patterns
CTEs (Common Table Expressions)
WITH recent_orders AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY customer_id
),
high_value_customers AS (
SELECT customer_id
FROM recent_orders
WHERE total_spent > 1000
)
SELECT c.name, c.email, ro.order_count, ro.total_spent
FROM customers c
INNER JOIN high_value_customers hvc ON c.id = hvc.customer_id
INNER JOIN recent_orders ro ON c.id = ro.customer_id;
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;
Window Functions
SELECT
product_id,
category_id,
price,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank,
RANK() OVER (ORDER BY price DESC) AS overall_rank,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dense_rank
FROM products;
SELECT
date,
revenue,
SUM(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7day
FROM daily_sales
ORDER BY date;
SELECT
customer_id,
order_date,
total,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date,
LEAD(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_total,
total - LAG(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS total_diff
FROM orders;
Examples
Example 1: Query Optimization with EXPLAIN
SELECT o.*, c.name, c.email
FROM orders o, customers c
WHERE o.customer_id = c.id
AND o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT o.*, c.name, c.email
FROM orders o, customers c
WHERE o.customer_id = c.id
AND o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC;
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
SELECT o.id, o.total, o.created_at, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;
Example 2: N+1 Query Problem
SELECT
o.id AS order_id,
o.total,
o.created_at,
oi.product_id,
oi.quantity,
oi.unit_price,
p.name AS product_name
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 1
ORDER BY o.created_at DESC, oi.id;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM order_items WHERE order_id IN (1, 2, 3, 4, 5);
Example 3: Index Design Strategies
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at DESC);
CREATE INDEX idx_orders_pending
ON orders(created_at DESC)
WHERE status = 'pending';
CREATE INDEX idx_orders_summary
ON orders(user_id, status)
INCLUDE (total, created_at);
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT
indexname,
indexdef,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'orders';
SELECT
schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Example 4: Query Rewriting Patterns
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'US');
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'US';
SELECT * FROM products
WHERE id IN (SELECT product_id FROM order_items);
SELECT * FROM products p
WHERE EXISTS (
SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;
SELECT * FROM products
WHERE id > 10000
ORDER BY id
LIMIT 20;
UPDATE products SET price = price * 1.1 WHERE id = 1;
UPDATE products SET price = price * 1.1 WHERE id = 2;
UPDATE products SET price = price * 1.1
WHERE id = ANY(ARRAY[1, 2, 3, ..., 1000]);
WITH price_updates AS (
SELECT id, new_price FROM temp_price_updates
)
UPDATE products p
SET price = pu.new_price
FROM price_updates pu
WHERE p.id = pu.id;
Expert Practices: Idioms, Anti-Patterns & Gotchas
High-signal guidance for thinking like a query-tuning expert. Defaults and version notes assume PostgreSQL.
Reading EXPLAIN
Index Cond is an access predicate; Filter is not. On an Index Scan, Index Cond decides which index leaf entries are traversed; a Filter line is applied to every row those entries return, discarding non-matching rows afterward. A Filter on an Index Scan plus a high Rows Removed by Filter means the index is doing more work than the query needs — usually because a filtering column is not in the access predicate. Fix by reordering/extending the composite index so that column joins the Index Cond. (A Filter after a Seq Scan instead means no usable index at all.)
Diagnose bad plans by estimated-vs-actual row divergence, not cost-vs-time. Cost units (arbitrary) and actual time (ms) are not numerically comparable. The signal that matters is whether estimated rows=N tracks actual rows=M. A divergence of ~10× or more means the planner chose its architecture (nested loop vs hash join, index vs seq scan) on wrong cardinality — the usual root cause of a slow plan. Fixes in order: (1) ANALYZE to refresh stats; (2) raise per-column statistics target for skewed columns (ALTER TABLE t ALTER COLUMN c SET STATISTICS 500); (3) CREATE STATISTICS for correlated column groups. The enable_* GUCs (e.g. SET enable_nestloop = off) are diagnostic probes only — never a permanent fix, since they affect every query in the session.
Looped-node times/rows are per-loop averages — multiply by loops. For a node inside a loop (e.g. the inner side of a Nested Loop), EXPLAIN ANALYZE reports actual time and rows as averages PER execution, with loops=N the count. A node showing actual time=0.003..0.005 rows=1 loops=10000 is ~30 ms total, not 0.003 ms. Related trap: under LIMIT, child nodes report only the rows delivered before the limit stopped them, not what they would have produced.
Use the modern EXPLAIN options. SETTINGS (PG12, non-default GUCs influencing the plan — essential for cross-environment reproduction); WAL (PG13, WAL generated by DML, needs ANALYZE); GENERIC_PLAN (PG16, the plan a prepared statement would use with $1 placeholders, WITHOUT executing — cannot be combined with ANALYZE); SERIALIZE (PG17, cost of serializing output); MEMORY (PG18, planner memory). In PG18 BUFFERS is auto-included with ANALYZE. Recommended PG16+ form:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'pending';
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1 AND status = $2;
Index Design
Equality columns must precede the range/sort column in a composite index. In a B-tree, leading equality columns plus a single range column set the start/stop of the index scan (access predicates). Once a column is used for a range, every subsequent column can only filter row-by-row — narrowing nothing. So an index (a, range_col, b) with WHERE a=? AND range_col>? AND b=? scans the whole range_col span and merely filters on b. Rule: index for equality first, then for a single range, with the ORDER BY column last so it can also serve the sort.
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);
CREATE INDEX idx_bad ON orders(user_id, created_at, status);
Sort direction (ASC/DESC, NULLS) must match ORDER BY exactly, or a Sort is forced. A B-tree on (x ASC, y ASC) satisfies ORDER BY x ASC, y ASC (forward scan) or x DESC, y DESC (backward scan), but NOT mixed x ASC, y DESC — that needs an index declared (x ASC, y DESC). NULLS placement is a second axis: default is ASC NULLS LAST / DESC NULLS FIRST, so ORDER BY x ASC NULLS FIRST won't match a default index. The mismatch is silent (correct results, but an explicit Sort node) and worst with LIMIT, which otherwise stops after N rows. Confirm by the presence/absence of a Sort node in EXPLAIN.
Partial indexes need a literal predicate match — parameterized queries can't use them. A partial index is used only if the planner can prove at PLAN time that the query's WHERE implies the index predicate (it recognizes only simple inequality implications like x<1 ⇒ x<2). A prepared statement WHERE status = $1 will NEVER use a WHERE status='pending' partial index because the value is unknown at plan time — use a composite index (status, created_at) when the column must be bound. Many non-overlapping partial indexes as poor-man's partitioning is explicitly "a bad idea" per the docs; use real partitioning or a composite index.
INCLUDE builds covering indexes; Index-Only Scans depend on the visibility map. INCLUDE (cols) stores payload columns only in leaf pages (keeping the B-tree key compact, not affecting uniqueness). But an Index-Only Scan avoids the heap only for pages whose visibility-map bit is set; on high-UPDATE/DELETE tables the bit is often unset, so the scan silently degrades to heap fetches. Watch Heap Fetches: N in EXPLAIN; check churn via n_dead_tup in pg_stat_user_tables and VACUUM to restore VM coverage.
HOT updates are lost when any indexed column changes — driving index bloat. Heap-Only Tuple updates skip all indexes when only non-indexed columns change and the new tuple fits the same heap page. But updating even one INDEXED column (even to the same value) bypasses HOT and inserts an entry into EVERY index, leaving one dead entry per index per update for VACUUM to reclaim. Common trap: adding a frequently-updated column like updated_at to many indexes "for completeness" destroys HOT. Index only columns used in WHERE/JOIN, and set fillfactor < 100 on hot tables so in-page HOT updates have room.
BRIN indexes only help when values correlate with physical row order. A BRIN stores only per-block-range summaries (min/max), making it 2–3 orders of magnitude smaller than a B-tree — ideal for append-only time-series/audit tables where timestamps or sequential IDs grow monotonically with insert order. It is useless for randomly-distributed columns (UUIDs, churned status): no block range can be eliminated, so the planner ignores it. Check SELECT correlation FROM pg_stats WHERE tablename='t' AND attname='col' (near ±1.0 is good) before creating. BRIN serves large range scans, not point lookups (still need a B-tree).
CREATE INDEX idx_events_ts_brin ON events USING BRIN (occurred_at);
PG18 B-tree skip scan relaxes the leading-column rule for low-cardinality prefixes. As of PG18 (2025-09-25), a composite B-tree on (a, b) can serve a query constraining only b if a has FEW distinct values — the planner issues one index search per distinct a value. It fires only when distinct leading values are few enough to skip most of the index; with many, it scans the whole index and the planner usually prefers a seq scan. On PG18 you may no longer need a redundant single-column index behind a low-cardinality leading column. It is automatic and cost-based — verify with EXPLAIN.
Query Rewrites & Anti-Patterns
NOT IN with a nullable subquery silently returns zero rows — use NOT EXISTS. One NULL in the subquery makes x NOT IN (..., NULL) evaluate to UNKNOWN (never TRUE) for every row, so the WHERE filters out ALL rows with no error. This is a correctness bug, triggered whenever the subquery column lacks a NOT NULL constraint or an outer join feeds NULLs in. NOT EXISTS tests existence, is NULL-safe, and lets the planner use a hash anti-join; NOT IN with nullable input degrades to an expensive correlated subplan.
SELECT * FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);
Keyset (seek) pagination eliminates cumulative OFFSET cost. OFFSET N fetches and discards N rows, so latency grows linearly with page depth. Keyset pagination anchors a WHERE to the last-seen sort-key value, letting an index seek jump straight to position — constant time regardless of page. The sort key must be unique (add an id tiebreaker); use a row-value comparison so it maps to a composite index.
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC LIMIT 20;
Prefer timestamptz over timestamp; use half-open ranges (>=, <) not BETWEEN. timestamp (without zone) has no zone context, so arithmetic across DST or between servers is silently wrong; timestamptz stores an absolute UTC instant. Separately, BETWEEN is inclusive on BOTH ends: created_at BETWEEN '2024-01-01' AND '2024-01-31' includes only midnight Jan 31, excluding the rest of that day. The idiomatic range >= lower AND < upper_exclusive is unambiguous, microsecond-correct, and index-friendly.
Planner Statistics
Correlated columns need CREATE STATISTICS — per-column stats assume independence. PostgreSQL multiplies individual column selectivities, badly underestimating result rows for correlated columns (zip→city, status→substatus), which pushes the planner into a nested loop sized for a tiny result — catastrophic on the real large result. Adding indexes does NOT fix a misestimate; CREATE STATISTICS (PG10+) does. Pick the kind: dependencies (functional deps on equality), ndistinct (GROUP BY combination counts), mcv (skewed multi-column value pairs). Always ANALYZE after and confirm with EXPLAIN.
CREATE STATISTICS order_stats (mcv) ON status, region FROM orders;
ANALYZE orders;
Prepared Statements & Concurrency
Prepared statements switch from custom to generic plans after 5 executions. The first five executions use custom plans (planned with actual values); PostgreSQL then builds a generic plan (planned without knowing values, on average stats) and uses it if its cost is "not so much higher" than the custom average (plan_cache_mode='auto'). For skewed columns a generic plan optimized for one value can be disastrous for another. Inspect it directly with EXPLAIN (GENERIC_PLAN) (PG16+) — the often-repeated "read $1 vs literals in EXPLAIN EXECUTE" trick is unreliable. Force per-execution plans with SET plan_cache_mode = 'force_custom_plan'.
User-defined functions are PARALLEL UNSAFE by default, silently disabling parallel query. Any PARALLEL UNSAFE object anywhere in a query (WHERE, SELECT, JOIN) disables parallelism for the whole query — no Gather node appears, no warning. Mark a function PARALLEL SAFE only if it has no side effects, does not write, and touches no connection-local state; functions reading temp tables or session state must be PARALLEL RESTRICTED (leader-only). Mislabeling a writing function as SAFE is silently incorrect.
CREATE FUNCTION score_record(val int) RETURNS int
AS $$ SELECT val * 2; $$ LANGUAGE SQL PARALLEL SAFE IMMUTABLE;
READ COMMITTED allows mid-statement inconsistency; REPEATABLE READ needs retry logic. READ COMMITTED (default) takes a new snapshot per statement, so one UPDATE/DELETE can see a mix of pre- and post-commit data and silently skip rows whose qualifying columns changed mid-scan. REPEATABLE READ uses one snapshot per transaction but raises could not serialize access due to concurrent update errors the application MUST catch and retry from the beginning. Orthogonal trap: sequences (nextval) are non-transactional — values consumed are never returned on ROLLBACK, so gaps are permanent and expected.
Maintenance
Transaction ID wraparound silently approaches, then forces read-only — monitor relfrozenxid age. With 32-bit XIDs (~4 billion circular), a table's relfrozenxid aging past autovacuum_freeze_max_age (default 200M) triggers a forced freeze. If autovacuum is disabled/starved/blocked, age climbs: warnings begin at 40M transactions from wraparound, and below 3M remaining the server refuses new XIDs — writes fail until VACUUM runs. Long-running/idle-in-transaction sessions, forgotten prepared transactions (pg_prepared_xacts), and replication slots all hold back the oldest XID. Alert well before 150M; in a crisis run plain VACUUM (VACUUM FULL/FREEZE consume more XIDs and worsen it).
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
Verification Checklist
Before declaring a query "optimized":
