How do I confirm a missing index is slowing a PostgreSQL query using EXPLAIN ANALYZE and index inspection?

When a search or listing query feels slow, the useful first question is not “which index should I add?” It is “what plan did PostgreSQL actually choose for this parameter set, and why?” The difference matters because a slow query can come from a sequential scan, a nested loop that multiplies work, a sort that spills, or a bad row estimate that makes an otherwise reasonable plan look cheap to the planner. If you change the schema before you read the plan, you can easily fix the wrong thing or miss the real bottleneck entirely.

EXPLAIN ANALYZE is the tool that turns that guess into evidence. PostgreSQL executes the query, then reports estimated rows and actual rows, plus actual timing for each node in the plan tree. That lets you compare the planner's model with reality instead of relying on intuition. The same output also shows whether the query used a sequential scan, an index scan, an index-only scan, or a bitmap path, and whether nodes such as Sort or Hash consumed noticeable time.

The workflow in this article uses those facts in a strict order. First, inspect the plan for the specific query shape that is slow in production. Next, check whether the table already has an index whose columns and ordering actually match the predicate and sort order. Only then decide whether to add a new multi-column index, adjust the query so the planner can use an index-only scan, or stop because the existing plan is already the right one for the data you have.

• Run EXPLAIN ANALYZE on the real query shape, with realistic parameters and current statistics.
• Read estimated rows next to actual rows, then inspect the slow node instead of guessing the fix.
• Check whether an existing index matches both the WHERE clause and the ORDER BY shape before creating a new one.

The most valuable EXPLAIN ANALYZE habit is to read one plan node at a time and compare its estimate to what really happened. PostgreSQL documents that the command shows true row counts and true run time accumulated within each node, alongside the planner's estimates. That means the output is not just a report of what happened; it is a test of how well the planner understood the data distribution. If the estimates are close, the planner probably had enough information to make a good choice. If they are far off, the query may be slow because the planner was navigating with stale or weak statistics.

The node that matters is often not the top one. A top-level execution time can be dominated by a child scan, a nested loop repeated many times, or a sort that must process far more rows than expected. A nested loop can look harmless at the root while hiding expensive repeated work in its inner child. Likewise, a Sort node can be the visible symptom even when the real problem is upstream row inflation. The plan gives you the sequence of work; the real diagnosis comes from spotting which node does too much work for the rows it should have handled.

A good reading pattern is to ask three questions for each suspicious node. First, is the node type itself expensive for the data shape, such as Seq Scan on a large filtered table? Second, do the actual rows differ greatly from the estimate, which suggests the planner misread selectivity? Third, is there a supporting node below it, such as an index scan, that indicates the query already has partial index help but still pays for heap fetches or sorting? Those answers are the bridge between “this is slow” and “this index or query shape is the likely repair.”

• EXPLAIN ANALYZE reports both estimated and actual row counts per node, so mismatches are easy to spot.
• Plan node timing is shown in milliseconds of real time, while cost is an arbitrary estimate used for planning.
• The top-level execution time includes executor startup and shutdown, but not client display time or parsing time.

A sequential scan is the obvious red flag, but it is not proof that the right answer is to create an index. PostgreSQL's documentation warns that very small tables often favor sequential scans even when indexes exist, because one page read can be cheaper than the overhead of an index lookup. The same documentation also says that EXPLAIN results should not be extrapolated from toy-sized tables to large ones. In other words, a sequential scan on a small relation can be correct, while a sequential scan on a selective query over a large relation is the signal you need to investigate index support or statistics quality.

The first thing to check is whether ANALYZE has been run recently enough for the data distribution you are querying. PostgreSQL's index-usage guidance is explicit: run ANALYZE first, because the planner needs statistics to estimate row counts realistically. Without them, default assumptions are often wrong, and those wrong assumptions can produce a plan that looks like a missing index problem when the true issue is stale statistics. That is why an index-addition reflex is risky. You want to know whether the planner is choosing a sequential scan because it truly expects a large portion of the table to qualify, or because it does not yet know that the predicate is highly selective.

That distinction also explains why real data matters. If your test fixture has too few rows, or values that are unrealistically uniform, the planner can make an entirely different choice than it would on production data. This article's workflow assumes the opposite: capture the slow query with realistic parameters, inspect the actual plan, and only then decide whether the scan type is evidence of a missing index or merely evidence that the query is operating over too much of the table for an index to help.

