An execution plan is not a conclusion about the cause of a slow query; it is a description of the decision the optimizer made. That is why Seq Scan, Nested Loop, a high cost, or an expensive sort prove nothing on their own.

Reliable diagnosis works differently:

  1. Get the plan and runtime metrics for the exact problematic execution.
  2. Find the part of the tree where the actual volume of work increased sharply.
  3. Check whether, before that point, there is a significant gap between the estimated and the actual number of rows.
  4. If such a gap exists, check whether it could have changed the join order, the join algorithm, or the data access method.
  5. If there is no gap or it does not explain the delay, check the volume of I/O, CPU, temporary files, waits, and work outside the plan.

The main question when reading a plan is not "which operator looks bad?" but "why did the DBMS decide to do exactly this much work in exactly this way?".

What an execution plan shows

An execution plan is a tree of operators, or nodes: individual actions such as reading a table, filtering, joining, sorting, and aggregating. Leaf nodes fetch rows from tables and indexes, intermediate nodes transform or join them, and the root node produces the result. The visual direction of the tree differs between tools, but the data flows logically from the leaves to the root. SQL Server also describes a plan as a sequence of physical and logical operators selected to execute the query (Execution Plan Overview).

Four terms are needed to read a plan:

  • Cardinality - the number of rows at a given stage of the plan.
  • Selectivity - the fraction of rows that pass a condition. The smaller the fraction, the more selective the predicate.
  • Optimizer estimate - a forecast of the row count and relative cost of an operation, made before execution.
  • Actual plan - a plan enriched with runtime data from a specific run: actual rows, loops, time, and, depending on the DBMS, resource metrics.

A plan depends on more than the SQL text. It is affected by parameters, schema, indexes, statistics, settings, optimizer version, and the state of the environment at compile time. The plan from one run does not prove that the same plan was used at a different parameter value or at the moment of the incident.

Oracle EXPLAIN PLAN in particular calls for caution: Oracle explicitly warns that such a plan may not match the plan the cursor actually uses, for example because of differences in the environment and bind variables (SQL Tuning Guide).

Estimates and measurements must not be mixed

An ordinary EXPLAIN, estimated plan, or EXPLAIN PLAN answers the question: "How does the optimizer expect to execute the query?". It does not execute the query and contains no reliable actual time or row count.

The actual plan answers a different question: "What happened in this run?". Even that does not establish the cause automatically, but it provides the necessary measurements.

Metric What it means What it does not prove
Estimated rows How many rows the optimizer expected How many rows were actually read or returned
Actual rows How many rows the operator emitted in a particular plan format How many rows it examined, if dropped rows are shown separately
Loops / executions How many times the operator ran That the repetitions were individually expensive
Estimated cost The optimizer's internal comparative estimate Milliseconds, CPU time, or physical I/O
Actual time The measured time of an operator or iterator in the semantics of the specific DBMS That this is the node's own time, that it can be compared directly with CPU time, or that it excludes the work of child operators
Buffers / reads The volume of work with buffers or storage The duration of I/O and the presence of a slow disk
Temp read/write, spill Intermediate data going out to temporary storage That the spill alone determined the entire delay

In PostgreSQL cost is expressed in conditional units of the planner's model, and rows denotes the expected number of rows emitted by a node, not necessarily the number of rows examined (Using EXPLAIN). So the phrase "this operator accounts for 80% of the cost" only means that the model assigned it that share of the estimated cost. It is not a time measurement.

There is one more trap: the time of a parent node often includes part or all of the work of its child nodes, and the format may show an average per pass or a value per worker. Therefore the largest actual time does not make the tree root the bottleneck, and subtracting the times of adjacent nodes is unreliable without knowing the format's semantics. For causal analysis, match the operator hierarchy against the number of passes, the volume of work, and the metrics of one run; with a parallel plan, clarify separately whether the values are a sum, an average, or a figure for an individual worker.

Collecting the actual plan safely

In PostgreSQL, EXPLAIN ANALYZE really executes the analyzed query; the documentation separately warns about the consequences for data-modifying statements (EXPLAIN). MySQL 8.4 EXPLAIN ANALYZE also executes the query and returns iterator measurements, but is supported only for SELECT, TABLE, and multi-table UPDATE/DELETE, not for arbitrary DML, including INSERT and single-table UPDATE/DELETE (MySQL 8.4 EXPLAIN Statement). In SQL Server and Oracle the actual plan and runtime statistics are obtained by other means, so the execution risk and the collection conditions must be checked for the specific tool and mode rather than inferred from the word ANALYZE.

