The part that I have always considered confusing is the startup costs and the total cost. I google it every time I forget about it, which brings me back here, which does not explain the difference, so I am writing this answer. This is what I learned from the Postgres EXPLAIN documentation , explained as I understand it.
Here is an example from the application that manages the forum:
EXPLAIN SELECT * FROM post LIMIT 50; Limit (cost=0.00..3.39 rows=50 width=422) -> Seq Scan on post (cost=0.00..15629.12 rows=230412 width=422)
Here's a graphical explanation from PgAdmin:
(When you use PgAdmin, you can hover over the component to read the cost data.)
Cost is presented as a tuple, for example. the cost of LIMIT is cost=0.00..3.39 , and the cost of sequential post scanning is cost=0.00..15629.12 . The first number in the tuple is the launch cost, and the second is the total cost. Since I used EXPLAIN and not EXPLAIN ANALYZE , these costs are estimates, not actual measures.
- Launch cost is a complex concept. It does not just represent the amount of time before starting this component. It represents the amount of time between when the component starts execution (reading in data) and when the component displays its first line .
- The total cost is the total runtime of the component, starting from the moment of reading data until the completion of writing its output.
As a complication, each "parent" cost of a node includes the cost of its child nodes. In a textual representation, the tree is indented, for example. LIMIT is the parent node and Seq Scan is its child. In the PgAdmin view, arrows indicate from child to parent — the direction of the data stream — which may be inaccurate if you are familiar with graph theory.
The documentation says that the costs include all the child nodes, but note that the total cost of the parent 3.39 much less than the total cost of its child 15629.12 . The total cost is not included because a component, such as LIMIT , does not need to process all its input. See EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; in the Postgres EXPLAIN documentation .
In the above example, the start time for both components is zero, since none of the components should do any processing before it starts writing rows: sequential scanning reads the first row of the table and emits it. LIMIT reads its first row and then emits it.
When does a component have to do a lot of processing before it can begin to print any lines? There are many possible reasons, but let's look at one striking example. Here's the same query from earlier, but now containing an ORDER BY :
EXPLAIN SELECT * FROM post ORDER BY body LIMIT 50; Limit (cost=23283.24..23283.37 rows=50 width=422) -> Sort (cost=23283.24..23859.27 rows=230412 width=422) Sort Key: body -> Seq Scan on post (cost=0.00..15629.12 rows=230412 width=422)
And graphically:
Again, sequential scanning on post does not have a launch cost: it immediately prints lines. But sorting has a significant launch cost of 23283.24 , because it must sort the entire table before it even displays at least one row. The total cost of sorting 23859.27 slightly higher than the cost of starting, which reflects the fact that once the entire data set has been sorted, the sorted data can be emitted very quickly.
Please note that the start time of LIMIT 23283.24 exactly matches the start time of the sort. This is due not only to the fact that LIMIT has a long start time. In fact, it actually has a zero start time, but EXPLAIN collapses all child costs for each parent, so the LIMIT start time includes the start time of its children.
This set of costs can make it difficult to understand the cost of each individual component. For example, our LIMIT has zero start time, but this is not obvious at first glance. For this reason, several other people are associated with explain.depesz.com , a tool created by Hubert Lubaczewski (aka depesz) that helps to understand EXPLAIN by - among other things - subtract the cost of a child from material costs. He mentions some other difficulties in a short blog post about his tool.