SQL Query Optimization: 15 Techniques That Turn Slow Queries Into Fast Ones

-- Always ANALYZE + BUFFERS
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name;

-- Warning signs:
-- "Seq Scan on orders" + large table = missing index
-- "actual rows=1 loops=50000" = N+1 query pattern
-- "Rows Removed by Filter: 490000" = filter not using index
-- "Buffers: shared hit=12 read=98000" = mostly disk, cold cache

-- Rule: filter columns first, then sort, then select
SELECT id, email FROM users
WHERE status = 'active'
ORDER BY created_at DESC LIMIT 20;

-- BAD: wrong column order
CREATE INDEX ON users(created_at, status);

-- GOOD: filter first, sort second
CREATE INDEX ON users(status, created_at DESC);

-- BEST: covering index (no table heap access at all)
CREATE INDEX ON users(status, created_at DESC) INCLUDE (id, email);
-- Entire query from index alone = Index Only Scan

-- SLOW: function disables index
SELECT * FROM orders WHERE DATE(created_at) = '2026-04-07';
-- Seq Scan: applies DATE() to every row

-- FAST: range on raw column
SELECT * FROM orders
WHERE created_at >= '2026-04-07'
 AND created_at < '2026-04-08';
-- Index Scan: uses index directly

-- For case-insensitive search: functional index
CREATE INDEX ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- SLOW: runs COUNT(*) once per user row
SELECT u.name,
 (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as cnt
FROM users u;

-- FAST: single hash join, one pass
SELECT u.name, COUNT(o.id) as cnt
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- For EXISTS:
-- SLOW: COUNT(*) > 0
SELECT * FROM users u WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 0;
-- FAST: EXISTS with early termination
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- SLOW: self-join for running total = O(n²)
SELECT o.id, o.amount,
 (SELECT SUM(o2.amount) FROM orders o2 WHERE o2.created_at <= o.created_at) as running
FROM orders o;

-- FAST: window function = single pass
SELECT id, amount,
 SUM(amount) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING) as running
FROM orders;

-- Other essential window functions:
-- ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) -- rank per user
-- LAG(amount, 1) OVER (ORDER BY created_at) -- previous row value
-- NTILE(4) OVER (ORDER BY amount) -- quartile

-- SLOW: OFFSET reads and discards rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Reads 10,020 rows to return 20. Gets slower every page.

-- FAST: keyset pagination uses index directly
-- First page:
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (use last row values as cursor):
SELECT * FROM posts
WHERE (created_at, id) < ('2026-04-01 12:00:00', 9876)
ORDER BY created_at DESC, id DESC LIMIT 20;
-- O(log n) regardless of page depth
-- Requires: index on (created_at DESC, id DESC)

-- Full index on orders(status) includes 10M completed orders
CREATE INDEX idx_orders_status ON orders(status);

-- Partial index: only index the 100K pending orders you query
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- 100x smaller, 100x faster to maintain
-- Query MUST include WHERE status = 'pending' to use it