Therefore in PostgreSQL you must not run an instrumented INSERT, UPDATE, DELETE, or code with side effects in production without checking first. The same rule applies to the facilities of other DBMSs in those modes and for those statement types where the instrumentation really executes the command. A transaction followed by a rollback sometimes protects table changes, but is not universal isolation from all side effects. The safe approach depends on the DBMS, version, statement type, and how the application is written.

The instrumentation itself also adds overhead. The actual plan should be treated as a measurement of a specific run, not as an exact model of the median or peak latency of the service.

A practical order of investigation

1. Record the query, parameters, and context

First make sure that the investigation concerns the same SQL, the same parameter values and, where possible, the same plan that were used in the slow execution. For parameterized queries this is essential: the data distribution can make different plans appropriate for different values.

In SQL Server this behavior is addressed, in particular, by the Parameter Sensitive Plan optimization mechanism, which allows several plan variants for different parameter ranges (Parameter Sensitive Plan Optimization). But the general diagnostic principle applies to other DBMSs too: one fast run with a convenient parameter does not refute the problem.

It is also worth separating planning time from execution time. If a significant delay arises during compilation, recompilation, or hard parse, changing the access operator will not remove it. PostgreSQL, for example, outputs Planning Time and Execution Time separately.

2. Find the volume of actual work

Read the plan from the row sources to the root, noting:

  • how many rows each operator received and emitted;
  • how many rows the filter removed;
  • how many times the node ran;
  • where sorting, hashing, materialization, or temporary data appeared;
  • which subtrees read especially many pages or rows;
  • where actual time increased noticeably.

The rows field does not have exactly the same semantics in every format. You must distinguish the rows emitted by an operator, the rows read from the source, and the rows dropped by a filter. For example, an index operator may return one row after examining significantly more records or pages.

A separate case is LIMIT and other early-stop conditions. If the top operator already has enough rows, a child node may not go through all the rows that the optimizer estimated as potentially available. Therefore smaller actual rows with LIMIT is not by itself evidence of a cardinality error: first establish whether the work was stopped by the result consumer.

3. Always account for repetitions

The inner side of Nested Loop is underestimated especially often. A fast index lookup becomes expensive if it is run hundreds of thousands of times.

For PostgreSQL and other formats where actual rows is shown as an average per pass, the approximate total number of emitted rows is computed as:

\[ \text{total rows} \approx \text{actual rows} \times \text{loops} \]

Suppose that in such a format an inner lookup returns 5 rows on average and has loops = 100000. This is not "a five-row operator" but roughly 500 000 rows of result across all passes. Time must be interpreted the same way if the specific format reports it per iteration. PostgreSQL averages the time and row values for a repeatedly executed node so that the total after multiplying by loops agrees with the overall execution (Using EXPLAIN). In a parallel plan you must first establish whether the value refers to an individual worker, an average, or an aggregated node; multiplying by loops is allowed only when the semantics of the specific representation have been confirmed.

For SQL Server and MySQL the semantics of Actual Rows, Actual Number of Executions, and iterator metrics in a given format must be checked separately. The formula cannot be transferred automatically between DBMSs and plan representations.

4. Compare estimated rows with actual rows, if that explains the path to the delay

A strong diagnostic signal is a persistent gap between estimate and fact before a sharp growth in work. It is not necessarily the slowest node. If such a gap exists, it is useful to find the first place along the data flow from leaf nodes to the root where it could have changed the subsequent decisions.

But this is not a necessary cause of a slow query. The estimates may be reasonably accurate while the delay is still produced by a large but expected volume of data, insufficient memory, spill, blocking, I/O, network, or contention for resources. If there is no noticeable gap, do not artificially hunt for a "cardinality error": the next step is to check which measured resource or external factor really explains the time.

There is no universal threshold such as "an error of ten times is always critical". An estimate of 1 row instead of 20 may not matter. An estimate of 10 000 instead of 200 000 sometimes does not change the plan either. What matters together is:

  • the absolute number of rows;
  • the position of the node in the tree;
  • the number of repetitions;
  • the chosen join order;
  • the sensitivity of the algorithm to the input size.

