PostgreSQL deep dive — EXPLAIN · indexes

A slow SELECT can come from missing access paths, poor joins, stale statistics, excessive result sets, locks, or storage reads. EXPLAIN turns a guess into an inspectable execution path.

From measurement to production

Representative query

Keep the real predicates, ordering, and LIMIT.

Execution plan

Inspect rows, buffers, and sort work.

Candidate validation

Measure improvement and write cost on staging data.

Production rollout

Apply idempotently and require zero catalog drift.

1. EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;

Index Scan — walks a matching key range
Index Only Scan — can reduce heap access when the index and visibility map suffice
Bitmap Index Scan — can efficiently collect and batch-fetch many matching rows
Seq Scan — reads the table range; it can be correct for a small table or a query returning many rows
actual time — real time

EXPLAIN ANALYZE executes the statement. Before using it on writes or production data, establish transaction, replica, and rollback boundaries.

2. Indexes that silently don't work

WHERE LOWER(email) = 'a@b.c'         -- no. Use functional index
WHERE name ILIKE '%kim%'             -- no. Use pg_trgm GIN
WHERE user_id::text = '123'          -- no. Match the type
WHERE a = 1 OR b = 2                 -- use UNION or composite

3. Composite indexes — column order

CREATE INDEX ON posts (user_id, created_at DESC);

Left-to-right prefix matters:

WHERE user_id = 1                         -- ✓
WHERE user_id = 1 AND created_at > ...    -- ✓
WHERE created_at > ...                    -- ✗

Repeated equality predicates commonly lead, followed by range or ordering columns. Do not rely on “highest selectivity first” alone; design from the actual WHERE and ORDER BY together.

LIMIT does not remove sort cost

SELECT id, name, district
FROM facilities
WHERE region_code = $1 AND type_code = $2
ORDER BY district NULLS LAST, name
LIMIT 100;

With only a region_code index, PostgreSQL may read thousands of matching rows, run a top-N heapsort, and then return 100. The following index places the user-visible ordering after the equality predicates, allowing the scan to stop at the first 100 rows.

CREATE INDEX idx_facilities_region_type_list
ON facilities (region_code, type_code, district, name);

If the list without a type filter is also frequent, it needs (region_code, district, name). An index with type_code in the middle cannot guarantee that ordering when the type is omitted. Verify a sort-free Index Scan into Limit, then remove only a standalone type_code index that has no real use and is covered by the new composite path.

4. Covering index

CREATE INDEX ON posts (user_id, created_at DESC) INCLUDE (title);

Enables Index Only Scan.

5. Partial index

CREATE INDEX idx_published_posts ON posts (created_at DESC)
WHERE published = true;

The index stays small when published = true selects a minority of rows and the representative query states the same predicate. If 95% of rows are true, the size advantage is small.

The query predicate must logically imply the partial-index predicate. A missing condition—or a parameter form the planner cannot prove—can prevent its use.

SELECT id, title
FROM posts
WHERE published = true
ORDER BY created_at DESC
LIMIT 20;

Read path	Suitable index	Cost to check
Latest rows by user	`(user_id, created_at DESC)`	page depth and selected columns
Unprocessed work queue	`(created_at, id) WHERE processed_at IS NULL`	claim contention and retries
Latest rows by status	`(status, created_at DESC)`	status distribution and write amplification

6. GIN / GiST

Index	For
btree	default (eq · range · sort)
hash	equality only
GIN	arrays · JSONB · trigram · tsvector
GiST	PostGIS · ranges
BRIN	huge time-series tables

CREATE INDEX ON posts USING gin (tags);
CREATE INDEX ON posts USING gin (content gin_trgm_ops);

7. ANALYZE

Runs via autovacuum, but manual after large changes:

ANALYZE posts;

8. Slow query log

postgresql.conf:

log_min_duration_statement = 1000

Measure before tuning.

9. pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Find the worst offenders.

10. Gotchas

Too many indexes → slow writes
UUID btree → page fragmentation (consider UUID v7)
Nullable columns with mostly NULL → partial index
Skipping VACUUM → stale stats, bad plans

11. A safe production rollout

Preserve the real predicates, ordering, and LIMIT in the representative query.
Compare EXPLAIN (ANALYZE, BUFFERS) before and after on staging data.
Create the new index first; remove a covered index only after the read plan is verified.
A foreign-key leading index may still matter for parent updates and deletes even if it is invisible in a user query.
Bring the production catalog and CREATE definition to the same final state, then require zero schema drift.
Do not conclude from an empty-table plan alone. Distribution and statistics can change the planner's choice.

Closing

Treat “representative query → plan → blocks and rows → write cost → drift verification” as one operational tuning loop.

02-multi-pool-orchestration