Does each member of execution plan run in its entirety before the next execution starts?

Question

I am looking at an execution plan and reading it from right to left.

Example:

1<-2<-3<-4
      ^
      |_5

So 4 and 5 execute in parallel. When both are fully done, then 3 executes in its entirely, then 2 run fully, then 1.

Is this correct or can there be case where:

1. 3 starts execution even before 4 and 5 fully complete.

2. 2 starts execution even before 3 fully completes.

Answer 1

You are misunderstanding what these arrows mean. They do not represent the sequence of execution, but the flow of data.

Each operator does not execute in its entirety, then pass all rows to the next operator, unless it is a blocking operator. Nor do they necessarily mean that they execute in parallel.

Instead, each operator passes one row at a time to the next operator. The joining operator 3 will wait for a row from 4 before calling 5 for its first row. This is called an iterator.

So if 4 is a blocking operator (for example a sort) then it will generate all rows internally, before passing them one by one to 3, which in turn will request rows from 5. If 5 is also blocking then it will have to wait.

But most operators are not blocking, and therefore normally each row passes through one at a time. A row is fetched from 4 by the join at 3, then a row is fetched from 5 and joined, more rows are fetched from 5 if relevant, then the process begins again with another row from 4.

---

Operators 4 and 5 will only execute in parallel if this is a parallel plan.

In a parallel plan, the data is partitioned, and each thread works on part of the data, in a similar fashion to a serial plan.