Materialize/test/sqllogictest/chbench.slt

# 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.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
CREATE TABLE warehouse (
    w_id integer,
    w_name char(10),
    w_street_1 char(20),
    w_street_2 char(20),
    w_city char(20),
    w_state char(2),
    w_zip char(9),
    w_tax decimal(4, 4),
    w_ytd decimal(12, 2),
    PRIMARY KEY (w_id)
)

statement ok
CREATE TABLE district (
    -- should be smallint, see database-issues#1291
    d_id integer,
    d_w_id integer,
    d_name char(10),
    d_street_1 char(20),
    d_street_2 char(20),
    d_city char(20),
    d_state char(2),
    d_zip char(9),
    d_tax decimal(4, 4),
    d_ytd decimal(12, 2),
    d_next_o_id integer,
    PRIMARY KEY (d_w_id, d_id)
)

statement ok
CREATE INDEX fk_district_warehouse ON district (d_w_id ASC)

statement ok
CREATE TABLE customer (
    -- should be smallint, see database-issues#1291
    c_id integer,
    -- should be smallint, see database-issues#1291
    c_d_id integer,
    c_w_id integer,
    c_first char(16),
    c_middle char(2),
    c_last char(16),
    c_street_1 char(20),
    c_street_2 char(20),
    c_city char(20),
    c_state char(2),
    c_zip char(9),
    c_phone char(16),
    c_since DATE,
    c_credit char(2),
    c_credit_lim decimal(12, 2),
    c_discount decimal(4, 4),
    c_balance decimal(12, 2),
    c_ytd_payment decimal(12, 2),
    -- should be smallint, see database-issues#1291
    c_payment_cnt integer,
    -- should be smallint, see database-issues#1291
    c_delivery_cnt integer,
    c_data text,
    c_n_nationkey integer,
    PRIMARY KEY(c_w_id, c_d_id, c_id)
)

statement ok
CREATE INDEX fk_customer_district ON customer(c_w_id ASC, c_d_id ASC)

statement ok
CREATE INDEX fk_customer_nation ON customer(c_n_nationkey ASC)

statement ok
CREATE TABLE history (
    -- should be smallint, see database-issues#1291
    h_c_id integer,
    -- should be smallint, see database-issues#1291
    h_c_d_id integer,
    h_c_w_id integer,
    -- should be smallint, see database-issues#1291
    h_d_id integer,
    h_w_id integer,
    h_date date,
    h_amount decimal(6, 2),
    h_data char(24)
)

statement ok
CREATE INDEX fk_history_customer ON history (h_c_w_id ASC, h_c_d_id ASC, h_c_id ASC)

statement ok
CREATE INDEX fk_history_district ON history (h_w_id ASC, h_d_id ASC)

statement ok
CREATE TABLE neworder (
    no_o_id integer,
    -- should be smallint, see database-issues#1291
    no_d_id integer,
    no_w_id integer,
    PRIMARY KEY (no_w_id, no_d_id, no_o_id)
)

statement ok
CREATE TABLE "order" (
    o_id integer,
    -- should be smallint, see database-issues#1291
    o_d_id integer,
    o_w_id integer,
    -- should be smallint, see database-issues#1291
    o_c_id integer,
    o_entry_d date,
    -- should be smallint, see database-issues#1291
    o_carrier_id integer,
    -- should be smallint, see database-issues#1291
    o_ol_cnt integer,
    -- should be smallint, see database-issues#1291
    o_all_local integer,
    PRIMARY KEY (o_w_id, o_d_id, o_id)
)

statement ok
CREATE INDEX fk_order_customer ON order (o_w_id ASC, o_d_id ASC, o_c_id ASC)

statement ok
CREATE TABLE orderline (
    ol_o_id integer,
    -- should be smallint, see database-issues#1291
    ol_d_id integer,
    ol_w_id integer,
    -- should be smallint, see database-issues#1291
    ol_number integer,
    ol_i_id integer,
    ol_supply_w_id integer,
    ol_delivery_d date,
    -- should be smallint, see database-issues#1291
    ol_quantity integer,
    ol_amount decimal(6, 2),
    ol_dist_info char(24),
    PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)

statement ok
CREATE INDEX fk_orderline_order ON orderline (ol_w_id ASC, ol_d_id ASC, ol_o_id ASC)

statement ok
CREATE INDEX fk_orderline_stock ON orderline (ol_supply_w_id ASC, ol_i_id ASC)

statement ok
CREATE INDEX fk_orderline_item ON orderline (ol_i_id ASC)

statement ok
CREATE TABLE item (
    i_id integer,
    -- should be smallint, see database-issues#1291
    i_im_id integer,
    i_name char(24),
    i_price decimal(5, 2),
    i_data char(50),
    PRIMARY KEY (i_id)
)

statement ok
CREATE TABLE stock (
    s_i_id integer,
    s_w_id integer,
    -- smallint
    s_quantity integer,
    s_dist_01 char(24),
    s_dist_02 char(24),
    s_dist_03 char(24),
    s_dist_04 char(24),
    s_dist_05 char(24),
    s_dist_06 char(24),
    s_dist_07 char(24),
    s_dist_08 char(24),
    s_dist_09 char(24),
    s_dist_10 char(24),
    s_ytd integer,
    -- should be smallint, see database-issues#1291
    s_order_cnt integer,
    -- should be smallint, see database-issues#1291
    s_remote_cnt integer,
    s_data char(50),
    s_su_suppkey integer NOT NULL,
    PRIMARY KEY (s_w_id, s_i_id)
)

statement ok
CREATE INDEX fk_stock_warehouse ON stock (s_w_id ASC)

statement ok
CREATE INDEX fk_stock_item ON stock (s_i_id ASC)

statement ok
CREATE INDEX fk_stock_supplier ON stock (s_su_suppkey ASC)

statement ok
CREATE TABLE nation (
    -- should be smallint, see database-issues#1291
    n_nationkey integer NOT NULL,
    n_name char(25) NOT NULL,
    -- should be smallint, see database-issues#1291
    n_regionkey integer NOT NULL,
    n_comment char(152) NOT NULL,
    PRIMARY KEY (n_nationkey)
)

statement ok
CREATE INDEX fk_nation_regionkey ON nation (n_regionkey ASC)

statement ok
CREATE TABLE supplier (
    -- should be smallint, see database-issues#1291
    su_suppkey integer NOT NULL,
    su_name char(25) NOT NULL,
    su_address char(40) NOT NULL,
    -- should be smallint, see database-issues#1291
    su_nationkey integer NOT NULL,
    su_phone char(15) NOT NULL,
    su_acctbal decimal(12, 2) NOT NULL,
    su_comment char(101) NOT NULL,
    PRIMARY KEY (su_suppkey)
)

statement ok
CREATE INDEX fk_supplier_nationkey ON supplier (su_nationkey ASC)

statement ok
CREATE TABLE region (
    -- should be smallint, see database-issues#1291
    r_regionkey integer NOT NULL,
    r_name char(55) NOT NULL,
    r_comment char(152) NOT NULL,
    PRIMARY KEY (r_regionkey)
)

# Query 01
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    ol_number,
    sum(ol_quantity) AS sum_qty,
    sum(ol_amount) AS sum_amount,
    avg(ol_quantity) AS avg_qty,
    avg(ol_amount) AS avg_amount,
    count(*) AS count_order
