operators: - operator: Constant plan_types: "optimized,raw" description: | Always produces the same collection of rows. uses_memory: False memory_details: "" expansive: False example: | ```mzsql Constant - ((1, 2) x 2) - (3, 4) ``` - operator: Constant plan_types: "LIR" description: | Always produces the same collection of rows. uses_memory: False memory_details: "" expansive: False example: "`→Constant (2 rows)`" - operator: Get plan_types: "optimized,raw" description: | Produces rows from either an existing relation (source/view/materialized view/table) or from a previous CTE in the same plan. uses_memory: False memory_details: "" expansive: False example: "`Get materialize.public.ordered`" - operator: Stream, Arranged, Index Lookup, Read plan_types: "LIR" description: | Produces rows from either an existing relation (source/view/materialized view/table) or from a previous CTE in the same plan. A parent `Fused Map/Filter/Project` operator can combine with this operator. There are four types of `Get`. 1. `Stream` indicates that the results are not [arranged](/get-started/arrangements/#arrangements) in memory and will be streamed directly. 2. `Arranged` indicates that the results are [arranged](/get-started/arrangements/#arrangements) in memory. 2. `Index Lookup` indicates the results will be _looked up_ in an existing [arrangement]((/get-started/arrangements/#arrangements). 3. `Read` indicates that the results are unarranged, and will be processed as they arrive. uses_memory: False memory_details: "" expansive: False example: "`Arranged materialize.public.t`" - operator: Project plan_types: "optimized,raw" description: | Produces a subset of the [columns](#explain-plan-columns) in the input rows. See also [column numbering](#explain-plan-columns). uses_memory: False memory_details: "" expansive: False expansive_details: | Each row has _less_ data (i.e., shorter rows, but same number of rows). example: "`Project (#2, #3)`" - operator: Map plan_types: "optimized,raw" description: | Appends the results of some scalar expressions to each row in the input. uses_memory: False memory_details: "" expansive: True expansive_details: | Each row has more data (i.e., longer rows but same number of rows). example: "`Map (((#1 * 10000000dec) / #2) * 1000dec)`" - operator: Map/Filter/Project plan_types: "LIR" description: | Computes new columns (maps), filters columns, and projects away columns. Works row-by-row. Maps and filters will be printed, but projects will not. These may be marked as **`Fused`** `Map/Filter/Project`, which means they will combine with the operator beneath them to run more efficiently. uses_memory: False memory_details: "" expansive: True expansive_details: | Each row may have more data, from the `Map`. Each row may also have less data, from the `Project`. There may be fewer rows, from the `Filter`. example: | ```mzsql →Map/Filter/Project Filter: (#0{a} < 7) Map: (#0{a} + #1{b}) ``` - operator: FlatMap plan_types: "optimized" description: | Appends the result of some (one-to-many) [table function](/sql/functions/#table-functions) to each row in the input. uses_memory: False memory_details: "" expansive: True expansive_details: | Depends on the [table function](/sql/functions/#table-functions) used. example: "`FlatMap jsonb_foreach(#3)`" - operator: Table Function plan_types: "LIR" description: | Appends the result of some (one-to-many) [table function](/sql/functions/#table-functions) to each row in the input. A parent `Fused Table Function unnest_list` operator will fuse with its child `GroupAggregate` operator. Fusing these operator is part of how we efficiently compile window functions from SQL to dataflows. A parent `Fused Map/Filter/Project` can combine with this operator. uses_memory: False memory_details: "" expansive: True expansive_details: | Depends on the [table function](/sql/functions/#table-functions) used. example: | ```mzsql →Table Function generate_series(#0{a}, #1{b}, 1) Input key: (#0{a}) ``` - operator: CallTable plan_types: "raw" description: | Appends the result of some (one-to-many) [table function](/sql/functions/#table-functions) to each row in the input. uses_memory: False memory_details: "" expansive: True expansive_details: | Depends on the [table function](/sql/functions/#table-functions) used. example: "`CallTable generate_series(1, 7, 1)`" - operator: Filter plan_types: "optimized,raw" description: | Removes rows of the input for which some scalar predicates return `false`. uses_memory: False memory_details: "" expansive: False example: "`Filter (#20 < #21)`" expansive_details: | May reduce the number of rows. - operator: ~Join plan_types: "raw" description: | Performs one of `INNER` / `LEFT` / `RIGHT` / `FULL OUTER` / `CROSS` join on the two inputs, using the given predicate. uses_memory: True memory_details: | Uses memory proportional to the input sizes, unless [the inputs have appropriate indexes](/transform-data/optimization/#join). Certain joins with more than 2 inputs use additional memory, see details in the optimized plan. expansive: True expansive_details: | For `CrossJoin`s, Cartesian product of the inputs (|N| x |M|). Note that, in many cases, a join that shows up as a cross join in the RAW PLAN will actually be turned into an inner join in the OPTIMIZED PLAN, by making use of an equality WHERE condition. For other join types, depends on the join order and facts about the joined collections. example: "`InnerJoin (#0 = #2)`" - operator: Join plan_types: "optimized" description: | Returns combinations of rows from each input whenever some equality predicates are `true`. uses_memory: True memory_details: | The `Join` operator itself uses memory only for `type=differential` with more than 2 inputs. However, `Join` operators need [arrangements](/get-started/arrangements/#arrangements) on their inputs (shown by the `ArrangeBy` operator). These arrangements use memory proportional to the input sizes. If an input has an [appropriate index](/transform-data/optimization/#join), then the arrangement of the index will be reused. expansive: True expansive_details: | Depends on the join order and facts about the joined collections. example: "`Join on=(#1 = #2) type=delta`" - operator: Differential Join, Delta Join plan_types: "LIR" description: | Both join operators indicate the join ordering selected. Returns combinations of rows from each input whenever some equality predicates are `true`. Joins will indicate the join order of their children, starting from 0. For example, `Differential Join %1 » %0` will join its second child into its first. The [two joins differ in performance characteristics](/transform-data/optimization/#join). uses_memory: True memory_details: | Uses memory for 3-way or more differential joins. expansive: True expansive_details: | Depends on the join order and facts about the joined collections. example: | ```mzsql →Differential Join %1 » %0 Join stage %0: Lookup key #0{a} in %0 ``` - operator: CrossJoin plan_types: "optimized" description: | An alias for a `Join` with an empty predicate (emits all combinations). Note that not all cross joins are marked as `CrossJoin`: In a join with more than 2 inputs, it can happen that there is a cross join between some of the inputs. You can recognize this case by `ArrangeBy` operators having empty keys, i.e., `ArrangeBy keys=[[]]`. uses_memory: True memory_details: | Uses memory for 3-way or more differential joins. expansive: True expansive_details: | Cartesian product of the inputs (|N| x |M|). example: "`CrossJoin type=differential`" - operator: Reduce plan_types: "optimized" description: | Groups the input rows by some scalar expressions, reduces each group using some aggregate functions, and produces rows containing the group key and aggregate outputs. uses_memory: True memory_details: | `SUM`, `COUNT`, and most other aggregations use a moderate amount of memory (proportional either to twice the output size or to input size + output size). `MIN` and `MAX` aggregates can use significantly more memory. This can be improved by including group size hints in the query, see [`mz_introspection.mz_expected_group_size_advice`](/sql/system-catalog/mz_introspection/#mz_expected_group_size_advice). expansive: False example: "`Reduce group_by=[#0] aggregates=[max((#0 * #1))]`" - operator: GroupAggregate plan_types: "LIR" description: | Groups the input rows by some scalar expressions, reduces each group using some aggregate functions, and produces rows containing the group key and aggregate outputs. There are five types of `GroupAggregate`, ordered by increasing complexity: 1. `Distinct GroupAggregate` corresponds to the SQL `DISTINCT` operator. 2. `Accumulable GroupAggregate` (e.g., `SUM`, `COUNT`) corresponds to several easy to implement aggregations that can be executed simultaneously and efficiently. 3. `Hierarchical GroupAggregate` (e.g., `MIN`, `MAX`) corresponds to an aggregation requiring a tower of arrangements. These can be either monotonic (more efficient) or bucketed. These may benefit from a hint; [see `mz_introspection.mz_expected_group_size_advice`](/sql/system-catalog/mz_introspection/#mz_expected_group_size_advice). These may either be bucketed or monotonic (more efficient). These may consolidate their output, which will increase memory usage. 4. `Collated Multi-GroupAggregate` corresponds to an arbitrary mix of reductions of different types, which will be performed separately and then joined together. 5. `Non-incremental GroupAggregate` (e.g., window functions, `list_agg`) corresponds to a single non-incremental aggregation. These are the most computationally intensive reductions. A parent `Fused Map/Filter/Project` can combine with this operator. uses_memory: True memory_details: | `Distinct` and `Accumulable` aggregates use a moderate amount of memory (proportional to twice the output size). `MIN` and `MAX` aggregates can use significantly more memory. This can be improved by including group size hints in the query, see [`mz_introspection.mz_expected_group_size_advice`](/sql/system-catalog/mz_introspection/#mz_expected_group_size_advice). `Non-incremental` aggregates use memory proportional to the input + output size. `Collated` aggregates use memory that is the sum of their constituents, plus some memory for the join at the end. expansive: False example: | ```mzsql →Accumulable GroupAggregate Simple aggregates: count(*) Post-process Map/Filter/Project Filter: (#0 > 1) ``` - operator: Reduce plan_types: "raw" description: | Groups the input rows by some scalar expressions, reduces each group using some aggregate functions, and produces rows containing the group key and aggregate outputs. In the case where the group key is empty and the input is empty, returns a single row with the aggregate functions applied to the empty input collection. uses_memory: True memory_details: | `SUM`, `COUNT`, and most other aggregations use a moderate amount of memory (proportional either to twice the output size or to input size + output size). `MIN` and `MAX` aggregates can use significantly more memory. This can be improved by including group size hints in the query, see [`mz_introspection.mz_expected_group_size_advice`](/sql/system-catalog/mz_introspection/#mz_expected_group_size_advice). expansive: False example: "`Reduce group_by=[#0] aggregates=[max((#0 * #1))]`" - operator: Distinct plan_types: "optimized" description: | Alias for a `Reduce` with an empty aggregate list. uses_memory: True memory_details: | Uses memory proportional to twice the output size. expansive: False example: "`Distinct`" - operator: Distinct plan_types: "raw" description: | Removes duplicate copies of input rows. uses_memory: True memory_details: | Uses memory proportional to twice the output size. expansive: False example: "`Distinct`" - operator: TopK plan_types: "optimized,raw" description: | Groups the input rows by some scalar expressions, sorts each group using the group key, removes the top `offset` rows in each group, and returns the next `limit` rows. uses_memory: True memory_details: | Can use significant amount as the operator can significantly overestimate the group sizes. Consult [`mz_introspection.mz_expected_group_size_advice`](/sql/system-catalog/mz_introspection/#mz_expected_group_size_advice). expansive: False example: "`TopK order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5`" - operator: TopK plan_types: "LIR" description: | Groups the input rows, sorts them according to some ordering, and returns at most `K` rows at some offset from the top of the list, where `K` is some (possibly computed) limit. There are three types of `TopK`. Two are special cased for monotonic inputs (i.e., inputs which never retract data). 1. `Monotonic Top1`. 2. `Monotonic TopK`, which may give an expression indicating the limit. 3. `Non-monotonic TopK`, a generic `TopK` plan. Each version of the `TopK` operator may include grouping, ordering, and limit directives. uses_memory: True memory_details: | `Monotonic Top1` and `Monotonic TopK` use a moderate amount of memory. `Non-monotonic TopK` uses significantly more memory as the operator can significantly overestimate the group sizes. Consult [`mz_introspection.mz_expected_group_size_advice`](/sql/system-catalog/mz_introspection/#mz_expected_group_size_advice). expansive: False example: | ```mzsql →Consolidating Monotonic TopK Order By #1 asc nulls_last, #0 desc nulls_first Limit 5 ``` - operator: Negate plan_types: "optimized,raw" description: | Negates the row counts of the input. This is usually used in combination with union to remove rows from the other union input. uses_memory: False memory_details: "" expansive: False example: "`Negate`" - operator: Negate Diffs plan_types: "LIR" description: | Negates the row counts of the input. This is usually used in combination with union to remove rows from the other union input. uses_memory: False memory_details: "" expansive: False example: "`→Negate Diffs`" - operator: Threshold plan_types: "optimized,raw" description: | Removes any rows with negative counts. uses_memory: True memory_details: | Uses memory proportional to the input and output size, twice. expansive: False example: "`Threshold`" - operator: Threshold Diffs plan_types: "LIR" description: | Removes any rows with negative counts. uses_memory: True memory_details: | Uses memory proportional to the input and output size, twice. expansive: False example: "`→Threshold Diffs`" - operator: Union plan_types: "optimized,raw" description: | Sums the counts of each row of all inputs. (Corresponds to `UNION ALL` rather than `UNION`/`UNION DISTINCT`.) uses_memory: True memory_details: | Moderate use of memory. Some union operators force consolidation, which results in a memory spike, largely at hydration time. expansive: False example: "`Union`" - operator: Union plan_types: "LIR" description: | Combines its inputs into a unified output, emitting one row for each row on any input. (Corresponds to `UNION ALL` rather than `UNION`/`UNION DISTINCT`.) uses_memory: True memory_details: | A `Consolidating Union` will make moderate use of memory, particularly at hydration time. A `Union` that is not `Consolidating` will not consume memory. expansive: False example: "`→Consolidating Union`" - operator: ArrangeBy plan_types: "optimized" description: | Indicates a point that will become an [arrangement](/get-started/arrangements/#arrangements) in the dataflow engine (each `keys` element will be a different arrangement). Note that if an appropriate index already exists on the input or the output of the previous operator is already arranged with a key that is also requested here, then this operator will just pass on that existing arrangement instead of creating a new one. uses_memory: True memory_details: | Depends. If arrangements need to be created, they use memory proportional to the input size. expansive: False example: "`ArrangeBy keys=[[#0]]`" - operator: Arrange plan_types: "LIR" description: | Indicates a point that will become an [arrangement](/get-started/arrangements/#arrangements) in the dataflow engine, i.e., it will consume memory to cache results. uses_memory: True memory_details: | Uses memory proportional to the input size. Note that in the LIR / physical plan, `Arrange`/`ArrangeBy` almost always means that an arrangement will actually be created. (This is in contrast to the "optimized" plan, where an `ArrangeBy` being present in the plan often does not mean that an arrangement will actually be created.) expansive: False example: | ```mzsql →Arrange Keys: 1 arrangement available, plus raw stream Arrangement 0: #0 ``` - operator: Unarranged Raw Stream plan_types: "LIR" description: | Indicates a point where data will be streamed (even if it is somehow already arranged). uses_memory: False expansive: False example: "`→Unarranged Raw Stream`" - operator: With ... Return ... plan_types: "optimized,raw,LIR" description: | Introduces CTEs, i.e., makes it possible for sub-plans to be consumed multiple times by downstream operators. uses_memory: False memory_details: "" expansive: False example: "[See above](#reading-plans)"