# Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. statement ok CREATE TABLE t ( a int, b int ) statement ok CREATE TABLE u ( c int, d int ) statement ok CREATE TABLE v ( e int, f int ) statement ok CREATE INDEX t_a_idx ON T(a); statement ok CREATE INDEX t_b_idx ON T(b); statement ok CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5 statement ok CREATE INDEX ov_a_idx ON ov(a); statement ok CREATE INDEX ov_b_idx ON ov(b); statement ok CREATE MATERIALIZED VIEW mv AS SELECT * FROM t WHERE a IS NOT NULL statement ok CREATE VIEW hierarchical_group_by AS SELECT a, MIN(b), MAX(DISTINCT b) FROM t GROUP BY a statement ok CREATE MATERIALIZED VIEW hierarchical_group_by_mv AS SELECT * FROM hierarchical_group_by statement ok CREATE VIEW hierarchical_global AS SELECT MIN(b), MAX(DISTINCT b) FROM t statement ok CREATE MATERIALIZED VIEW hierarchical_global_mv AS SELECT * FROM hierarchical_global statement ok CREATE VIEW collated_group_by AS SELECT a, COUNT(DISTINCT b), STRING_AGG(b::text || '1', ',') AS b1, MIN(b), MAX(DISTINCT b), SUM(b), STRING_AGG(b::text || '2', ',') AS b2 FROM t GROUP BY a statement ok CREATE MATERIALIZED VIEW collated_group_by_mv AS SELECT * FROM collated_group_by statement ok CREATE VIEW collated_global AS SELECT COUNT(DISTINCT b), STRING_AGG(b::text || '1', ',') AS b1, MIN(b), MAX(DISTINCT b), SUM(b), STRING_AGG(b::text || '2', ',') AS b2 FROM t statement ok CREATE MATERIALIZED VIEW collated_global_mv AS SELECT * FROM collated_global mode cockroach # Test constant error. query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR SELECT 1 / 0 ---- Explained Query: Error █ Target cluster: quickstart EOF # Test constant with two elements. query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR (SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4) ---- Explained Query: Constant - ((█, █) x 2) - (█, █) Target cluster: mz_catalog_server EOF # Test basic linear chains. # PassArrangements plan (identity transform on an arranged input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR SELECT * FROM t ---- Explained Query: Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # PassArrangements plan (identity transform on a raw input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR SELECT * FROM u ---- Explained Query: Get::PassArrangements materialize.public.u raw=true Source materialize.public.u Target cluster: quickstart EOF # GetArrangement plan (linear transform of an arranged input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR SELECT a + b, 1 FROM t ---- Explained Query: Get::Arrangement materialize.public.t project=(#2, #3) map=((#0{a} + #1{b}), █) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # GetCollection plan (linear transform of a raw input). query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR SELECT c + d, 1 FROM u ---- Explained Query: Get::Collection materialize.public.u raw=true Source materialize.public.u project=(#2, #3) map=((#0{c} + #1{d}), █) Target cluster: quickstart EOF # TopKBasic plan. query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR INDEX ov_a_idx ---- materialize.public.ov_a_idx: ArrangeBy raw=true arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Get::PassArrangements materialize.public.ov raw=true materialize.public.ov: TopK::Basic order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=█ ArrangeBy input_key=[#0{a}] raw=true Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # MonotonicTopK plan. query T multiline EXPLAIN PHYSICAL PLAN WITH(no fast path, redacted) AS VERBOSE TEXT FOR SELECT * FROM (SELECT * FROM t ORDER BY b asc, a desc LIMIT 5) ---- Explained Query: TopK::MonotonicTopK order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=█ must_consolidate ArrangeBy input_key=[#0{a}] raw=true Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Threshold, Union, Distinct, Negate. query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT a FROM t EXCEPT ALL SELECT b FROM mv ---- Explained Query: Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() } ArrangeBy raw=false arrangements[0]={ key=[#0], permutation=id, thinning=() } types=[integer?] Union consolidate_output=true Get::Arrangement materialize.public.t project=(#0) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Negate Get::Collection materialize.public.mv raw=true Source materialize.public.mv project=(#1) Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test CTEs. query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv) (SELECT x + 1 FROM cte UNION ALL SELECT x - 1 FROM cte) ---- Explained Query: With cte l0 = Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() } ArrangeBy raw=false arrangements[0]={ key=[#0], permutation=id, thinning=() } types=[integer?] Union consolidate_output=true Get::Arrangement materialize.public.t project=(#0) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Negate Get::Collection materialize.public.mv raw=true Return Union Get::Arrangement l0 project=(#1) map=((#0{x} + █)) key=#0 raw=false arrangements[0]={ key=[#0], permutation=id, thinning=() } types=[integer?] Get::Arrangement l0 project=(#1) map=((#0{x} - █)) key=#0 raw=false arrangements[0]={ key=[#0], permutation=id, thinning=() } types=[integer?] Source materialize.public.mv project=(#1) Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Mfp. query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv) SELECT x * 5 FROM cte WHERE x = 5 ---- Explained Query: Mfp project=(#1) map=(█) input_key=#0 Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() } ArrangeBy raw=false arrangements[0]={ key=[#0], permutation=id, thinning=() } types=[integer] Union consolidate_output=true Join::Linear linear_stage[0] closure project=(#0) lookup={ relation=0, key=[#0{a}] } stream={ key=[#0], thinning=() } source={ relation=1, key=[#0] } Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] ArrangeBy raw=true arrangements[0]={ key=[#0], permutation=id, thinning=() } types=[integer] Constant - (█) Negate Get::Collection materialize.public.mv raw=true Source materialize.public.mv project=(#1) filter=((#1{x} = █)) Used Indexes: - materialize.public.t_a_idx (lookup) Target cluster: quickstart EOF # Test FlatMap. query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT generate_series(a, b) from t ---- Explained Query: FlatMap generate_series(#0{a}, #1{b}, █) mfp_after project=(#2) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Distinct. query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT DISTINCT a, b FROM t ---- Explained Query: Reduce::Distinct val_plan project=() key_plan=id input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Accumulable (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT a, SUM(b), COUNT(DISTINCT b) FROM t GROUP BY a ---- Explained Query: Reduce::Accumulable simple_aggrs[0]=(0, 0, sum(#1{b})) distinct_aggrs[0]=(1, 1, count(distinct #1{b})) val_plan project=(#1, #1) key_plan project=(#0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Accumulable (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT SUM(b), COUNT(DISTINCT b) FROM t ---- Explained Query: With cte l0 = Reduce::Accumulable simple_aggrs[0]=(0, 0, sum(#0{b})) distinct_aggrs[0]=(1, 1, count(distinct #0{b})) val_plan project=(#0, #0) key_plan project=() Get::Arrangement materialize.public.t project=(#1) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Return Union ArrangeBy input_key=[] raw=true Get::PassArrangements l0 raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Mfp project=(#0, #1) map=(█, █) Union consolidate_output=true Negate Get::Arrangement l0 project=() key= raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Constant - () Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Hierarchical (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR MATERIALIZED VIEW hierarchical_group_by_mv ---- materialize.public.hierarchical_group_by_mv: Reduce::Hierarchical aggr_funcs=[min, max] skips=[0, 0] buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16] val_plan project=(#1, #1) key_plan project=(#0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Hierarchical (with GROUP BY, one-shot). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT * FROM hierarchical_group_by ---- Explained Query: Reduce::Hierarchical aggr_funcs=[min, max] skips=[0, 0] monotonic must_consolidate val_plan project=(#1, #1) key_plan project=(#0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Hierarchical (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR MATERIALIZED VIEW hierarchical_global_mv ---- materialize.public.hierarchical_global_mv: With cte l0 = Reduce::Hierarchical aggr_funcs=[min, max] skips=[0, 0] buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16] val_plan project=(#0, #0) key_plan project=() Get::Arrangement materialize.public.t project=(#1) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Return Union ArrangeBy input_key=[] raw=true Get::PassArrangements l0 raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Mfp project=(#0, #1) map=(█, █) Union consolidate_output=true Negate Get::Arrangement l0 project=() key= raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Constant - () Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Hierarchical (global aggregate, one-shot). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT * FROM hierarchical_global ---- Explained Query: With cte l0 = Reduce::Hierarchical aggr_funcs=[min, max] skips=[0, 0] monotonic must_consolidate val_plan project=(#0, #0) key_plan project=() Get::Arrangement materialize.public.t project=(#1) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Return Union ArrangeBy input_key=[] raw=true Get::PassArrangements l0 raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Mfp project=(#0, #1) map=(█, █) Union consolidate_output=true Negate Get::Arrangement l0 project=() key= raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Constant - () Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Basic (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT a, STRING_AGG(b::text || '1', ','), STRING_AGG(b::text || '2', ',') FROM t GROUP BY a ---- Explained Query: Reduce::Basic aggrs[0]=(0, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || █), █)))) aggrs[1]=(1, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || █), █)))) val_plan project=(#3, #4) map=(integer_to_text(#1{b}), row(row((#2 || █), █)), row(row((#2 || █), █))) key_plan project=(#0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Basic (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT STRING_AGG(b::text || '1', ','), STRING_AGG(b::text || '2', ',') FROM t ---- Explained Query: With cte l0 = Reduce::Basic aggrs[0]=(0, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || █), █)))) aggrs[1]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || █), █)))) val_plan project=(#2, #3) map=(integer_to_text(#0{b}), row(row((#1 || █), █)), row(row((#1 || █), █))) key_plan project=() Get::Arrangement materialize.public.t project=(#1) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Return Union ArrangeBy input_key=[] raw=true Get::PassArrangements l0 raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Mfp project=(#0, #1) map=(█, █) Union consolidate_output=true Negate Get::Arrangement l0 project=() key= raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) } Constant - () Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Collated (with GROUP BY). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR MATERIALIZED VIEW collated_group_by_mv ---- materialize.public.collated_group_by_mv: Reduce::Collation aggregate_types=[a, b, h, h, a, b] accumulable simple_aggrs[0]=(1, 4, sum(#1{b})) distinct_aggrs[0]=(0, 0, count(distinct #1{b})) hierarchical aggr_funcs=[min, max] skips=[2, 0] buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16] basic aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || █), █)))) aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || █), █)))) val_plan project=(#1, #3, #1, #1, #1, #4) map=(integer_to_text(#1{b}), row(row((#2 || █), █)), row(row((#2 || █), █))) key_plan project=(#0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Collated (with GROUP BY, one-shot). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT * FROM collated_group_by ---- Explained Query: Reduce::Collation aggregate_types=[a, b, h, h, a, b] accumulable simple_aggrs[0]=(1, 4, sum(#1{b})) distinct_aggrs[0]=(0, 0, count(distinct #1{b})) hierarchical aggr_funcs=[min, max] skips=[2, 0] monotonic must_consolidate basic aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || █), █)))) aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || █), █)))) val_plan project=(#1, #3, #1, #1, #1, #4) map=(integer_to_text(#1{b}), row(row((#2 || █), █)), row(row((#2 || █), █))) key_plan project=(#0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Collated (global aggregate). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR MATERIALIZED VIEW collated_global_mv ---- materialize.public.collated_global_mv: With cte l0 = Reduce::Collation aggregate_types=[a, b, h, h, a, b] accumulable simple_aggrs[0]=(1, 4, sum(#0{b})) distinct_aggrs[0]=(0, 0, count(distinct #0{b})) hierarchical aggr_funcs=[min, max] skips=[2, 0] buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16] basic aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || █), █)))) aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || █), █)))) val_plan project=(#0, #2, #0, #0, #0, #3) map=(integer_to_text(#0{b}), row(row((#1 || █), █)), row(row((#1 || █), █))) key_plan project=() Get::Arrangement materialize.public.t project=(#1) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Return Union ArrangeBy input_key=[] raw=true Get::PassArrangements l0 raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) } Mfp project=(#0..=#5) map=(█, █, █, █, █, █) Union consolidate_output=true Negate Get::Arrangement l0 project=() key= raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) } Constant - () Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test Reduce::Collated (global aggregate, one-shot). query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT * FROM collated_global ---- Explained Query: With cte l0 = Reduce::Collation aggregate_types=[a, b, h, h, a, b] accumulable simple_aggrs[0]=(1, 4, sum(#0{b})) distinct_aggrs[0]=(0, 0, count(distinct #0{b})) hierarchical aggr_funcs=[min, max] skips=[2, 0] monotonic must_consolidate basic aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || █), █)))) aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || █), █)))) val_plan project=(#0, #2, #0, #0, #0, #3) map=(integer_to_text(#0{b}), row(row((#1 || █), █)), row(row((#1 || █), █))) key_plan project=() Get::Arrangement materialize.public.t project=(#1) key=#0{a} raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Return Union ArrangeBy input_key=[] raw=true Get::PassArrangements l0 raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) } Mfp project=(#0..=#5) map=(█, █, █, █, █, █) Union consolidate_output=true Negate Get::Arrangement l0 project=() key= raw=false arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) } Constant - () Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test EXPLAIN INDEX for an indexed source query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR INDEX t_a_idx ---- materialize.public.t_a_idx: ArrangeBy raw=true arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Get::PassArrangements materialize.public.t raw=true Source materialize.public.t Target cluster: quickstart EOF # Test EXPLAIN INDEX for an indexed view (first index) query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR INDEX ov_a_idx; ---- materialize.public.ov_a_idx: ArrangeBy raw=true arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Get::PassArrangements materialize.public.ov raw=true materialize.public.ov: TopK::Basic order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=█ ArrangeBy input_key=[#0{a}] raw=true Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF # Test EXPLAIN INDEX for an indexed view (based on a prior index) query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR INDEX ov_b_idx; ---- materialize.public.ov_b_idx: ArrangeBy input_key=[#0{a}] raw=false arrangements[0]={ key=[#1{b}], permutation={#0: #1, #1: #0}, thinning=(#0) } types=[integer?, integer?] Get::PassArrangements materialize.public.ov raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.ov_a_idx (*** full scan ***, index export) Target cluster: quickstart EOF # Test Join::Differential (acyclic). query T multiline EXPLAIN PHYSICAL PLAN WITH(non negative) AS VERBOSE TEXT FOR SELECT b + d, c + e, a + e FROM t, u, v WHERE a = c AND d = e AND b + d > 42 ---- Explained Query: Join::Delta plan_path[0] final_closure project=(#0, #1, #1) delta_stage[1] closure project=(#1, #2) lookup={ relation=2, key=[#0{e}] } stream={ key=[#0{d}], thinning=(#1, #2) } delta_stage[0] closure project=(#2..=#4) filter=((#3 > 42)) map=((#1{b} + #2{d}), (#0{a} + #2{d})) lookup={ relation=1, key=[#0{c}] } stream={ key=[#0{a}], thinning=(#1) } source={ relation=0, key=[#0{a}] } plan_path[1] final_closure project=(#0, #1, #1) delta_stage[1] closure project=(#1, #2) lookup={ relation=2, key=[#0{e}] } stream={ key=[#0{d}], thinning=(#1, #2) } delta_stage[0] closure project=(#1, #3, #4) filter=((#3 > 42)) map=((#2{b} + #1{d}), (#0{a} + #1{d})) lookup={ relation=0, key=[#0{a}] } stream={ key=[#0{c}], thinning=(#1) } source={ relation=1, key=[#0{c}] } plan_path[2] final_closure project=(#0, #1, #1) delta_stage[1] closure project=(#3, #4) filter=((#3 > 42)) map=((#2{b} + #1{d}), (#0{a} + #1{d})) lookup={ relation=0, key=[#0{a}] } stream={ key=[#0{c}], thinning=(#1) } delta_stage[0] closure project=(#1, #0) lookup={ relation=1, key=[#1{d}] } stream={ key=[#0{e}], thinning=() } source={ relation=2, key=[#0{e}] } Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] ArrangeBy raw=true arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) } arrangements[1]={ key=[#1{d}], permutation={#0: #1, #1: #0}, thinning=(#0) } types=[integer, integer] Get::Collection materialize.public.u raw=true ArrangeBy raw=true arrangements[0]={ key=[#0{e}], permutation=id, thinning=() } types=[integer] Get::Collection materialize.public.v raw=true Source materialize.public.u filter=((#0{c}) IS NOT NULL AND (#1{d}) IS NOT NULL) Source materialize.public.v project=(#0) filter=((#0{e}) IS NOT NULL) Used Indexes: - materialize.public.t_a_idx (delta join 1st input (full scan)) Target cluster: quickstart EOF # Create indexes required for differential join tests statement ok CREATE INDEX u_c_idx ON U(c); statement ok CREATE INDEX u_d_idx ON U(d); statement ok CREATE INDEX v_e_idx ON V(e); # Test Join::Differential (cyclic). query T multiline EXPLAIN PHYSICAL PLAN WITH(non negative) AS VERBOSE TEXT FOR SELECT a, b, c, d, e, f FROM t, u, v WHERE a = c AND d = e AND f = a ---- Explained Query: Join::Delta plan_path[0] final_closure project=(#0, #1, #0, #2, #2, #0) delta_stage[1] closure project=(#1, #2, #0) lookup={ relation=2, key=[#0{e}, #1{f}] } stream={ key=[#2{d}, #0{a}], thinning=(#1) } delta_stage[0] lookup={ relation=1, key=[#0{c}] } stream={ key=[#0{a}], thinning=(#1) } source={ relation=0, key=[#0{a}] } plan_path[1] final_closure project=(#0, #1, #0, #2, #2, #0) delta_stage[1] closure project=(#0, #2, #1) lookup={ relation=0, key=[#0{a}] } stream={ key=[#0{c}], thinning=(#1) } delta_stage[0] closure project=(#1, #0) lookup={ relation=2, key=[#0{e}, #1{f}] } stream={ key=[#1{d}, #0{c}], thinning=() } source={ relation=1, key=[#0{c}] } plan_path[2] final_closure project=(#0, #1, #0, #2, #2, #0) delta_stage[1] closure project=(#0, #2, #1) lookup={ relation=0, key=[#0{a}] } stream={ key=[#1{f}], thinning=(#0) } delta_stage[0] closure project=(#1, #0) lookup={ relation=1, key=[#0{c}, #1{d}] } stream={ key=[#1{f}, #0{e}], thinning=() } source={ relation=2, key=[#0{e}, #1{f}] } Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] ArrangeBy raw=true arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) } arrangements[1]={ key=[#0{c}, #1{d}], permutation=id, thinning=() } types=[integer, integer] Get::Arrangement materialize.public.u filter=((#0{c}) IS NOT NULL AND (#1{d}) IS NOT NULL) key=#0{c} raw=false arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) } types=[integer?, integer?] ArrangeBy raw=true arrangements[0]={ key=[#0{e}, #1{f}], permutation=id, thinning=() } types=[integer, integer] Get::Arrangement materialize.public.v filter=((#0{e}) IS NOT NULL AND (#1{f}) IS NOT NULL) key=#0{e} raw=false arrangements[0]={ key=[#0{e}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (delta join 1st input (full scan)) - materialize.public.u_c_idx (*** full scan ***) - materialize.public.v_e_idx (*** full scan ***) Target cluster: quickstart EOF # Test Join::Delta (star). query T multiline EXPLAIN PHYSICAL PLAN WITH(non negative) AS VERBOSE TEXT FOR SELECT a, b, c, d, e, f FROM t, u, v WHERE a = c and a = e ---- Explained Query: Join::Delta plan_path[0] final_closure project=(#0, #1, #0, #2, #0, #3) delta_stage[1] lookup={ relation=2, key=[#0{e}] } stream={ key=[#0{a}], thinning=(#1, #2) } delta_stage[0] lookup={ relation=1, key=[#0{c}] } stream={ key=[#0{a}], thinning=(#1) } initial_closure filter=((#0{a}) IS NOT NULL) source={ relation=0, key=[#0{a}] } plan_path[1] final_closure project=(#0, #1, #0, #2, #0, #3) delta_stage[1] closure project=(#1..=#4) lookup={ relation=2, key=[#0{e}] } stream={ key=[#2{c}], thinning=(#0, #1, #3) } delta_stage[0] closure project=(#0, #2, #0, #1) filter=((#0{a}) IS NOT NULL) lookup={ relation=0, key=[#0{a}] } stream={ key=[#0{c}], thinning=(#1) } source={ relation=1, key=[#0{c}] } plan_path[2] final_closure project=(#0, #1, #0, #2, #0, #3) delta_stage[1] closure project=(#1, #2, #4, #3) lookup={ relation=1, key=[#0{c}] } stream={ key=[#2{e}], thinning=(#0, #1, #3) } delta_stage[0] closure project=(#0, #2, #0, #1) filter=((#0{a}) IS NOT NULL) lookup={ relation=0, key=[#0{a}] } stream={ key=[#0{e}], thinning=(#1) } source={ relation=2, key=[#0{e}] } Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Get::PassArrangements materialize.public.u raw=false arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) } types=[integer?, integer?] Get::PassArrangements materialize.public.v raw=false arrangements[0]={ key=[#0{e}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (delta join 1st input (full scan)) - materialize.public.u_c_idx (delta join lookup) - materialize.public.v_e_idx (delta join lookup) Target cluster: quickstart EOF # Test materialize#17348. statement ok CREATE TABLE r(f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT, f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT); query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM r AS r0, r AS r1 WHERE r0.f0=r1.f0 AND r0.f2=r1.f2 AND r0.f3=r1.f3 AND r0.f4=r1.f4 AND r0.f6=r1.f6 AND r0.f8=r1.f8 AND r0.f9=r1.f9 AND r0.f11=r1.f11 AND r0.f12=r1.f12 AND r0.f13=r1.f13 AND r0.f15=r1.f15 AND r0.f16=r1.f16; ---- Explained Query: With cte l0 = ArrangeBy keys=[[#0{f0}, #2{f2}..=#4{f4}, #6{f6}, #8{f8}, #9{f9}, #11{f11}..=#13{f13}, #15{f15}, #16{f16}]] Filter (#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL ReadStorage materialize.public.r Return Project (#0{f0}..=#16{f16}, #0{f0}, #18{f1}, #2{f2}..=#4{f4}, #22{f5}, #6{f6}, #24{f7}, #8{f8}, #9{f9}, #27{f10}, #11{f11}..=#13{f13}, #31{f14}, #15{f15}, #16{f16}) Join on=(#0{f0} = #17{f0} AND #2{f2} = #19{f2} AND #3{f3} = #20{f3} AND #4{f4} = #21{f4} AND #6{f6} = #23{f6} AND #8{f8} = #25{f8} AND #9{f9} = #26{f9} AND #11{f11} = #28{f11} AND #12{f12} = #29{f12} AND #13{f13} = #30{f13} AND #15{f15} = #32{f15} AND #16{f16} = #33{f16}) type=differential Get l0 Get l0 Source materialize.public.r filter=((#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL) Target cluster: quickstart EOF # Test `LetRec` printing, with and without RECURSION LIMIT # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT * FROM ( WITH MUTUALLY RECURSIVE (RECURSION LIMIT 5) foo (a int8) AS (SELECT DISTINCT a FROM foo) SELECT * FROM foo ) UNION ALL SELECT * FROM ( WITH MUTUALLY RECURSIVE bar (a int8) AS (SELECT DISTINCT a - 2 FROM bar) SELECT * FROM bar ); ---- Explained Query (fast path): Constant Target cluster: mz_catalog_server EOF query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT lead(b, 3, -5) IGNORE NULLS OVER () as l FROM t; ---- Explained Query: Mfp project=(#1) map=(record_get[0](#0)) input_key= Reduce::Basic aggr=(0, lead[ignore_nulls=true, order_by=[]](row(row(row(#0, #1), row(#1{b}, █, █)))), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(#1{b}, █, █)))) key_plan project=() input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF query T multiline EXPLAIN PHYSICAL PLAN WITH(redacted) AS VERBOSE TEXT FOR SELECT lag(b, 3, -5) IGNORE NULLS OVER (PARTITION BY b, a ORDER BY b+8, a-7) as l FROM t; ---- Explained Query: Mfp project=(#3) map=(record_get[0](#2)) input_key=#0, #1 Reduce::Basic aggr=(0, lag[ignore_nulls=true, order_by=[#0 asc nulls_last, #1 asc nulls_last]](row(row(row(#0, #1), row(#1{b}, █, █)), (#1{b} + █), (#0{a} - █))), fused_unnest_list=true) val_plan project=(#2) map=(row(row(row(#0, #1), row(#1{b}, █, █)), (#1{b} + █), (#0{a} - █))) key_plan project=(#1, #0) input_key=#0{a} Get::PassArrangements materialize.public.t raw=false arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) } types=[integer?, integer?] Used Indexes: - materialize.public.t_a_idx (*** full scan ***) Target cluster: quickstart EOF