About

A execution plan is an ordered set of steps to execute a query

During SQL processing, the row source generator receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan, that is usable by the rest of the database.

The iterative plan is a binary program that, when executed by the SQL virtual machine, produces the result set.

Also known as SQL Engine - How to read a physical plan (Execution Plan | Execution Tree)

Articles Related

Logical Query Plan Example

Read data from storage, and then do join on some join keys, and finally aggregate with some aggregation keys

SQL

SELECT
  item.brand,
  sum(price)
FROM
  sales,
  item
WHERE
  sales.item_key =
  item.item_key
GROUP BY
  item.brand

Logical Query Plan

Distributed Query Plan (Master Worker)

Relational algebra

Every query is represented as a tree of relational operators. You can translate from SQL to relational algebra, or you can build the tree directly.

Planner rules transform expression trees using mathematical identities that preserve semantics. For example, it is valid to push a filter into an input of an inner join if the filter does not reference columns from the other input.

For instance, you can optimizes queries by repeatedly applying planner rules to a relational expression. A cost model guides the process, and the planner engine generates an alternative expression that has the same semantics as the original but a lower cost.

A query with an Aggregate, and a Filter:

final RelNode node = builder
  .scan("EMP")
  .aggregate(builder.groupKey("DEPTNO"),
      builder.count(false, "C"),
      builder.sum(false, "S", builder.field("SAL")))
  .filter(
      builder.call(SqlStdOperatorTable.GREATER_THAN,
          builder.field("C"),
          builder.literal(10)))
  .build();
System.out.println(RelOptUtil.toString(node));

is equivalent to SQL

SELECT deptno, count(*) AS c, sum(sal) AS s
FROM emp
GROUP BY deptno
HAVING count(*) > 10

and produces the following query plan

LogicalFilter(condition=[>($1, 10)])
  LogicalAggregate(group=[{7}], C=[COUNT()], S=[SUM($5)])
    LogicalTableScan(table=[[scott, EMP]])

Documentation / Reference

• Calcite Algebra