PostgreSQL Indexes: Read EXPLAIN Before Adding One
A slow query is not proof that every filtered column needs an index. This guide gives you a small, testable way to approach the issue before changing a live system.
A practical workflow
- Capture EXPLAIN (ANALYZE, BUFFERS) for a representative query and note the actual rows, scans and time.
- Add a candidate index on a staging copy, refresh statistics and compare the same plan and workload.
What to check
An index consumes storage and slows writes; remove a candidate that does not improve the measured workload. Compare the result with the original problem and record what changed. If a measurement or test does not support the change, revisit the assumption rather than adding more complexity.
Keep the experiment small enough to reverse. Document the starting state, the change and the result so another person can repeat the process. Product menus and versions can change, so check the current documentation for your environment.
Official reference: Read the documentation.