When a listing or search query feels slow, adding an index is an attractive immediate step. The safer, more reliable workflow starts with EXPLAIN (ANALYZE) and a careful comparison of planner estimates to actual execution. This short guide explains an implementation-focused approach and what you can expect.
Implementation
Capture the exact slow query with realistic parameters and run EXPLAIN (ANALYZE, FORMAT JSON). Use the JSON form for deterministic parsing and automated checks.
Read the plan node by node. Compare estimated rows to actual rows; identify the slow child node. Nested loop inner scans, Sort nodes, and Seq Scans on large tables are common hotspots.
Inspect existing indexes (pg_indexes or pg_catalog).…