Query Optimization

Overview

Query optimization is the practice of restructuring queries and schema so the database returns results with the least time and resources. The planner chooses an execution strategy based on statistics, indexes, and estimated row counts. The key skill is reading the execution plan with EXPLAIN to find scans, join methods, and bad estimates, then addressing the true bottleneck.

Syntax / Usage

Use EXPLAIN to see the planned strategy and EXPLAIN ANALYZE to run the query and report actual timings and row counts. Compare estimated versus actual rows to spot stale statistics.

-- Show the plan without running
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

-- Run and measure actual cost, timing, and rows
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name;

-- Refresh planner statistics after large data changes
ANALYZE orders;

Examples

Replace a sequential scan by adding an index that matches the filter and sort:

-- Plan shows "Seq Scan on orders" + expensive Sort
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- Composite index lets the planner use an Index Scan and skip the sort
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

Make a filter sargable so the index can actually be used:

-- Not sargable: function on the column disables the index
SELECT * FROM users WHERE lower(email) = 'a@b.com';

-- Sargable: index a normalized column or store email lowercased
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';

Common Mistakes

• Optimizing by guesswork instead of reading EXPLAIN ANALYZE output
• Wrapping indexed columns in functions or type casts, defeating the index
• Selecting SELECT * when a covering index over a few columns would suffice
• Ignoring stale statistics—run ANALYZE after bulk inserts, updates, or deletes
• Adding indexes without measuring their cost to write throughput and storage
• Fixing the outermost query when a correlated subquery is the real bottleneck

See Also

indexes joins sql-views