Reading a Query Plan Well Enough to Fix It Yourself

Run EXPLAIN ANALYZE, not just EXPLAIN, since the former actually executes the query and reports real timing and row counts, while the latter only estimates. The two numbers worth comparing first are the planner's estimated row count against the actual row count at each step; a large gap between them means the planner's statistics are stale or the query's structure is confusing it, and that gap is often the real root cause even when the symptom looks like a missing index.

• Sequential scan on a large table with a selective filter: the clearest missing-index signal, especially when the filter column has high cardinality.
• Index exists but isn't used: often caused by a function wrapped around the indexed column in the WHERE clause, which prevents the planner from using a standard index at all.
• Nested loop join with a huge outer row count: usually means a join order the planner chose poorly, often fixable by adding a more selective index on the join or filter column.
• Bitmap heap scan spilling to a large recheck: suggests the index is only partially selective for this query and a composite index covering more of the filter conditions would help.

Most real-world slow queries are one of these four, not something exotic.

Every index speeds up the specific read patterns it covers and slows down every write to that table, since the database has to maintain the index on every insert, update, and delete. Adding an index for a query that runs once a day while degrading write performance on a table that gets updated constantly is a bad trade even if it technically makes that one query faster. Check write volume on the table before adding an index, not just read frequency of the slow query.

A composite index on columns (a, b) is efficient for a query filtering on a alone, or on a and b together, but does not help a query filtering on b alone; Postgres can't use a composite index starting from a column that isn't its leading column. Order composite index columns with the most selective, most commonly filtered-alone column first, and verify this against your actual query patterns rather than the order the columns happen to appear in the table schema.

After adding or changing an index, run EXPLAIN ANALYZE again and confirm the plan actually changed to use it, since the planner won't always switch immediately, particularly if table statistics are stale. Run ANALYZE on the table manually if needed, and compare the new actual execution time against the old one under a realistic data volume, not a small test table where every plan looks fast regardless of whether the index is actually helping.

A team adds an index on a status column they believe is the bottleneck in a slow report query, reruns EXPLAIN ANALYZE, and the planner still shows a sequential scan. Looking closer, the query wraps that column in a function call to normalize case before comparing it, which silently prevents the plain index from being used at all. The fix isn't a different index, it's either removing the function call from the query or building the index on the function's result directly, and only after that change does the plan finally switch to an index scan and the query's execution time drop.