August 29, 2025
Summary: In this tutorial, you will learn various basic fields that you might see in explain plans.
Table of Contents
EXPLAIN Glossary
PostgreSQL does an amazing job populating query plans with useful information, but reading and understanding them can be tricky.
Here is a simple example:
explain select * from t order by c;
QUERY PLAN
------------------------------------------------------------
Sort (cost=813.32..837.48 rows=9664 width=32)
Sort Key: c
-> Seq Scan on t (cost=0.00..173.64 rows=9664 width=32)
This tells us that Postgres would do a Seq Scan of our table “t”, then a Sort by column “c”. The numbers tell us the Startup Cost, the Total Cost, the Plan Rows, and the Plan Width at each stage.
We will show you a glossary of the most common fields you’ll see on the operations in a query plan, and a detailed description of what each one means. This glossary is broken down into sections, based on which flag to EXPLAIN causes the field to be shown:
- Query Structure Fields – always present.
- Estimate Fields – present when the COSTS flag is set.
- Actual Value Fields – present when the ANALYZE flag is set.
Query Structure Fields
These fields represent what the plan will actually do: how the database will process the data and return the results for your query. When applicable, they’ll be present whatever flags you use to generate the query plan.
Node Type
The operation the node is performing. On what different operation types actually do, please refer to PostgreSQL Optimization tutorials on different operations and the annotations to the code itself — both in the code for node execution and the planner nodes.
Plans
The child operations executed to provide input to this operation.
Parent Relationship
A guide as to why this operation needs to be run in order to facilitate the parent operation. There are six different possibilities:
- Outer is the value you’ll see most often. It means “take in the rows from this operation as input, process them and pass them on”.
- Inner is only ever seen on the second child of join operations, and is always seen there. This is the “inner” part of the loop. ie, for each outer row, we look up its match using this operation
- Member is used for all children of append and modifyTable nodes, and on bitmap processing nodes like BitmapAnd and BitmapOr operations.
- InitPlan: Used for calculations performed before the query can start, eg a constant referred to in the query or the result of a CTE scan.
- Subquery: The child is a subquery of the parent operation. Since Postgres always uses subquery scans to feed subquery data to parent queries, only ever appears on the children of subquery scans
- SubPlan: Like a Subquery, represents a new query, but used when a subquery scan is not necessary.
Filter
When present, this is a filter used to remove rows.
The important thing to note is that this is a filter in the traditional sense: these rows are read in (either from a data source or another operation in the plan), inspected, and then approved or removed based on the filter.
Although similar in purpose to the “Index Cond” that you see on Index Scans or Index Only Scans, the implementation is completely different. In an “Index Cond”, the index is used to select rows based on their indexed values, without examining the rows themselves at all. In fact, in some cases, you might see an “Index Cond” and a “Filter” on the same operation. You can read more about the difference between Index Conditions and Filters in the tutorial about index efficiency (focusing on multi-column indexes).
Parallel Aware
Whether or not the operation will be run in a special mode to support parallelism. Some operations need to be aware that they are running in parallel, for example sequential scans need to know they only need to scan a smaller proportion of the table. Other operations can be run on several workers without each one having any knowledge of the others.
Relation Name
The data source being read/written from. Almost always a table name (including when the data is accessed via an index), but can also be a materialised view or foreign data source.
Alias
The alias used to refer to the Relation Name object.
Estimate Fields
These fields are added to nodes whenever the COSTS flag is set. It is on by default, but you can turn it off.
Total Cost
The estimated total cost of this operation and its descendants. The Postgres query planner often has several different ways it could resolve the same query. It calculates a cost — which is hopefully correlated with the amount of time taken — for each potential plan, and then picks the one with the smallest cost. It’s worth bearing in mind that the costs are unit-free — they’re not designed to convert into time, or disk reads. They’re just supposed to be bigger for slower operations, and smaller for faster ones. You can get a taste of what sort of things they consider by looking at the cost calculation code.
Startup Cost
The estimated amount of overhead necessary to start the operation. Note that, unlike “Actual Startup Time”, this is a fixed value, which won’t change for different numbers of rows.
Plan Rows
The number of rows the planner expects to be returned by the operation. This is a per-loop average, like “Actual Rows”.
Plan Width
The estimated average size of each row returned by the operation, in bytes.
Actual Value Fields
When you run EXPLAIN with the ANALYZE flag set, the query is actually executed — allowing real performance data to be gathered.
Actual Loops
The number of times the operation is executed. For a lot of operations it will have a value of one, but when it is not, there are three different cases:
- Some operations can be executed more than once. For example, “Nested Loops” run their “Inner” child once for every row returned by their “Outer” child.
- When an operation that would normally only consist of one loop is split across multiple workers, each partial operation is counted as a Loop.
- The number of loops can be zero when an operation doesn’t need to be executed at all. For example if a table read is planned to provide candidates for an inner join, but there turns out to be no rows on the other side of the join, the operation can be effectively eliminated.
Actual Total Time
The actual amount of time in milliseconds spent on this operation and all of its children. It’s a per-loop average, rounded to the nearest thousandth of a millisecond.
This can cause some odd occurrences, particularly for “Materialize” nodes. “Materialize”operations persist the data they receive in memory, to allow for multiple accesses, counting each access as a loop. They often get that data from a data read operation, which is only executed once, like in this example:
{
"Node Type": "Materialize",
"Actual Loops": 9902,
"Actual Total Time": 0.000,
"Plans": [{
"Node Type": "Seq Scan",
"Actual Loops": 1,
"Actual Total Time": 0.035
}]
}
As you can see, the total time spent across all loops on the “Materialize” node and its child “Seq Scan” is less than 9902 × 0.0005 = 4.951ms, so the per-loop “Actual Total Time” value is less than 0.0005, and is rounded to zero.
So we’re in an odd situation where the “Materialize” node and its children seem to take 0ms in total, while it has a child that takes 0.035ms to execute, implying that the “Materialize” operation on its own somehow takes negative time!
Because the rounding is to such a high degree of accuracy, these issues usually occur only with very fast operations, rather than the slow ones that are often your focus. Still, in the future, I’d love to see query plans also include a value stating how much the “total total” (not the per-loop total), to avoid any of these annoying niggles with rounding, which can be noticeable when the number of loops is high.
Actual Startup Time
When we started out, I thought that this was a constant for any operation, so if you halved the number of rows on a linear operation, then this would remain the same, while the “Actual Total Time” would decrease by half the difference between it and the “Actual Startup Time”.
In reality, this is the amount of time it takes to get the first row out of the operation. Sometimes, this is very close to the setup time — for example on a sequential scan which returns all the rows in a table.
Other times, though, it’s more or less the total time. For example, consider a sort of 10,000 rows. In order to return the first row, you have to sort all 10,000 rows to work out which one comes first, so the startup time will be almost equal to the total time, and will vary dramatically based on how many rows you have.
Actual Rows
The number of rows returned by the operation per loop.
The important thing to notice about this is that it’s an average of all the loops executed, rounded to the nearest integer. This means that while “Actual Loops” × “Actual Rows” is a pretty good approximation for total number of rows returned most of the time, it can be off by as much as half of the number of loops. Usually it’s just slightly confusing as you see a row or two appear or disappear between operations, but on operations with a large number of loops there is potential for serious miscalculation.
Rows Removed by Filter
The rows removed by the “Filter”, as described above. Like most of the other “Actual” values, this is a per-loop average, with all the confusion and loss of accuracy that entails.