Cardinality estimation models rely on statistical assumptions, including uniformity, independence, and correlation of data; these assumptions do not always match the real distribution (Cardinality Estimation).

5. Check resources and only then state the cause

After localizing the extra work, determine its cost:

  • CPU - predicate evaluation, aggregation, hashing, sorting;
  • logical reads - accesses to pages in the buffer cache;
  • physical reads - fetching data from storage;
  • temporary reads and writes - spill of sort or hash operations;
  • memory and parallelism - taking into account the tools of the specific DBMS.

A large number of buffer hits means a large volume of work with the buffer cache, not a slow disk. Conversely, a relatively small volume of physical reads can be expensive under high storage latency. A block counter must be matched against time and system metrics.

Example: when Nested Loop is a consequence rather than a ready-made diagnosis

The plan rows alone are not enough to name the join as the cause. By way of illustration, here is a conditional profile of one and the same execution: it includes time, repetitions, and several work-volume metrics.

Nested Loop  (actual time: 0.15..4200 ms, actual rows: 500000, loops: 1)
  buffers: shared hit=120000 read=4800

  -> Filtered Scan on orders
       estimated rows: 100
       actual rows: 100000
       actual time: 0.05..110 ms
       loops: 1

  -> Index Lookup on order_items
       estimated rows: 1
       actual rows: 5
       actual time: 0.01..0.03 ms
       loops: 100000

These are illustrative numbers, not a fragment of a plan from a specific DBMS. They assume a format in which actual rows and the time of a repeated node are given as an average per iteration, as in PostgreSQL. In such a format roughly 0,03 ms over 100 000 passes accounts for about three seconds of total inner lookup work; in another format this arithmetic must not be applied. In a parallel plan it would additionally be necessary to establish how the worker metrics are aggregated.

The superficial conclusion: "Nested Loop is running slowly, so it should be replaced with another join".

But the tree shows a more meaningful chain:

  1. The optimizer expected 100 rows from orders.
  2. In fact the operator emitted 100 000 rows.
  3. The inner index lookup therefore ran 100 000 times.
  4. In total it returned about 500 000 rows.
  5. Nested Loop is the place where the work accumulates; underestimation of the outer input is a plausible hypothesis about why the chosen path turned out expensive.

Such a profile supports the hypothesis "a cardinality estimation error could have led to an unsuitable join decision", but it does not prove it, nor does it prove that another join algorithm would be faster. To move from hypothesis to causal conclusion, you must compare the same query and parameters with an alternative obtained in a controlled way, and check whether I/O, blocking, cache state, or an external component explains the delay. Only after that does it make sense to consider changing statistics, an index, or the query.

So the conclusion has three levels:

  • observed: the outer operator emitted far more rows, and the inner lookup was repeated many times;
  • supported as a hypothesis: underestimation of the outer input could have influenced the choice of join;
  • requires separate verification: the source of the estimation error and the benefit of a specific change.

It is exactly this distinction that keeps the analysis from prematurely "fixing" a join hint or an index.

Typical symptoms and testable hypotheses

Visible symptom Possible hypothesis What to check
Seq Scan or Full Table Scan Too much unnecessary data is read Relation size, actual rows, dropped rows, selectivity, pages and read time
Index Scan Many random accesses or lookups for a large result Number of executions, rows per pass, logical reads, coverage of the needed columns
High cost or a percentage on the diagram The optimizer considers the subtree expensive Actual time, CPU, I/O and differences between estimated/actual rows
Nested Loop The inner side runs too many times loops or executions, size of the outer input, its cardinality estimate
Sort or Hash with spill The intermediate set did not fit into the allocated resource Actual rows and their width, temp read/write, spill volume and its contribution to time
Large number of buffers The query processes many pages Type of reads, cache state, elapsed time, repetitions and external latency
Large sort Too large an intermediate result is being sorted Where rows could have been filtered earlier and why the optimizer expected a different volume

A full scan of a small table, or of a table from which a significant fraction of rows is needed, can be rational. Index access to a large part of a table, on the contrary, sometimes does more work because of the numerous accesses. A scan type is a decision that must be explained, not a ready-made diagnosis.

Where cardinality errors come from

Planner statistics represent the data approximately. In PostgreSQL they include, among other things, frequent values, histograms, and information about distinctness; the volume and quality of these data are limited by the statistics collection settings (Statistics Used by the Planner).

