SQL query optimization is a critical skill for backend engineers and appears frequently in database-focused interviews. Understanding how the query planner works, how to read EXPLAIN output, and how indexes affect performance separates senior engineers from junior ones. This guide covers the essential SQL optimization techniques with PostgreSQL examples — applicable to MySQL, SQL Server, and other relational databases.
How the Query Planner Works
When you execute a SQL query, the database does not execute it literally. The query planner (also called the optimizer) analyzes the query, considers multiple execution strategies, estimates the cost of each, and chooses the cheapest plan. Steps: (1) Parsing — verify SQL syntax and resolve table and column names. (2) Rewriting — apply rules: expand views, simplify expressions, apply constraint exclusions. (3) Planning — generate candidate execution plans and estimate their costs. The cost model considers: number of rows to scan (table statistics), I/O cost (sequential scan vs random I/O for index lookups), CPU cost (filtering, sorting, hashing), and available indexes. (4) Execution — run the chosen plan and return results. The planner makes decisions based on table statistics (row count, value distribution, null fraction) maintained by the ANALYZE command. Stale statistics lead to bad plans. Run ANALYZE after bulk data changes. In PostgreSQL, autovacuum runs ANALYZE automatically, but after a large import, run it manually: ANALYZE table_name.
Reading EXPLAIN ANALYZE Output
EXPLAIN shows the planned execution strategy. EXPLAIN ANALYZE executes the query and shows actual timing and row counts. Key elements: (1) Scan types: Seq Scan (full table scan — reads every row), Index Scan (uses an index to find specific rows — fast for selective queries), Index Only Scan (answers the query from the index alone without visiting the table — fastest), Bitmap Index Scan (uses an index to build a bitmap of matching pages, then scans those pages — good for moderate selectivity). (2) Join types: Nested Loop (for each row in the outer table, scan the inner table — fast when the inner table is small or indexed), Hash Join (build a hash table from one table, probe it with the other — fast for large unsorted tables), Merge Join (sort both tables and merge — fast when both inputs are already sorted). (3) Cost estimate: cost=0.00..123.45 means startup cost 0, total cost 123.45. Lower is better. (4) Actual time: the real execution time in milliseconds. (5) Rows: estimated vs actual row count. A large discrepancy indicates stale statistics. The most important optimization: look for Seq Scan on large tables where an Index Scan would be faster. This usually means a missing index.
Index Types and When to Use Them
B-tree index (default): supports equality and range queries (=, , BETWEEN, ORDER BY). Suitable for most columns. Create on columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses. Hash index: supports only equality (=). Faster than B-tree for equality lookups but does not support range queries. Rarely used — B-tree handles equality efficiently too. GIN index (Generalized Inverted Index): for full-text search, JSONB containment queries, and array operations. GIN indexes every element in the indexed value. Use for JSONB columns queried with @> (contains) operator. GiST index (Generalized Search Tree): for geometric data, full-text search, and range types. Supports nearest-neighbor queries. Partial index: indexes only a subset of rows. CREATE INDEX idx ON orders(created_at) WHERE status = “pending”. Smaller than a full index, faster to maintain, and efficient for queries that match the predicate. Composite index: indexes multiple columns. CREATE INDEX idx ON orders(user_id, created_at). The column order matters: the index is useful for queries filtering on user_id alone, or user_id AND created_at, but NOT created_at alone (leftmost prefix rule).
Common Query Anti-Patterns
Anti-patterns that prevent index usage: (1) Functions on indexed columns: WHERE LOWER(email) = “user@example.com” cannot use an index on email. Solution: create a functional index: CREATE INDEX idx ON users(LOWER(email)). Or normalize data at write time. (2) Implicit type casting: WHERE user_id = “123” when user_id is an integer. The string “123” forces a cast, potentially preventing index use. Use the correct type: WHERE user_id = 123. (3) Leading wildcard: WHERE name LIKE “%smith” cannot use a B-tree index (the index orders by the first character). Solution: use a trigram GIN index (pg_trgm extension) for pattern matching. (4) OR conditions on different columns: WHERE email = “x” OR phone = “y” cannot efficiently use a single index. Solution: use UNION of two queries, each using its own index. (5) SELECT * — fetching all columns prevents Index Only Scan (which only works when all selected columns are in the index). Select only needed columns. (6) N+1 queries — fetching a list of users, then one query per user to fetch their orders. Solution: JOIN or use IN with a subquery: SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = true).
Join Optimization
Join performance depends on table sizes, indexes, and join type. Nested Loop Join: the planner uses this when one side is small. For each row in the outer table (driving table), it looks up matching rows in the inner table using an index. Time: O(N * log M) with an index on the inner table. Optimize: ensure the inner table has an index on the join column. The planner chooses the smaller table as the outer table. Hash Join: builds an in-memory hash table from the smaller table, then probes it for each row in the larger table. Time: O(N + M). Used when no useful index exists and both tables are moderately sized. Requires memory for the hash table (work_mem in PostgreSQL). Merge Join: sorts both tables on the join key, then merges. Time: O(N log N + M log M) for sorting, O(N + M) for merging. Efficient when both inputs are already sorted (e.g., from an index scan or a preceding sort). Optimization tips: index the join columns on both sides, increase work_mem for hash joins on large tables, and ensure statistics are current so the planner estimates cardinalities correctly.
Debugging Slow Queries
Systematic approach: (1) Enable slow query logging: in PostgreSQL, set log_min_duration_statement = 200 (log queries taking more than 200ms). In MySQL, enable slow_query_log with long_query_time = 0.2. (2) Run EXPLAIN ANALYZE on the slow query. Identify the most expensive node in the plan (highest actual time). (3) Common culprits: Seq Scan on a large table (add an index), Sort with high cost (add an index matching the ORDER BY), Hash Join spilling to disk (increase work_mem), many rows estimated vs few actual (stale statistics, run ANALYZE), and Nested Loop with Seq Scan inner (add an index on the inner join column). (4) Check for lock contention: SELECT * FROM pg_stat_activity WHERE wait_event_type = “Lock”. Long-running transactions holding locks can block queries. (5) Check connection pool saturation: if all connections are in use, new queries queue. Monitor pg_stat_activity for idle in transaction connections (application not releasing connections). (6) After applying the fix, verify with EXPLAIN ANALYZE that the plan changed and the query is faster.