Postgres EXPLAIN and Practical Query Tuning

Most slow queries are not slow because Postgres is slow. They are slow because the planner picked a bad strategy, or because the data layout works against the query. The fastest way to fix that is to read the plan.

What EXPLAIN actually does

EXPLAIN shows the plan Postgres intends to use. EXPLAIN ANALYZE actually runs the query and reports real timings and row counts. Use ANALYZE when you need truth, not when you are touching production tables with side effects.

EXPLAIN ANALYZE
SELECT id, email
FROM users
WHERE created_at > now() - interval '7 days';

A typical line looks like this:

Seq Scan on users  (cost=0.00..18532.00 rows=1200 width=36)
                   (actual time=0.012..42.118 rows=1187 loops=1)

The first parenthesis is the planner estimate. The second is reality. The gap between them is the single most important signal in tuning.

The plan shapes you will see

• Seq Scan: read every row. Fine for small tables or queries that touch most rows.
• Index Scan: walk an index, fetch matching rows from the heap. Good for selective filters.
• Index Only Scan: answer the query from the index alone. Excellent, requires the index to cover all columns used.
• Bitmap Heap Scan: build a bitmap of matching pages, then fetch them. Good for medium selectivity.
• Nested Loop: join by probing the inner side once per outer row. Great when the outer side is small.
• Hash Join: build a hash on one side, probe with the other. Great for large unsorted inputs.
• Merge Join: requires both inputs sorted on the join key. Great when sorted indexes already exist.

If you see a Seq Scan plus a high rows removed by filter, you are reading data you do not need.

Estimated vs actual rows

Compare rows= in the estimate to actual ... rows= in reality. If the planner thinks 10 rows will match but actually 100,000 do, you will get a nested loop where you wanted a hash join.

Fix by running:

ANALYZE users;

Or raise statistics targets for skewed columns:

ALTER TABLE users ALTER COLUMN country SET STATISTICS 1000;
ANALYZE users;

A real tuning example

Consider:

SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'IN'
  AND o.created_at >= '2026-01-01';

Plan shows a Seq Scan on orders and a Hash Join. Two questions:

1. Is orders.created_at selective enough to deserve an index?
2. Is the join hitting many customers or few?

If most rows pass created_at, an index there will not help. If only 3 percent pass, add:

CREATE INDEX orders_created_at_idx ON orders (created_at);

If the query frequently filters by both customer_id and created_at, a composite index is often better:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);

Order matters. Put equality columns first, then range columns.

Index only scans

If the query needs just a few columns, include them in the index:

CREATE INDEX users_email_id_idx ON users (email) INCLUDE (id);

Now SELECT id FROM users WHERE email = $1 can be answered from the index alone. Confirm by looking for Index Only Scan and Heap Fetches: 0 in the plan. Heap fetches above zero mean the visibility map is stale. Run VACUUM to refresh it.

When indexes hurt

Every index slows writes and consumes space. Skip indexes when:

• The table is small. Seq Scan wins.
• The filter is not selective. An index that returns 60 percent of rows is worse than a scan.
• The column is heavily updated. Index maintenance dominates.

Check existing indexes before adding more:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

Common rewrites that unlock plans

LIKE with leading wildcard cannot use a btree index. Use a trigram index instead:

CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);

Functions on indexed columns disable the index. Rewrite:

-- bad
WHERE lower(email) = 'a@b.com'
-- good: functional index
CREATE INDEX users_lower_email_idx ON users (lower(email));

OR across columns often beats the planner. Convert to UNION ALL when each branch can use a different index:

SELECT * FROM orders WHERE customer_id = 42
UNION ALL
SELECT * FROM orders WHERE coupon_code = 'SAVE10';

SELECT * blocks Index Only Scans. List only what you need.

buffers tells you what was read

Add BUFFERS:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Look for shared read= vs shared hit=. Reads come from disk, hits from cache. A query that does 50,000 disk reads is bound by I/O. Improve cache locality with a better index or by reducing the data scanned.

A short tuning workflow

1. Capture the slow query with bind values.
2. Run EXPLAIN (ANALYZE, BUFFERS) in a transaction you roll back.
3. Compare estimated vs actual rows. Run ANALYZE if the gap is large.
4. Identify the dominant cost node. Tune that one first.
5. Add or adjust one index. Re-run. Stop when good enough.
6. Document the index in the schema, not just in your head.

For broader background on indexing strategy, see SQL Indexes and Performance. For the JOIN patterns that shape these plans, revisit SQL Joins.

Wrap up

Tuning Postgres is mostly a literacy exercise. Once you can read a plan, the path to a fix is short: find the expensive node, check whether row estimates match reality, then either give the planner better statistics or give it a better index. Treat EXPLAIN as your first instinct, not your last resort.