EXPLAIN

EXPLAIN displays the plan used for a SELECT statement, a view, or a materialized view.

Conceptual framework

To execute SELECT statements, Materialize generates a plan consisting of operators that interface with our underlying Differential dataflow engine. EXPLAIN lets you see the plan for a given query, which can provide insight into Materialize’s behavior for specific queries, e.g. performance.

Syntax

Explained object

The following three objects can be explained.

Output format

You can select between JSON and TEXT for the output format of EXPLAIN. Non-text output is more machine-readable and can be parsed by common graph visualization libraries, while formatted text is more human-readable.

Explained stage

This stage determines the query optimization stage at which the plan snapshot will be taken.

Output modifiers

Output modifiers act as boolean toggles and can be combined in order to slightly tweak the information and rendering style of the generated explanation output.

Query compilation pipeline

The job of the Materialize planner is to turn SQL code into a differential dataflow program. We get there via a series of progressively lower-level plans:

SQL ⇒ raw plan ⇒ decorrelated plan ⇒ optimized plan ⇒ physical plan ⇒ dataflow

From SQL to raw plan

In this stage, the planner:

• Replaces SQL variable names with column numbers.
• Infers the type of each expression.
• Choose the correct overload for each function.

From raw plan to decorrelated plan

In this stage, the planner:

• Replaces subqueries and lateral joins with non-nested operations.
• Replaces OUTER joins with lower-level operations.
• Replaces aggregate default values with lower-level operations.

From decorrelated plan to optimized plan

In this stage, the planner performs various optimizing rewrites:

• Coalesces joins.
• Chooses join order and implementation.
• Fuses adjacent operations.
• Removes redundant operations.
• Evaluates any operations on constants.

From optimized plan to physical plan

In this stage, the planner:

• Maps plan operators to differential dataflow operators.
• Locates existing arrangements which can be reused.

From physical plan to dataflow

In the final stage, the planner:

• Renders an actual dataflow from the physical plan, and
• Installs the new dataflow into the running system.

No smart logic runs as part of the rendering step, as the physical plan is meant to be a definitive and complete description of the rendered dataflow.

Reading decorrelated/optimized plans

Materialize plans are directed acyclic graphs of operators. Each operator in the graph receives inputs from zero or more other operators and produces a single output. Sub-graphs where each output is consumed only once are rendered as tree-shaped fragments. Sub-graphs consumed more than once are represented as common table expressions (CTEs). In the example below, the CTE l0 represents a linear sub-plan (a chain of Get, Filter, and Project operators) which is used in both inputs of a self-join.

Return
  Join on=(#1 = #2)
    Get l0
    Get l0
With
  cte l0 =
    Project (#0, #1)
      Filter (#0 > #2)
        Get materialize.public.t

Many operators need to refer to columns in their input. These are displayed like #3 for column number 3. (Columns are numbered starting from column 0). To get a better sense of columns assigned to Map operators, it might be useful to request the arity output modifier.

Each operator can also be annotated with additional metadata. Details are shown by default in the EXPLAIN PHYSICAL PLAN output, but are hidden elsewhere. In EXPLAIN OPTIMIZED PLAN, details about the implementation in the Join operator can be requested with the join_impls output modifier:

Join on=(#1 = #2 AND #3 = #4) type=delta
  implementation
    %0:t » %1:u[#0]KA » %2:v[#0]KA
    %1:u » %0:t[#1]KA » %2:v[#0]KA
    %2:v » %1:u[#1]KA » %0:t[#1]KA
  ArrangeBy keys=[[#1]]
    Get materialize.public.t
  ArrangeBy keys=[[#0], [#1]]
    Get materialize.public.u
  ArrangeBy keys=[[#0]]
    Get materialize.public.v

The %0, %1, etc. refer to each of the join inputs. A differential join shows one join path, which is simply a sequence of binary joins (each of whose results need to be maintained as state). A delta join shows a join path for each of the inputs. The expressions in a bracket show the key for joining with that input. The letters after the brackets indicate the input characteristics used for join ordering. U means unique, the number of Ks means the key length, A means already arranged (e.g., an index exists). The small letters refer to filter characteristics: equality to a literal, like, is null, inequality to a literal, any filter.

A plan can optionally end with a finishing action which can sort, limit and project the result data. This operator is special, as it can only occur at the top of the plan. Finishing actions are executed outside the parallel dataflow that implements the rest of the plan.

Finish order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 output=[#0, #1]
  CrossJoin
    Get materialize.public.r
    Get materialize.public.s

Finally, simple queries are sometimes implemented using a so-called fast path. In this mode, the program that implements the query will just hit an existing index, transform the results, and optionally apply a finishing action. For fast path queries, all of these actions happen outside of the regular dataflow engine. The fast path is indicated by an “Explained Query (fast path):” heading before the explained query in the EXPLAIN OPTIMIZED PLAN and EXPLAIN PHYSICAL PLAN result.

Explained Query (fast path):
  Finish order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 output=[#0, #1]
    ReadExistingIndex materialize.public.t_a_idx

Operators in decorrelated and optimized plans

Operators in raw plans

Timestamps

EXPLAIN TIMESTAMP displays the timestamps used for a SELECT statement, view, or materialized view – valuable information to acknowledge query delays.

The explanation divides in two parts:

1. Determinations for a timestamp
2. Sources frontiers

Determinations for a timestamp

Queries in Materialize have a logical timestamp, known as query timestamp. It plays a critical role to return a correct result. Returning a correct result implies retrieving data with the same logical time from each source present in a query.

In this case, sources are objects providing data: materialized views, views, indexes, tables, or sources. Each will have a pair of logical timestamps frontiers, denoted as sources frontiers.

Sources frontiers

Every source has a beginning read frontier and an ending write frontier. They stand for a source’s limits to return a correct result immediately:

• Read frontier: Indicates the minimum logical timestamp to return a correct result (known as compaction)
• Write frontier: Indicates the maximum timestamp to build a correct result without waiting unprocessed data.

Having a query timestamp outside the values of the frontiers can explain the presence of delays. While in the middle, the space of processed but not yet compacted data, allows building and returning a correct result immediately.

Example

EXPLAIN TIMESTAMP FOR MATERIALIZED VIEW users;

                                 Timestamp
---------------------------------------------------------------------------
                 query timestamp: 1673618185152 (2023-01-13 13:56:25.152) +
           oracle read timestamp: 1673618185152 (2023-01-13 13:56:25.152) +
 largest not in advance of upper: 1673618185152 (2023-01-13 13:56:25.152) +
                           upper:[1673618185153 (2023-01-13 13:56:25.153)]+
                           since:[1673618184000 (2023-01-13 13:56:24.000)]+
         can respond immediately: true                                    +
                        timeline: Some(EpochMilliseconds)                 +
                                                                          +
 source materialize.public.raw_users (u2014, storage):                    +
                   read frontier:[1673618184000 (2023-01-13 13:56:24.000)]+
                  write frontier:[1673618185153 (2023-01-13 13:56:25.153)]+

Definitions

Timeline values

Sources frontiers