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

A poorly written SQL query against a large table is one of the most common causes of production incidents. The fix is rarely just “add an index” — it requires understanding query planners, what data you actually need, and which query shape lets the database do the least work. These 15 techniques cover the full spectrum from index design to query rewriting.

⚡ TL;DR: Read EXPLAIN ANALYZE before optimizing. Composite indexes in (filter, sort, select) order. Never apply functions to indexed columns. Rewrite correlated subqueries as JOINs. Use window functions instead of self-joins. Use keyset pagination not OFFSET. Partial indexes for filtered subsets.

1. Read EXPLAIN ANALYZE before touching anything

-- 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

2. Composite indexes in the right order

-- 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

3. Never apply functions to indexed columns

-- 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';

4. Rewrite correlated subqueries as JOINs

-- 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);

5. Window functions replace self-joins

-- 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

6. Keyset pagination beats OFFSET

-- 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)

7. Partial indexes for filtered subsets

-- 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

8-15: Quick wins

• 8. SELECT only needed columns — never SELECT *. Reduces I/O, memory, and network transfer especially for wide tables.
• 9. Batch INSERTs — INSERT INTO t VALUES (...),(...),...) is 10–50x faster than individual inserts (reduced WAL, fewer round-trips).
• 10. Connection pooling — each new database connection costs 10–30ms. Use PgBouncer or RDS Proxy to reuse connections.
• 11. VACUUM + ANALYZE — dead tuples and stale statistics cause wrong query plans. Schedule both regularly, especially after bulk operations.
• 12. Materialized views — pre-compute expensive aggregations. Refresh on schedule or trigger-based for expensive reporting queries.
• 13. Table partitioning — partition time-series tables by month/year. Queries on recent data only scan recent partitions (partition pruning).
• 14. Read replicas for analytics — never run heavy analytical queries on the primary. Route to read replicas to protect write throughput.
• 15. Raise statistics target — ALTER TABLE t ALTER COLUMN c SET STATISTICS 500 for high-cardinality columns. Helps the planner make better decisions.

For PostgreSQL-specific EXPLAIN interpretation, the PostgreSQL query planner guide goes deeper. For debugging slow queries in production without downtime, see the production Postgres debugging guide covering pg_stat_statements. External reference: Use The Index, Luke.