SQL Query Optimization: Read the Plan Before You Guess

Query optimisation starts with EXPLAIN ANALYZE, not with intuition. Everything below is about reading what it tells you and acting on it.

Read the plan first#

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Four things to look for, in order:

1. Seq Scan on a large table. A sequential scan is fine on 500 rows and a problem on 5 million.
2. A big gap between rows= estimated and actual rows. The planner is working from bad statistics, so every decision downstream is suspect. ANALYZE the_table; refreshes them.
3. Nested Loop with a high row count on the outer side. Usually a missing index on the join key.
4. Sort with Sort Method: external merge Disk. The sort spilled to disk; either add a covering index or raise work_mem.

Index rules that actually matter#

Index your foreign keys. Most databases index primary keys automatically and foreign keys not at all. Every JOIN ... ON o.user_id = u.id wants an index on orders.user_id.

Column order in a composite index is not arbitrary. An index on (status, created_at) serves:

WHERE status = 'active'                              -- ✅ uses it
WHERE status = 'active' AND created_at > '2026-01-01' -- ✅ uses it fully
WHERE created_at > '2026-01-01'                       -- ❌ cannot use it

The leftmost columns must be present. Put equality columns before range columns.

Covering indexes avoid the table entirely. If the index contains every column the query needs, the engine never touches the heap:

CREATE INDEX idx_orders_lookup ON orders (user_id, status) INCLUDE (total);

Partial indexes for skewed data. If 98% of rows are archived, an index over all rows is mostly waste:

CREATE INDEX idx_active ON orders (created_at) WHERE status = 'active';

Patterns that defeat an index#

A predicate is "sargable" when the engine can use an index for it. These are not:

-- Function on the indexed column
WHERE LOWER(email) = 'a@b.com'
-- Fix: index the expression
CREATE INDEX ON users (LOWER(email));
 
-- Arithmetic on the column
WHERE price * 1.2 > 100
-- Fix: move it to the other side
WHERE price > 100 / 1.2
 
-- Leading wildcard
WHERE name LIKE '%smith'
-- Fix: trigram index, or a reversed-string column
 
-- Type mismatch forcing a cast
WHERE user_id = '123'      -- user_id is an integer
-- Fix: pass the right type from your application
 
-- Date extraction
WHERE EXTRACT(YEAR FROM created_at) = 2026
-- Fix: a range, which is sargable
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

SELECT * costs more than you think#

It is not only bandwidth. Selecting every column defeats covering indexes, forces the engine to read the heap, and pulls large TEXT/JSONB columns you never use. Naming columns is also a compatibility win — adding a column to the table cannot change your result shape.

N+1 is a query-count problem, not a query-speed problem#

SELECT * FROM posts LIMIT 20;            -- 1 query
SELECT * FROM users WHERE id = ?;        -- x20

Each query is fast; the round-trips are not. Fix it with a join or a single WHERE id = ANY($1). An ORM's eager-loading option usually does this for you, and this is worth checking before optimising anything else — it is the most common cause of a slow endpoint with fast queries.

Pagination: OFFSET does not scale#

OFFSET 100000 requires the engine to produce and discard 100,000 rows. Keyset pagination is O(1):

-- Instead of OFFSET, remember the last row you saw
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The tuple comparison handles ties correctly, which a bare created_at < does not.

Frequently asked questions#

How many indexes is too many?#

Every index slows writes and consumes space. A rough guide: if an index has not been used (check pg_stat_user_indexes), drop it.

Should I use an ORM?#

Yes for the 95% of queries that are simple, and drop to raw SQL for the reporting queries where you need control. Fighting an ORM to express a window function is wasted effort.

Does adding an index ever make a query slower?#

Indirectly — the planner may choose it wrongly on skewed data. It also always slows inserts and updates.

Why is my query fast in staging and slow in production?#

Different data volume and distribution, hence a different plan. Compare EXPLAIN ANALYZE output between the two rather than assuming the query is at fault.

Related reading#

• SQL Joins Explained
• MongoDB Indexes — the same principles, different syntax
• SQL Formatter for making a 200-line query readable.

References#

• PostgreSQL docs: Using EXPLAIN
• Use The Index, Luke