Find the Lowest Line Where the Two Numbers Stop Agreeing

Last timeChoosing the Order

A plan with estimated and actual counts side by side tells you exactly where the reasoning broke. There is one place to look and everything above it is a symptom.

Four lessons of theory have a practical payoff, which is that the database will

show you its reasoning. This lesson is about reading it.

What a plan actually is

Ask for a plan and you get a tree printed with indentation. It is not a list of

steps in order. The deepest, most indented lines run first and hand their rows

to the line above, which hands its rows upward in turn, until the outermost line

produces the answer.

FIG 1One plan, with both numbers on every line

plaintext

Nested Loop  (est rows=120  actual rows=94850  loops=1  time=8420ms)
  -> Hash Join  (est rows=120  actual rows=94850  loops=1  time=610ms)
       -> Seq Scan on orders  (est rows=9400  actual rows=9380  loops=1  time=40ms)
       -> Hash  (est rows=31  actual rows=24300  loops=1  time=180ms)
            -> Seq Scan on accounts  (est rows=31  actual rows=24300  loops=1  time=175ms)
                 Filter: region and plan both match
  -> Index Scan on line_items  (est rows=1  actual rows=1  loops=94850  time=7800ms)

Read this from the inside out. The scan of accounts runs first, then the scan of orders, then the hash join combines them, and only then does the nested loop begin, once per row it received. The two row counts sit side by side on every line, which is the whole value of asking for the plan with the counts rather than without them. Everything in this lesson is about reading the three columns on the right in the correct order.

The shape is worth naming before the numbers. There is a hash join, whose two

children are the scan of orders and a hash built over the scan of accounts.

Above it is a nested loop whose other child is an index read of line items. That

is the plan: three reads, a hash join and a nested loop.

The lesson stops here

5 more paragraphs to go

You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.

The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.

See the plan The contents

This is the reading half

Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.

The contents

Find the Lowest Line Where the Two Numbers Stop Agreeing

What a plan actually is

5 more paragraphs to go

This is the reading half

Read alongside