🚀AdvancedOptimization
Query Optimization Techniques
Identify and fix slow queries using indexes, statistics, and query rewriting.
Overview
Query optimization is the process of making slow queries fast. The main levers are: creating the right indexes, ensuring statistics are up to date, rewriting queries to use more efficient access patterns, and adjusting planner configuration. Always start with EXPLAIN ANALYZE to understand what is slow before optimizing.
The most common causes of slow queries: missing index on a WHERE/JOIN column, stale statistics, N+1 queries in applications, and inefficient query patterns (like NOT IN with NULLs, or LIKE with a leading wildcard).
Key Points
- •Always profile first: EXPLAIN (ANALYZE, BUFFERS)
- •Create indexes on columns used in WHERE, JOIN, ORDER BY
- •Run ANALYZE to update statistics after bulk loads
- •Rewrite correlated subqueries as JOINs or CTEs
- •Use covering indexes: CREATE INDEX ON t (a) INCLUDE (b) for index-only scans
- •Partial indexes for frequently-filtered subsets
Examples
Common optimization patterns
-- 1. Check for sequential scans on large tables
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
-- If Seq Scan: add index
CREATE INDEX ON orders (customer_id);
-- 2. Covering index (avoids heap fetch)
CREATE INDEX ON orders (customer_id) INCLUDE (status, total);
-- 3. Rewrite correlated subquery as JOIN
-- Slow:
SELECT * FROM customers WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) > 5;
-- Fast:
SELECT c.* FROM customers c JOIN (SELECT customer_id, COUNT(*) cnt FROM orders GROUP BY 1 HAVING COUNT(*) > 5) o ON o.customer_id = c.id;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4