FROM orderline
WHERE ol_delivery_d > TIMESTAMP '2007-01-02 00:00:00.000000'
GROUP BY ol_number
ORDER BY ol_number
----
Explained Query:
  Finish order_by=[#0{ol_number} asc nulls_last] output=[#0..=#5]
    Project (#0{ol_number}..=#2{sum_ol_amount}, #6, #7, #5{count}) // { arity: 6 }
      Map ((bigint_to_numeric(#1{sum_ol_quantity}) / bigint_to_numeric(case when (#3{count_ol_quantity} = 0) then null else #3{count_ol_quantity} end)), (#2{sum_ol_amount} / bigint_to_numeric(case when (#4{count_ol_amount} = 0) then null else #4{count_ol_amount} end))) // { arity: 8 }
        Reduce group_by=[#0{ol_number}] aggregates=[sum(#1{ol_quantity}), sum(#2{ol_amount}), count(#1{ol_quantity}), count(#2{ol_amount}), count(*)] // { arity: 6 }
          Project (#3{ol_number}, #7{ol_quantity}, #8{ol_amount}) // { arity: 3 }
            Filter (date_to_timestamp(#6{ol_delivery_d}) > 2007-01-02 00:00:00) // { arity: 10 }
              ReadIndex on=orderline fk_orderline_order=[*** full scan ***] // { arity: 10 }

Used Indexes:
  - materialize.public.fk_orderline_order (*** full scan ***)

Target cluster: quickstart

EOF

# Query 02
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT su_suppkey, su_name, n_name, i_id, i_name, su_address, su_phone, su_comment
FROM
    item, supplier, stock, nation, region,
    (
        SELECT
            s_i_id AS m_i_id,
            min(s_quantity) AS m_s_quantity
        FROM stock, supplier, nation, region
        WHERE s_su_suppkey = su_suppkey
        AND su_nationkey = n_nationkey
        AND n_regionkey = r_regionkey
        AND r_name like 'EUROP%'
        GROUP BY s_i_id
    ) m
WHERE i_id = s_i_id
AND s_su_suppkey = su_suppkey
AND su_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND i_data like '%b'
AND r_name like 'EUROP%'
AND i_id = m_i_id
AND s_quantity = m_s_quantity
ORDER BY n_name, su_name, i_id
----
Explained Query:
  Finish order_by=[#2{n_name} asc nulls_last, #1{su_name} asc nulls_last, #3{i_id} asc nulls_last] output=[#0..=#7]
    With
      cte l0 =
        ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 1 }
          Project (#0{r_regionkey}) // { arity: 1 }
            Filter like["EUROP%"](padchar(#1{r_name})) // { arity: 3 }
              ReadStorage materialize.public.region // { arity: 3 }
    Return // { arity: 8 }
      Project (#2{su_suppkey}, #3{su_name}, #12{n_name}, #0{i_id}, #1{i_name}, #4{su_address}, #6{su_phone}, #7{su_comment}) // { arity: 8 }
        Join on=(#0{i_id} = #8{s_i_id} = #15{s_i_id} AND #2{su_suppkey} = #10{s_su_suppkey} AND #5{su_nationkey} = #11{n_nationkey} AND #9{s_quantity} = #16{min_s_quantity} AND #13{n_regionkey} = #14{r_regionkey}) type=delta // { arity: 17 }
          implementation
            %0:item » %5[#0]UKA » %2:stock[#0{s_i_id}, #1{s_quantity}]KK » %1:supplier[#0{su_suppkey}]UK » %3:nation[#0{n_nationkey}]UK » %4:l0[#0{r_regionkey}]UKlf
            %1:supplier » %3:nation[#0{n_nationkey}]UK » %4:l0[#0{r_regionkey}]UKlf » %2:stock[#2{s_su_suppkey}]KA » %5[#0{m_i_id}, #1{m_s_quantity}]UKK » %0:item[#0{i_id}]UKlf
            %2:stock » %5[#0{m_i_id}, #1{m_s_quantity}]UKK » %0:item[#0{i_id}]UKlf » %1:supplier[#0{su_suppkey}]UK » %3:nation[#0{n_nationkey}]UK » %4:l0[#0{r_regionkey}]UKlf
            %3:nation » %4:l0[#0{r_regionkey}]UKlf » %1:supplier[#3{su_nationkey}]KA » %2:stock[#2{s_su_suppkey}]KA » %5[#0{m_i_id}, #1{m_s_quantity}]UKK » %0:item[#0{i_id}]UKlf
            %4:l0 » %3:nation[#2{n_regionkey}]KA » %1:supplier[#3{su_nationkey}]KA » %2:stock[#2{s_su_suppkey}]KA » %5[#0{m_i_id}, #1{m_s_quantity}]UKK » %0:item[#0{i_id}]UKlf
            %5 » %0:item[#0{i_id}]UKlf » %2:stock[#0{s_i_id}, #1{s_quantity}]KK » %1:supplier[#0{su_suppkey}]UK » %3:nation[#0{n_nationkey}]UK » %4:l0[#0{r_regionkey}]UKlf
          ArrangeBy keys=[[#0{i_id}]] // { arity: 2 }
            Project (#0{i_id}, #2{i_name}) // { arity: 2 }
              Filter like["%b"](padchar(#4{i_data})) // { arity: 5 }
                ReadStorage materialize.public.item // { arity: 5 }
          ArrangeBy keys=[[#0{su_suppkey}], [#3{su_nationkey}]] // { arity: 6 }
            Project (#0{su_suppkey}..=#4{su_phone}, #6{su_comment}) // { arity: 6 }
              ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
          ArrangeBy keys=[[#0{s_i_id}, #1{s_quantity}], [#2{s_su_suppkey}]] // { arity: 3 }
            Project (#0{s_i_id}, #2{s_quantity}, #17{s_su_suppkey}) // { arity: 3 }
              Filter (#2{s_quantity}) IS NOT NULL // { arity: 18 }
                ReadIndex on=stock fk_stock_supplier=[*** full scan ***] // { arity: 18 }
          ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 3 }
            Project (#0{n_nationkey}..=#2{n_regionkey}) // { arity: 3 }
              ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
          Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{s_i_id}], [#0{s_i_id}, #1{min_s_quantity}]] // { arity: 2 }
            Filter (#1{min_s_quantity}) IS NOT NULL // { arity: 2 }
              Reduce group_by=[#0{s_i_id}] aggregates=[min(#1{s_quantity})] // { arity: 2 }
                Project (#0{s_i_id}, #2{s_quantity}) // { arity: 2 }
                  Join on=(#17{s_su_suppkey} = #18{su_suppkey} AND #19{su_nationkey} = #20{n_nationkey} AND #21{n_regionkey} = #22{r_regionkey}) type=delta // { arity: 23 }
                    implementation
                      %0:stock » %1:supplier[#0{su_suppkey}]UK » %2:nation[#0{n_nationkey}]UK » %3:l0[#0{r_regionkey}]UKlf
                      %1:supplier » %2:nation[#0{n_nationkey}]UK » %3:l0[#0{r_regionkey}]UKlf » %0:stock[#17{s_su_suppkey}]KA
                      %2:nation » %3:l0[#0{r_regionkey}]UKlf » %1:supplier[#1{su_nationkey}]KA » %0:stock[#17{s_su_suppkey}]KA
                      %3:l0 » %2:nation[#1{n_regionkey}]KA » %1:supplier[#1{su_nationkey}]KA » %0:stock[#17{s_su_suppkey}]KA
                    ArrangeBy keys=[[#17{s_su_suppkey}]] // { arity: 18 }
                      ReadIndex on=stock fk_stock_supplier=[delta join 1st input (full scan)] // { arity: 18 }
                    ArrangeBy keys=[[#0{su_suppkey}], [#1{su_nationkey}]] // { arity: 2 }
                      Project (#0{su_suppkey}, #3{su_nationkey}) // { arity: 2 }
                        ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
                    ArrangeBy keys=[[#0{n_nationkey}], [#1{n_regionkey}]] // { arity: 2 }
                      Project (#0{n_nationkey}, #2{n_regionkey}) // { arity: 2 }
                        ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
                    Get l0 // { arity: 1 }

Source materialize.public.item
  filter=(like["%b"](padchar(#4{i_data})))
Source materialize.public.region
  filter=(like["EUROP%"](padchar(#1{r_name})))

Used Indexes:
  - materialize.public.fk_stock_supplier (*** full scan ***, delta join 1st input (full scan))
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 03
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT ol_o_id, ol_w_id, ol_d_id, sum(ol_amount) AS revenue, o_entry_d
FROM customer, neworder, "order", orderline
WHERE c_state LIKE 'A%'
AND c_id = o_c_id
AND c_w_id = o_w_id
AND c_d_id = o_d_id
AND no_w_id = o_w_id
AND no_d_id = o_d_id
AND no_o_id = o_id
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
AND o_entry_d > TIMESTAMP '2007-01-02 00:00:00.000000'
GROUP BY ol_o_id, ol_w_id, ol_d_id, o_entry_d
ORDER BY revenue DESC, o_entry_d
----
Explained Query:
  Finish order_by=[#3{sum_ol_amount} desc nulls_first, #4{o_entry_d} asc nulls_last] output=[#0..=#4]
    Project (#0{no_o_id}..=#2{c_d_id}, #4{sum_ol_amount}, #3{o_entry_d}) // { arity: 5 }
      Reduce group_by=[#2{no_o_id}, #1{c_w_id}, #0{c_d_id}, #3{o_entry_d}] aggregates=[sum(#4{ol_amount})] // { arity: 5 }
        Project (#1{c_d_id}..=#3{no_o_id}, #10{o_entry_d}, #19{ol_amount}) // { arity: 5 }
          Join on=(#0{c_id} = #9{o_c_id} AND #1{c_d_id} = #4{no_d_id} = #7{o_d_id} = #12{ol_d_id} AND #2{c_w_id} = #5{no_w_id} = #8{o_w_id} = #13{ol_w_id} AND #3{no_o_id} = #6{o_id} = #11{ol_o_id}) type=delta // { arity: 21 }
            implementation
              %0:customer » %2:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %1:neworder[#0{no_o_id}..=#2{no_w_id}]UKKK » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
              %1:neworder » %2:order[#0{o_id}..=#2{o_w_id}]UKKKif » %0:customer[#0{c_id}..=#2{c_w_id}]UKKKlf » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
              %2:order » %0:customer[#0{c_id}..=#2{c_w_id}]UKKKlf » %1:neworder[#0{no_o_id}..=#2{no_w_id}]UKKK » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
              %3:orderline » %2:order[#0{o_id}..=#2{o_w_id}]UKKKif » %0:customer[#0{c_id}..=#2{c_w_id}]UKKKlf » %1:neworder[#0{no_o_id}..=#2{no_w_id}]UKKK
            ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}]] // { arity: 3 }
              Project (#0{c_id}..=#2{c_w_id}) // { arity: 3 }
                Filter like["A%"](padchar(#9{c_state})) // { arity: 22 }
                  ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
            ArrangeBy keys=[[#0{no_o_id}..=#2{no_w_id}]] // { arity: 3 }
              ReadStorage materialize.public.neworder // { arity: 3 }
            ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}], [#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 5 }
              Project (#0{o_id}..=#4{o_entry_d}) // { arity: 5 }
                Filter (#3{o_c_id}) IS NOT NULL AND (date_to_timestamp(#4{o_entry_d}) > 2007-01-02 00:00:00) // { arity: 8 }
                  ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
            ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 10 }
              ReadIndex on=orderline fk_orderline_order=[delta join lookup] // { arity: 10 }

Source materialize.public.neworder

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (delta join lookup)

Target cluster: quickstart

EOF

# Query 04
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT o_ol_cnt, count(*) AS order_count
FROM "order"
WHERE o_entry_d >= TIMESTAMP '2007-01-02 00:00:00.000000'
AND o_entry_d < TIMESTAMP '2012-01-02 00:00:00.000000'
AND EXISTS (
    SELECT *
    FROM orderline
    WHERE o_id = ol_o_id
    AND o_w_id = ol_w_id
    AND o_d_id = ol_d_id
    AND ol_delivery_d >= o_entry_d
)
GROUP BY o_ol_cnt
ORDER BY o_ol_cnt
----
Explained Query:
  Finish order_by=[#0{o_ol_cnt} asc nulls_last] output=[#0, #1]
    With
      cte l0 =
        Project (#0{o_id}..=#2{o_w_id}, #4{o_entry_d}, #6{o_ol_cnt}) // { arity: 5 }
          Filter (#8 < 2012-01-02 00:00:00) AND (#8 >= 2007-01-02 00:00:00) // { arity: 9 }
            Map (date_to_timestamp(#4{o_entry_d})) // { arity: 9 }
              ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
    Return // { arity: 2 }
      Reduce group_by=[#0{o_ol_cnt}] aggregates=[count(*)] // { arity: 2 }
        Project (#4{o_ol_cnt}) // { arity: 1 }
          Join on=(#0{o_id} = #5{o_id} AND #1{o_d_id} = #6{o_d_id} AND #2{o_w_id} = #7{o_w_id} AND #3{o_entry_d} = #8{o_entry_d}) type=differential // { arity: 9 }
            implementation
              %1[#0..=#3]UKKKKA » %0:l0[#0..=#3]UKKKKiif
            ArrangeBy keys=[[#0{o_id}..=#3{o_entry_d}]] // { arity: 5 }
              Get l0 // { arity: 5 }
            ArrangeBy keys=[[#0{o_id}..=#3{o_entry_d}]] // { arity: 4 }
              Distinct project=[#0{o_id}..=#3{o_entry_d}] // { arity: 4 }
                Project (#0{o_id}..=#3{o_entry_d}) // { arity: 4 }
                  Filter (#10{ol_delivery_d} >= #3{o_entry_d}) // { arity: 14 }
                    Join on=(#0{o_id} = #4{ol_o_id} AND #1{o_d_id} = #5{ol_d_id} AND #2{o_w_id} = #6{ol_w_id}) type=differential // { arity: 14 }
                      implementation
                        %0:l0[#2{o_w_id}, #1{o_d_id}, #0{o_id}]UKKKiif » %1:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAiif
                      ArrangeBy keys=[[#2{o_w_id}, #1{o_d_id}, #0{o_id}]] // { arity: 4 }
                        Project (#0{o_id}..=#3{o_entry_d}) // { arity: 4 }
                          Get l0 // { arity: 5 }
                      ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 10 }
                        ReadIndex on=orderline fk_orderline_order=[differential join] // { arity: 10 }

Used Indexes:
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (differential join)

Target cluster: quickstart

EOF

# Query 05
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    n_name,
    sum(ol_amount) AS revenue
FROM customer, "order", orderline, stock, supplier, nation, region
WHERE c_id = o_c_id
AND c_w_id = o_w_id
AND c_d_id = o_d_id
AND ol_o_id = o_id
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_w_id = s_w_id
AND ol_i_id = s_i_id
AND s_su_suppkey = su_suppkey
AND c_n_nationkey = su_nationkey
AND su_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE'
AND o_entry_d >= TIMESTAMP '2007-01-02 00:00:00.000000'
GROUP BY n_name
ORDER BY revenue DESC
----
Explained Query:
  Finish order_by=[#1{sum_ol_amount} desc nulls_first] output=[#0, #1]
    Reduce group_by=[#1{n_name}] aggregates=[sum(#0{ol_amount})] // { arity: 2 }
      Project (#12{ol_amount}, #19{n_name}) // { arity: 2 }
        Join on=(#0{c_id} = #7{o_c_id} AND #1{c_d_id} = #5{o_d_id} = #9{ol_d_id} AND #2{c_w_id} = #6{o_w_id} = #10{ol_w_id} = #14{s_w_id} AND #3{c_n_nationkey} = #17{su_nationkey} = #18{n_nationkey} AND #4{o_id} = #8{ol_o_id} AND #11{ol_i_id} = #13{s_i_id} AND #15{s_su_suppkey} = #16{su_suppkey} AND #20{n_regionkey} = #21{r_regionkey}) type=delta // { arity: 22 }
          implementation
            %0:customer » %5:nation[#0{n_nationkey}]UK » %6:region[#0{r_regionkey}]UKef » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %4:supplier[#0{su_suppkey}, #1{su_nationkey}]UKK
            %1:order » %0:customer[#0{c_id}..=#2{c_w_id}]UKKK » %5:nation[#0{n_nationkey}]UK » %6:region[#0{r_regionkey}]UKef » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %4:supplier[#0{su_suppkey}, #1{su_nationkey}]UKK
            %2:orderline » %1:order[#0{o_id}..=#2{o_w_id}]UKKKif » %0:customer[#0{c_id}..=#2{c_w_id}]UKKK » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %4:supplier[#0{su_suppkey}, #1{su_nationkey}]UKK » %5:nation[#0{n_nationkey}]UK » %6:region[#0{r_regionkey}]UKef
            %3:stock » %4:supplier[#0{su_suppkey}]UK » %5:nation[#0{n_nationkey}]UK » %6:region[#0{r_regionkey}]UKef » %0:customer[#2{c_w_id}, #3{c_n_nationkey}]KK » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#0{ol_o_id}..=#3{ol_i_id}]KKKK
            %4:supplier » %5:nation[#0{n_nationkey}]UK » %6:region[#0{r_regionkey}]UKef » %0:customer[#3{c_n_nationkey}]KA » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %3:stock[#0{s_i_id}..=#2{s_su_suppkey}]UKKK
            %5:nation » %6:region[#0{r_regionkey}]UKef » %0:customer[#3{c_n_nationkey}]KA » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %4:supplier[#0{su_suppkey}, #1{su_nationkey}]UKK
            %6:region » %5:nation[#2{n_regionkey}]KA » %0:customer[#3{c_n_nationkey}]KA » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %4:supplier[#0{su_suppkey}, #1{su_nationkey}]UKK
          ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}], [#2{c_w_id}, #3{c_n_nationkey}], [#3{c_n_nationkey}]] // { arity: 4 }
            Project (#0{c_id}..=#2{c_w_id}, #21{c_n_nationkey}) // { arity: 4 }
              Filter (#21{c_n_nationkey}) IS NOT NULL // { arity: 22 }
                ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
          ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}], [#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 4 }
            Project (#0{o_id}..=#3{o_c_id}) // { arity: 4 }
              Filter (#3{o_c_id}) IS NOT NULL AND (date_to_timestamp(#4{o_entry_d}) >= 2007-01-02 00:00:00) // { arity: 8 }
                ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
          ArrangeBy keys=[[#0{ol_o_id}..=#3{ol_i_id}], [#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 5 }
            Project (#0{ol_o_id}..=#2{ol_w_id}, #4{ol_i_id}, #8{ol_amount}) // { arity: 5 }
              Filter (#4{ol_i_id}) IS NOT NULL // { arity: 10 }
                ReadIndex on=orderline fk_orderline_order=[*** full scan ***] // { arity: 10 }
          ArrangeBy keys=[[#0{s_i_id}, #1{s_w_id}], [#0{s_i_id}..=#2{s_su_suppkey}]] // { arity: 3 }
            Project (#0{s_i_id}, #1{s_w_id}, #17{s_su_suppkey}) // { arity: 3 }
              ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
          ArrangeBy keys=[[#0{su_suppkey}], [#0{su_suppkey}, #1{su_nationkey}]] // { arity: 2 }
            Project (#0{su_suppkey}, #3{su_nationkey}) // { arity: 2 }
              ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
          ArrangeBy keys=[[#0{n_nationkey}], [#2{n_regionkey}]] // { arity: 3 }
            Project (#0{n_nationkey}..=#2{n_regionkey}) // { arity: 3 }
              ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
          ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 1 }
            Project (#0{r_regionkey}) // { arity: 1 }
              Filter (#1{r_name} = "EUROPE") // { arity: 3 }
                ReadStorage materialize.public.region // { arity: 3 }

Source materialize.public.region
  filter=((#1{r_name} = "EUROPE"))

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (*** full scan ***)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 06
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT sum(ol_amount) AS revenue
FROM orderline
WHERE ol_delivery_d >= TIMESTAMP '1999-01-01 00:00:00.000000'
AND ol_delivery_d < TIMESTAMP '2020-01-01 00:00:00.000000'
AND ol_quantity BETWEEN 1 AND 100000
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(#0{ol_amount})] // { arity: 1 }
        Project (#8{ol_amount}) // { arity: 1 }
          Filter (#10 < 2020-01-01 00:00:00) AND (#7{ol_quantity} <= 100000) AND (#7{ol_quantity} >= 1) AND (#10 >= 1999-01-01 00:00:00) // { arity: 11 }
            Map (date_to_timestamp(#6{ol_delivery_d})) // { arity: 11 }
              ReadIndex on=orderline fk_orderline_order=[*** full scan ***] // { arity: 10 }
  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.fk_orderline_order (*** full scan ***)

Target cluster: quickstart

EOF

# Query 07
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    su_nationkey AS supp_nation,
    substr(c_state, 1, 1) AS cust_nation,
    EXTRACT(year FROM o_entry_d) AS l_year,
    sum(ol_amount) AS revenue
FROM supplier, stock, orderline, "order", customer, nation n1, nation n2
WHERE ol_supply_w_id = s_w_id
AND ol_i_id = s_i_id
AND s_su_suppkey = su_suppkey
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
AND c_id = o_c_id
AND c_w_id = o_w_id
AND c_d_id = o_d_id
AND su_nationkey = n1.n_nationkey
AND c_n_nationkey = n2.n_nationkey
AND (
    (n1.n_name = 'GERMANY' AND n2.n_name = 'CAMBODIA')
    OR
    (n1.n_name = 'CAMBODIA' AND n2.n_name = 'GERMANY')
)
AND ol_delivery_d BETWEEN TIMESTAMP '2007-01-02 00:00:00.000000' AND TIMESTAMP '2012-01-02 00:00:00.000000'
GROUP BY su_nationkey, substr(c_state, 1, 1), EXTRACT(year FROM o_entry_d)
ORDER BY su_nationkey, cust_nation, l_year
----
Explained Query:
  Finish order_by=[#0{su_nationkey} asc nulls_last, #1 asc nulls_last, #2 asc nulls_last] output=[#0..=#3]
    With
      cte l0 =
        ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 2 }
          Project (#0{n_nationkey}, #1{n_name}) // { arity: 2 }
            Filter ((#1{n_name} = "GERMANY") OR (#1{n_name} = "CAMBODIA")) // { arity: 4 }
              ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
    Return // { arity: 4 }
      Reduce group_by=[#0{su_nationkey}, substr(char_to_text(#3{c_state}), 1, 1), extract_year_d(#2{o_entry_d})] aggregates=[sum(#1{ol_amount})] // { arity: 4 }
        Project (#1{su_nationkey}, #13{ol_amount}, #19{o_entry_d}, #23{c_state}) // { arity: 4 }
          Filter (#29 <= 2012-01-02 00:00:00) AND (#29 >= 2007-01-02 00:00:00) AND (((#26{n_name} = "GERMANY") AND (#28{n_name} = "CAMBODIA")) OR ((#26{n_name} = "CAMBODIA") AND (#28{n_name} = "GERMANY"))) // { arity: 30 }
            Map (date_to_timestamp(#11{ol_delivery_d})) // { arity: 30 }
              Join on=(#0{su_suppkey} = #4{s_su_suppkey} AND #1{su_nationkey} = #25{n_nationkey} AND #2{s_i_id} = #9{ol_i_id} AND #3{s_w_id} = #10{ol_supply_w_id} AND #5{ol_o_id} = #15{o_id} AND #6{ol_d_id} = #16{o_d_id} = #21{c_d_id} AND #7{ol_w_id} = #17{o_w_id} = #22{c_w_id} AND #18{o_c_id} = #20{c_id} AND #24{c_n_nationkey} = #27{n_nationkey}) type=delta // { arity: 29 }
                implementation
                  %0:supplier » %5:l0[#0{n_nationkey}]UKef » %1:stock[#2{s_su_suppkey}]KA » %2:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAiif » %3:order[#0{o_id}..=#2{o_w_id}]UKKK » %4:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:l0[#0{n_nationkey}]UKef
                  %1:stock » %0:supplier[#0{su_suppkey}]UK » %5:l0[#0{n_nationkey}]UKef » %2:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAiif » %3:order[#0{o_id}..=#2{o_w_id}]UKKK » %4:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:l0[#0{n_nationkey}]UKef
                  %2:orderline » %3:order[#0{o_id}..=#2{o_w_id}]UKKK » %4:customer[#0{c_id}..=#2{c_w_id}]UKKK » %1:stock[#0{s_i_id}, #1{s_w_id}]UKK » %6:l0[#0{n_nationkey}]UKef » %0:supplier[#0{su_suppkey}]UK » %5:l0[#0{n_nationkey}]UKef
                  %3:order » %4:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:l0[#0{n_nationkey}]UKef » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAiif » %1:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:supplier[#0{su_suppkey}]UK » %5:l0[#0{n_nationkey}]UKef
                  %4:customer » %6:l0[#0{n_nationkey}]UKef » %3:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKA » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAiif » %1:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:supplier[#0{su_suppkey}]UK » %5:l0[#0{n_nationkey}]UKef
                  %5:l0 » %0:supplier[#1{su_nationkey}]KA » %1:stock[#2{s_su_suppkey}]KA » %2:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAiif » %3:order[#0{o_id}..=#2{o_w_id}]UKKK » %4:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:l0[#0{n_nationkey}]UKef
                  %6:l0 » %4:customer[#4{c_n_nationkey}]KA » %3:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKA » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAiif » %1:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:supplier[#0{su_suppkey}]UK » %5:l0[#0{n_nationkey}]UKef
                ArrangeBy keys=[[#0{su_suppkey}], [#1{su_nationkey}]] // { arity: 2 }
                  Project (#0{su_suppkey}, #3{su_nationkey}) // { arity: 2 }
                    ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
                ArrangeBy keys=[[#0{s_i_id}, #1{s_w_id}], [#2{s_su_suppkey}]] // { arity: 3 }
                  Project (#0{s_i_id}, #1{s_w_id}, #17{s_su_suppkey}) // { arity: 3 }
                    ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
                ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}], [#5{ol_supply_w_id}, #4{ol_i_id}]] // { arity: 10 }
                  ReadIndex on=orderline fk_orderline_order=[delta join lookup] fk_orderline_stock=[delta join lookup] // { arity: 10 }
                ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}], [#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 5 }
                  Project (#0{o_id}..=#4{o_entry_d}) // { arity: 5 }
                    Filter (#3{o_c_id}) IS NOT NULL // { arity: 8 }
                      ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
                ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}], [#4{c_n_nationkey}]] // { arity: 5 }
                  Project (#0{c_id}..=#2{c_w_id}, #9{c_state}, #21{c_n_nationkey}) // { arity: 5 }
                    Filter (#21{c_n_nationkey}) IS NOT NULL // { arity: 22 }
                      ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
                Get l0 // { arity: 2 }
                Get l0 // { arity: 2 }

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (delta join lookup)
  - materialize.public.fk_orderline_stock (delta join lookup)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 08
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    EXTRACT(year FROM o_entry_d) AS l_year,
    sum(CASE WHEN n2.n_name = 'GERMANY' THEN ol_amount ELSE 0 END) / CASE WHEN sum(ol_amount) = 0 THEN 1 ELSE sum(ol_amount) END AS mkt_share
FROM item, supplier, stock, orderline, "order", customer, nation n1, nation n2, region
WHERE i_id = s_i_id
AND ol_i_id = s_i_id
AND ol_supply_w_id = s_w_id
AND s_su_suppkey = su_suppkey
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
AND c_id = o_c_id
AND c_w_id = o_w_id
AND c_d_id = o_d_id
AND n1.n_nationkey = c_n_nationkey
AND n1.n_regionkey = r_regionkey
AND ol_i_id < 1000
AND r_name = 'EUROPE'
AND su_nationkey = n2.n_nationkey
AND o_entry_d BETWEEN TIMESTAMP '2007-01-02 00:00:00.000000' AND TIMESTAMP '2012-01-02 00:00:00.000000'
AND i_data like '%b'
AND i_id = ol_i_id
GROUP BY EXTRACT(year FROM o_entry_d)
ORDER BY l_year
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] output=[#0, #1]
    Project (#0, #3) // { arity: 2 }
      Map ((#1{sum} / case when (#2{sum_ol_amount} = 0) then 1 else #2{sum_ol_amount} end)) // { arity: 4 }
        Reduce group_by=[extract_year_d(#1{o_entry_d})] aggregates=[sum(case when (#2{n_name} = "GERMANY") then #0{ol_amount} else 0 end), sum(#0{ol_amount})] // { arity: 3 }
          Project (#14{ol_amount}, #20{o_entry_d}, #28{n_name}) // { arity: 3 }
            Filter (#0{i_id} < 1000) // { arity: 30 }
              Join on=(#0{i_id} = #3{s_i_id} = #10{ol_i_id} AND #1{su_suppkey} = #5{s_su_suppkey} AND #2{su_nationkey} = #27{n_nationkey} AND #4{s_w_id} = #11{ol_supply_w_id} AND #6{ol_o_id} = #16{o_id} AND #7{ol_d_id} = #17{o_d_id} = #22{c_d_id} AND #8{ol_w_id} = #18{o_w_id} = #23{c_w_id} AND #19{o_c_id} = #21{c_id} AND #24{c_n_nationkey} = #25{n_nationkey} AND #26{n_regionkey} = #29{r_regionkey}) type=delta // { arity: 30 }
                implementation
                  %0:item » %2:stock[#0{s_i_id}]KAif » %1:supplier[#0{su_suppkey}]UK » %7:nation[#0{n_nationkey}]UK » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAif » %4:order[#0{o_id}..=#2{o_w_id}]UKKKiif » %5:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef
                  %1:supplier » %7:nation[#0{n_nationkey}]UK » %2:stock[#2{s_su_suppkey}]KAif » %0:item[#0{i_id}]UKlif » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAif » %4:order[#0{o_id}..=#2{o_w_id}]UKKKiif » %5:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef
                  %2:stock » %0:item[#0{i_id}]UKlif » %1:supplier[#0{su_suppkey}]UK » %7:nation[#0{n_nationkey}]UK » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAif » %4:order[#0{o_id}..=#2{o_w_id}]UKKKiif » %5:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef
                  %3:orderline » %4:order[#0{o_id}..=#2{o_w_id}]UKKKiif » %5:customer[#0{c_id}..=#2{c_w_id}]UKKK » %2:stock[#0{s_i_id}, #1{s_w_id}]UKKif » %0:item[#0{i_id}]UKlif » %1:supplier[#0{su_suppkey}]UK » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef » %7:nation[#0{n_nationkey}]UK
                  %4:order » %5:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAif » %2:stock[#0{s_i_id}, #1{s_w_id}]UKKif » %0:item[#0{i_id}]UKlif » %1:supplier[#0{su_suppkey}]UK » %7:nation[#0{n_nationkey}]UK
                  %5:customer » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef » %4:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAiif » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAif » %2:stock[#0{s_i_id}, #1{s_w_id}]UKKif » %0:item[#0{i_id}]UKlif » %1:supplier[#0{su_suppkey}]UK » %7:nation[#0{n_nationkey}]UK
                  %6:nation » %8:region[#0{r_regionkey}]UKef » %5:customer[#3{c_n_nationkey}]KA » %4:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAiif » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAif » %2:stock[#0{s_i_id}, #1{s_w_id}]UKKif » %0:item[#0{i_id}]UKlif » %1:supplier[#0{su_suppkey}]UK » %7:nation[#0{n_nationkey}]UK
                  %7:nation » %1:supplier[#1{su_nationkey}]KA » %2:stock[#2{s_su_suppkey}]KAif » %0:item[#0{i_id}]UKlif » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAif » %4:order[#0{o_id}..=#2{o_w_id}]UKKKiif » %5:customer[#0{c_id}..=#2{c_w_id}]UKKK » %6:nation[#0{n_nationkey}]UK » %8:region[#0{r_regionkey}]UKef
                  %8:region » %6:nation[#1{n_regionkey}]KA » %5:customer[#3{c_n_nationkey}]KA » %4:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAiif » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAif » %2:stock[#0{s_i_id}, #1{s_w_id}]UKKif » %0:item[#0{i_id}]UKlif » %1:supplier[#0{su_suppkey}]UK » %7:nation[#0{n_nationkey}]UK
                ArrangeBy keys=[[#0{i_id}]] // { arity: 1 }
                  Project (#0{i_id}) // { arity: 1 }
                    Filter (#0{i_id} < 1000) AND like["%b"](padchar(#4{i_data})) // { arity: 5 }
                      ReadStorage materialize.public.item // { arity: 5 }
                ArrangeBy keys=[[#0{su_suppkey}], [#1{su_nationkey}]] // { arity: 2 }
                  Project (#0{su_suppkey}, #3{su_nationkey}) // { arity: 2 }
                    ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
                ArrangeBy keys=[[#0{s_i_id}], [#0{s_i_id}, #1{s_w_id}], [#2{s_su_suppkey}]] // { arity: 3 }
                  Project (#0{s_i_id}, #1{s_w_id}, #17{s_su_suppkey}) // { arity: 3 }
                    Filter (#0{s_i_id} < 1000) // { arity: 18 }
                      ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
                ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}], [#5{ol_supply_w_id}, #4{ol_i_id}]] // { arity: 10 }
                  ReadIndex on=orderline fk_orderline_order=[delta join lookup] fk_orderline_stock=[delta join lookup] // { arity: 10 }
                ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}], [#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 5 }
                  Project (#0{o_id}..=#4{o_entry_d}) // { arity: 5 }
                    Filter (#8 <= 2012-01-02 00:00:00) AND (#8 >= 2007-01-02 00:00:00) AND (#3{o_c_id}) IS NOT NULL // { arity: 9 }
                      Map (date_to_timestamp(#4{o_entry_d})) // { arity: 9 }
                        ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
                ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}], [#3{c_n_nationkey}]] // { arity: 4 }
                  Project (#0{c_id}..=#2{c_w_id}, #21{c_n_nationkey}) // { arity: 4 }
                    Filter (#21{c_n_nationkey}) IS NOT NULL // { arity: 22 }
                      ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
                ArrangeBy keys=[[#0{n_nationkey}], [#1{n_regionkey}]] // { arity: 2 }
                  Project (#0{n_nationkey}, #2{n_regionkey}) // { arity: 2 }
                    ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
                ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 2 }
                  Project (#0{n_nationkey}, #1{n_name}) // { arity: 2 }
                    ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
                ArrangeBy keys=[[#0{r_regionkey}]] // { arity: 1 }
                  Project (#0{r_regionkey}) // { arity: 1 }
                    Filter (#1{r_name} = "EUROPE") // { arity: 3 }
                      ReadStorage materialize.public.region // { arity: 3 }

Source materialize.public.item
  filter=((#0{i_id} < 1000) AND like["%b"](padchar(#4{i_data})))
Source materialize.public.region
  filter=((#1{r_name} = "EUROPE"))

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (delta join lookup)
  - materialize.public.fk_orderline_stock (delta join lookup)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 09
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    n_name, EXTRACT(year FROM o_entry_d) AS l_year,
    sum(ol_amount) AS sum_profit
FROM item, stock, supplier, orderline, "order", nation
WHERE ol_i_id = s_i_id
AND ol_supply_w_id = s_w_id
AND s_su_suppkey = su_suppkey
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
AND ol_i_id = i_id
AND su_nationkey = n_nationkey
AND i_data like '%BB'
GROUP BY n_name, EXTRACT(year FROM o_entry_d)
ORDER BY n_name, l_year DESC
----
Explained Query:
  Finish order_by=[#0{n_name} asc nulls_last, #1 desc nulls_first] output=[#0..=#2]
    Reduce group_by=[#2{n_name}, extract_year_d(#1{o_entry_d})] aggregates=[sum(#0{ol_amount})] // { arity: 3 }
      Project (#14{ol_amount}, #19{o_entry_d}, #21{n_name}) // { arity: 3 }
        Join on=(#0{i_id} = #1{s_i_id} = #10{ol_i_id} AND #2{s_w_id} = #11{ol_supply_w_id} AND #3{s_su_suppkey} = #4{su_suppkey} AND #5{su_nationkey} = #20{n_nationkey} AND #6{ol_o_id} = #16{o_id} AND #7{ol_d_id} = #17{o_d_id} AND #8{ol_w_id} = #18{o_w_id}) type=delta // { arity: 22 }
          implementation
            %0:item » %1:stock[#0{s_i_id}]KA » %2:supplier[#0{su_suppkey}]UK » %5:nation[#0{n_nationkey}]UK » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKA » %4:order[#0{o_id}..=#2{o_w_id}]UKKK
            %1:stock » %0:item[#0{i_id}]UKlf » %2:supplier[#0{su_suppkey}]UK » %5:nation[#0{n_nationkey}]UK » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKA » %4:order[#0{o_id}..=#2{o_w_id}]UKKK
            %2:supplier » %5:nation[#0{n_nationkey}]UK » %1:stock[#2{s_su_suppkey}]KA » %0:item[#0{i_id}]UKlf » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKA » %4:order[#0{o_id}..=#2{o_w_id}]UKKK
            %3:orderline » %4:order[#0{o_id}..=#2{o_w_id}]UKKK » %1:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:item[#0{i_id}]UKlf » %2:supplier[#0{su_suppkey}]UK » %5:nation[#0{n_nationkey}]UK
            %4:order » %3:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %1:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:item[#0{i_id}]UKlf » %2:supplier[#0{su_suppkey}]UK » %5:nation[#0{n_nationkey}]UK
            %5:nation » %2:supplier[#1{su_nationkey}]KA » %1:stock[#2{s_su_suppkey}]KA » %0:item[#0{i_id}]UKlf » %3:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKA » %4:order[#0{o_id}..=#2{o_w_id}]UKKK
          ArrangeBy keys=[[#0{i_id}]] // { arity: 1 }
            Project (#0{i_id}) // { arity: 1 }
              Filter like["%BB"](padchar(#4{i_data})) // { arity: 5 }
                ReadStorage materialize.public.item // { arity: 5 }
          ArrangeBy keys=[[#0{s_i_id}], [#0{s_i_id}, #1{s_w_id}], [#2{s_su_suppkey}]] // { arity: 3 }
            Project (#0{s_i_id}, #1{s_w_id}, #17{s_su_suppkey}) // { arity: 3 }
              ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
          ArrangeBy keys=[[#0{su_suppkey}], [#1{su_nationkey}]] // { arity: 2 }
            Project (#0{su_suppkey}, #3{su_nationkey}) // { arity: 2 }
              ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
          ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}], [#5{ol_supply_w_id}, #4{ol_i_id}]] // { arity: 10 }
            ReadIndex on=orderline fk_orderline_order=[delta join lookup] fk_orderline_stock=[delta join lookup] // { arity: 10 }
          ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}]] // { arity: 4 }
            Project (#0{o_id}..=#2{o_w_id}, #4{o_entry_d}) // { arity: 4 }
              ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
          ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 2 }
            Project (#0{n_nationkey}, #1{n_name}) // { arity: 2 }
              ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }

Source materialize.public.item
  filter=(like["%BB"](padchar(#4{i_data})))

Used Indexes:
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (delta join lookup)
  - materialize.public.fk_orderline_stock (delta join lookup)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 10
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    c_id, c_last, sum(ol_amount) AS revenue, c_city, c_phone, n_name
FROM customer, "order", orderline, nation
WHERE c_id = o_c_id
AND c_w_id = o_w_id
AND c_d_id = o_d_id
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
AND o_entry_d >= TIMESTAMP '2007-01-02 00:00:00.000000'
AND o_entry_d <= ol_delivery_d
AND n_nationkey = c_n_nationkey
GROUP BY c_id, c_last, c_city, c_phone, n_name
ORDER BY revenue DESC
----
Explained Query:
  Finish order_by=[#2{sum_ol_amount} desc nulls_first] output=[#0..=#5]
    Project (#0{c_id}, #1{c_last}, #5{sum_ol_amount}, #2{c_city}..=#4{n_name}) // { arity: 6 }
      Reduce group_by=[#0{c_id}..=#3{c_phone}, #5{n_name}] aggregates=[sum(#4{ol_amount})] // { arity: 6 }
        Project (#0{c_id}, #3{c_last}..=#5{c_phone}, #20{ol_amount}, #23{n_name}) // { arity: 6 }
          Filter (#11{o_entry_d} <= #18{ol_delivery_d}) // { arity: 24 }
            Join on=(#0{c_id} = #10{o_c_id} AND #1{c_d_id} = #8{o_d_id} = #13{ol_d_id} AND #2{c_w_id} = #9{o_w_id} = #14{ol_w_id} AND #6{c_n_nationkey} = #22{n_nationkey} AND #7{o_id} = #12{ol_o_id}) type=delta // { arity: 24 }
              implementation
                %0:customer » %3:nation[#0{n_nationkey}]UK » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
                %1:order » %0:customer[#0{c_id}..=#2{c_w_id}]UKKK » %3:nation[#0{n_nationkey}]UK » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
                %2:orderline » %1:order[#0{o_id}..=#2{o_w_id}]UKKKif » %0:customer[#0{c_id}..=#2{c_w_id}]UKKK » %3:nation[#0{n_nationkey}]UK
                %3:nation » %0:customer[#6{c_n_nationkey}]KA » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
              ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}], [#6{c_n_nationkey}]] // { arity: 7 }
                Project (#0{c_id}..=#2{c_w_id}, #5{c_last}, #8{c_city}, #11{c_phone}, #21{c_n_nationkey}) // { arity: 7 }
                  Filter (#21{c_n_nationkey}) IS NOT NULL // { arity: 22 }
                    ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
              ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}], [#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 5 }
                Project (#0{o_id}..=#4{o_entry_d}) // { arity: 5 }
                  Filter (#3{o_c_id}) IS NOT NULL AND (date_to_timestamp(#4{o_entry_d}) >= 2007-01-02 00:00:00) // { arity: 8 }
                    ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
              ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 10 }
                ReadIndex on=orderline fk_orderline_order=[delta join lookup] // { arity: 10 }
              ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 2 }
                Project (#0{n_nationkey}, #1{n_name}) // { arity: 2 }
                  ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (delta join lookup)
  - materialize.public.fk_nation_regionkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 11
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT s_i_id, sum(s_order_cnt) AS ordercount
FROM stock, supplier, nation
WHERE s_su_suppkey = su_suppkey
AND su_nationkey = n_nationkey
AND n_name = 'GERMANY'
GROUP BY s_i_id
HAVING sum(s_order_cnt) > (
    SELECT sum(s_order_cnt) * 0.005
    FROM stock, supplier, nation
    WHERE s_su_suppkey = su_suppkey
    AND su_nationkey = n_nationkey
    AND n_name = 'GERMANY'
)
ORDER BY ordercount DESC
----
Explained Query:
  Finish order_by=[#1{sum_s_order_cnt} desc nulls_first] output=[#0, #1]
    With
      cte l0 =
        Project (#0{s_i_id}, #14{s_order_cnt}) // { arity: 2 }
          Join on=(#17{s_su_suppkey} = #18{su_suppkey} AND #19{su_nationkey} = #20{n_nationkey}) type=delta // { arity: 21 }
            implementation
              %0:stock » %1:supplier[#0{su_suppkey}]UK » %2:nation[#0{n_nationkey}]UKef
              %1:supplier » %2:nation[#0{n_nationkey}]UKef » %0:stock[#17{s_su_suppkey}]KA
              %2:nation » %1:supplier[#1{su_nationkey}]KA » %0:stock[#17{s_su_suppkey}]KA
            ArrangeBy keys=[[#17{s_su_suppkey}]] // { arity: 18 }
              ReadIndex on=stock fk_stock_supplier=[delta join 1st input (full scan)] // { arity: 18 }
            ArrangeBy keys=[[#0{su_suppkey}], [#1{su_nationkey}]] // { arity: 2 }
              Project (#0{su_suppkey}, #3{su_nationkey}) // { arity: 2 }
                ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
            ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 1 }
              Project (#0{n_nationkey}) // { arity: 1 }
                Filter (#1{n_name} = "GERMANY") // { arity: 4 }
                  ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
    Return // { arity: 2 }
      Project (#0{s_i_id}, #1{sum_s_order_cnt}) // { arity: 2 }
        Filter (bigint_to_numeric(#1{sum_s_order_cnt}) > (bigint_to_numeric(#2{sum_s_order_cnt}) * 0.005)) // { arity: 3 }
          CrossJoin type=differential // { arity: 3 }
            implementation
              %1[×]UA » %0[×]
            ArrangeBy keys=[[]] // { arity: 2 }
              Reduce group_by=[#0{s_i_id}] aggregates=[sum(#1{s_order_cnt})] // { arity: 2 }
                Get l0 // { arity: 2 }
            ArrangeBy keys=[[]] // { arity: 1 }
              Reduce aggregates=[sum(#0{s_order_cnt})] // { arity: 1 }
                Project (#1{s_order_cnt}) // { arity: 1 }
                  Get l0 // { arity: 2 }

Used Indexes:
  - materialize.public.fk_stock_supplier (delta join 1st input (full scan))
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 12
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    o_ol_cnt,
    sum(CASE WHEN o_carrier_id = 1 OR o_carrier_id = 2 THEN 1 ELSE 0 END) AS high_line_count,
    sum(CASE WHEN o_carrier_id <> 1 AND o_carrier_id <> 2 THEN 1 ELSE 0 END) AS low_line_count
FROM
    "order", orderline
WHERE ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
AND o_entry_d <= ol_delivery_d
AND ol_delivery_d < TIMESTAMP '2020-01-01 00:00:00.000000'
GROUP BY o_ol_cnt
ORDER BY o_ol_cnt
----
Explained Query:
  Finish order_by=[#0{o_ol_cnt} asc nulls_last] output=[#0..=#2]
    Reduce group_by=[#1{o_ol_cnt}] aggregates=[sum(case when ((#0{o_carrier_id} = 1) OR (#0{o_carrier_id} = 2)) then 1 else 0 end), sum(case when ((#0{o_carrier_id} != 1) AND (#0{o_carrier_id} != 2)) then 1 else 0 end)] // { arity: 3 }
      Project (#4{o_carrier_id}, #5{o_ol_cnt}) // { arity: 2 }
        Filter (date_to_timestamp(#12{ol_delivery_d}) < 2020-01-01 00:00:00) AND (#3{o_entry_d} <= #12{ol_delivery_d}) // { arity: 16 }
          Join on=(#0{o_id} = #6{ol_o_id} AND #1{o_d_id} = #7{ol_d_id} AND #2{o_w_id} = #8{ol_w_id}) type=differential // { arity: 16 }
            implementation
              %0:order[#2{o_w_id}, #1{o_d_id}, #0{o_id}]UKKK » %1:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKAif
            ArrangeBy keys=[[#2{o_w_id}, #1{o_d_id}, #0{o_id}]] // { arity: 6 }
              Project (#0{o_id}..=#2{o_w_id}, #4{o_entry_d}..=#6{o_ol_cnt}) // { arity: 6 }
                ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
            ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 10 }
              ReadIndex on=orderline fk_orderline_order=[differential join] // { arity: 10 }

Used Indexes:
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (differential join)

Target cluster: quickstart

EOF

# Query 13
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    c_count, count(*) AS custdist
FROM (
    SELECT c_id, count(o_id) as c_count
    FROM customer
    LEFT OUTER JOIN "order" ON (
        c_w_id = o_w_id AND c_d_id = o_d_id AND c_id = o_c_id AND o_carrier_id > 8
    )
    GROUP BY c_id
) AS c_orders
GROUP BY c_count
ORDER BY custdist DESC, c_count DESC
----
Explained Query:
  Finish order_by=[#1{count} desc nulls_first, #0{count_o_id} desc nulls_first] output=[#0, #1]
    With
      cte l0 =
        Project (#0{c_id}..=#3{o_id}) // { arity: 4 }
          Filter (#8{o_carrier_id} > 8) // { arity: 11 }
            Join on=(#0{c_id} = #6{o_c_id} AND #1{c_d_id} = #4{o_d_id} AND #2{c_w_id} = #5{o_w_id}) type=differential // { arity: 11 }
              implementation
                %0:customer[#2{c_w_id}, #1{c_d_id}, #0{c_id}]UKKK » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKAif
              ArrangeBy keys=[[#2{c_w_id}, #1{c_d_id}, #0{c_id}]] // { arity: 3 }
                Project (#0{c_id}..=#2{c_w_id}) // { arity: 3 }
                  ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
              ArrangeBy keys=[[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 8 }
                ReadIndex on=order fk_order_customer=[differential join] // { arity: 8 }
    Return // { arity: 2 }
      Reduce group_by=[#0{count_o_id}] aggregates=[count(*)] // { arity: 2 }
        Project (#1{count_o_id}) // { arity: 1 }
          Reduce group_by=[#0{c_id}] aggregates=[count(#1{o_id})] // { arity: 2 }
            Union // { arity: 2 }
              Map (null) // { arity: 2 }
                Union // { arity: 1 }
                  Negate // { arity: 1 }
                    Project (#0{c_id}) // { arity: 1 }
                      Distinct project=[#0{c_id}..=#2{c_w_id}] // { arity: 3 }
                        Project (#0{c_id}..=#2{c_w_id}) // { arity: 3 }
                          Get l0 // { arity: 4 }
                  Project (#0{c_id}) // { arity: 1 }
                    ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
              Project (#0{c_id}, #3{o_id}) // { arity: 2 }
                Get l0 // { arity: 4 }

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (differential join)

Target cluster: quickstart

EOF

# Query 14
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    100.00 * sum(CASE WHEN i_data LIKE 'PR%' THEN ol_amount ELSE 0 END) / (1 + sum(ol_amount)) AS promo_revenue
FROM orderline, item
WHERE ol_i_id = i_id
AND ol_delivery_d >= TIMESTAMP '2007-01-02 00:00:00.000000'
AND ol_delivery_d < TIMESTAMP '2020-01-02 00:00:00.000000'
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(case when like["PR%"](padchar(#1{i_data})) then #0{ol_amount} else 0 end), sum(#0{ol_amount})] // { arity: 2 }
        Project (#8{ol_amount}, #11{i_data}) // { arity: 2 }
          Filter (#12 < 2020-01-02 00:00:00) AND (#12 >= 2007-01-02 00:00:00) // { arity: 13 }
            Map (date_to_timestamp(#6{ol_delivery_d})) // { arity: 13 }
              Join on=(#4{ol_i_id} = #10{i_id}) type=differential // { arity: 12 }
                implementation
                  %1:item[#0{i_id}]UK » %0:orderline[#4{ol_i_id}]KAiif
                ArrangeBy keys=[[#4{ol_i_id}]] // { arity: 10 }
                  ReadIndex on=orderline fk_orderline_item=[differential join] // { arity: 10 }
                ArrangeBy keys=[[#0{i_id}]] // { arity: 2 }
                  Project (#0{i_id}, #4{i_data}) // { arity: 2 }
                    ReadStorage materialize.public.item // { arity: 5 }
  Return // { arity: 1 }
    Project (#2) // { arity: 1 }
      Map (((100 * #0{sum}) / (1 + #1{sum_ol_amount}))) // { 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 }
                - ()

Source materialize.public.item

Used Indexes:
  - materialize.public.fk_orderline_item (differential join)

Target cluster: quickstart

EOF

# Query 15
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT su_suppkey, su_name, su_address, su_phone, total_revenue
FROM
    supplier,
    (
        SELECT
            s_su_suppkey AS supplier_no,
            sum(ol_amount) AS total_revenue
        FROM orderline, stock
        WHERE ol_i_id = s_i_id
        AND ol_supply_w_id = s_w_id
        AND ol_delivery_d >= TIMESTAMP '2007-01-02 00:00:00.000000'
        GROUP BY s_su_suppkey
    ) AS revenue
WHERE su_suppkey = supplier_no
AND total_revenue = (
    SELECT max(total_revenue)
    FROM (
        SELECT
            s_su_suppkey AS supplier_no,
            sum(ol_amount) AS total_revenue
            FROM orderline, stock
        WHERE ol_i_id = s_i_id
        AND ol_supply_w_id = s_w_id
        AND ol_delivery_d >= TIMESTAMP '2007-01-02 00:00:00.000000'
        GROUP BY s_su_suppkey
    ) AS revenue
)
ORDER BY su_suppkey
----
Explained Query:
  Finish order_by=[#0{su_suppkey} asc nulls_last] output=[#0..=#4]
    With
      cte l0 =
        Reduce group_by=[#1{s_su_suppkey}] aggregates=[sum(#0{ol_amount})] // { arity: 2 }
          Project (#8{ol_amount}, #12{s_su_suppkey}) // { arity: 2 }
            Filter (date_to_timestamp(#6{ol_delivery_d}) >= 2007-01-02 00:00:00) // { arity: 13 }
              Join on=(#4{ol_i_id} = #10{s_i_id} AND #5{ol_supply_w_id} = #11{s_w_id}) type=differential // { arity: 13 }
                implementation
                  %1:stock[#1{s_w_id}, #0{s_i_id}]UKK » %0:orderline[#5{ol_supply_w_id}, #4{ol_i_id}]KKAif
                ArrangeBy keys=[[#5{ol_supply_w_id}, #4{ol_i_id}]] // { arity: 10 }
                  ReadIndex on=orderline fk_orderline_stock=[differential join] // { arity: 10 }
                ArrangeBy keys=[[#1{s_w_id}, #0{s_i_id}]] // { arity: 3 }
                  Project (#0{s_i_id}, #1{s_w_id}, #17{s_su_suppkey}) // { arity: 3 }
                    ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
    Return // { arity: 5 }
      Project (#0{su_suppkey}..=#3{su_phone}, #5{sum_ol_amount}) // { arity: 5 }
        Join on=(#0{su_suppkey} = #4{s_su_suppkey} AND #5{sum_ol_amount} = #6{max_sum_ol_amount}) type=delta // { arity: 7 }
          implementation
            %0:supplier » %1:l0[#0]UKA » %2[#0]UK
            %1:l0 » %0:supplier[#0{su_suppkey}]UK » %2[#0]UK
            %2 » %1:l0[#1{total_revenue}]K » %0:supplier[#0{su_suppkey}]UK
          ArrangeBy keys=[[#0{su_suppkey}]] // { arity: 4 }
            Project (#0{su_suppkey}..=#2{su_address}, #4{su_phone}) // { arity: 4 }
              ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
          ArrangeBy keys=[[#0{s_su_suppkey}], [#1{sum_ol_amount}]] // { arity: 2 }
            Filter (#1{sum_ol_amount}) IS NOT NULL // { arity: 2 }
              Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{max_sum_ol_amount}]] // { arity: 1 }
            Filter (#0{max_sum_ol_amount}) IS NOT NULL // { arity: 1 }
              Reduce aggregates=[max(#0{sum_ol_amount})] // { arity: 1 }
                Project (#1{sum_ol_amount}) // { arity: 1 }
                  Get l0 // { arity: 2 }

Used Indexes:
  - materialize.public.fk_orderline_stock (differential join)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 16
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    i_name,
    substr(i_data, 1, 3) AS brand,
    i_price,
    count(DISTINCT s_su_suppkey) AS supplier_cnt
FROM stock, item
WHERE i_id = s_i_id
AND i_data NOT LIKE 'zz%'
AND (
    s_su_suppkey NOT IN (SELECT su_suppkey FROM supplier WHERE su_comment like '%bad%')
)
GROUP BY i_name, substr(i_data, 1, 3), i_price
ORDER BY supplier_cnt DESC
----
Explained Query:
  Finish order_by=[#3{count_s_su_suppkey} desc nulls_first] output=[#0..=#3]
    With
      cte l0 =
        Project (#17{s_su_suppkey}, #19{i_name}..=#21{i_data}) // { arity: 4 }
          Join on=(#0{s_i_id} = #18{i_id}) type=differential // { arity: 22 }
            implementation
              %1:item[#0{i_id}]UKf » %0:stock[#0{s_i_id}]KAf
            ArrangeBy keys=[[#0{s_i_id}]] // { arity: 18 }
              ReadIndex on=stock fk_stock_item=[differential join] // { arity: 18 }
            ArrangeBy keys=[[#0{i_id}]] // { arity: 4 }
              Project (#0{i_id}, #2{i_name}..=#4{i_data}) // { arity: 4 }
                Filter NOT(like["zz%"](padchar(#4{i_data}))) // { arity: 5 }
                  ReadStorage materialize.public.item // { arity: 5 }
      cte l1 =
        Distinct project=[#0{s_su_suppkey}] // { arity: 1 }
          Project (#0{s_su_suppkey}) // { arity: 1 }
            Get l0 // { arity: 4 }
    Return // { arity: 4 }
      Reduce group_by=[#1{i_name}, substr(char_to_text(#3{i_data}), 1, 3), #2{i_price}] aggregates=[count(distinct #0{s_su_suppkey})] // { arity: 4 }
        Project (#0{s_su_suppkey}..=#3{i_data}) // { arity: 4 }
          Join on=(#0{s_su_suppkey} = #4{s_su_suppkey}) type=differential // { arity: 5 }
            implementation
              %0:l0[#0]K » %1[#0]K
            ArrangeBy keys=[[#0{s_su_suppkey}]] // { arity: 4 }
              Get l0 // { arity: 4 }
            ArrangeBy keys=[[#0{s_su_suppkey}]] // { arity: 1 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Project (#0{s_su_suppkey}) // { arity: 1 }
                    Join on=(#0{s_su_suppkey} = #1{su_suppkey}) type=differential // { arity: 2 }
                      implementation
                        %0:l1[#0]UKA » %1:supplier[#0]UKlf
                      ArrangeBy keys=[[#0{s_su_suppkey}]] // { arity: 1 }
                        Get l1 // { arity: 1 }
                      ArrangeBy keys=[[#0{su_suppkey}]] // { arity: 1 }
                        Project (#0{su_suppkey}) // { arity: 1 }
                          Filter like["%bad%"](padchar(#6{su_comment})) // { arity: 7 }
                            ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
                Get l1 // { arity: 1 }

Source materialize.public.item
  filter=(NOT(like["zz%"](padchar(#4{i_data}))))

Used Indexes:
  - materialize.public.fk_stock_item (differential join)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 17
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    sum(ol_amount) / 2.0 AS avg_yearly
FROM
    orderline,
    (
        SELECT i_id, avg(ol_quantity) AS a
        FROM item, orderline
        WHERE i_data LIKE '%b' AND ol_i_id = i_id
        GROUP BY i_id
    ) t
WHERE ol_i_id = t.i_id
AND ol_quantity < t.a
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#4{ol_i_id}]] // { arity: 10 }
        ReadIndex on=orderline fk_orderline_item=[differential join] // { arity: 10 }
    cte l1 =
      Reduce aggregates=[sum(#0{ol_amount})] // { arity: 1 }
        Project (#8{ol_amount}) // { arity: 1 }
          Filter (integer_to_numeric(#7{ol_quantity}) < (bigint_to_numeric(#11{sum_ol_quantity}) / bigint_to_numeric(case when (#12{count_ol_quantity} = 0) then null else #12{count_ol_quantity} end))) // { arity: 13 }
            Join on=(#4{ol_i_id} = #10{i_id}) type=differential // { arity: 13 }
              implementation
                %1[#0{i_id}]UKA » %0:l0[#4{ol_i_id}]KA
              Get l0 // { arity: 10 }
              ArrangeBy keys=[[#0{i_id}]] // { arity: 3 }
                Reduce group_by=[#0{i_id}] aggregates=[sum(#1{ol_quantity}), count(#1{ol_quantity})] // { arity: 3 }
                  Project (#0{i_id}, #8{ol_quantity}) // { arity: 2 }
                    Join on=(#0{i_id} = #5{ol_i_id}) type=differential // { arity: 11 }
                      implementation
                        %0:item[#0{i_id}]UKlf » %1:l0[#4{ol_i_id}]KAlf
                      ArrangeBy keys=[[#0{i_id}]] // { arity: 1 }
                        Project (#0{i_id}) // { arity: 1 }
                          Filter like["%b"](padchar(#4{i_data})) // { arity: 5 }
                            ReadStorage materialize.public.item // { arity: 5 }
                      Get l0 // { arity: 10 }
  Return // { arity: 1 }
    Project (#1) // { arity: 1 }
      Map ((#0{sum_ol_amount} / 2)) // { arity: 2 }
        Union // { arity: 1 }
          Get l1 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Project () // { arity: 0 }
                  Get l1 // { arity: 1 }
              Constant // { arity: 0 }
                - ()

Source materialize.public.item
  filter=(like["%b"](padchar(#4{i_data})))

Used Indexes:
  - materialize.public.fk_orderline_item (differential join)

Target cluster: quickstart

EOF

# Query 18
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT c_last, c_id, o_id, o_entry_d, o_ol_cnt, sum(ol_amount)
FROM customer, "order", orderline
WHERE c_id = o_c_id
AND c_w_id = o_w_id
AND c_d_id = o_d_id
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_o_id = o_id
GROUP BY o_id, o_w_id, o_d_id, c_id, c_last, o_entry_d, o_ol_cnt
HAVING sum(ol_amount) > 200
ORDER BY sum(ol_amount) DESC, o_entry_d
----
Explained Query:
  Finish order_by=[#5{sum_ol_amount} desc nulls_first, #3{o_entry_d} asc nulls_last] output=[#0..=#5]
    Project (#4{c_last}, #3{c_id}, #0{o_id}, #5{o_entry_d}..=#7{sum_ol_amount}) // { arity: 6 }
      Filter (#7{sum_ol_amount} > 200) // { arity: 8 }
        Reduce group_by=[#4{o_id}, #2{c_w_id}, #1{c_d_id}, #0{c_id}, #3{c_last}, #5{o_entry_d}, #6{o_ol_cnt}] aggregates=[sum(#7{ol_amount})] // { arity: 8 }
          Project (#0{c_id}..=#4{o_id}, #8{o_entry_d}, #9{o_ol_cnt}, #18{ol_amount}) // { arity: 8 }
            Join on=(#0{c_id} = #7{o_c_id} AND #1{c_d_id} = #5{o_d_id} = #11{ol_d_id} AND #2{c_w_id} = #6{o_w_id} = #12{ol_w_id} AND #4{o_id} = #10{ol_o_id}) type=delta // { arity: 20 }
              implementation
                %0:customer » %1:order[#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]KKKA » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
                %1:order » %0:customer[#0{c_id}..=#2{c_w_id}]UKKK » %2:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
                %2:orderline » %1:order[#0{o_id}..=#2{o_w_id}]UKKK » %0:customer[#0{c_id}..=#2{c_w_id}]UKKK
              ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}]] // { arity: 4 }
                Project (#0{c_id}..=#2{c_w_id}, #5{c_last}) // { arity: 4 }
                  ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
              ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}], [#2{o_w_id}, #1{o_d_id}, #3{o_c_id}]] // { arity: 6 }
                Project (#0{o_id}..=#4{o_entry_d}, #6{o_ol_cnt}) // { arity: 6 }
                  Filter (#3{o_c_id}) IS NOT NULL // { arity: 8 }
                    ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
              ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 10 }
                ReadIndex on=orderline fk_orderline_order=[delta join lookup] // { arity: 10 }

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (delta join lookup)

Target cluster: quickstart

EOF

# Query 19
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT sum(ol_amount) AS revenue
FROM orderline, item
WHERE (
    ol_i_id = i_id
    AND i_data LIKE '%a'
    AND ol_quantity >= 1
    AND ol_quantity <= 10
    AND i_price BETWEEN 1 AND 400000
    AND ol_w_id in (1, 2, 3)
) OR (
    ol_i_id = i_id
    AND i_data LIKE '%b'
    AND ol_quantity >= 1
    AND ol_quantity <= 10
    AND i_price BETWEEN 1 AND 400000
    AND ol_w_id IN (1, 2, 4)
) OR (
    ol_i_id = i_id
    AND i_data LIKE '%c'
    AND ol_quantity >= 1
    AND ol_quantity <= 10
    AND i_price BETWEEN 1 AND 400000
    AND ol_w_id in (1, 5, 3)
)
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(#0{ol_amount})] // { arity: 1 }
        Project (#8{ol_amount}) // { arity: 1 }
          Filter (#7{ol_quantity} <= 10) AND (#7{ol_quantity} >= 1) AND (#12 OR #13 OR #14 OR #15 OR #16) AND ((like["%a"](#17) AND (#12 OR #13 OR #14)) OR (like["%b"](#17) AND (#12 OR #13 OR #15)) OR (like["%c"](#17) AND (#12 OR #14 OR #16))) // { arity: 18 }
            Map ((#2{ol_w_id} = 1), (#2{ol_w_id} = 2), (#2{ol_w_id} = 3), (#2{ol_w_id} = 4), (#2{ol_w_id} = 5), padchar(#11{i_data})) // { arity: 18 }
              Join on=(#4{ol_i_id} = #10{i_id}) type=differential // { arity: 12 }
                implementation
                  %1:item[#0{i_id}]UKliif » %0:orderline[#4{ol_i_id}]KAeliiiif
                ArrangeBy keys=[[#4{ol_i_id}]] // { arity: 10 }
                  ReadIndex on=orderline fk_orderline_item=[differential join] // { arity: 10 }
                ArrangeBy keys=[[#0{i_id}]] // { arity: 2 }
                  Project (#0{i_id}, #4{i_data}) // { arity: 2 }
                    Filter (#3{i_price} <= 400000) AND (#3{i_price} >= 1) AND (like["%a"](#5) OR like["%b"](#5) OR like["%c"](#5)) // { arity: 6 }
                      Map (padchar(#4{i_data})) // { arity: 6 }
                        ReadStorage materialize.public.item // { arity: 5 }
  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 }
            - ()

Source materialize.public.item
  filter=((#3{i_price} <= 400000) AND (#3{i_price} >= 1) AND (like["%a"](#5) OR like["%b"](#5) OR like["%c"](#5)))
  map=(padchar(#4{i_data}))

Used Indexes:
  - materialize.public.fk_orderline_item (differential join)

Target cluster: quickstart

Notices:
  - Notice: Index materialize.public.fk_orderline_order on orderline(ol_w_id, ol_d_id, ol_o_id) is too wide to use for literal equalities `ol_w_id IN (1, 2, 3, 4, 5)`.
    Hint: If your literal equalities filter out many rows, create an index whose key exactly matches your literal equalities: (ol_w_id).

EOF


# Query 20
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT su_name, su_address
FROM supplier, nation
WHERE su_suppkey IN (
    SELECT mod(s_i_id * s_w_id, 10000)
    FROM stock, orderline
    WHERE s_i_id IN (SELECT i_id FROM item WHERE i_data LIKE 'co%')
    AND ol_i_id = s_i_id
    AND ol_delivery_d > TIMESTAMP '2010-05-23 12:00:00'
    GROUP BY s_i_id, s_w_id, s_quantity
    HAVING 2 * s_quantity > sum(ol_quantity)
)
AND su_nationkey = n_nationkey
AND n_name = 'GERMANY'
ORDER BY su_name
----
Explained Query:
  Finish order_by=[#0{su_name} asc nulls_last] output=[#0, #1]
    With
      cte l0 =
        Project (#0{su_suppkey}..=#2{su_address}) // { arity: 3 }
          Join on=(#3{su_nationkey} = #7{n_nationkey}) type=differential // { arity: 8 }
            implementation
              %1:nation[#0{n_nationkey}]UKef » %0:supplier[#3{su_nationkey}]KAef
            ArrangeBy keys=[[#3{su_nationkey}]] // { arity: 7 }
              ReadIndex on=supplier fk_supplier_nationkey=[differential join] // { arity: 7 }
            ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 1 }
              Project (#0{n_nationkey}) // { arity: 1 }
                Filter (#1{n_name} = "GERMANY") // { arity: 4 }
                  ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
    Return // { arity: 2 }
      Project (#1{su_name}, #2{su_address}) // { arity: 2 }
        Join on=(#0{su_suppkey} = #3{su_suppkey}) type=differential // { arity: 4 }
          implementation
            %1[#0]UKA » %0:l0[#0]UK
          ArrangeBy keys=[[#0{su_suppkey}]] // { arity: 3 }
            Get l0 // { arity: 3 }
          ArrangeBy keys=[[#0{su_suppkey}]] // { arity: 1 }
            Distinct project=[#0{su_suppkey}] // { arity: 1 }
              Project (#0{su_suppkey}) // { arity: 1 }
                Filter (integer_to_bigint((2 * #3{s_quantity})) > #4{sum_ol_quantity}) // { arity: 5 }
                  Reduce group_by=[#0{su_suppkey}..=#3{s_quantity}] aggregates=[sum(#4{ol_quantity})] // { arity: 5 }
                    Project (#0{su_suppkey}..=#3{s_quantity}, #11{ol_quantity}) // { arity: 5 }
                      Filter (date_to_timestamp(#10{ol_delivery_d}) > 2010-05-23 12:00:00) // { arity: 15 }
                        Join on=(#0{su_suppkey} = ((#1{s_i_id} * #2{s_w_id}) % 10000) AND #1{s_i_id} = #8{ol_i_id} = #14{i_id}) type=delta // { arity: 15 }
                          implementation
                            %0:l0 » %1:stock[((#0 * #1) % 10000)]K » %3:item[#0]UKlf » %2:orderline[#4{ol_i_id}]KAif
                            %1:stock » %3:item[#0]UKlf » %0:l0[#0]UK » %2:orderline[#4{ol_i_id}]KAif
                            %2:orderline » %3:item[#0]UKlf » %1:stock[#0{s_i_id}]KA » %0:l0[#0]UK
                            %3:item » %2:orderline[#4{ol_i_id}]KAif » %1:stock[#0{s_i_id}]KA » %0:l0[#0]UK
                          ArrangeBy keys=[[#0{su_suppkey}]] // { arity: 1 }
                            Project (#0{su_suppkey}) // { arity: 1 }
                              Get l0 // { arity: 3 }
                          ArrangeBy keys=[[#0{s_i_id}], [((#0{s_i_id} * #1{s_w_id}) % 10000)]] // { arity: 3 }
                            Project (#0{s_i_id}..=#2{s_quantity}) // { arity: 3 }
                              ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
                          ArrangeBy keys=[[#4{ol_i_id}]] // { arity: 10 }
                            ReadIndex on=orderline fk_orderline_item=[delta join lookup] // { arity: 10 }
                          ArrangeBy keys=[[#0{i_id}]] // { arity: 1 }
                            Project (#0{i_id}) // { arity: 1 }
                              Filter like["co%"](padchar(#4{i_data})) // { arity: 5 }
                                ReadStorage materialize.public.item // { arity: 5 }

Source materialize.public.item
  filter=(like["co%"](padchar(#4{i_data})))

Used Indexes:
  - materialize.public.fk_orderline_item (delta join lookup)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (differential join)

Target cluster: quickstart

EOF

# Query 21
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    su_name, count(*) as numwait
FROM
    supplier, orderline l1, "order", stock, nation
WHERE ol_o_id = o_id
AND ol_w_id = o_w_id
AND ol_d_id = o_d_id
AND ol_w_id = s_w_id
AND ol_i_id = s_i_id
AND s_su_suppkey = su_suppkey
AND l1.ol_delivery_d > o_entry_d
AND NOT EXISTS (
    SELECT *
    FROM orderline l2
    WHERE l2.ol_o_id = l1.ol_o_id
    AND l2.ol_w_id = l1.ol_w_id
    AND l2.ol_d_id = l1.ol_d_id
    AND l2.ol_delivery_d > l1.ol_delivery_d
)
AND su_nationkey = n_nationkey
AND n_name = 'GERMANY'
GROUP BY su_name
ORDER BY numwait DESC, su_name
----
Explained Query:
  Finish order_by=[#1{count} desc nulls_first, #0{su_name} asc nulls_last] output=[#0, #1]
    With
      cte l0 =
        Project (#1{su_name}, #3{ol_o_id}..=#5{ol_w_id}, #7{ol_delivery_d}) // { arity: 5 }
          Filter (#7{ol_delivery_d} > #11{o_entry_d}) // { arity: 16 }
            Join on=(#0{su_suppkey} = #14{s_su_suppkey} AND #2{su_nationkey} = #15{n_nationkey} AND #3{ol_o_id} = #8{o_id} AND #4{ol_d_id} = #9{o_d_id} AND #5{ol_w_id} = #10{o_w_id} = #13{s_w_id} AND #6{ol_i_id} = #12{s_i_id}) type=delta // { arity: 16 }
              implementation
                %0:supplier » %4:nation[#0{n_nationkey}]UKef » %3:stock[#2{s_su_suppkey}]KA » %1:orderline[#2{ol_w_id}, #3{ol_i_id}]KK » %2:order[#0{o_id}..=#2{o_w_id}]UKKK
                %1:orderline » %2:order[#0{o_id}..=#2{o_w_id}]UKKK » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:supplier[#0{su_suppkey}]UK » %4:nation[#0{n_nationkey}]UKef
                %2:order » %1:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA » %3:stock[#0{s_i_id}, #1{s_w_id}]UKK » %0:supplier[#0{su_suppkey}]UK » %4:nation[#0{n_nationkey}]UKef
                %3:stock » %0:supplier[#0{su_suppkey}]UK » %4:nation[#0{n_nationkey}]UKef » %1:orderline[#2{ol_w_id}, #3{ol_i_id}]KK » %2:order[#0{o_id}..=#2{o_w_id}]UKKK
                %4:nation » %0:supplier[#2{su_nationkey}]KA » %3:stock[#2{s_su_suppkey}]KA » %1:orderline[#2{ol_w_id}, #3{ol_i_id}]KK » %2:order[#0{o_id}..=#2{o_w_id}]UKKK
              ArrangeBy keys=[[#0{su_suppkey}], [#2{su_nationkey}]] // { arity: 3 }
                Project (#0{su_suppkey}, #1{su_name}, #3{su_nationkey}) // { arity: 3 }
                  ReadIndex on=supplier fk_supplier_nationkey=[*** full scan ***] // { arity: 7 }
              ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}], [#2{ol_w_id}, #3{ol_i_id}]] // { arity: 5 }
                Project (#0{ol_o_id}..=#2{ol_w_id}, #4{ol_i_id}, #6{ol_delivery_d}) // { arity: 5 }
                  Filter (#4{ol_i_id}) IS NOT NULL // { arity: 10 }
                    ReadIndex on=orderline fk_orderline_order=[*** full scan ***] // { arity: 10 }
              ArrangeBy keys=[[#0{o_id}..=#2{o_w_id}]] // { arity: 4 }
                Project (#0{o_id}..=#2{o_w_id}, #4{o_entry_d}) // { arity: 4 }
                  ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
              ArrangeBy keys=[[#0{s_i_id}, #1{s_w_id}], [#2{s_su_suppkey}]] // { arity: 3 }
                Project (#0{s_i_id}, #1{s_w_id}, #17{s_su_suppkey}) // { arity: 3 }
                  ReadIndex on=stock fk_stock_warehouse=[*** full scan ***] // { arity: 18 }
              ArrangeBy keys=[[#0{n_nationkey}]] // { arity: 1 }
                Project (#0{n_nationkey}) // { arity: 1 }
                  Filter (#1{n_name} = "GERMANY") // { arity: 4 }
                    ReadIndex on=nation fk_nation_regionkey=[*** full scan ***] // { arity: 4 }
      cte l1 =
        Distinct project=[#0{ol_o_id}..=#3{ol_delivery_d}] // { arity: 4 }
          Project (#1{ol_o_id}..=#4{ol_delivery_d}) // { arity: 4 }
            Get l0 // { arity: 5 }
    Return // { arity: 2 }
      Reduce group_by=[#0{su_name}] aggregates=[count(*)] // { arity: 2 }
        Project (#0{su_name}) // { arity: 1 }
          Join on=(#1{ol_o_id} = #5{ol_o_id} AND #2{ol_d_id} = #6{ol_d_id} AND #3{ol_w_id} = #7{ol_w_id} AND #4{ol_delivery_d} = #8{ol_delivery_d}) type=differential // { arity: 9 }
            implementation
              %0:l0[#1..=#4]KKKK » %1[#0..=#3]KKKK
            ArrangeBy keys=[[#1{ol_o_id}..=#4{ol_delivery_d}]] // { arity: 5 }
              Get l0 // { arity: 5 }
            ArrangeBy keys=[[#0{ol_o_id}..=#3{ol_delivery_d}]] // { arity: 4 }
              Union // { arity: 4 }
                Negate // { arity: 4 }
                  Distinct project=[#0{ol_o_id}..=#3{ol_delivery_d}] // { arity: 4 }
                    Project (#0{ol_o_id}..=#3{ol_delivery_d}) // { arity: 4 }
                      Filter (#10{ol_delivery_d} > #3{ol_delivery_d}) // { arity: 14 }
                        Join on=(#0{ol_o_id} = #4{ol_o_id} AND #1{ol_d_id} = #5{ol_d_id} AND #2{ol_w_id} = #6{ol_w_id}) type=differential // { arity: 14 }
                          implementation
                            %0:l1[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKK » %1:orderline[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]KKKA
                          ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 4 }
                            Get l1 // { arity: 4 }
                          ArrangeBy keys=[[#2{ol_w_id}, #1{ol_d_id}, #0{ol_o_id}]] // { arity: 10 }
                            ReadIndex on=orderline fk_orderline_order=[differential join] // { arity: 10 }
                Get l1 // { arity: 4 }

Used Indexes:
  - materialize.public.fk_order_customer (*** full scan ***)
  - materialize.public.fk_orderline_order (*** full scan ***, differential join)
  - materialize.public.fk_stock_warehouse (*** full scan ***)
  - materialize.public.fk_nation_regionkey (*** full scan ***)
  - materialize.public.fk_supplier_nationkey (*** full scan ***)

Target cluster: quickstart

EOF

# Query 22
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT
    substr(c_state, 1, 1) AS country,
    count(*) AS numcust,
    sum(c_balance) AS totacctbal
FROM customer
WHERE substr(c_phone, 1, 1) IN ('1', '2', '3', '4', '5', '6', '7')
AND c_balance > (
    SELECT avg(c_balance)
    FROM customer
    WHERE c_balance > 0.00
    AND substr(c_phone, 1, 1) IN ('1', '2', '3', '4', '5', '6', '7')
)
AND NOT EXISTS (
    SELECT *
    FROM "order"
    WHERE o_c_id = c_id AND o_w_id = c_w_id AND o_d_id = c_d_id
)
GROUP BY substr(c_state, 1, 1)
ORDER BY substr(c_state, 1, 1)
----
Explained Query:
  Finish order_by=[#0 asc nulls_last] output=[#0..=#2]
    With
      cte l0 =
        Project (#0{c_id}..=#2{c_w_id}, #9{c_state}, #16{c_balance}, #22) // { arity: 6 }
          Map (substr(char_to_text(#11{c_phone}), 1, 1)) // { arity: 23 }
            ReadIndex on=customer fk_customer_district=[*** full scan ***] // { arity: 22 }
      cte l1 =
        Project (#0{c_id}..=#4{c_balance}) // { arity: 5 }
          Filter (#4{c_balance} > (#5{sum_c_balance} / bigint_to_numeric(case when (#6{count} = 0) then null else #6{count} end))) // { arity: 7 }
            CrossJoin type=differential // { arity: 7 }
              implementation
                %1[×]UA » %0:l0[×]ef
              ArrangeBy keys=[[]] // { arity: 5 }
                Project (#0{c_id}..=#4{c_balance}) // { arity: 5 }
                  Filter ((#5 = "1") OR (#5 = "2") OR (#5 = "3") OR (#5 = "4") OR (#5 = "5") OR (#5 = "6") OR (#5 = "7")) // { arity: 6 }
                    Get l0 // { arity: 6 }
              ArrangeBy keys=[[]] // { arity: 2 }
                Reduce aggregates=[sum(#0{c_balance}), count(*)] // { arity: 2 }
                  Project (#4{c_balance}) // { arity: 1 }
                    Filter (#4{c_balance} > 0) AND ((#5 = "1") OR (#5 = "2") OR (#5 = "3") OR (#5 = "4") OR (#5 = "5") OR (#5 = "6") OR (#5 = "7")) // { arity: 6 }
                      Get l0 // { arity: 6 }
      cte l2 =
        Project (#0{c_id}..=#2{c_w_id}) // { arity: 3 }
          Get l1 // { arity: 5 }
    Return // { arity: 3 }
      Reduce group_by=[substr(char_to_text(#0{c_state}), 1, 1)] aggregates=[count(*), sum(#1{c_balance})] // { arity: 3 }
        Project (#3{c_state}, #4{c_balance}) // { arity: 2 }
          Join on=(#0{c_id} = #5{c_id} AND #1{c_d_id} = #6{c_d_id} AND #2{c_w_id} = #7{c_w_id}) type=differential // { arity: 8 }
            implementation
              %0:l1[#0..=#2]UKKK » %1[#0..=#2]KKK
            ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}]] // { arity: 5 }
              Get l1 // { arity: 5 }
            ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}]] // { arity: 3 }
              Union // { arity: 3 }
                Negate // { arity: 3 }
                  Project (#0{c_id}..=#2{c_w_id}) // { arity: 3 }
                    Join on=(#0{c_id} = #3{o_c_id} AND #1{c_d_id} = #4{o_d_id} AND #2{c_w_id} = #5{o_w_id}) type=differential // { arity: 6 }
                      implementation
                        %1[#0..=#2]UKKKA » %0:l2[#0..=#2]UKKK
                      ArrangeBy keys=[[#0{c_id}..=#2{c_w_id}]] // { arity: 3 }
                        Get l2 // { arity: 3 }
                      ArrangeBy keys=[[#0{o_c_id}..=#2{o_w_id}]] // { arity: 3 }
                        Distinct project=[#2{o_c_id}, #0{o_d_id}, #1{o_w_id}] // { arity: 3 }
                          Project (#1{o_d_id}..=#3{o_c_id}) // { arity: 3 }
                            Filter (#3{o_c_id}) IS NOT NULL // { arity: 8 }
                              ReadIndex on=order fk_order_customer=[*** full scan ***] // { arity: 8 }
                Get l2 // { arity: 3 }

Used Indexes:
  - materialize.public.fk_customer_district (*** full scan ***)
  - materialize.public.fk_order_customer (*** full scan ***)

Target cluster: quickstart

EOF