# 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. # Plans for the TPC-H workload modeled as CREATE INDEX statements # on views for each workload query. # PRIMARY KEY annotations (which are in the spec) are currently # removed from this slt, because we don't support them at the moment. # (Note that _in slts_ they are actually supported, but it's better # to match the plans of real runs more closely.) statement ok CREATE TABLE nation ( n_nationkey integer, n_name char(25) NOT NULL, n_regionkey integer NOT NULL, n_comment varchar(152) ); statement ok CREATE INDEX pk_nation_nationkey ON nation (n_nationkey ASC); statement ok CREATE INDEX fk_nation_regionkey ON nation (n_regionkey ASC); statement ok CREATE TABLE region ( r_regionkey integer, r_name char(25) NOT NULL, r_comment varchar(152) ); statement ok CREATE INDEX pk_region_regionkey ON region (r_regionkey ASC); statement ok CREATE TABLE part ( p_partkey integer, p_name varchar(55) NOT NULL, p_mfgr char(25) NOT NULL, p_brand char(10) NOT NULL, p_type varchar(25) NOT NULL, p_size integer NOT NULL, p_container char(10) NOT NULL, p_retailprice decimal(15, 2) NOT NULL, p_comment varchar(23) NOT NULL ); statement ok CREATE INDEX pk_part_partkey ON part (p_partkey ASC); statement ok CREATE TABLE supplier ( s_suppkey integer, s_name char(25) NOT NULL, s_address varchar(40) NOT NULL, s_nationkey integer NOT NULL, s_phone char(15) NOT NULL, s_acctbal decimal(15, 2) NOT NULL, s_comment varchar(101) NOT NULL ); statement ok CREATE INDEX pk_supplier_suppkey ON supplier (s_suppkey ASC); statement ok CREATE INDEX fk_supplier_nationkey ON supplier (s_nationkey ASC); statement ok CREATE TABLE partsupp ( ps_partkey integer NOT NULL, ps_suppkey integer NOT NULL, ps_availqty integer NOT NULL, ps_supplycost decimal(15, 2) NOT NULL, ps_comment varchar(199) NOT NULL ); statement ok CREATE INDEX pk_partsupp_partkey_suppkey ON partsupp (ps_partkey ASC, ps_suppkey ASC); statement ok CREATE INDEX fk_partsupp_partkey ON partsupp (ps_partkey ASC); statement ok CREATE INDEX fk_partsupp_suppkey ON partsupp (ps_suppkey ASC); statement ok CREATE TABLE customer ( c_custkey integer, c_name varchar(25) NOT NULL, c_address varchar(40) NOT NULL, c_nationkey integer NOT NULL, c_phone char(15) NOT NULL, c_acctbal decimal(15, 2) NOT NULL, c_mktsegment char(10) NOT NULL, c_comment varchar(117) NOT NULL ); statement ok CREATE INDEX pk_customer_custkey ON customer (c_custkey ASC); statement ok CREATE INDEX fk_customer_nationkey ON customer (c_nationkey ASC); statement ok CREATE TABLE orders ( o_orderkey integer, o_custkey integer NOT NULL, o_orderstatus char(1) NOT NULL, o_totalprice decimal(15, 2) NOT NULL, o_orderdate DATE NOT NULL, o_orderpriority char(15) NOT NULL, o_clerk char(15) NOT NULL, o_shippriority integer NOT NULL, o_comment varchar(79) NOT NULL ); statement ok CREATE INDEX pk_orders_orderkey ON orders (o_orderkey ASC); statement ok CREATE INDEX fk_orders_custkey ON orders (o_custkey ASC); statement ok CREATE TABLE lineitem ( l_orderkey integer NOT NULL, l_partkey integer NOT NULL, l_suppkey integer NOT NULL, l_linenumber integer NOT NULL, l_quantity decimal(15, 2) NOT NULL, l_extendedprice decimal(15, 2) NOT NULL, l_discount decimal(15, 2) NOT NULL, l_tax decimal(15, 2) NOT NULL, l_returnflag char(1) NOT NULL, l_linestatus char(1) NOT NULL, l_shipdate date NOT NULL, l_commitdate date NOT NULL, l_receiptdate date NOT NULL, l_shipinstruct char(25) NOT NULL, l_shipmode char(10) NOT NULL, l_comment varchar(44) NOT NULL ); statement ok CREATE INDEX pk_lineitem_orderkey_linenumber ON lineitem (l_orderkey ASC, l_linenumber ASC); statement ok CREATE INDEX fk_lineitem_orderkey ON lineitem (l_orderkey ASC); statement ok CREATE INDEX fk_lineitem_partkey ON lineitem (l_partkey ASC); statement ok CREATE INDEX fk_lineitem_suppkey ON lineitem (l_suppkey ASC); statement ok CREATE INDEX fk_lineitem_partsuppkey ON lineitem (l_partkey ASC, l_suppkey ASC); statement ok -- Query 01 CREATE VIEW Q01 AS SELECT l_returnflag, l_linestatus, sum(l_quantity) AS sum_qty, sum(l_extendedprice) AS sum_base_price, sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, avg(l_quantity) AS avg_qty, avg(l_extendedprice) AS avg_price, avg(l_discount) AS avg_disc, count(*) AS count_order FROM lineitem WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '60' day GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus; query T multiline -- Explain index on Q01 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q01; ---- materialize.public.q01_primary_idx: ArrangeBy keys=[[#0{l_returnflag}, #1{l_linestatus}]] // { arity: 10 } ReadGlobalFromSameDataflow materialize.public.q01 // { arity: 10 } materialize.public.q01: Project (#0{l_returnflag}..=#5{sum}, #9..=#11, #6{count}) // { arity: 10 } Map (bigint_to_numeric(case when (#6{count} = 0) then null else #6{count} end), (#2{sum_l_quantity} / #8), (#3{sum_l_extendedprice} / #8), (#7{sum_l_discount} / #8)) // { arity: 12 } Reduce group_by=[#4{l_returnflag}, #5{l_linestatus}] aggregates=[sum(#0{l_quantity}), sum(#1{l_extendedprice}), sum((#1{l_extendedprice} * (1 - #2{l_discount}))), sum(((#1{l_extendedprice} * (1 - #2{l_discount})) * (1 + #3{l_tax}))), count(*), sum(#2{l_discount})] // { arity: 8 } Project (#4{l_quantity}..=#9{l_linestatus}) // { arity: 6 } Filter (date_to_timestamp(#10{l_shipdate}) <= 1998-10-02 00:00:00) // { arity: 16 } ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 } Used Indexes: - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***) Target cluster: quickstart EOF # Create a default index on Q01 in order to ensure that the EXPLAIN CREATE INDEX # output does not produce a plan that just reads from the explained index if it # already exists. statement ok CREATE DEFAULT INDEX ON Q01; statement ok -- Query 02 CREATE VIEW Q02 AS SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment FROM part, supplier, partsupp, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND p_size = CAST (15 AS smallint) AND p_type LIKE '%BRASS' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' AND ps_supplycost = ( SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'EUROPE' ) ORDER BY s_acctbal DESC, n_name, s_name, p_partkey; query T multiline -- Explain index on Q02 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q02; ---- materialize.public.q02_primary_idx: ArrangeBy keys=[[#0{s_acctbal}..=#7{s_comment}]] // { arity: 8 } ReadGlobalFromSameDataflow materialize.public.q02 // { arity: 8 } materialize.public.q02: With cte l0 = ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 } cte l1 = ArrangeBy keys=[[#0{ps_partkey}], [#1{ps_suppkey}]] // { arity: 5 } ReadIndex on=partsupp fk_partsupp_partkey=[delta join lookup] fk_partsupp_suppkey=[delta join lookup] // { arity: 5 } cte l2 = ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] fk_nation_regionkey=[delta join lookup] // { arity: 4 } cte l3 = ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 3 } ReadIndex on=region pk_region_regionkey=[delta join lookup] // { arity: 3 } cte l4 = Project (#0{p_partkey}, #2{p_mfgr}, #10{s_name}, #11{s_address}, #13{s_phone}..=#15{s_comment}, #19{ps_supplycost}, #22{n_name}) // { arity: 9 } Filter (#5{p_size} = 15) AND (#26{r_name} = "EUROPE") AND like["%BRASS"](varchar_to_text(#4{p_type})) // { arity: 28 } Join on=(#0{p_partkey} = #16{ps_partkey} AND #9{s_suppkey} = #17{ps_suppkey} AND #12{s_nationkey} = #21{n_nationkey} AND #23{n_regionkey} = #25{r_regionkey}) type=delta // { arity: 28 } implementation %0:part » %2:l1[#0{ps_partkey}]KA » %1:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef %1:l0 » %2:l1[#1{ps_suppkey}]KA » %0:part[#0{p_partkey}]KAelf » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef %2:l1 » %0:part[#0{p_partkey}]KAelf » %1:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef %3:l2 » %4:l3[#0{r_regionkey}]KAef » %1:l0[#3{s_nationkey}]KA » %2:l1[#1{ps_suppkey}]KA » %0:part[#0{p_partkey}]KAelf %4:l3 » %3:l2[#2{n_regionkey}]KA » %1:l0[#3{s_nationkey}]KA » %2:l1[#1{ps_suppkey}]KA » %0:part[#0{p_partkey}]KAelf ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[delta join 1st input (full scan)] // { arity: 9 } Get l0 // { arity: 7 } Get l1 // { arity: 5 } Get l2 // { arity: 4 } Get l3 // { arity: 3 } Return // { arity: 8 } Project (#5{s_acctbal}, #2{s_name}, #8{n_name}, #0{p_partkey}, #1{p_mfgr}, #3{s_address}, #4{s_phone}, #6{s_comment}) // { arity: 8 } Join on=(#0{p_partkey} = #9{p_partkey} AND #7{ps_supplycost} = #10{min_ps_supplycost}) type=differential // { arity: 11 } implementation %1[#0, #1]UKK » %0:l4[#0, #7{ps_supplycost}]KK ArrangeBy keys=[[#0{p_partkey}, #7{ps_supplycost}]] // { arity: 9 } Get l4 // { arity: 9 } ArrangeBy keys=[[#0{p_partkey}, #1{min_ps_supplycost}]] // { arity: 2 } Reduce group_by=[#0{p_partkey}] aggregates=[min(#1{ps_supplycost})] // { arity: 2 } Project (#0{p_partkey}, #4{ps_supplycost}) // { arity: 2 } Filter (#18{r_name} = "EUROPE") // { arity: 20 } Join on=(#0{p_partkey} = #1{ps_partkey} AND #2{ps_suppkey} = #6{s_suppkey} AND #9{s_nationkey} = #13{n_nationkey} AND #15{n_regionkey} = #17{r_regionkey}) type=delta // { arity: 20 } implementation %0 » %1:l1[#0{ps_partkey}]KA » %2:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef %1:l1 » %0[#0]UKA » %2:l0[#0{s_suppkey}]KA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef %2:l0 » %1:l1[#1{ps_suppkey}]KA » %0[#0]UKA » %3:l2[#0{n_nationkey}]KA » %4:l3[#0{r_regionkey}]KAef %3:l2 » %4:l3[#0{r_regionkey}]KAef » %2:l0[#3{s_nationkey}]KA » %1:l1[#1{ps_suppkey}]KA » %0[#0]UKA %4:l3 » %3:l2[#2{n_regionkey}]KA » %2:l0[#3{s_nationkey}]KA » %1:l1[#1{ps_suppkey}]KA » %0[#0]UKA ArrangeBy keys=[[#0{p_partkey}]] // { arity: 1 } Distinct project=[#0{p_partkey}] // { arity: 1 } Project (#0{p_partkey}) // { arity: 1 } Get l4 // { arity: 9 } Get l1 // { arity: 5 } Get l0 // { arity: 7 } Get l2 // { arity: 4 } Get l3 // { arity: 3 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.fk_nation_regionkey (delta join lookup) - materialize.public.pk_region_regionkey (delta join lookup) - materialize.public.pk_part_partkey (delta join 1st input (full scan)) - materialize.public.pk_supplier_suppkey (delta join lookup) - materialize.public.fk_supplier_nationkey (delta join lookup) - materialize.public.fk_partsupp_partkey (delta join lookup) - materialize.public.fk_partsupp_suppkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 03 CREATE VIEW Q03 AS SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < DATE '1995-03-15' AND l_shipdate > DATE '1995-03-15' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate; query T multiline -- Explain index on Q03 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q03; ---- materialize.public.q03_primary_idx: ArrangeBy keys=[[#0{l_orderkey}, #2{o_orderdate}, #3{o_shippriority}]] // { arity: 4 } ReadGlobalFromSameDataflow materialize.public.q03 // { arity: 4 } materialize.public.q03: Project (#0{o_orderkey}, #3{sum}, #1{o_orderdate}, #2{o_shippriority}) // { arity: 4 } Reduce group_by=[#0{o_orderkey}..=#2{o_shippriority}] aggregates=[sum((#3{l_extendedprice} * (1 - #4{l_discount})))] // { arity: 4 } Project (#8{o_orderkey}, #12{o_orderdate}, #15{o_shippriority}, #22{l_extendedprice}, #23{l_discount}) // { arity: 5 } Filter (#6{c_mktsegment} = "BUILDING") AND (#12{o_orderdate} < 1995-03-15) AND (#27{l_shipdate} > 1995-03-15) // { arity: 33 } Join on=(#0{c_custkey} = #9{o_custkey} AND #8{o_orderkey} = #17{l_orderkey}) type=delta // { arity: 33 } implementation %0:customer » %1:orders[#1{o_custkey}]KAif » %2:lineitem[#0{l_orderkey}]KAif %1:orders » %0:customer[#0{c_custkey}]KAef » %2:lineitem[#0{l_orderkey}]KAif %2:lineitem » %1:orders[#0{o_orderkey}]KAif » %0:customer[#0{c_custkey}]KAef ArrangeBy keys=[[#0{c_custkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] // { arity: 8 } ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] // { arity: 16 } Used Indexes: - materialize.public.pk_customer_custkey (delta join 1st input (full scan)) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_orders_custkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 04 CREATE VIEW Q04 AS SELECT o_orderpriority, count(*) AS order_count FROM orders WHERE o_orderdate >= DATE '1993-07-01' AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' month AND EXISTS ( SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate ) GROUP BY o_orderpriority ORDER BY o_orderpriority; query T multiline -- Explain index on Q04 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q04; ---- materialize.public.q04_primary_idx: ArrangeBy keys=[[#0{o_orderpriority}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q04 // { arity: 2 } materialize.public.q04: Reduce group_by=[#0{o_orderpriority}] aggregates=[count(*)] // { arity: 2 } Project (#5{o_orderpriority}) // { arity: 1 } Filter (#4{o_orderdate} >= 1993-07-01) AND (date_to_timestamp(#4{o_orderdate}) < 1993-10-01 00:00:00) // { arity: 10 } Join on=(#0{o_orderkey} = #9{l_orderkey}) type=differential // { arity: 10 } implementation %1[#0]UKA » %0:orders[#0{o_orderkey}]KAiif ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[differential join] // { arity: 9 } ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 1 } Distinct project=[#0{l_orderkey}] // { arity: 1 } Project (#0{l_orderkey}) // { arity: 1 } Filter (#11{l_commitdate} < #12{l_receiptdate}) // { arity: 16 } ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 } Used Indexes: - materialize.public.pk_orders_orderkey (differential join) - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***) Target cluster: quickstart EOF statement ok -- Query 05 CREATE VIEW Q05 AS SELECT n_name, sum(l_extendedprice * (1 - l_discount)) AS revenue FROM customer, orders, lineitem, supplier, nation, region WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA' AND o_orderdate >= DATE '1994-01-01' AND o_orderdate < DATE '1995-01-01' GROUP BY n_name ORDER BY revenue DESC; query T multiline -- Explain index on Q05 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q05; ---- materialize.public.q05_primary_idx: ArrangeBy keys=[[#0{n_name}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q05 // { arity: 2 } materialize.public.q05: Reduce group_by=[#2{n_name}] aggregates=[sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 2 } Project (#19{l_extendedprice}, #20{l_discount}, #24{n_name}) // { arity: 3 } Filter (#28{r_name} = "ASIA") AND (#12{o_orderdate} < 1995-01-01) AND (#12{o_orderdate} >= 1994-01-01) // { arity: 30 } Join on=(#0{c_custkey} = #9{o_custkey} AND #3{c_nationkey} = #22{s_nationkey} = #23{n_nationkey} AND #8{o_orderkey} = #17{l_orderkey} AND #18{l_suppkey} = #21{s_suppkey} AND #25{n_regionkey} = #27{r_regionkey}) type=delta // { arity: 30 } implementation %0:customer » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef %1:orders » %0:customer[#0{c_custkey}]KA » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef %2:lineitem » %1:orders[#0{o_orderkey}]KAiif » %0:customer[#0{c_custkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef %3:supplier » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}, #1{l_suppkey}]KK » %4:nation[#0{n_nationkey}]KA » %5:region[#0{r_regionkey}]KAef %4:nation » %5:region[#0{r_regionkey}]KAef » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK %5:region » %4:nation[#2{n_regionkey}]KA » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %3:supplier[#0{s_suppkey}, #1{s_nationkey}]KK ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] fk_customer_nationkey=[delta join lookup] // { arity: 8 } ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{l_orderkey}], [#0{l_orderkey}, #1{l_suppkey}]] // { arity: 4 } Project (#0{l_orderkey}, #2{l_suppkey}, #5{l_extendedprice}, #6{l_discount}) // { arity: 4 } ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 } ArrangeBy keys=[[#0{s_suppkey}, #1{s_nationkey}]] // { arity: 2 } Project (#0{s_suppkey}, #3{s_nationkey}) // { arity: 2 } Filter (#0{s_suppkey}) IS NOT NULL // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[*** full scan ***] // { arity: 7 } ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] fk_nation_regionkey=[delta join lookup] // { arity: 4 } ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 3 } ReadIndex on=region pk_region_regionkey=[delta join lookup] // { arity: 3 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.fk_nation_regionkey (delta join lookup) - materialize.public.pk_region_regionkey (delta join lookup) - materialize.public.pk_supplier_suppkey (*** full scan ***) - materialize.public.pk_customer_custkey (delta join 1st input (full scan)) - materialize.public.fk_customer_nationkey (delta join lookup) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_orders_custkey (delta join lookup) - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***) Target cluster: quickstart EOF statement ok -- Query 06 CREATE VIEW Q06 AS SELECT sum(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_quantity < 24 AND l_shipdate >= DATE '1994-01-01' AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' year AND l_discount BETWEEN 0.06 - 0.01 AND 0.07; query T multiline -- Explain index on Q06 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q06; ---- materialize.public.q06_primary_idx: ArrangeBy keys=[[#0{revenue}]] // { arity: 1 } ReadGlobalFromSameDataflow materialize.public.q06 // { arity: 1 } materialize.public.q06: With cte l0 = Reduce aggregates=[sum((#0{l_extendedprice} * #1{l_discount}))] // { arity: 1 } Project (#5{l_extendedprice}, #6{l_discount}) // { arity: 2 } Filter (#4{l_quantity} < 24) AND (#6{l_discount} <= 0.07) AND (#6{l_discount} >= 0.05) AND (#10{l_shipdate} >= 1994-01-01) AND (date_to_timestamp(#10{l_shipdate}) < 1995-01-01 00:00:00) // { arity: 16 } ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 } Return // { arity: 1 } Union // { arity: 1 } Get l0 // { arity: 1 } Map (null) // { arity: 1 } Union // { arity: 0 } Negate // { arity: 0 } Project () // { arity: 0 } Get l0 // { arity: 1 } Constant // { arity: 0 } - () Used Indexes: - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***) Target cluster: quickstart EOF statement ok -- Query 07 CREATE VIEW Q07 AS SELECT supp_nation, cust_nation, l_year, sum(volume) AS revenue FROM ( SELECT n1.n_name AS supp_nation, n2.n_name AS cust_nation, extract(year FROM l_shipdate) AS l_year, l_extendedprice * (1 - l_discount) AS volume FROM supplier, lineitem, orders, customer, nation n1, nation n2 WHERE s_suppkey = l_suppkey AND o_orderkey = l_orderkey AND c_custkey = o_custkey AND s_nationkey = n1.n_nationkey AND c_nationkey = n2.n_nationkey AND ( (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') or (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE') ) AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' ) AS shipping GROUP BY supp_nation, cust_nation, l_year ORDER BY supp_nation, cust_nation, l_year; query T multiline -- Explain index on Q07 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q07; ---- materialize.public.q07_primary_idx: ArrangeBy keys=[[#0{supp_nation}..=#2{l_year}]] // { arity: 4 } ReadGlobalFromSameDataflow materialize.public.q07 // { arity: 4 } materialize.public.q07: With cte l0 = ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 } Return // { arity: 4 } Reduce group_by=[#3{n_name}, #4{n_name}, extract_year_d(#2{l_shipdate})] aggregates=[sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 4 } Project (#12{l_extendedprice}, #13{l_discount}, #17{l_shipdate}, #41{n_name}, #45{n_name}) // { arity: 5 } Filter (#17{l_shipdate} <= 1996-12-31) AND (#17{l_shipdate} >= 1995-01-01) AND (#48 OR #49) AND (#50 OR #51) AND ((#48 AND #51) OR (#49 AND #50)) // { arity: 52 } Map ((#41{n_name} = "FRANCE"), (#41{n_name} = "GERMANY"), (#45{n_name} = "FRANCE"), (#45{n_name} = "GERMANY")) // { arity: 52 } Join on=(#0{s_suppkey} = #9{l_suppkey} AND #3{s_nationkey} = #40{n_nationkey} AND #7{l_orderkey} = #23{o_orderkey} AND #24{o_custkey} = #32{c_custkey} AND #35{c_nationkey} = #44{n_nationkey}) type=delta // { arity: 48 } implementation %0:supplier » %4:l0[#0{n_nationkey}]KAef » %1:lineitem[#2{l_suppkey}]KAiif » %2:orders[#0{o_orderkey}]KA » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef %1:lineitem » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef » %2:orders[#0{o_orderkey}]KA » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef %2:orders » %1:lineitem[#0{l_orderkey}]KAiif » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef %3:customer » %5:l0[#0{n_nationkey}]KAef » %2:orders[#1{o_custkey}]KA » %1:lineitem[#0{l_orderkey}]KAiif » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef %4:l0 » %0:supplier[#3{s_nationkey}]KA » %1:lineitem[#2{l_suppkey}]KAiif » %2:orders[#0{o_orderkey}]KA » %3:customer[#0{c_custkey}]KA » %5:l0[#0{n_nationkey}]KAef %5:l0 » %3:customer[#3{c_nationkey}]KA » %2:orders[#1{o_custkey}]KA » %1:lineitem[#0{l_orderkey}]KAiif » %0:supplier[#0{s_suppkey}]KA » %4:l0[#0{n_nationkey}]KAef ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join 1st input (full scan)] fk_supplier_nationkey=[delta join lookup] // { arity: 7 } ArrangeBy keys=[[#0{l_orderkey}], [#2{l_suppkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] // { arity: 16 } ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[delta join lookup] fk_customer_nationkey=[delta join lookup] // { arity: 8 } Get l0 // { arity: 4 } Get l0 // { arity: 4 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.pk_supplier_suppkey (delta join 1st input (full scan)) - materialize.public.fk_supplier_nationkey (delta join lookup) - materialize.public.pk_customer_custkey (delta join lookup) - materialize.public.fk_customer_nationkey (delta join lookup) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_orders_custkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (delta join lookup) - materialize.public.fk_lineitem_suppkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 08 CREATE VIEW Q08 AS SELECT o_year, sum(case when nation = 'BRAZIL' then volume else 0 end) / sum(volume) AS mkt_share FROM ( SELECT extract(year FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part, supplier, lineitem, orders, customer, nation n1, nation n2, region WHERE p_partkey = l_partkey AND s_suppkey = l_suppkey AND l_orderkey = o_orderkey AND o_custkey = c_custkey AND c_nationkey = n1.n_nationkey AND n1.n_regionkey = r_regionkey AND r_name = 'AMERICA' AND s_nationkey = n2.n_nationkey AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' AND p_type = 'ECONOMY ANODIZED STEEL' ) AS all_nations GROUP BY o_year ORDER BY o_year; query T multiline -- Explain index on Q08 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q08; ---- materialize.public.q08_primary_idx: ArrangeBy keys=[[#0{o_year}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q08 // { arity: 2 } materialize.public.q08: Project (#0, #3) // { arity: 2 } Map ((#1{sum} / #2{sum})) // { arity: 4 } Reduce group_by=[extract_year_d(#2{o_orderdate})] aggregates=[sum(case when (#3{n_name} = "BRAZIL") then (#0{l_extendedprice} * (1 - #1{l_discount})) else 0 end), sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 3 } Project (#21{l_extendedprice}, #22{l_discount}, #36{o_orderdate}, #54{n_name}) // { arity: 4 } Filter (#58{r_name} = "AMERICA") AND (#36{o_orderdate} <= 1996-12-31) AND (#36{o_orderdate} >= 1995-01-01) AND ("ECONOMY ANODIZED STEEL" = varchar_to_text(#4{p_type})) // { arity: 60 } Join on=(#0{p_partkey} = #17{l_partkey} AND #9{s_suppkey} = #18{l_suppkey} AND #12{s_nationkey} = #53{n_nationkey} AND #16{l_orderkey} = #32{o_orderkey} AND #33{o_custkey} = #41{c_custkey} AND #44{c_nationkey} = #49{n_nationkey} AND #51{n_regionkey} = #57{r_regionkey}) type=delta // { arity: 60 } implementation %0:part » %2:lineitem[#1{l_partkey}]KA » %3:orders[#0{o_orderkey}]KAiif » %1:supplier[#0{s_suppkey}]KA » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA %1:supplier » %2:lineitem[#2{l_suppkey}]KA » %0:part[#0{p_partkey}]KAef » %3:orders[#0{o_orderkey}]KAiif » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA %2:lineitem » %0:part[#0{p_partkey}]KAef » %3:orders[#0{o_orderkey}]KAiif » %1:supplier[#0{s_suppkey}]KA » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA %3:orders » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA %4:customer » %3:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef » %6:nation[#0{n_nationkey}]KA %5:nation » %7:region[#0{r_regionkey}]KAef » %4:customer[#3{c_nationkey}]KA » %3:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %6:nation[#0{n_nationkey}]KA %6:nation » %1:supplier[#3{s_nationkey}]KA » %2:lineitem[#2{l_suppkey}]KA » %0:part[#0{p_partkey}]KAef » %3:orders[#0{o_orderkey}]KAiif » %4:customer[#0{c_custkey}]KA » %5:nation[#0{n_nationkey}]KA » %7:region[#0{r_regionkey}]KAef %7:region » %5:nation[#2{n_regionkey}]KA » %4:customer[#3{c_nationkey}]KA » %3:orders[#1{o_custkey}]KAiif » %2:lineitem[#0{l_orderkey}]KA » %0:part[#0{p_partkey}]KAef » %1:supplier[#0{s_suppkey}]KA » %6:nation[#0{n_nationkey}]KA ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[delta join 1st input (full scan)] // { arity: 9 } ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 } ArrangeBy keys=[[#0{l_orderkey}], [#1{l_partkey}], [#2{l_suppkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_partkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] // { arity: 16 } ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[delta join lookup] fk_customer_nationkey=[delta join lookup] // { arity: 8 } ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] fk_nation_regionkey=[delta join lookup] // { arity: 4 } ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 } ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 3 } ReadIndex on=region pk_region_regionkey=[delta join lookup] // { arity: 3 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.fk_nation_regionkey (delta join lookup) - materialize.public.pk_region_regionkey (delta join lookup) - materialize.public.pk_part_partkey (delta join 1st input (full scan)) - materialize.public.pk_supplier_suppkey (delta join lookup) - materialize.public.fk_supplier_nationkey (delta join lookup) - materialize.public.pk_customer_custkey (delta join lookup) - materialize.public.fk_customer_nationkey (delta join lookup) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_orders_custkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (delta join lookup) - materialize.public.fk_lineitem_partkey (delta join lookup) - materialize.public.fk_lineitem_suppkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 09 CREATE VIEW Q09 AS SELECT nation, o_year, sum(amount) AS sum_profit FROM ( SELECT n_name AS nation, extract(year FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount FROM part, supplier, lineitem, partsupp, orders, nation WHERE s_suppkey = l_suppkey AND ps_suppkey = l_suppkey AND ps_partkey = l_partkey AND p_partkey = l_partkey AND o_orderkey = l_orderkey AND s_nationkey = n_nationkey AND p_name like '%green%' ) AS profit GROUP BY nation, o_year ORDER BY nation, o_year DESC; query T multiline -- Explain index on Q09 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q09; ---- materialize.public.q09_primary_idx: ArrangeBy keys=[[#0{nation}, #1{o_year}]] // { arity: 3 } ReadGlobalFromSameDataflow materialize.public.q09 // { arity: 3 } materialize.public.q09: Reduce group_by=[#5{n_name}, extract_year_d(#4{o_orderdate})] aggregates=[sum(((#1{l_extendedprice} * (1 - #2{l_discount})) - (#3{ps_supplycost} * #0{l_quantity})))] // { arity: 3 } Project (#20{l_quantity}..=#22{l_discount}, #35{ps_supplycost}, #41{o_orderdate}, #47{n_name}) // { arity: 6 } Filter like["%green%"](varchar_to_text(#1{p_name})) // { arity: 50 } Join on=(#0{p_partkey} = #17{l_partkey} = #32{ps_partkey} AND #9{s_suppkey} = #18{l_suppkey} = #33{ps_suppkey} AND #12{s_nationkey} = #46{n_nationkey} AND #16{l_orderkey} = #37{o_orderkey}) type=delta // { arity: 50 } implementation %0:part » %2:lineitem[#1{l_partkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %1:supplier[#0{s_suppkey}]KA » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA %1:supplier » %2:lineitem[#2{l_suppkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA %2:lineitem » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %1:supplier[#0{s_suppkey}]KA » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA %3:partsupp » %2:lineitem[#1{l_partkey}, #2{l_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %1:supplier[#0{s_suppkey}]KA » %4:orders[#0{o_orderkey}]KA » %5:nation[#0{n_nationkey}]KA %4:orders » %2:lineitem[#0{l_orderkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %1:supplier[#0{s_suppkey}]KA » %5:nation[#0{n_nationkey}]KA %5:nation » %1:supplier[#3{s_nationkey}]KA » %2:lineitem[#2{l_suppkey}]KA » %3:partsupp[#0{ps_partkey}, #1{ps_suppkey}]KKA » %0:part[#0{p_partkey}]KAlf » %4:orders[#0{o_orderkey}]KA ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[delta join 1st input (full scan)] // { arity: 9 } ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 } ArrangeBy keys=[[#0{l_orderkey}], [#1{l_partkey}], [#1{l_partkey}, #2{l_suppkey}], [#2{l_suppkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_partkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] fk_lineitem_partsuppkey=[delta join lookup] // { arity: 16 } ArrangeBy keys=[[#0{ps_partkey}, #1{ps_suppkey}]] // { arity: 5 } ReadIndex on=partsupp pk_partsupp_partkey_suppkey=[delta join lookup] // { arity: 5 } ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.pk_part_partkey (delta join 1st input (full scan)) - materialize.public.pk_supplier_suppkey (delta join lookup) - materialize.public.fk_supplier_nationkey (delta join lookup) - materialize.public.pk_partsupp_partkey_suppkey (delta join lookup) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (delta join lookup) - materialize.public.fk_lineitem_partkey (delta join lookup) - materialize.public.fk_lineitem_suppkey (delta join lookup) - materialize.public.fk_lineitem_partsuppkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 10 CREATE VIEW Q10 AS SELECT c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) AS revenue, c_acctbal, n_name, c_address, c_phone, c_comment FROM customer, orders, lineitem, nation WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate >= DATE '1993-10-01' AND o_orderdate < DATE '1994-01-01' AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' month AND l_returnflag = 'R' AND c_nationkey = n_nationkey GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ORDER BY revenue DESC; query T multiline -- Explain index on Q10 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q10; ---- materialize.public.q10_primary_idx: ArrangeBy keys=[[#0{c_custkey}, #1{c_name}, #3{c_acctbal}..=#7{c_comment}]] // { arity: 8 } ReadGlobalFromSameDataflow materialize.public.q10 // { arity: 8 } materialize.public.q10: Project (#0{c_custkey}, #1{c_name}, #7{sum}, #2{c_acctbal}, #4{n_name}, #5{c_address}, #3{c_phone}, #6{c_comment}) // { arity: 8 } Reduce group_by=[#0{c_custkey}, #1{c_name}, #4{c_acctbal}, #3{c_phone}, #8{n_name}, #2{c_address}, #5{c_comment}] aggregates=[sum((#6{l_extendedprice} * (1 - #7{l_discount})))] // { arity: 8 } Project (#0{c_custkey}..=#2{c_address}, #4{c_phone}, #5{c_acctbal}, #7{c_comment}, #22{l_extendedprice}, #23{l_discount}, #34{n_name}) // { arity: 9 } Filter (#25{l_returnflag} = "R") AND (#12{o_orderdate} < 1994-01-01) AND (#12{o_orderdate} >= 1993-10-01) AND (date_to_timestamp(#12{o_orderdate}) < 1994-01-01 00:00:00) // { arity: 37 } Join on=(#0{c_custkey} = #9{o_custkey} AND #3{c_nationkey} = #33{n_nationkey} AND #8{o_orderkey} = #17{l_orderkey}) type=delta // { arity: 37 } implementation %0:customer » %1:orders[#1{o_custkey}]KAiiif » %2:lineitem[#0{l_orderkey}]KAef » %3:nation[#0{n_nationkey}]KA %1:orders » %2:lineitem[#0{l_orderkey}]KAef » %0:customer[#0{c_custkey}]KA » %3:nation[#0{n_nationkey}]KA %2:lineitem » %1:orders[#0{o_orderkey}]KAiiif » %0:customer[#0{c_custkey}]KA » %3:nation[#0{n_nationkey}]KA %3:nation » %0:customer[#3{c_nationkey}]KA » %1:orders[#1{o_custkey}]KAiiif » %2:lineitem[#0{l_orderkey}]KAef ArrangeBy keys=[[#0{c_custkey}], [#3{c_nationkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] fk_customer_nationkey=[delta join lookup] // { arity: 8 } ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] // { arity: 16 } ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.pk_customer_custkey (delta join 1st input (full scan)) - materialize.public.fk_customer_nationkey (delta join lookup) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_orders_custkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 11 CREATE VIEW Q11 AS SELECT ps_partkey, sum(ps_supplycost * ps_availqty) AS value FROM partsupp, supplier, nation WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = 'GERMANY' GROUP BY ps_partkey having sum(ps_supplycost * ps_availqty) > ( SELECT sum(ps_supplycost * ps_availqty) * 0.0001 FROM partsupp, supplier, nation WHERE ps_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_name = 'GERMANY' ) ORDER BY value DESC; query T multiline -- Explain index on Q11 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q11; ---- materialize.public.q11_primary_idx: ArrangeBy keys=[[#0{ps_partkey}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q11 // { arity: 2 } materialize.public.q11: With cte l0 = Project (#0{ps_partkey}, #2{ps_availqty}, #3{ps_supplycost}) // { arity: 3 } Filter (#13{n_name} = "GERMANY") // { arity: 16 } Join on=(#1{ps_suppkey} = #5{s_suppkey} AND #8{s_nationkey} = #12{n_nationkey}) type=delta // { arity: 16 } implementation %0:partsupp » %1:supplier[#0{s_suppkey}]KA » %2:nation[#0{n_nationkey}]KAef %1:supplier » %2:nation[#0{n_nationkey}]KAef » %0:partsupp[#1{ps_suppkey}]KA %2:nation » %1:supplier[#3{s_nationkey}]KA » %0:partsupp[#1{ps_suppkey}]KA ArrangeBy keys=[[#1{ps_suppkey}]] // { arity: 5 } ReadIndex on=partsupp fk_partsupp_suppkey=[delta join 1st input (full scan)] // { arity: 5 } ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join lookup] fk_supplier_nationkey=[delta join lookup] // { arity: 7 } ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 } Return // { arity: 2 } Project (#0{ps_partkey}, #1{sum}) // { arity: 2 } Filter (#1{sum} > (#2{sum} * 0.0001)) // { arity: 3 } CrossJoin type=differential // { arity: 3 } implementation %1[×]UA » %0[×] ArrangeBy keys=[[]] // { arity: 2 } Reduce group_by=[#0{ps_partkey}] aggregates=[sum((#2{ps_supplycost} * integer_to_numeric(#1{ps_availqty})))] // { arity: 2 } Get l0 // { arity: 3 } ArrangeBy keys=[[]] // { arity: 1 } Reduce aggregates=[sum((#1{ps_supplycost} * integer_to_numeric(#0{ps_availqty})))] // { arity: 1 } Project (#1{ps_availqty}, #2{ps_supplycost}) // { arity: 2 } Get l0 // { arity: 3 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.pk_supplier_suppkey (delta join lookup) - materialize.public.fk_supplier_nationkey (delta join lookup) - materialize.public.fk_partsupp_suppkey (delta join 1st input (full scan)) Target cluster: quickstart EOF statement ok -- Query 12 CREATE VIEW Q12 AS SELECT l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) AS high_line_count, sum(case when o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' then 1 else 0 end) AS low_line_count FROM orders, lineitem WHERE o_orderkey = l_orderkey AND l_shipmode IN ('MAIL', 'SHIP') AND l_commitdate < l_receiptdate AND l_shipdate < l_commitdate AND l_receiptdate >= DATE '1994-01-01' AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' year GROUP BY l_shipmode ORDER BY l_shipmode; query T multiline -- Explain index on Q12 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q12; ---- materialize.public.q12_primary_idx: ArrangeBy keys=[[#0{l_shipmode}]] // { arity: 3 } ReadGlobalFromSameDataflow materialize.public.q12 // { arity: 3 } materialize.public.q12: Reduce group_by=[#1{l_shipmode}] aggregates=[sum(case when ((#0{o_orderpriority} = "2-HIGH") OR (#0{o_orderpriority} = "1-URGENT")) then 1 else 0 end), sum(case when ((#0{o_orderpriority} != "2-HIGH") AND (#0{o_orderpriority} != "1-URGENT")) then 1 else 0 end)] // { arity: 3 } Project (#5{o_orderpriority}, #23{l_shipmode}) // { arity: 2 } Filter (#21{l_receiptdate} >= 1994-01-01) AND (#19{l_shipdate} < #20{l_commitdate}) AND (#20{l_commitdate} < #21{l_receiptdate}) AND (date_to_timestamp(#21{l_receiptdate}) < 1995-01-01 00:00:00) AND ((#23{l_shipmode} = "MAIL") OR (#23{l_shipmode} = "SHIP")) // { arity: 25 } Join on=(#0{o_orderkey} = #9{l_orderkey}) type=differential // { arity: 25 } implementation %1:lineitem[#0{l_orderkey}]KAeiif » %0:orders[#0{o_orderkey}]KAeiif ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[differential join] // { arity: 9 } ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[differential join] // { arity: 16 } Used Indexes: - materialize.public.pk_orders_orderkey (differential join) - materialize.public.fk_lineitem_orderkey (differential join) Target cluster: quickstart EOF statement ok -- Query 13 CREATE VIEW Q13 AS SELECT c_count, count(*) AS custdist FROM ( SELECT c_custkey, count(o_orderkey) c_count -- workaround for no column aliases FROM customer LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_comment NOT LIKE '%special%requests%' GROUP BY c_custkey ) AS c_orders -- (c_custkey, c_count) -- no column aliases yet GROUP BY c_count ORDER BY custdist DESC, c_count DESC; query T multiline -- Explain index on Q13 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q13; ---- materialize.public.q13_primary_idx: ArrangeBy keys=[[#0{c_count}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q13 // { arity: 2 } materialize.public.q13: With cte l0 = ArrangeBy keys=[[#0{c_custkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[differential join] // { arity: 8 } cte l1 = Project (#0{c_custkey}, #8{o_orderkey}) // { arity: 2 } Filter NOT(like["%special%requests%"](varchar_to_text(#16{o_comment}))) // { arity: 17 } Join on=(#0{c_custkey} = #9{o_custkey}) type=differential // { arity: 17 } implementation %1:orders[#1{o_custkey}]KAf » %0:l0[#0{c_custkey}]KAf Get l0 // { arity: 8 } ArrangeBy keys=[[#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders fk_orders_custkey=[differential join] // { arity: 9 } Return // { arity: 2 } Reduce group_by=[#0{count_o_orderkey}] aggregates=[count(*)] // { arity: 2 } Project (#1{count_o_orderkey}) // { arity: 1 } Reduce group_by=[#0{c_custkey}] aggregates=[count(#1{o_orderkey})] // { arity: 2 } Union // { arity: 2 } Map (null) // { arity: 2 } Union // { arity: 1 } Negate // { arity: 1 } Project (#0{c_custkey}) // { arity: 1 } Join on=(#0{c_custkey} = #8{c_custkey}) type=differential // { arity: 9 } implementation %1[#0]UKA » %0:l0[#0{c_custkey}]KA Get l0 // { arity: 8 } ArrangeBy keys=[[#0{c_custkey}]] // { arity: 1 } Distinct project=[#0{c_custkey}] // { arity: 1 } Project (#0{c_custkey}) // { arity: 1 } Get l1 // { arity: 2 } Project (#0{c_custkey}) // { arity: 1 } ReadIndex on=customer pk_customer_custkey=[*** full scan ***] // { arity: 8 } Get l1 // { arity: 2 } Used Indexes: - materialize.public.pk_customer_custkey (*** full scan ***, differential join) - materialize.public.fk_orders_custkey (differential join) Target cluster: quickstart EOF statement ok -- Query 14 CREATE VIEW Q14 AS SELECT 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue FROM lineitem, part WHERE l_partkey = p_partkey AND l_shipdate >= DATE '1995-09-01' AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' month; query T multiline -- Explain index on Q14 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q14; ---- materialize.public.q14_primary_idx: ArrangeBy keys=[[#0{promo_revenue}]] // { arity: 1 } ReadGlobalFromSameDataflow materialize.public.q14 // { arity: 1 } materialize.public.q14: With cte l0 = Reduce aggregates=[sum(case when like["PROMO%"](varchar_to_text(#2{p_type})) then (#0{l_extendedprice} * (1 - #1{l_discount})) else 0 end), sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 2 } Project (#5{l_extendedprice}, #6{l_discount}, #20{p_type}) // { arity: 3 } Filter (#10{l_shipdate} >= 1995-09-01) AND (date_to_timestamp(#10{l_shipdate}) < 1995-10-01 00:00:00) // { arity: 25 } Join on=(#1{l_partkey} = #16{p_partkey}) type=differential // { arity: 25 } implementation %0:lineitem[#1{l_partkey}]KAiif » %1:part[#0{p_partkey}]KAiif ArrangeBy keys=[[#1{l_partkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_partkey=[differential join] // { arity: 16 } ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 } Return // { arity: 1 } Project (#2) // { arity: 1 } Map (((100 * #0{sum}) / #1{sum})) // { arity: 3 } Union // { arity: 2 } Get l0 // { arity: 2 } Map (null, null) // { arity: 2 } Union // { arity: 0 } Negate // { arity: 0 } Project () // { arity: 0 } Get l0 // { arity: 2 } Constant // { arity: 0 } - () Used Indexes: - materialize.public.pk_part_partkey (differential join) - materialize.public.fk_lineitem_partkey (differential join) Target cluster: quickstart EOF statement ok create view revenue (supplier_no, total_revenue) as SELECT l_suppkey, sum(l_extendedprice * (1 - l_discount)) FROM lineitem WHERE l_shipdate >= DATE '1996-01-01' AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' month GROUP BY l_suppkey statement ok -- Query 15 CREATE VIEW Q15 AS SELECT s_suppkey, s_name, s_address, s_phone, total_revenue FROM supplier, revenue WHERE s_suppkey = supplier_no AND total_revenue = ( SELECT max(total_revenue) FROM revenue ) ORDER BY s_suppkey; query T multiline -- Explain index on Q15 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q15; ---- materialize.public.q15_primary_idx: ArrangeBy keys=[[#0{s_suppkey}..=#4{total_revenue}]] // { arity: 5 } ReadGlobalFromSameDataflow materialize.public.q15 // { arity: 5 } materialize.public.q15: With cte l0 = Reduce group_by=[#0{l_suppkey}] aggregates=[sum((#1{l_extendedprice} * (1 - #2{l_discount})))] // { arity: 2 } Project (#2{l_suppkey}, #5{l_extendedprice}, #6{l_discount}) // { arity: 3 } Filter (#10{l_shipdate} >= 1996-01-01) AND (date_to_timestamp(#10{l_shipdate}) < 1996-04-01 00:00:00) // { arity: 16 } ReadIndex on=lineitem pk_lineitem_orderkey_linenumber=[*** full scan ***] // { arity: 16 } Return // { arity: 5 } Project (#0{s_suppkey}..=#2{s_address}, #4{s_phone}, #8{sum}) // { arity: 5 } Join on=(#0{s_suppkey} = #7{l_suppkey} AND #8{sum} = #9{max_sum}) type=delta // { arity: 10 } implementation %0:supplier » %1:l0[#0]UKA » %2[#0]UK %1:l0 » %2[#0]UK » %0:supplier[#0{s_suppkey}]KA %2 » %1:l0[#1{total_revenue}]K » %0:supplier[#0{s_suppkey}]KA ArrangeBy keys=[[#0{s_suppkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join 1st input (full scan)] // { arity: 7 } ArrangeBy keys=[[#0{l_suppkey}], [#1{sum}]] // { arity: 2 } Get l0 // { arity: 2 } ArrangeBy keys=[[#0{max_sum}]] // { arity: 1 } Reduce aggregates=[max(#0{sum})] // { arity: 1 } Project (#1{sum}) // { arity: 1 } Get l0 // { arity: 2 } Used Indexes: - materialize.public.pk_supplier_suppkey (delta join 1st input (full scan)) - materialize.public.pk_lineitem_orderkey_linenumber (*** full scan ***) Target cluster: quickstart EOF statement ok -- Query 16 CREATE VIEW Q16 AS SELECT p_brand, p_type, p_size, count(DISTINCT ps_suppkey) AS supplier_cnt FROM partsupp, part WHERE p_partkey = ps_partkey AND p_brand <> 'Brand#45' AND p_type NOT LIKE 'MEDIUM POLISHED%' AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9) AND ps_suppkey NOT IN ( SELECT s_suppkey FROM supplier WHERE s_comment like '%Customer%Complaints%' ) GROUP BY p_brand, p_type, p_size ORDER BY supplier_cnt DESC, p_brand, p_type, p_size; query T multiline -- Explain index on Q16 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q16; ---- materialize.public.q16_primary_idx: ArrangeBy keys=[[#0{p_brand}..=#2{p_size}]] // { arity: 4 } ReadGlobalFromSameDataflow materialize.public.q16 // { arity: 4 } materialize.public.q16: With cte l0 = Project (#1{ps_suppkey}, #8{p_brand}..=#10{p_size}) // { arity: 4 } Filter (#8{p_brand} != "Brand#45") AND NOT(like["MEDIUM POLISHED%"](varchar_to_text(#9{p_type}))) AND ((#10{p_size} = 3) OR (#10{p_size} = 9) OR (#10{p_size} = 14) OR (#10{p_size} = 19) OR (#10{p_size} = 23) OR (#10{p_size} = 36) OR (#10{p_size} = 45) OR (#10{p_size} = 49)) // { arity: 14 } Join on=(#0{ps_partkey} = #5{p_partkey}) type=differential // { arity: 14 } implementation %1:part[#0{p_partkey}]KAef » %0:partsupp[#0{ps_partkey}]KAef ArrangeBy keys=[[#0{ps_partkey}]] // { arity: 5 } ReadIndex on=partsupp fk_partsupp_partkey=[differential join] // { arity: 5 } ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 } cte l1 = Distinct project=[#0{ps_suppkey}] // { arity: 1 } Project (#0{ps_suppkey}) // { arity: 1 } Get l0 // { arity: 4 } Return // { arity: 4 } Reduce group_by=[#1{p_brand}..=#3{p_size}] aggregates=[count(distinct #0{ps_suppkey})] // { arity: 4 } Project (#0{ps_suppkey}..=#3{p_size}) // { arity: 4 } Join on=(#0{ps_suppkey} = #4{ps_suppkey}) type=differential // { arity: 5 } implementation %0:l0[#0]K » %1[#0]K ArrangeBy keys=[[#0{ps_suppkey}]] // { arity: 4 } Get l0 // { arity: 4 } ArrangeBy keys=[[#0{ps_suppkey}]] // { arity: 1 } Union // { arity: 1 } Negate // { arity: 1 } Distinct project=[#0{ps_suppkey}] // { arity: 1 } Project (#0{ps_suppkey}) // { arity: 1 } Filter ((#1{s_suppkey}) IS NULL OR (#0{ps_suppkey} = #1{s_suppkey})) // { arity: 2 } CrossJoin type=differential // { arity: 2 } implementation %1:supplier[×]lf » %0:l1[×]lf ArrangeBy keys=[[]] // { arity: 1 } Get l1 // { arity: 1 } ArrangeBy keys=[[]] // { arity: 1 } Project (#0{s_suppkey}) // { arity: 1 } Filter like["%Customer%Complaints%"](varchar_to_text(#6{s_comment})) // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[*** full scan ***] // { arity: 7 } Get l1 // { arity: 1 } Used Indexes: - materialize.public.pk_part_partkey (differential join) - materialize.public.pk_supplier_suppkey (*** full scan ***) - materialize.public.fk_partsupp_partkey (differential join) Target cluster: quickstart EOF statement ok -- Query 17 CREATE VIEW Q17 AS SELECT sum(l_extendedprice) / 7.0 AS avg_yearly FROM lineitem, part WHERE p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container = 'MED BOX' AND l_quantity < ( SELECT 0.2 * avg(l_quantity) FROM lineitem WHERE l_partkey = p_partkey ); query T multiline -- Explain index on Q17 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q17; ---- materialize.public.q17_primary_idx: ArrangeBy keys=[[#0{avg_yearly}]] // { arity: 1 } ReadGlobalFromSameDataflow materialize.public.q17 // { arity: 1 } materialize.public.q17: With cte l0 = ArrangeBy keys=[[#1{l_partkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_partkey=[differential join] // { arity: 16 } cte l1 = Project (#1{l_partkey}, #4{l_quantity}, #5{l_extendedprice}) // { arity: 3 } Filter (#19{p_brand} = "Brand#23") AND (#22{p_container} = "MED BOX") // { arity: 25 } Join on=(#1{l_partkey} = #16{p_partkey}) type=differential // { arity: 25 } implementation %1:part[#0{p_partkey}]KAef » %0:l0[#1{l_partkey}]KAef Get l0 // { arity: 16 } ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 } cte l2 = Reduce aggregates=[sum(#0{l_extendedprice})] // { arity: 1 } Project (#2{l_extendedprice}) // { arity: 1 } Filter (#1{l_quantity} < (0.2 * (#4{sum_l_quantity} / bigint_to_numeric(case when (#5{count} = 0) then null else #5{count} end)))) // { arity: 6 } Join on=(#0{l_partkey} = #3{l_partkey}) type=differential // { arity: 6 } implementation %1[#0]UKA » %0:l1[#0]K ArrangeBy keys=[[#0{l_partkey}]] // { arity: 3 } Get l1 // { arity: 3 } ArrangeBy keys=[[#0{l_partkey}]] // { arity: 3 } Reduce group_by=[#0{l_partkey}] aggregates=[sum(#1{l_quantity}), count(*)] // { arity: 3 } Project (#0{l_partkey}, #5{l_quantity}) // { arity: 2 } Join on=(#0{l_partkey} = #2{l_partkey}) type=differential // { arity: 17 } implementation %0[#0{p_partkey}]UKA » %1:l0[#1{l_partkey}]KA ArrangeBy keys=[[#0{l_partkey}]] // { arity: 1 } Distinct project=[#0{l_partkey}] // { arity: 1 } Project (#0{l_partkey}) // { arity: 1 } Get l1 // { arity: 3 } Get l0 // { arity: 16 } Return // { arity: 1 } Project (#1) // { arity: 1 } Map ((#0{sum_l_extendedprice} / 7)) // { arity: 2 } Union // { arity: 1 } Get l2 // { arity: 1 } Map (null) // { arity: 1 } Union // { arity: 0 } Negate // { arity: 0 } Project () // { arity: 0 } Get l2 // { arity: 1 } Constant // { arity: 0 } - () Used Indexes: - materialize.public.pk_part_partkey (differential join) - materialize.public.fk_lineitem_partkey (differential join) Target cluster: quickstart EOF statement ok -- Query 18 CREATE VIEW Q18 AS SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) FROM customer, orders, lineitem WHERE o_orderkey IN ( SELECT l_orderkey FROM lineitem GROUP BY l_orderkey having sum(l_quantity) > 300 ) AND c_custkey = o_custkey AND o_orderkey = l_orderkey GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice ORDER BY o_totalprice DESC, o_orderdate; query T multiline -- Explain index on Q18 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q18; ---- materialize.public.q18_primary_idx: ArrangeBy keys=[[#0{c_name}..=#4{o_totalprice}]] // { arity: 6 } ReadGlobalFromSameDataflow materialize.public.q18 // { arity: 6 } materialize.public.q18: With cte l0 = ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[differential join, delta join lookup] // { arity: 16 } cte l1 = Project (#0{c_custkey}, #1{c_name}, #8{o_orderkey}, #11{o_totalprice}, #12{o_orderdate}, #21{l_quantity}) // { arity: 6 } Join on=(#0{c_custkey} = #9{o_custkey} AND #8{o_orderkey} = #17{l_orderkey}) type=delta // { arity: 33 } implementation %0:customer » %1:orders[#1{o_custkey}]KA » %2:l0[#0{l_orderkey}]KA %1:orders » %0:customer[#0{c_custkey}]KA » %2:l0[#0{l_orderkey}]KA %2:l0 » %1:orders[#0{o_orderkey}]KA » %0:customer[#0{c_custkey}]KA ArrangeBy keys=[[#0{c_custkey}]] // { arity: 8 } ReadIndex on=customer pk_customer_custkey=[delta join 1st input (full scan)] // { arity: 8 } ArrangeBy keys=[[#0{o_orderkey}], [#1{o_custkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] fk_orders_custkey=[delta join lookup] // { arity: 9 } Get l0 // { arity: 16 } Return // { arity: 6 } Reduce group_by=[#1{c_name}, #0{c_custkey}, #2{o_orderkey}, #4{o_orderdate}, #3{o_totalprice}] aggregates=[sum(#5{l_quantity})] // { arity: 6 } Project (#0{c_custkey}..=#5{l_quantity}) // { arity: 6 } Filter (#7{sum_l_quantity} > 300) // { arity: 8 } Join on=(#2{o_orderkey} = #6{o_orderkey}) type=differential // { arity: 8 } implementation %1[#0]UKAif » %0:l1[#2]Kif ArrangeBy keys=[[#2{o_orderkey}]] // { arity: 6 } Get l1 // { arity: 6 } ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 2 } Reduce group_by=[#0{o_orderkey}] aggregates=[sum(#1{l_quantity})] // { arity: 2 } Project (#0{o_orderkey}, #5{l_quantity}) // { arity: 2 } Join on=(#0{o_orderkey} = #1{l_orderkey}) type=differential // { arity: 17 } implementation %0[#0]UKA » %1:l0[#0{l_orderkey}]KA ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 1 } Distinct project=[#0{o_orderkey}] // { arity: 1 } Project (#2{o_orderkey}) // { arity: 1 } Get l1 // { arity: 6 } Get l0 // { arity: 16 } Used Indexes: - materialize.public.pk_customer_custkey (delta join 1st input (full scan)) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_orders_custkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (differential join, delta join lookup) Target cluster: quickstart EOF statement ok -- Query 19 CREATE VIEW Q19 AS SELECT sum(l_extendedprice* (1 - l_discount)) AS revenue FROM lineitem, part WHERE ( p_partkey = l_partkey AND p_brand = 'Brand#12' AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= CAST (1 AS smallint) AND l_quantity <= CAST (1 + 10 AS smallint) AND p_size BETWEEN CAST (1 AS smallint) AND CAST (5 AS smallint) AND l_shipmode IN ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= CAST (10 AS smallint) AND l_quantity <= CAST (10 + 10 AS smallint) AND p_size BETWEEN CAST (1 AS smallint) AND CAST (10 AS smallint) AND l_shipmode IN ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey AND p_brand = 'Brand#34' AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= CAST (20 AS smallint) AND l_quantity <= CAST (20 + 10 AS smallint) AND p_size BETWEEN CAST (1 AS smallint) AND CAST (15 AS smallint) AND l_shipmode IN ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ); query T multiline -- Explain index on Q19 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q19; ---- materialize.public.q19_primary_idx: ArrangeBy keys=[[#0{revenue}]] // { arity: 1 } ReadGlobalFromSameDataflow materialize.public.q19 // { arity: 1 } materialize.public.q19: With cte l0 = Reduce aggregates=[sum((#0{l_extendedprice} * (1 - #1{l_discount})))] // { arity: 1 } Project (#5{l_extendedprice}, #6{l_discount}) // { arity: 2 } Filter (#13{l_shipinstruct} = "DELIVER IN PERSON") AND (#21{p_size} >= 1) AND ((#14{l_shipmode} = "AIR") OR (#14{l_shipmode} = "AIR REG")) AND ((#25 AND #26) OR (#27 AND #28) OR (#29 AND #30)) AND ((#31 AND #32 AND #33) OR (#34 AND #35 AND #36) OR (#37 AND #38 AND #39)) AND ((#25 AND #26 AND #34 AND #35 AND #36) OR (#27 AND #28 AND #37 AND #38 AND #39) OR (#29 AND #30 AND #31 AND #32 AND #33)) // { arity: 40 } Map ((#4{l_quantity} <= 20), (#4{l_quantity} >= 10), (#4{l_quantity} <= 30), (#4{l_quantity} >= 20), (#4{l_quantity} <= 11), (#4{l_quantity} >= 1), (#19{p_brand} = "Brand#12"), (#21{p_size} <= 5), ((#22{p_container} = "SM BOX") OR (#22{p_container} = "SM PKG") OR (#22{p_container} = "SM CASE") OR (#22{p_container} = "SM PACK")), (#19{p_brand} = "Brand#23"), (#21{p_size} <= 10), ((#22{p_container} = "MED BAG") OR (#22{p_container} = "MED BOX") OR (#22{p_container} = "MED PKG") OR (#22{p_container} = "MED PACK")), (#19{p_brand} = "Brand#34"), (#21{p_size} <= 15), ((#22{p_container} = "LG BOX") OR (#22{p_container} = "LG PKG") OR (#22{p_container} = "LG CASE") OR (#22{p_container} = "LG PACK"))) // { arity: 40 } Join on=(#1{l_partkey} = #16{p_partkey}) type=differential // { arity: 25 } implementation %1:part[#0{p_partkey}]KAeiiif » %0:lineitem[#1{l_partkey}]KAeiiiiif ArrangeBy keys=[[#1{l_partkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_partkey=[differential join] // { arity: 16 } ArrangeBy keys=[[#0{p_partkey}]] // { arity: 9 } ReadIndex on=part pk_part_partkey=[differential join] // { arity: 9 } Return // { arity: 1 } Union // { arity: 1 } Get l0 // { arity: 1 } Map (null) // { arity: 1 } Union // { arity: 0 } Negate // { arity: 0 } Project () // { arity: 0 } Get l0 // { arity: 1 } Constant // { arity: 0 } - () Used Indexes: - materialize.public.pk_part_partkey (differential join) - materialize.public.fk_lineitem_partkey (differential join) Target cluster: quickstart EOF statement ok -- Query 20 CREATE VIEW Q20 AS SELECT s_name, s_address FROM supplier, nation WHERE s_suppkey IN ( SELECT ps_suppkey FROM partsupp WHERE ps_partkey IN ( SELECT p_partkey FROM part WHERE p_name like 'forest%' ) AND ps_availqty > ( SELECT 0.5 * sum(l_quantity) FROM lineitem WHERE l_partkey = ps_partkey AND l_suppkey = ps_suppkey AND l_shipdate >= DATE '1995-01-01' AND l_shipdate < DATE '1995-01-01' + INTERVAL '1' year ) ) AND s_nationkey = n_nationkey AND n_name = 'CANADA' ORDER BY s_name; query T multiline -- Explain index on Q20 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q20; ---- materialize.public.q20_primary_idx: ArrangeBy keys=[[#0{s_name}, #1{s_address}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q20 // { arity: 2 } materialize.public.q20: With cte l0 = Project (#0{s_suppkey}..=#2{s_address}) // { arity: 3 } Filter (#8{n_name} = "CANADA") // { arity: 11 } Join on=(#3{s_nationkey} = #7{n_nationkey}) type=differential // { arity: 11 } implementation %1:nation[#0{n_nationkey}]KAef » %0:supplier[#3{s_nationkey}]KAef ArrangeBy keys=[[#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier fk_supplier_nationkey=[differential join] // { arity: 7 } ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[differential join] // { arity: 4 } cte l1 = Project (#0{s_suppkey}..=#3{ps_availqty}) // { arity: 4 } Join on=(#1{ps_partkey} = #4{p_partkey}) type=delta // { arity: 5 } implementation %0 » %1:partsupp[×] » %2[#0]UKA %1:partsupp » %2[#0]UKA » %0[×] %2 » %1:partsupp[#0{ps_partkey}]KA » %0[×] ArrangeBy keys=[[]] // { arity: 1 } Distinct project=[#0{s_suppkey}] // { arity: 1 } Project (#0{s_suppkey}) // { arity: 1 } Get l0 // { arity: 3 } ArrangeBy keys=[[], [#0{ps_partkey}]] // { arity: 3 } Project (#0{ps_partkey}..=#2{ps_availqty}) // { arity: 3 } ReadIndex on=partsupp pk_partsupp_partkey_suppkey=[*** full scan ***] // { arity: 5 } ArrangeBy keys=[[#0{p_partkey}]] // { arity: 1 } Distinct project=[#0{p_partkey}] // { arity: 1 } Project (#0{p_partkey}) // { arity: 1 } Filter (#0{p_partkey}) IS NOT NULL AND like["forest%"](varchar_to_text(#1{p_name})) // { arity: 9 } ReadIndex on=part pk_part_partkey=[*** full scan ***] // { arity: 9 } Return // { arity: 2 } Project (#1{s_name}, #2{s_address}) // { arity: 2 } Join on=(#0{s_suppkey} = #3{s_suppkey}) type=differential // { arity: 4 } implementation %1[#0]UKA » %0:l0[#0]K ArrangeBy keys=[[#0{s_suppkey}]] // { arity: 3 } Get l0 // { arity: 3 } ArrangeBy keys=[[#0{s_suppkey}]] // { arity: 1 } Distinct project=[#0{s_suppkey}] // { arity: 1 } Project (#0{s_suppkey}) // { arity: 1 } Filter (integer_to_numeric(#2{ps_availqty}) > #5) // { arity: 6 } Join on=(#0{s_suppkey} = #4{ps_suppkey} AND #1{ps_partkey} = #3{ps_partkey}) type=differential // { arity: 6 } implementation %1[#1, #0]UKK » %0:l1[#0{s_suppkey}, #1]KKf ArrangeBy keys=[[#0{s_suppkey}, #1{ps_partkey}]] // { arity: 3 } Project (#0{s_suppkey}, #1{ps_partkey}, #3{ps_availqty}) // { arity: 3 } Filter (#0{s_suppkey} = #2{ps_suppkey}) // { arity: 4 } Get l1 // { arity: 4 } ArrangeBy keys=[[#1{ps_suppkey}, #0{ps_partkey}]] // { arity: 3 } Project (#0{ps_partkey}, #1{ps_suppkey}, #3) // { arity: 3 } Map ((0.5 * #2{sum_l_quantity})) // { arity: 4 } Reduce group_by=[#0{ps_partkey}, #1{ps_suppkey}] aggregates=[sum(#2{l_quantity})] // { arity: 3 } Project (#0{ps_partkey}, #1{ps_suppkey}, #6{l_quantity}) // { arity: 3 } Filter (#12{l_shipdate} >= 1995-01-01) AND (date_to_timestamp(#12{l_shipdate}) < 1996-01-01 00:00:00) // { arity: 18 } Join on=(#0{ps_partkey} = #3{l_partkey} AND #1{ps_suppkey} = #4{l_suppkey}) type=differential // { arity: 18 } implementation %0[#0{ps_partkey}, #1{ps_suppkey}]UKKA » %1:lineitem[#1{l_partkey}, #2{l_suppkey}]KKAiif ArrangeBy keys=[[#0{ps_partkey}, #1{ps_suppkey}]] // { arity: 2 } Distinct project=[#0{ps_partkey}, #1{ps_suppkey}] // { arity: 2 } Project (#1{ps_partkey}, #2{ps_suppkey}) // { arity: 2 } Get l1 // { arity: 4 } ArrangeBy keys=[[#1{l_partkey}, #2{l_suppkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_partsuppkey=[differential join] // { arity: 16 } Used Indexes: - materialize.public.pk_nation_nationkey (differential join) - materialize.public.pk_part_partkey (*** full scan ***) - materialize.public.fk_supplier_nationkey (differential join) - materialize.public.pk_partsupp_partkey_suppkey (*** full scan ***) - materialize.public.fk_lineitem_partsuppkey (differential join) Target cluster: quickstart EOF statement ok -- Query 21 CREATE VIEW Q21 AS SELECT s_name, count(*) AS numwait FROM supplier, lineitem l1, orders, nation WHERE s_suppkey = l1.l_suppkey AND o_orderkey = l1.l_orderkey AND o_orderstatus = 'F' AND l1.l_receiptdate > l1.l_commitdate AND EXISTS ( SELECT * FROM lineitem l2 WHERE l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey ) AND not EXISTS ( SELECT * FROM lineitem l3 WHERE l3.l_orderkey = l1.l_orderkey AND l3.l_suppkey <> l1.l_suppkey AND l3.l_receiptdate > l3.l_commitdate ) AND s_nationkey = n_nationkey AND n_name = 'SAUDI ARABIA' GROUP BY s_name ORDER BY numwait DESC, s_name; query T multiline -- Explain index on Q21 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q21; ---- materialize.public.q21_primary_idx: ArrangeBy keys=[[#0{s_name}]] // { arity: 2 } ReadGlobalFromSameDataflow materialize.public.q21 // { arity: 2 } materialize.public.q21: With cte l0 = Project (#0{s_suppkey}, #1{s_name}, #7{l_orderkey}) // { arity: 3 } Filter (#25{o_orderstatus} = "F") AND (#33{n_name} = "SAUDI ARABIA") AND (#19{l_receiptdate} > #18{l_commitdate}) // { arity: 36 } Join on=(#0{s_suppkey} = #9{l_suppkey} AND #3{s_nationkey} = #32{n_nationkey} AND #7{l_orderkey} = #23{o_orderkey}) type=delta // { arity: 36 } implementation %0:supplier » %3:nation[#0{n_nationkey}]KAef » %1:lineitem[#2{l_suppkey}]KAf » %2:orders[#0{o_orderkey}]KAef %1:lineitem » %2:orders[#0{o_orderkey}]KAef » %0:supplier[#0{s_suppkey}]KA » %3:nation[#0{n_nationkey}]KAef %2:orders » %1:lineitem[#0{l_orderkey}]KAf » %0:supplier[#0{s_suppkey}]KA » %3:nation[#0{n_nationkey}]KAef %3:nation » %0:supplier[#3{s_nationkey}]KA » %1:lineitem[#2{l_suppkey}]KAf » %2:orders[#0{o_orderkey}]KAef ArrangeBy keys=[[#0{s_suppkey}], [#3{s_nationkey}]] // { arity: 7 } ReadIndex on=supplier pk_supplier_suppkey=[delta join 1st input (full scan)] fk_supplier_nationkey=[delta join lookup] // { arity: 7 } ArrangeBy keys=[[#0{l_orderkey}], [#2{l_suppkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[delta join lookup] fk_lineitem_suppkey=[delta join lookup] // { arity: 16 } ArrangeBy keys=[[#0{o_orderkey}]] // { arity: 9 } ReadIndex on=orders pk_orders_orderkey=[delta join lookup] // { arity: 9 } ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 4 } ReadIndex on=nation pk_nation_nationkey=[delta join lookup] // { arity: 4 } cte l1 = ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 16 } ReadIndex on=lineitem fk_lineitem_orderkey=[differential join] // { arity: 16 } cte l2 = Project (#0{s_suppkey}..=#2{l_orderkey}) // { arity: 3 } Join on=(#0{s_suppkey} = #4{s_suppkey} AND #2{l_orderkey} = #3{l_orderkey}) type=differential // { arity: 5 } implementation %1[#1, #0]UKK » %0:l0[#0, #2]KK ArrangeBy keys=[[#0{s_suppkey}, #2{l_orderkey}]] // { arity: 3 } Get l0 // { arity: 3 } ArrangeBy keys=[[#1{s_suppkey}, #0{l_orderkey}]] // { arity: 2 } Distinct project=[#0{l_orderkey}, #1{s_suppkey}] // { arity: 2 } Project (#0{l_orderkey}, #1{s_suppkey}) // { arity: 2 } Filter (#1{s_suppkey} != #4{l_suppkey}) // { arity: 18 } Join on=(#0{l_orderkey} = #2{l_orderkey}) type=differential // { arity: 18 } implementation %1:l1[#0{l_orderkey}]KA » %0[#0{l_orderkey}]K ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 2 } Distinct project=[#1{l_orderkey}, #0{s_suppkey}] // { arity: 2 } Project (#0{s_suppkey}, #2{l_orderkey}) // { arity: 2 } Get l0 // { arity: 3 } Get l1 // { arity: 16 } cte l3 = Distinct project=[#1{l_orderkey}, #0{s_suppkey}] // { arity: 2 } Project (#0{s_suppkey}, #2{l_orderkey}) // { arity: 2 } Get l2 // { arity: 3 } Return // { arity: 2 } Reduce group_by=[#0{s_name}] aggregates=[count(*)] // { arity: 2 } Project (#1{s_name}) // { arity: 1 } Join on=(#0{s_suppkey} = #4{s_suppkey} AND #2{l_orderkey} = #3{l_orderkey}) type=differential // { arity: 5 } implementation %0:l2[#2, #0]KK » %1[#0, #1]KK ArrangeBy keys=[[#2{l_orderkey}, #0{s_suppkey}]] // { arity: 3 } Get l2 // { arity: 3 } ArrangeBy keys=[[#0{l_orderkey}, #1{s_suppkey}]] // { arity: 2 } Union // { arity: 2 } Negate // { arity: 2 } Distinct project=[#0{l_orderkey}, #1{s_suppkey}] // { arity: 2 } Project (#0{l_orderkey}, #1{s_suppkey}) // { arity: 2 } Filter (#1{s_suppkey} != #4{l_suppkey}) AND (#14{l_receiptdate} > #13{l_commitdate}) // { arity: 18 } Join on=(#0{l_orderkey} = #2{l_orderkey}) type=differential // { arity: 18 } implementation %1:l1[#0{l_orderkey}]KAf » %0:l3[#0{l_orderkey}]Kf ArrangeBy keys=[[#0{l_orderkey}]] // { arity: 2 } Get l3 // { arity: 2 } Get l1 // { arity: 16 } Get l3 // { arity: 2 } Used Indexes: - materialize.public.pk_nation_nationkey (delta join lookup) - materialize.public.pk_supplier_suppkey (delta join 1st input (full scan)) - materialize.public.fk_supplier_nationkey (delta join lookup) - materialize.public.pk_orders_orderkey (delta join lookup) - materialize.public.fk_lineitem_orderkey (differential join, delta join lookup) - materialize.public.fk_lineitem_suppkey (delta join lookup) Target cluster: quickstart EOF statement ok -- Query 22 CREATE VIEW Q22 AS SELECT cntrycode, count(*) AS numcust, sum(c_acctbal) AS totacctbal FROM ( SELECT substring(c_phone, 1, 2) AS cntrycode, c_acctbal FROM customer WHERE substring(c_phone, 1, 2) IN ('13', '31', '23', '29', '30', '18', '17') AND c_acctbal > ( SELECT avg(c_acctbal) FROM customer WHERE c_acctbal > 0.00 AND substring(c_phone, 1, 2) IN ( '13', '31', '23', '29', '30', '18', '17' ) ) AND NOT EXISTS( SELECT * FROM orders WHERE o_custkey = c_custkey ) ) AS custsale GROUP BY cntrycode ORDER BY cntrycode; query T multiline -- Explain index on Q22 EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR CREATE DEFAULT INDEX ON Q22; ---- materialize.public.q22_primary_idx: ArrangeBy keys=[[#0{cntrycode}]] // { arity: 3 } ReadGlobalFromSameDataflow materialize.public.q22 // { arity: 3 } materialize.public.q22: With cte l0 = Project (#0{c_custkey}, #4{c_phone}, #5{c_acctbal}, #8) // { arity: 4 } Map (substr(char_to_text(#4{c_phone}), 1, 2)) // { arity: 9 } ReadIndex on=customer pk_customer_custkey=[*** full scan ***] // { arity: 8 } cte l1 = Project (#0{c_custkey}..=#2{c_acctbal}) // { arity: 3 } Filter (#2{c_acctbal} > (#3{sum_c_acctbal} / bigint_to_numeric(case when (#4{count} = 0) then null else #4{count} end))) // { arity: 5 } CrossJoin type=differential // { arity: 5 } implementation %1[×]UA » %0:l0[×]ef ArrangeBy keys=[[]] // { arity: 3 } Project (#0{c_custkey}..=#2{c_acctbal}) // { arity: 3 } Filter ((#3 = "13") OR (#3 = "17") OR (#3 = "18") OR (#3 = "23") OR (#3 = "29") OR (#3 = "30") OR (#3 = "31")) // { arity: 4 } Get l0 // { arity: 4 } ArrangeBy keys=[[]] // { arity: 2 } Reduce aggregates=[sum(#0{c_acctbal}), count(*)] // { arity: 2 } Project (#2{c_acctbal}) // { arity: 1 } Filter (#2{c_acctbal} > 0) AND ((#3 = "13") OR (#3 = "17") OR (#3 = "18") OR (#3 = "23") OR (#3 = "29") OR (#3 = "30") OR (#3 = "31")) // { arity: 4 } Get l0 // { arity: 4 } cte l2 = Distinct project=[#0{c_custkey}] // { arity: 1 } Project (#0{c_custkey}) // { arity: 1 } Get l1 // { arity: 3 } Return // { arity: 3 } Reduce group_by=[substr(char_to_text(#0{c_phone}), 1, 2)] aggregates=[count(*), sum(#1{c_acctbal})] // { arity: 3 } Project (#1{c_phone}, #2{c_acctbal}) // { arity: 2 } Join on=(#0{c_custkey} = #3{c_custkey}) type=differential // { arity: 4 } implementation %0:l1[#0]K » %1[#0]K ArrangeBy keys=[[#0{c_custkey}]] // { arity: 3 } Get l1 // { arity: 3 } ArrangeBy keys=[[#0{c_custkey}]] // { arity: 1 } Union // { arity: 1 } Negate // { arity: 1 } Project (#0{c_custkey}) // { arity: 1 } Join on=(#0{c_custkey} = #1{o_custkey}) type=differential // { arity: 2 } implementation %0:l2[#0]UKA » %1[#0]UKA ArrangeBy keys=[[#0{c_custkey}]] // { arity: 1 } Get l2 // { arity: 1 } ArrangeBy keys=[[#0{o_custkey}]] // { arity: 1 } Distinct project=[#0{o_custkey}] // { arity: 1 } Project (#1{o_custkey}) // { arity: 1 } ReadIndex on=orders pk_orders_orderkey=[*** full scan ***] // { arity: 9 } Get l2 // { arity: 1 } Used Indexes: - materialize.public.pk_customer_custkey (*** full scan ***) - materialize.public.pk_orders_orderkey (*** full scan ***) Target cluster: quickstart EOF