• A sequential scan is not automatically bad, but on a large selective query it is a strong clue that the plan is not using a useful index path.
• Planner estimates can be wrong when statistics are stale or too coarse, so run ANALYZE before deciding that an index is missing.
• Use real parameters and real data; toy tables often produce plans that do not generalize.

A slow query often reveals itself through a node that is not obviously the root cause. PostgreSQL documents that Sort nodes report the sort method and memory usage, which lets you tell whether the operation stayed in memory or needed more resources than expected. That matters for list and search pages, where the query may be mostly fine until an extra sort appears because the requested ordering does not match the index order. A sort is often the symptom of a query that could benefit from a different index shape, not a universal sign that the database is failing.

Nested loops deserve special attention because the plan shows the inner child as if it were run once per outer row. PostgreSQL's documentation notes that the loops value reports total executions, while actual time and rows are averaged per execution. That means a tiny inner index scan can still be expensive if the outer side produces many rows. The same output can show rows removed by filter, which helps distinguish “the planner found rows but rejected most of them later” from “the planner never had a useful access path.” Those patterns are often more informative than a raw wall-time number.

Heap fetches are especially important when you think you have an index-only plan. PostgreSQL's plan output can show Heap Fetches for index-only scans, and a nonzero value means the engine still had to visit the table heap for some tuples. That does not necessarily mean the index is useless, but it does mean the scan is not fully covering the query's data needs. If your goal is to remove heap visits entirely, the question is not just “is there an index?” but “does this index contain the columns needed for the predicate, the ordering, and the projected result set?”

• Sort nodes expose whether the operation stayed in memory or needed more work than expected.
• Nested loops multiply the cost of the inner plan by the outer row count, so a cheap-looking inner node can become expensive overall.
• Index searches, heap fetches, and rows removed by filter are the three details that often reveal why an index path is still not ideal.

PostgreSQL's index-usage guidance says the right first move is to inspect the real query workload and to run ANALYZE before experimentation. For one slow query, the practical version of that advice is simple: list the table's indexes, read the definition of each candidate index, and compare the actual query text against the indexed columns and ordering. A matching index name is not enough. What matters is whether the index's leading columns align with the equality or range predicates you use most often, and whether its order can satisfy the requested ORDER BY without an extra sort.

The catalog view pg_indexes gives you the definition text, which is useful because the shape of the index often matters more than the name. If the query filters by tenant and status, then orders by created_at descending, a single-column index on created_at may not help much for that specific workload. A multi-column index whose prefix follows the filter columns can do more useful work, especially if it lines up with the ordering the query already wants. That is the kind of mismatch the plan should reveal: a slow sort or a bitmap heap scan can tell you that the current index support is partial, not absent.

For broader usage questions, pg_stat_user_indexes helps identify whether an index sees real work on the server. But that view is a workload statistic, not a per-query diagnosis. It tells you whether an index is used often, not whether it is the right access path for this one query and parameter set. EXPLAIN remains the proof for the current question, because it shows whether this exact predicate and this exact ordering can exploit the index you already have.

• Check the catalog view first to see whether an index exists at all, then read the definition to see what columns and order it actually covers.
• Match the index to both the WHERE clause and ORDER BY clause; a predicate match alone may still leave a sort.
• Use pg_stat_user_indexes for workload-level usage, but use EXPLAIN for the one query you are debugging right now.

Once the plan tells you what is missing, the repair follows the shape of the query. If the filter is selective and the ordering matters, a multi-column index that matches the predicate and sort order can remove both the scan cost and the extra sort. The goal is not simply to add columns to an index; it is to make the access path match the query's decision points. The leading columns should support the most selective or most stable filters, and the remaining columns should help the planner return rows in the order the caller already expects.

If the plan already uses an index but still performs heap fetches, the more useful repair may be to make the query index-friendly rather than to add a second index at random. An index-only scan can only avoid table visits when the index contains everything the query needs and the visibility conditions allow it. PostgreSQL's documentation shows Heap Fetches precisely because this is a useful boundary: a node can be index-shaped without yet being truly covering. In that case, the diagnosis is not “no index exists”; it is “the current index does not fully satisfy the read path.”

