Learn

EXPLAIN is the difference between guessing and knowing why a query is slow. Every modern DB has it.

SQLite

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';

Returns text like:

• SCAN users — full table scan, BAD on large tables
• SEARCH users USING INDEX idx_email (email=?) — index lookup, GOOD
• SEARCH users USING COVERING INDEX idx_email_name — index alone answered, BEST

Postgres

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Plan only

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
-- Plan + actual runtime

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;
-- Detailed JSON output

Readable output:

Seq Scan on users  (cost=0.00..1450.00 rows=1 width=85)
                   (actual time=2.1..3.5 rows=1 loops=1)
  Filter: (email = '[email protected]'::text)
  Rows Removed by Filter: 9999
Planning Time: 0.123 ms
Execution Time: 3.521 ms

The actual time and Rows Removed by Filter tell you:

• It scanned 10,000 rows to find 1
• Took 3.5ms

Add an index on email, re-run, and you'll see Index Scan with rows=1 and time well under 1ms.

Key things to look for

Operator types (Postgres terminology):

• Seq Scan — full table scan. Bad on large tables.
• Index Scan — uses an index, fetches rows from heap.
• Index Only Scan — index alone answers (covering). Fastest.
• Bitmap Heap Scan + Bitmap Index Scan — multiple index candidates.
• Nested Loop — pairwise. Fast for small inputs, terrible for large.
• Hash Join — builds hash table, scans the other side. Good for medium-sized.
• Merge Join — both sides sorted, walked together. Good for large + sorted.
• Sort — explicit sort step. Often a sign of missing index for ORDER BY.
• Materialize — buffers a node's output. Sometimes inserted by the planner.

The estimated row count (Postgres rows=) is what the planner thinks. If it's wildly off from the actual, statistics are stale — ANALYZE table_name.

Cost is in arbitrary units. Higher = slower. Compare costs across alternative plans.

Common slow patterns and fixes

1. Missing index

SELECT * FROM users WHERE email = ?;
-- Seq Scan -> add index on email

2. Function on indexed column

SELECT * FROM users WHERE LOWER(email) = ?;
-- Index unused. Either:
-- - Index the expression: CREATE INDEX ON users(LOWER(email));
-- - Normalize the data: store emails lowercase

3. ORDER BY without matching index

SELECT * FROM events WHERE user_id = ? ORDER BY created_at DESC;
-- Need composite index (user_id, created_at)

4. SELECT *** when you only need a few columns

SELECT * FROM users WHERE id = ?;
-- If you only need name, the row fetch is unnecessary.
-- A covering index on (id) INCLUDE (name) avoids the heap visit.

5. NOT IN with NULL

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned);
-- If banned.user_id has NULLs, this returns NOTHING.
-- Fix: NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = users.id)

6. Large IN list

SELECT * FROM users WHERE id IN (1, 2, ..., 10000);
-- Slow. Use a temp table or VALUES list and JOIN:
-- SELECT u.* FROM users u JOIN (VALUES (1), (2), ...) v(id) ON v.id = u.id;

Statistics

The planner uses statistics about each table — number of rows, distinct values per column, value distribution. Postgres collects these via auto-analyze; SQLite via ANALYZE.

When statistics are stale, the planner picks bad plans. Run ANALYZE after large data changes (deletes, restores).

Other tools

pg_stat_statements (Postgres) — what queries are slow in production?

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

auto_explain — log slow query plans automatically.

pg_stat_user_indexes — which indexes are used?

SELECT relname, indexrelname, idx_scan, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;
-- Indexes with 0 scans are unused — candidates for dropping.

The mindset

1. Identify slow queries (logs, monitoring, user reports).
2. EXPLAIN ANALYZE them.
3. Find the operator that's eating the time.
4. Try a fix (index, rewrite, denormalize).
5. Re-EXPLAIN. Compare.

Repeat until fast enough or out of options. Don't optimize blindly — profile first.

Every senior backend engineer has spent hundreds of hours staring at EXPLAIN output. It's the most leverage-per-hour skill in database work.