SQL Query Optimization: Find and Fix Your Slowest Queries

A single unindexed query on a table with 10 million rows can take 30 seconds and lock your application for every user. Database performance problems are disproportionately impactful because they affect every request that touches the slow path. The good news: most performance issues come from a handful of recurring patterns, and fixing them is often a matter of adding the right index or restructuring a query.

Reading EXPLAIN ANALYZE Output

Before optimizing anything, you need to understand what the database is actually doing. In PostgreSQL, prefix any query with EXPLAIN ANALYZE to get the execution plan with real timing data:

EXPLAIN ANALYZE
SELECT o.id, o.total, u.name, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Example output (simplified):
-- Limit (cost=1245.32..1245.45 rows=50) (actual time=342.1..342.3 rows=50)
--   -> Sort (cost=1245.32..1248.67 rows=1340) (actual time=342.1..342.2 rows=50)
--     -> Hash Join (cost=45.00..1198.67 rows=1340) (actual time=12.4..341.8 rows=1340)
--       -> Seq Scan on orders (cost=0.00..1145.00 rows=1340) (actual time=0.02..330.5 rows=1340)
--            Filter: (status = 'pending' AND created_at > ...)
--            Rows Removed by Filter: 498660
--       -> Hash (cost=30.00..30.00 rows=1200) (actual time=2.1..2.1 rows=1200)
--         -> Seq Scan on users (cost=0.00..30.00 rows=1200)

The critical line is Seq Scan on orders with Rows Removed by Filter: 498660. The database read 500,000 rows to find 1,340 matches. That sequential scan is your bottleneck.

Fixing It with the Right Index

-- Composite index matching the WHERE clause columns
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

-- Re-run EXPLAIN ANALYZE:
-- Index Scan using idx_orders_status_created on orders
--   (actual time=0.03..1.2 rows=1340)
-- Total: 1.8ms vs 342ms -- a 190x improvement

The composite index on (status, created_at DESC) lets PostgreSQL jump directly to status = 'pending' entries and walk them in order, satisfying both the filter and the sort without touching unrelated rows.

The N+1 Query Problem

The most common performance issue in ORM-based applications. Loading 50 orders, then loading each order’s user individually, produces 51 queries instead of one:

-- N+1 pattern (what your ORM generates without eager loading):
SELECT * FROM orders WHERE status = 'pending' LIMIT 50;  -- 1 query
SELECT * FROM users WHERE id = 101;  -- query 2
SELECT * FROM users WHERE id = 102;  -- query 3
-- ... 48 more individual user queries

-- Fixed with a JOIN or IN clause:
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
LIMIT 50;
-- 1 query, same result

In your ORM, this is typically solved with eager loading: .include() in Prisma, with() in Laravel Eloquent, select_related() in Django, or .Include() in Entity Framework.

Covering Indexes

A covering index includes all columns the query needs, eliminating the need to read the actual table rows (a “heap fetch”). PostgreSQL calls this an Index Only Scan:

-- This query only needs status, created_at, and total
SELECT status, created_at, total
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

-- Covering index: includes 'total' so the table is never touched
CREATE INDEX idx_orders_covering
ON orders (status, created_at DESC)
INCLUDE (total);

The INCLUDE clause adds columns to the leaf pages of the index without affecting the sort order. The query is answered entirely from the index. On large tables, this can be the difference between 2ms and 200ms.

Partial Indexes for Selective Queries

If you only query a subset of rows (active users, pending orders, unprocessed jobs), a partial index is smaller and faster:

-- Only index the rows you actually query
CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';

-- 2% of rows indexed instead of 100% -- dramatically smaller, faster to maintain

Common Anti-Patterns

Functions on indexed columns. WHERE LOWER(email) = 'alice@example.com' cannot use an index on email. Create a functional index: CREATE INDEX idx_email_lower ON users (LOWER(email));

SELECT * when you need two columns. Fetching 30 columns when you need 3 wastes IO and memory, and prevents covering index optimizations.

Missing LIMIT on exploratory queries. A dashboard showing “recent orders” without a LIMIT can scan millions of rows as the table grows.

Over-indexing. Every index slows writes. A table with 15 indexes has 15 B-trees to update on every INSERT. Index what you query, not everything.

Start with EXPLAIN ANALYZE, identify sequential scans on large tables, add targeted indexes, and measure again. Most applications have 3-5 queries responsible for 80% of database load. Find and fix those first.

Further reading: PostgreSQL EXPLAIN Docs | PostgreSQL Index Types | Use The Index, Luke