loom-sql-optimization

# 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 1. **Find** the slow query (logs, `pg_stat_statements` by total time, not just per-call). 2. **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. 3. **Fix** in priority order: refresh/extend statistics → add/reshape an index → rewrite the query → denormalize/derive → tune config. Change one thing at a time. 4. **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 ```sql -- Update table statistics for better query plans ANALYZE table_name; -- Check statistics freshness SELECT schemaname, tablename, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE schemaname = 'public' ORDER BY last_analyze NULLS FIRST; -- Find bloated tables 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 bloated tables VACUUM ANALYZE table_name; ``` ### Configuration Tuning ```sql -- Key parameters to check SHOW shared_buffers; -- Should be 25% of RAM SHOW effective_cache_size; -- Should be 50-75% of RAM SHOW work_mem; -- Per sort/hash operation AND per parallel worker SHOW random_page_cost; -- Lower for SSDs (1.1-2.0) ``` `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 ```sql -- BAD: Fetches unnecessary columns SELECT * FROM users WHERE id = 1; -- GOOD: Fetch only needed columns SELECT id, email, name FROM users WHERE id = 1; ``` ### 2. Implicit Type Conversion ```sql -- BAD: Can't use index if id is integer SELECT * FROM users WHERE id = '123'; -- GOOD: Match column type SELECT * FROM users WHERE id = 123; ``` ### 3. OR Conditions Without Indexes ```sql -- BAD: May not use indexes efficiently SELECT * FROM orders WHERE status = 'pending' OR status = 'processing'; -- GOOD: Rewrite same-column OR as IN 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 ```sql -- BAD: Executes subquery for each row SELECT p.name, (SELECT COUNT(*) FROM order_items WHERE product_id = p.id) AS order_count FROM products p; -- GOOD: Use JOIN with aggregation 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 ```sql -- BAD: Updates entire table UPDATE products SET updated_at = NOW(); -- GOOD: Update only what changed UPDATE products SET updated_at = NOW() WHERE id IN (SELECT product_id FROM price_changes); ``` ## Advanced Patterns ### CTEs (Common Table Expressions) ```sql -- CTEs for readability and reusability 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; -- Recursive CTEs for hierarchical data 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 ```sql -- Ranking and row numbers 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; -- Running totals and moving averages 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; -- Lead/Lag for time-series analysis 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 ```sql -- Original slow query 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; -- Step 1: Analyze with EXPLAIN ANALYZE -- SETTINGS (PG12) surfaces non-default GUCs so the plan reproduces across environments. -- For a prepared statement's plan without executing, use EXPLAIN (GENERIC_PLAN) (PG16+). 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; -- Output analysis: -- Seq Scan on orders (cost=0.00..15420.00 rows=50000) -- Filter: (status = 'pending' AND created_at > '2024-01-01') -- Rows Removed by Filter: 450000 -- Problem: Sequential scan on large table! -- Step 2: Composite index (equality col first, sort col last). -- Use a plain composite, not a partial index, when status is bound by a parameter -- ($1) — see Expert Practices → Index Design for why partial indexes go unused. CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC); -- Step 3: Rewrite with explicit JOIN 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; -- After optimization: -- Index Scan using idx_orders_status_created (cost=0.42..125.50 rows=100) -- 99% reduction in query time! ``` ### Example 2: N+1 Query Problem ```sql -- Problem: N+1 queries -- Application code: -- orders = SELECT * FROM orders WHERE user_id = 1 -- for order in orders: -- items = SELECT * FROM order_items WHERE order_id = order.id -- Solution: Single query with JOIN 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; -- Alternative: Batch query SELECT * FROM orders WHERE user_id = 1; -- Get order IDs: [1, 2, 3, 4, 5] SELECT * FROM order_items WHERE order_id IN (1, 2, 3, 4, 5); ``` ### Example 3: Index Design Strategies ```sql -- Single column index for equality checks CREATE INDEX idx_users_email ON users(email); -- Composite index: equality cols first, single range/sort col last. -- The trailing sort col skips a Sort node only if every preceding col is bound by -- equality AND the ASC/DESC/NULLS direction matches ORDER BY exactly (Expert Practices). CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at DESC); -- Partial index for filtered queries CREATE INDEX idx_orders_pending ON orders(created_at DESC) WHERE status = 'pending'; -- Covering index to avoid table lookups CREATE INDEX idx_orders_summary ON orders(user_id, status) INCLUDE (total, created_at); -- Expression index for computed conditions CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- Check existing indexes SELECT indexname, indexdef, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size FROM pg_indexes WHERE tablename = 'orders'; -- Find unused indexes 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 ```sql -- Pattern 1: Replace subquery with JOIN -- Before SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'US'); -- After SELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE c.region = 'US'; -- Pattern 2: Use EXISTS instead of IN for large subqueries -- Before SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items); -- After SELECT * FROM products p WHERE EXISTS ( SELECT 1 FROM order_items oi WHERE oi.product_id = p.id ); -- Pattern 3: Avoid functions on indexed columns -- Before (can't use index) SELECT * FROM users WHERE YEAR(created_at) = 2024; -- After (uses index) SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; -- Pattern 4: Optimize pagination -- Before (slow for large offsets) SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000; -- After (keyset pagination) SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 20; -- Pattern 5: Batch operations -- Before (row-by-row) UPDATE products SET price = price * 1.1 WHERE id = 1; UPDATE products SET price = price * 1.1 WHERE id = 2; -- ... repeated 1000 times -- After (single batch) UPDATE products SET price = price * 1.1 WHERE id = ANY(ARRAY[1, 2, 3, ..., 1000]); -- Or use CTE for complex batches 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.