Most SQL performance problems I encounter don't come from writing "bad SQL" in the obvious sense. They come from writing reasonable SQL that the optimizer misunderstands. A query that runs in 12ms on staging with 10,000 rows can take 4 seconds in production with 20 million rows, not because the logic changed, but because the planner chose a sequential scan where it should have used an index, or a nested loop where a hash join was needed. SQL query optimization, at its core, is about learning to read what the database is thinking through its EXPLAIN plan, designing indexing that matches your real access patterns, and understanding how join strategies behave under different data distributions. In my experience, the fastest way to improve performance is not to memorize hints or tricks, but to build a repeatable process: run EXPLAIN ANALYZE, interpret the costs and row estimates, test index designs against the actual workload, and measure with production-like data. This article walks through that process the way I use it on PostgreSQL and MySQL systems handling OLTP traffic.
Decoding EXPLAIN Output: What the Planner Is Really Telling You
Every major database exposes its execution plan, but the terminology and defaults differ enough to cause confusion. PostgreSQL's EXPLAIN is the most verbose, MySQL's EXPLAIN FORMAT=TREE is more readable in 8.0+, and SQL Server's graphical plans add their own layer. Regardless of engine, you are looking for the same four things: access path, join method, estimated vs. actual rows, and cost distribution. I've found that junior developers focus too much on the total cost number at the top, which is an arbitrary unit, and not enough on the row estimates and the difference between estimated and actual.
Reading Cost, Rows and Width Correctly
A typical PostgreSQL plan node looks like Index Scan using idx_orders_customer_id on orders (cost=0.42..120.50 rows=85 width=24). The cost is expressed as startup..total cost before any rows are returned and after the node completes. Rows is the planner's estimate of how many rows that node will produce, and width is the estimated average row size in bytes. If the planner estimates 85 rows but the node actually returns 85,000, every decision above it in the tree is likely wrong. That misestimate is almost always a statistics problem, not an indexing problem. I once traced a 9-second reporting query to a single estimate: the planner thought a WHERE status = 'pending' filter would return 200 rows; it returned 1.8 million. The plan used a nested loop based on that estimate and executed the inner index scan 1.8 million times.
EXPLAIN ANALYZE vs EXPLAIN and Buffer Insights
Plain EXPLAIN shows you what the planner thinks will happen. EXPLAIN (ANALYZE, BUFFERS) shows you what actually happened, including execution time and cache behavior. In my experience, you should default to the latter on non-production replicas. The BUFFERS: shared hit/read line tells you whether your query is memory-resident or hitting disk, which changes your optimization strategy entirely. A query that logically reads 50,000 buffers but hits 49,990 of them in shared_buffers is CPU-bound; one that reads 45,000 blocks from disk is I/O-bound and may benefit from more aggressive indexing or table clustering even if the plan shape looks identical.
-- Don't just EXPLAIN, run ANALYZE with timing and buffer stats
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01'
AND o.status = 'shipped'
ORDER BY o.created_at DESC
LIMIT 50;
-- Example output to watch:
-- Index Scan using idx_orders_created_at on orders o
-- (actual time=0.042..12.415 rows=50 loops=1)
-- Buffers: shared hit=412 read=89
-- -> Index Scan using customers_pkey on customers c
-- (actual time=0.003..0.003 rows=1 loops=50)
-- If "rows" estimate is 5x off from actual, run ANALYZE on the table
-- and check pg_stats for correlation and n_distinct.
Pay special attention to loops. A node with loops=5000 means it was executed 5,000 times by its parent, usually a nested loop join. Multiply its exclusive time by loops to understand its true contribution. I also recommend enabling track_io_timing on PostgreSQL if you can; without it, you can't tell if a slow node is slow due to CPU or due to waiting on storage.
Designing Indexes That Match Your Query Pattern, Not Your Table Schema
The most common indexing mistake I see is creating an index on every column that appears in a WHERE clause, individually. That rarely helps because the optimizer can typically use only one index per table scan (with index merging as an exception in MySQL, which is often less efficient than a proper composite index). Effective sql optimization with indexing is about matching the index to the entire predicate, join, and order by pattern.
Composite Index Column Order and Selectivity
For a B-Tree index, column order matters. The rule I use in practice: equality columns first, then the column used for ordering or range, and finally columns that allow an index-only scan. For example, WHERE customer_id = $1 AND status = 'pending' AND created_at > $2 ORDER BY created_at is best served by (customer_id, status, created_at) or even (customer_id, status, created_at DESC). If you put created_at first, the index can't efficiently filter on customer_id because the range scan on the first column breaks the ability to seek on the second. I've audited schemas where swapping two columns in a composite index reduced logical reads by 90% because the index went from scanning 800,000 entries to seeking directly to 120.
Selectivity alone does not determine order. A highly selective column that is never used with an equality predicate should not lead the index if another less selective column is filtered with =. The planner needs contiguous index ranges to seek efficiently. When I design an index, I look at the actual query logs for the preceding week, not the table's cardinality report.
Covering Indexes and Index-Only Scans
An index-only scan happens when every column the query needs can be satisfied from the index itself, without visiting the heap. This is visible in PostgreSQL as Index Only Scan versus Index Scan. To achieve it, append INCLUDE columns or add them to the index key tail. For coverage, I often use CREATE INDEX idx_orders_customer_covering ON orders (customer_id, created_at) INCLUDE (total, status). The benefit is not just avoiding heap fetches; it's avoiding random I/O. In my experience, converting a frequent pagination query from index scan to index-only scan cut p95 latency from 180ms to 22ms because the working set finally fit in memory.
Watch for table bloat and visibility map freshness. PostgreSQL can only do an index-only scan if the visibility map indicates all tuples on a page are visible to all transactions. If you rarely VACUUM, you will see Heap Fetches: 38421 in the EXPLAIN ANALYZE output even with a covering index. That's a maintenance signal, not a missing index signal.
Partial and Expression Indexes for Real Workloads
Not every index needs to cover all rows. If 95% of your queries filter for WHERE deleted_at IS NULL or WHERE status = 'active', a partial index WHERE deleted_at IS NULL will be smaller, faster to update, and more likely to stay cached. Similarly, when you filter on a transformation like WHERE lower(email) = lower($1), a normal index on email is useless. You need CREATE INDEX ON users (lower(email)). I use expression indexes frequently for JSONB and case-insensitive searches, a pattern I detailed in my write-up on PostgreSQL Advanced Features: CTEs, Window Functions and JSONB where indexing (data->>'type') made JSONB filters indexable without restructuring the table.
-- Composite, covering, and partial indexes tailored to one query pattern
-- Query: frequent lookup for active orders by customer, sorted by date
CREATE INDEX idx_orders_active_customer_created
ON orders (customer_id, status, created_at DESC)
INCLUDE (id, total)
WHERE deleted_at IS NULL AND status IN ('pending','shipped');
-- Expression index for case-insensitive lookup
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Verify index usage, don't guess
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 8421
AND status = 'pending'
AND deleted_at IS NULL
ORDER BY created_at DESC LIMIT 20;
-- Look for "Index Only Scan using idx_orders_active_customer_created"
How the Optimizer Chooses Join Strategies: Nested Loops, Hash Joins and Merge Joins
Join strategy selection is where explain plan analysis becomes most valuable. The optimizer is not choosing syntax; it is choosing a physical algorithm based on row counts, memory, and whether inputs are sorted. Misunderstanding this leads to attempts to "fix" joins by rewriting them as subqueries, which often leaves the same physical plan underneath.
In my experience, a hash join wins when you are joining a large unsorted fact table to a smaller dimension table that can fit in work memory. A nested loop wins when the outer table is highly selective and the inner table has a selective index. A merge join wins when both inputs are already sorted on the join key or when sorting is cheap compared to hashing a massive table. When I tune a slow join, I check three numbers: the estimated rows on each side, the actual rows on each side, and the memory used for the hash or sort.
| Join Strategy | How It Works | Best For | Watch Out For |
|---|---|---|---|
| Nested Loop | For each row in outer table, probe inner table via index lookup | Highly selective outer table (small result) + indexed inner table; OLTP lookups, LIMIT queries | Catastrophic if outer estimate is low; executes inner scan N times; sequential I/O amplification |
| Hash Join | Build in-memory hash table from smaller input, probe with larger input | Large-to-medium joins without suitable indexes; bulk ETL, analytics, joins on non-indexed keys | Spills to disk if work_mem too low (see "Hash Batches"); large build side causes high memory pressure |
| Merge Join | Sort both inputs on join key, then merge in single pass | Both inputs already sorted via indexes, or very large inputs where hash table would not fit | Sort cost dominates if no supporting index; requires equality join condition |
When Each Strategy Actually Shows Up
PostgreSQL will favor hash joins for SELECT ... FROM orders JOIN customers ON customers.id = orders.customer_id WHERE orders.created_at > '2024-01-01' when both tables are large and the join key is indexed but not selective enough to make nested loops cheap. You'll see Hash Join with Hash Cond and a Hash node that builds from the smaller table predicted by row counts. If that hash spills - shown as Batches: 4 or Disk Usage: 8192kB - increasing work_mem for that session can sometimes eliminate the spill and halve execution time without adding any index.
I've fixed nested loop disasters by adding a single composite index on the inner table that matched the join condition plus the filter, allowing the inner probe to become an index seek instead of a scan. Conversely, I've improved hash joins not by adding indexes at all, but by running ANALYZE so the planner correctly identified which table was smaller and flipped the build and probe sides.
Forcing Plans and Why Hints Are a Last Resort
Some databases allow hints like /*+ HASH_JOIN(orders customers) */ or SET enable_nestloop = off. I use those only as diagnostic tools to test what happens if the planner had chosen differently, never as permanent fixes in application code. Forcing a join strategy locks you into a plan that may be optimal for today's data size but terrible next quarter. Before reaching for hints, I examine whether Database Connection Pooling: PgBouncer, HikariCP and Why It Matters or session-level work_mem and random_page_cost settings are masking the real issue. In one system, lowering random_page_cost from 4.0 to 1.1 on an SSD-based server correctly shifted many queries from hash joins back to index-assisted nested loops because the cost model had been tuned for spinning disks.
-- Diagnose join strategy choice
-- Run once with default planner settings, then compare with nested loops disabled
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-06-01';
-- Test alternative without committing to a hint:
BEGIN;
SET LOCAL enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-06-01';
ROLLBACK;
-- If Hash Join is faster but spills to disk, check memory:
-- Hash Join (actual rows=982341) Batches: 8 Buckets: 131072 Memory Usage: 4033kB Disk Usage: 18432kB
-- => Consider increasing work_mem for this query class or indexing to enable Merge Join.
Rewriting Queries to Guide the Optimizer Without Fighting It
Query rewriting is not about outsmarting the planner; it's about expressing the same logic in a way that makes the planner's job easier. The optimizer handles predicates it can reason about statically far better than functions that obscure the underlying data.
Preserve Sargability and Avoid Functions on Indexed Columns
A predicate is sargable if the database can use an index seek to satisfy it. WHERE created_at >= '2024-01-01' is sargable; WHERE date_trunc('day', created_at) = '2024-01-01' is not, unless you have an expression index matching exactly that form. Similarly, WHERE customer_id::text = '8421' prevents an index on integer customer_id from being used. I frequently see WHERE COALESCE(status, 'pending') = 'pending' written to handle NULLs, which kills index usage. Rewriting as WHERE (status = 'pending' OR status IS NULL) or better yet normalizing the column to be NOT NULL with a default allows a plain index seek. The MDN Web Docs reference on JSON.stringify is useful when you need to understand how client-side serialization affects what you send as SQL parameters for JSONB columns, but on the SQL side, prefer WHERE data->>'status' = 'shipped' with a matching expression index rather than WHERE data::text LIKE '%shipped%'.
Pagination, LIMIT and Common Table Expression Pitfalls
Pagination with large OFFSET is a classic performance trap. OFFSET 100000 LIMIT 20 still has to scan and discard 100,000 rows. For user-facing pagination, keyset pagination using a cursor on an indexed column is dramatically faster: WHERE (created_at, id) < ($last_created_at, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20. This transforms an O(N) offset into an O(log N) index seek.
Common table expressions are another area where semantics matter. In PostgreSQL 11 and earlier, CTEs were optimization fences - the planner always materialized them. Since PostgreSQL 12, they can be inlined, but only if not specified as MATERIALIZED. I've measured queries where moving a filter from outside a CTE to inside it changed the row estimate from 2 million to 800 and allowed an index to be used. If you need materialization to avoid executing an expensive CTE multiple times, be explicit with WITH expensive AS MATERIALIZED (...).
Beware of SELECT * and Implicit Sorting
Using SELECT * prevents index-only scans and increases width, which blows up memory for hash and sort operations. Specify only the columns you need. Also, don't rely on UNION when you mean UNION ALL; the former forces a sort to deduplicate. When I replaced a UNION of two 400k-row sets with UNION ALL after proving duplicates were impossible, sort time dropped to zero and total execution went from 1.2s to 180ms.
Cardinality, Statistics and Parameterization: Why the Same Query Has Two Plans
The hardest sql optimization issues to diagnose are those where the same query text performs well for one parameter value and terribly for another. This is almost always a cardinality estimation problem driven by skewed data distributions, stale statistics, or parameterization that prevents the optimizer from seeing the literal value.
Histograms, MCV Lists and Stale Statistics
PostgreSQL maintains statistics in pg_statistic and exposes a readable view via pg_stats. For each column, it tracks most common values (MCV), histogram bounds, correlation, and n_distinct. When you run ANALYZE, it samples the table to refresh these. If a table has 50 million rows and autovacuum's ANALYZE threshold hasn't been hit because you do bulk loads, the planner may be working with statistics from last month. I schedule manual ANALYZE after large ETL jobs and increase default_statistics_target to 500 for columns with high skew, like status or tenant_id in multi-tenant tables.
To create a controlled sampling pattern for benchmarking or to simulate production skew locally, the approaches discussed in the Python Documentation for the random module are a pragmatic way to generate realistic parameter distributions in your load scripts, which surfaces parameter-sensitive plans far better than uniform random data.
Prepared Statements and the Generic Plan Trap
When you use prepared statements or a connection pooler that forces the protocol-level prepare, the database may generate a generic plan on the fifth execution that must work for any parameter value. If your data is skewed - 90% of orders are status='shipped' and 1% are status='pending' - the generic plan will be a full scan optimized for the common value and terrible for the selective one. In PostgreSQL, you can observe this with EXPLAIN (ANALYZE) EXECUTE. Solutions I use: add a CREATE STATISTICS extended statistics object on correlated columns, use separate query patterns for divergent selectivities, or in extreme skew, avoid server-side prepared statements for that query and use client-side parameter interpolation with proper escaping. MySQL's query cache and plan cache behaviors differ, but the underlying principle of bind-peeking versus generic plans applies across engines.
Extended statistics deserve a mention. If WHERE city = $1 AND zip = $2 has correlated columns, the planner multiplying their individual selectivities will dramatically underestimate row count. CREATE STATISTICS city_zip_stats (dependencies) ON city, zip FROM addresses followed by ANALYZE teaches the planner about the functional dependency and corrects the join order.
Measuring Optimization Work Reliably: Benchmarking Beyond EXPLAIN Timing
EXPLAIN ANALYZE timing is not a benchmark; it's a single execution with cold or warm caches, often on a replica that doesn't reflect production concurrency. Real measurement requires isolation and repetition. I learned this after "optimizing" a query that showed 3x improvement under EXPLAIN ANALYZE but no improvement under production load because the improvement depended on data being cached, which wasn't true when 200 concurrent sessions contended for shared_buffers.
Isolating Variables and Using Production-Like Data
Before and after tests must run on the same hardware class, same database parameters, and same dataset size. Restore a recent production snapshot to a separate instance with matching shared_buffers, effective_cache_size, and work_mem. Run EXPLAIN (ANALYZE, BUFFERS) at least five times and discard the first run to warm the cache. Then run a realistic workload with pgbench or your application load generator with at least 20 concurrent clients to see how buffer eviction and lock waits affect the plan. If you are planning for growth, review your long-term scaling approach with Database Sharding and Partitioning: Scaling Beyond a Single Node so you don't over-optimize a single-node query that should instead be partitioned.
Tracking Logical and Physical I/O, Not Just Wall Time
Wall time varies with system load. Logical I/O (buffers hit + read) is far more stable. I log both before and after: Buffers: shared hit=... from EXPLAIN, plus pg_stat_statements for mean_exec_time, calls, and shared_blks_hit versus shared_blks_read in production. A good optimization reduces logical I/O while keeping wall time stable under concurrency. When a rewrite reduces logical reads from 52,000 to 340 but wall time improves only modestly, it still matters because that reduced I/O leaves cache capacity for other queries, which improves fleet-wide p99.
Finally, record the plan, don't just describe it. Save the full EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output in your pull request for any performance-sensitive change. That artifact lets reviewers verify that the index is used as intended and provides a baseline to compare against regressions. In my teams, any migration that adds an index or rewrites a hot query must include that JSON plan in the migration notes, referencing the zero-downtime patterns from Database Migrations Without Downtime: Expand-Contract Pattern so the index creation itself doesn't lock the table and negate the optimization.
Frequently Asked Questions
When should I use EXPLAIN vs EXPLAIN ANALYZE in production?
Use plain EXPLAIN when you want to see the planned path without executing the query, which is safer for long-running writes or on a primary where you cannot tolerate extra load. Use EXPLAIN (ANALYZE, BUFFERS) on a replica or staging environment with production-like data when you need actual row counts and buffer usage. In my experience, never run EXPLAIN ANALYZE on a destructive statement like UPDATE or DELETE without wrapping it in a transaction you roll back, because ANALYZE actually executes the query.
Why is my new index not being used even though it matches my WHERE clause?
Three common reasons: the column order does not match your predicate pattern (equality before range), the statistics indicate the index would be more expensive than a sequential scan because the filter is not selective, or the predicate is not sargable due to a function wrapping the column. Check EXPLAIN to see the estimated rows for the sequential scan versus the index scan, run ANALYZE on the table, and ensure the query does not cast or transform the indexed column. Also verify the index is valid and not created with CONCURRENTLY left as invalid after a failure.
How do I choose between a hash join and a nested loop join?
You usually don't choose directly; you shape the conditions so the optimizer chooses correctly. Create selective indexes to make nested loops viable when your outer input is small, and ensure work_mem is sufficient to keep hash joins in memory when inputs are large. If EXPLAIN ANALYZE shows a nested loop executing its inner scan thousands of times with high actual rows, you likely need a hash join. If it shows a hash join spilling to disk with multiple batches, you either need more memory, a different join order driven by better statistics, or a merge join supported by an index that provides sorted order.
Does adding more indexes always improve sql optimization?
No. Every index slows down writes, increases vacuum and autovacuum work, and consumes cache. I have seen production systems with 18 indexes on a table where only 4 were ever used according to pg_stat_user_indexes. Unused indexes still need to be updated on every INSERT and UPDATE and can cause write amplification. Audit with pg_stat_user_indexes or MySQL's sys.schema_unused_indexes and drop indexes with zero scans after a full business cycle. Prefer fewer, well-designed composite and partial indexes over many single-column indexes.