The other repair is invisible in schema DDL: refresh statistics. PostgreSQL explicitly says inaccurate selectivity estimates can come from insufficient statistics, and that adjusting statistics-gathering may help. That matters when the plan is wrong even though the right index exists. A new index will not fix a planner that misjudged row counts because the table changed shape since the last ANALYZE. So the workflow keeps the fixes in this order: check the plan, check the existing indexes, check statistics, then change the schema only when the evidence says the current access path is genuinely unsupported.

• A multi-column index helps only when its column order matches how the query narrows and sorts rows.
• Index-only scans still need the table heap unless the visibility map and column coverage make a heap visit unnecessary.
• If the planner's estimates are wrong, fixing statistics can be more effective than adding a new index.

The supplied helper in this article turns the workflow into two small decisions. The first function, `analyzeExplainAnalyze`, expects parsed `EXPLAIN (ANALYZE, FORMAT JSON)` output. It walks the plan tree, flags sequential scans, index-related scans, heap fetches, and row-estimate mismatches, and then emits suggestions such as running ANALYZE or considering a covering index. The second function, `suggestIndexFromQuery`, performs a deliberately simple comparison between the query's predicate and ordering columns and an existing index's column list. That is enough to demonstrate the reasoning pattern without pretending to be a full optimizer.

The helper is intentionally narrow. It does not parse raw text-format EXPLAIN output. It does not derive exact DDL from arbitrary SQL. It does not know about collation, expression indexes, partial indexes, operator classes, or the many cases in which PostgreSQL can choose a bitmap path or a skip-scan pattern for valid reasons. Its value is diagnostic: it helps you notice when a plan contains the clues that a missing or mismatched index is likely part of the slowdown. The actual index design still belongs to the application's query semantics and data distribution.

That limitation is not a weakness of the article; it is the honest boundary of the evidence. PostgreSQL's own documentation says plan reading is an art, that estimates are approximate, and that real data plus ANALYZE are essential. So the helper is best used as a checklist in code form, not as an oracle. Its output is useful when it points you toward sequential scans, bad estimates, or a non-covering index. It is also useful when it tells you not to rush into an index change because the current plan already uses an appropriate path or because the statistics are the real problem.

• EXPLAIN ANALYZE changes reality by running the statement, so use a rollback when you need to inspect a data-modifying query safely.
• The reported execution time excludes client display time, so wall-clock behavior in an application can still be slower than the server-side plan suggests.
• The article's helper only analyzes JSON plan output; it does not prove a live query or the behavior of a specific production workload.

The final decision is not whether an index exists; it is whether the new access path is better enough to justify the maintenance cost. PostgreSQL makes that tradeoff real, because every index changes write behavior and storage use. A new index can remove a sort, shorten a filter path, or enable index-only access, but it can also slow inserts and updates while adding little read benefit if the predicate is not selective enough. That is why the article starts with measurement. The plan tells you whether the query currently performs a sequential scan, a nested-loop hotspot, an unnecessary sort, or a heap-fetch-heavy index path. The second measurement tells you whether the proposed repair actually changed that shape.

The right before-and-after comparison is operational, not theoretical. Run the same query with realistic parameters, confirm the original plan, apply only one repair, then run EXPLAIN ANALYZE again and compare wall time, row counts, and node types. If the repair was sound, you should see an index scan or index-only scan where the previous plan used a sequential scan or a slower partial path, and you should see the expensive node either disappear or shrink enough to matter. If the numbers do not improve, the evidence says the repair was the wrong one, not that PostgreSQL failed to use it.

That disciplined approach keeps the caveats visible. Statistics can be stale, the table can be too small for the index to help, and the query can be shaped in a way that prevents index-only access even when the index exists. Those limits are not edge cases to ignore; they are part of the diagnostic method. A missing index is only one possible cause of slowness, and EXPLAIN ANALYZE is most useful when it helps you rule causes in or out before you commit to schema change.

• A fast query should still be measured before and after a change so you can tell whether the index actually helped.
• A slow query with the wrong plan shape may improve from a new multi-column index, updated statistics, or a query rewrite that makes index-only access possible.
• If a plan is already correct but the table is tiny or the selectivity is low, a new index can add write cost without helping reads.