A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.

First, separate the two possibilities

There are only two explanations, and conflating them is the mistake most candidates make. Either the index is unusable for this predicate as written, or it is usable but rejected because the optimiser costed it as slower. These have completely different fixes, so establishing which one you are looking at is the first move.

Start with EXPLAIN (ANALYZE, BUFFERS) rather than plain EXPLAIN, because the actual row counts matter. The single most informative signal is the gap between estimated and actual rows. If the planner expected 30 rows and got 800,000, the problem is statistics, not indexing, and adding another index will not help.

Cases where the index cannot be used

A predicate that the optimiser cannot map onto an index is called non-sargable. The common causes are all forms of the same thing — the indexed column is not left bare on one side of the comparison:

• A function or expression wraps the column. WHERE LOWER(email) = ? cannot use a plain index on email, because the index stores the original values. The fix is an expression index on LOWER(email), or normalising the data on write.
• An implicit type cast. Comparing a varchar column to a numeric parameter, or a bigint column to a string, forces a cast on the column side. This one is nasty because the query looks correct and the cast is invisible unless you read the plan carefully.
• A collation mismatch, most often after a restore or a cross-database join, which makes the index ordering unusable for the comparison.
• A leading-wildcard LIKE. LIKE '%term' cannot use a B-tree because B-trees are ordered by prefix. LIKE 'term%' can.
• Wrong position in a composite index. An index on (tenant_id, created_at) cannot efficiently serve a query filtering only on created_at, because the leading column is not constrained.
• OR across different columns, which often defeats a single index and needs either a bitmap combination of two indexes or a rewritten UNION ALL.

Cases where the index is rejected

If the index is usable, the optimiser made a cost decision, and it may well be right.

The dominant factor is selectivity. An index scan costs one random read per matching row plus a heap fetch, while a sequential scan reads pages in order. Once the predicate matches a large enough fraction of the table — often somewhere around five to ten percent, though it depends on row width and cache state — sequential access genuinely wins. If your query matches half the table, the sequential scan is the correct plan and the premise of the question is wrong. Saying so is a strong answer.

The second factor is stale statistics. The planner estimates selectivity from sampled histograms. After a bulk load, a large delete, or on a column whose distribution has shifted, those estimates can be badly wrong. ANALYZE on the table is the cheap test, and if that fixes it you then investigate why autovacuum was not keeping up.

Third, correlated predicates defeat the planner's independence assumption. It multiplies the selectivity of city = 'Pune' and state = 'Maharashtra' as if they were unrelated, producing an estimate far too low. Extended statistics on the column pair is the targeted fix.

Fourth, cost parameters may not match the hardware. The default random_page_cost of 4.0 encodes an assumption about spinning disks. On SSD or when the working set is cached, lowering it toward 1.1 makes index scans correctly cheaper, and this is a common oversight on cloud databases.

Confirming the fix

Re-run EXPLAIN (ANALYZE, BUFFERS) and compare against the baseline you captured, checking that the estimate-versus-actual gap has closed rather than only that the runtime improved on a warm cache. Then check the plan under production-like data volume, because a plan that flips at ten thousand rows may flip back at ten million.

Always state which of the two categories you have landed in before proposing a fix. Candidates who jump straight to "I would add an index" without reading the plan lose the point even when the suggestion happens to work.