A gap between estimated and actual rows can arise for several reasons:

  • the data changed after the statistics were collected;
  • the distribution is heavily skewed;
  • the needed value is rare and insufficiently represented in the statistics;
  • two predicates are correlated while the model treats them as independent;
  • the condition contains an expression or function that is hard to estimate;
  • a particular parameter value is atypical;
  • the optimizer version, compatibility level, or compile environment changed.

In PostgreSQL, multivariate statistics can improve estimates for related columns. The documentation shows how statistics on functional dependencies and combinations of values correct some errors caused by the assumption of independence between conditions (Multivariate Statistics Examples). This is a targeted tool for a specific type of error, not a universal cure for bad plans.

Updating statistics must not be prescribed on the basis of a gap alone either. It helps if the existing data really are stale or insufficiently describe the distribution. It is not guaranteed to fix conditional correlation, a complex expression, or parameter sensitivity.

When the cause is not in the plan

An execution plan describes the work of the operators well, but not the entire path of the query. Delay can arise from:

  • waiting for a lock;
  • contention for CPU, memory, or I/O;
  • a resource limit;
  • network transfer of a large result;
  • slow reading of the result by the client;
  • background load;
  • waits inside an external or remote component.

If elapsed time is significantly greater than the time that can be explained by operator measurements and resource work in the same run, the analysis must be extended to wait statistics, locks, and system metrics. CPU time must not be mechanically subtracted from elapsed time: with waits, parallelism, and differing counter semantics these quantities do not form a simple balance. Microsoft's guidance on slow-running queries also separates plan problems, lock waits, CPU, I/O, and other system causes (Troubleshoot Slow-Running Queries).

The absence of a warning in the plan does not prove the absence of an external cause. The actual plan of a specific run also depends on cache warmth and current load. A cold run and a repeat run may use the same tree but have different timing.

How to get evidence in different DBMSs

The names and semantics of fields differ, so the interpretation of one format cannot be transferred literally to another.

DBMS Plan without execution Actual execution data Important caveat
PostgreSQL EXPLAIN EXPLAIN (ANALYZE, BUFFERS) and additional parameters ANALYZE executes the statement; values for repeated nodes must be read with loops in mind
SQL Server Estimated execution plan Actual execution plan The plan of the specific execution and actual rows/executions must be analyzed, not just estimated cost percentages (Display and save execution plans)
Oracle Database EXPLAIN PLAN The plan of the actually used cursor via DBMS_XPLAN.DISPLAY_CURSOR, usually with a format like 'ALLSTATS LAST', after prior collection of runtime statistics, for example with /*+ GATHER_PLAN_STATISTICS */ or a suitable STATISTICS_LEVEL DBMS_XPLAN does not by itself add actual rows and time to any cursor. EXPLAIN PLAN may differ from the plan actually used, and the availability of runtime fields depends on the collection method, version, and privileges (DBMS_XPLAN)
MySQL 8.x EXPLAIN EXPLAIN ANALYZE for supported statement types The output describes iterators; actual time, rows and loops must be interpreted in terms of iterator execution. In MySQL 8.4 the command supports SELECT, TABLE and multi-table UPDATE/DELETE, but not arbitrary DML (MySQL 8.4 EXPLAIN Statement)

The commands in the table indicate the direction of analysis, but they do not replace checking execution safety and the documentation of the specific version.

How to state an evidence-based conclusion

A good conclusion from a plan links the observation, the mechanism, and the limits of confidence. For example:

In this execution the filter operator returned 100 000 rows instead of the expected 100. Because of that the inner index lookup ran 100 000 times and produced about 500 000 rows. This explains the main part of the work in the subtree and points to a cardinality estimation error before the join choice. The cause of the estimation error itself has not yet been established; the data distribution, statistics, predicate correlation, and parameter sensitivity must be checked.

Such a conclusion is stronger than the claim "Nested Loop is to blame", because it can be verified by repeating the measurement. It also promises no more than the data show.

Before changing an index, a hint, the query, or the configuration, it is useful to check four points:

  • the plan actually used is analyzed with the needed parameters;
  • actual rows, repetitions, and the time hierarchy are accounted for;
  • the first significant gap before the growth in work is found;
  • the delay is matched against CPU, I/O, temporary data, and external waits.

A plan becomes a causal instrument only when the optimizer's hypotheses are compared with execution results. Without that, any noticeable operator remains a symptom, and the proposed fix remains a guess.