# 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. # testing cardinality estimation simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_cardinality_estimates = true ---- COMPLETE 0 # avoid flakes in CI # Was flaky with 100ms. Look into this when starting to use cardinality for real! simple conn=mz_system,user=mz_system ALTER SYSTEM SET optimizer_oneshot_stats_timeout TO '500ms' ---- COMPLETE 0 simple SET ENABLE_SESSION_CARDINALITY_ESTIMATES TO TRUE ---- COMPLETE 0 statement ok CREATE TABLE t (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t2 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE INDEX t_x ON t (x); statement ok CREATE INDEX tt_x ON t2 (x); statement ok CREATE TABLE cities (name TEXT NOT NULL, state TEXT NOT NULL, pop integer); statement ok CREATE TABLE states (state TEXT NOT NULL, pop integer); statement ok CREATE INDEX cities_name ON cities (name); statement ok CREATE INDEX cities_state ON cities (state); statement ok CREATE INDEX cities_name_and_state ON cities (name, state); statement ok CREATE INDEX state_state ON states (state); statement ok INSERT INTO t (x, y) VALUES (1, 1), (1, 2), (2, 3), (3, 1) query T multiline EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t as l, t as r WHERE l.x = r.x; ---- Explained Query: With cte l0 = ArrangeBy keys=[[#0{x}]] ReadIndex on=t t_x=[differential join] Return Project (#0{x}, #1{y}, #0{x}, #3{y}) Join on=(#0{x} = #2{x}) type=differential implementation %0:l0[#0{x}]KA|4| » %1:l0[#0{x}]KA|4| Get l0 Get l0 Used Indexes: - materialize.public.t_x (differential join) Target cluster: quickstart EOF statement ok INSERT INTO t2 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 200)) AS l, (SELECT generate_series(1, 50)) AS r query T multiline EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t, t2 WHERE t.x = t2.x; ---- Explained Query: Project (#0{x}, #1{y}, #0{x}, #3{y}) Join on=(#0{x} = #2{x}) type=differential implementation %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| ArrangeBy keys=[[#0{x}]] ReadIndex on=t t_x=[differential join] ArrangeBy keys=[[#0{x}]] ReadIndex on=t2 tt_x=[differential join] Used Indexes: - materialize.public.t_x (differential join) - materialize.public.tt_x (differential join) Target cluster: quickstart EOF # regression test drawn from LDBC-BI query 15 for having a selectivity of 0 # TODO(mgree): we could probably trim this down to be tighter, but the optimizer has been too clever for me statement ok CREATE TABLE Person_knows_Person ( creationDate timestamp with time zone NOT NULL, Person1id bigint NOT NULL, Person2id bigint NOT NULL ) statement ok CREATE INDEX Person_knows_Person_Person1id_Person2id ON Person_knows_Person (Person1id, Person2id) query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR WITH MUTUALLY RECURSIVE mm (src bigint, dst bigint, w bigint) AS (SELECT 3::bigint, 4::bigint, 5::bigint), path (src bigint, dst bigint, w double precision) AS ( SELECT pp.person1id, pp.person2id, 10::double precision / (coalesce(w, 0) + 10) FROM Person_knows_Person pp left join mm on least(pp.person1id, pp.person2id) = mm.src AND greatest(pp.person1id, pp.person2id) = mm.dst ), pexists (src bigint, dir bool) AS ( ( SELECT 1::bigint, true UNION SELECT 2::bigint, false ) UNION ( WITH ss (src, dir) AS (SELECT src, dir FROM pexists), ns (src, dir) AS (SELECT p.dst, ss.dir FROM ss, path p WHERE ss.src = p.src), bb (src, dir) AS (SELECT src, dir FROM ns UNION ALL SELECT src, dir FROM ss), found (found) AS ( SELECT 1 AS found FROM bb b1, bb b2 WHERE b1.dir AND (NOT b2.dir) AND b1.src = b2.src ) SELECT src, dir FROM ns WHERE NOT EXISTS (SELECT 1 FROM found) UNION SELECT -1, true WHERE EXISTS (SELECT 1 FROM found) ) ), pathfound (c bool) AS ( SELECT true AS c FROM pexists WHERE src = -1 AND dir ) SELECT * FROM pexists; ---- Explained Query: With Mutually Recursive cte l0 = Project (#1, #3{person2id}) Join on=(#0{src} = #2{person1id}) type=differential ArrangeBy keys=[[#0{person2id}]] Get l3 ArrangeBy keys=[[#0{person1id}]] Project (#1{person1id}, #2{person2id}) ReadIndex on=person_knows_person person_knows_person_person1id_person2id=[*** full scan ***] cte l1 = Union Project (#1{person2id}, #0) Get l0 Get l3 cte l2 = Distinct project=[] Project () Join on=(#0{person2id} = #1{person2id}) type=differential ArrangeBy keys=[[#0{person2id}]] Project (#0{person2id}) Filter #1{dir} Get l1 ArrangeBy keys=[[#0{person2id}]] Project (#0{person2id}) Filter NOT(#1{dir}) Get l1 cte l3 = Distinct project=[#0{person2id}, #1] Union Project (#1{person2id}, #0) CrossJoin type=differential ArrangeBy keys=[[]] Get l0 ArrangeBy keys=[[]] Union Negate Get l2 Constant - () Project (#1, #0) Map (true, -1) Get l2 Constant - (1, true) - (2, false) Return Get l3 Used Indexes: - materialize.public.person_knows_person_person1id_person2id (*** full scan ***) Target cluster: quickstart EOF # regression test drawn from LDBC-BI query 15 for having a selectivity of 0 # TODO(mgree): we could probably trim this down to be tighter, but the optimizer has been too clever for me query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR WITH MUTUALLY RECURSIVE mm (src bigint, dst bigint, w bigint) AS (SELECT 3::bigint, 4::bigint, 5::bigint), path (src bigint, dst bigint, w double precision) AS ( SELECT pp.person1id, pp.person2id, 10::double precision / (coalesce(w, 0) + 10) FROM Person_knows_Person pp left join mm on least(pp.person1id, pp.person2id) = mm.src AND greatest(pp.person1id, pp.person2id) = mm.dst ), pexists (src bigint, dir bool) AS ( ( SELECT 1::bigint, true UNION SELECT 2::bigint, false ) UNION ( WITH ss (src, dir) AS (SELECT src, dir FROM pexists), ns (src, dir) AS (SELECT p.dst, ss.dir FROM ss, path p WHERE ss.src = p.src), bb (src, dir) AS (SELECT src, dir FROM ns UNION ALL SELECT src, dir FROM ss), found (found) AS ( SELECT 1 AS found FROM bb b1, bb b2 WHERE b1.dir AND (NOT b2.dir) AND b1.src = b2.src ) SELECT src, dir FROM ns WHERE NOT EXISTS (SELECT 1 FROM found) UNION SELECT -1, true WHERE EXISTS (SELECT 1 FROM found) ) ), pathfound (c bool) AS ( SELECT true AS c FROM pexists WHERE src = -1 AND dir ) SELECT * FROM pexists; ---- Explained Query: With Mutually Recursive cte l0 = Project (#1, #3{person2id}) Join on=(#0{src} = #2{person1id}) type=differential ArrangeBy keys=[[#0{person2id}]] Get l3 ArrangeBy keys=[[#0{person1id}]] Project (#1{person1id}, #2{person2id}) ReadIndex on=person_knows_person person_knows_person_person1id_person2id=[*** full scan ***] cte l1 = Union Project (#1{person2id}, #0) Get l0 Get l3 cte l2 = Distinct project=[] Project () Join on=(#0{person2id} = #1{person2id}) type=differential ArrangeBy keys=[[#0{person2id}]] Project (#0{person2id}) Filter #1{dir} Get l1 ArrangeBy keys=[[#0{person2id}]] Project (#0{person2id}) Filter NOT(#1{dir}) Get l1 cte l3 = Distinct project=[#0{person2id}, #1] Union Project (#1{person2id}, #0) CrossJoin type=differential ArrangeBy keys=[[]] Get l0 ArrangeBy keys=[[]] Union Negate Get l2 Constant - () Project (#1, #0) Map (true, -1) Get l2 Constant - (1, true) - (2, false) Return Get l3 Used Indexes: - materialize.public.person_knows_person_person1id_person2id (*** full scan ***) Target cluster: quickstart EOF # timeouts should work simple conn=mz_system,user=mz_system ALTER SYSTEM SET optimizer_oneshot_stats_timeout TO '1us'; ---- COMPLETE 0 simple conn=mz_system,user=mz_system SHOW optimizer_oneshot_stats_timeout; ---- 1 us COMPLETE 1 statement ok CREATE TABLE t3 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t4 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t5 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t6 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t7 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t8 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t9 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok CREATE TABLE t10 (x INTEGER NOT NULL, y INTEGER NOT NULL); statement ok INSERT INTO t3 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 13)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t4 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 47)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t5 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 38)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t6 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 23)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t7 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 23)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t8 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 23)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t9 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 21)) AS l, (SELECT generate_series(1, 13)) AS r statement ok INSERT INTO t10 (x, y) SELECT l.*, r.* FROM (SELECT generate_series(1, 20)) AS l, (SELECT generate_series(1, 13)) AS r # TODO(mgree): Make this reproducible # query T multiline # EXPLAIN WITH(join implementations) SELECT * FROM t JOIN t2 ON t.x = t2.x JOIN t3 ON t.x = t3.x JOIN t4 ON t.x = t4.x JOIN t5 ON t.x = t5.x JOIN t6 ON t.x = t6.x JOIN t7 ON t.x = t7.x JOIN t8 ON t.x = t8.x JOIN t9 ON t.x = t9.x JOIN t10 ON t.x = t10.x; # ---- # Explained Query: # Project (#0, #1, #0, #3, #0, #5, #0, #7, #0, #9, #0, #11, #0, #13, #0, #15, #0, #17, #0, #19) # Join on=(eq(#0, #2, #4, #6, #8, #10, #12, #14, #16, #18)) type=delta # implementation # %0:t » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %1:t2 » %0:t[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %2:t3 » %0:t[#0]KA » %1:t2[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %3:t4 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %4:t5 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %5:t6 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %6:t7 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %7:t8 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %8:t9[#0]KA » %9:t10[#0]KA # %8:t9 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %9:t10[#0]KA # %9:t10 » %0:t[#0]KA » %1:t2[#0]KA » %2:t3[#0]KA » %3:t4[#0]KA » %4:t5[#0]KA » %5:t6[#0]KA » %6:t7[#0]KA » %7:t8[#0]KA » %8:t9[#0]KA # ArrangeBy keys=[[#0]] # ReadIndex on=t t_x=[delta join 1st input (full scan)] # ArrangeBy keys=[[#0]] # ReadIndex on=t2 tt_x=[delta join lookup] # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t3 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t4 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t5 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t6 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t7 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t8 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t9 # ArrangeBy keys=[[#0]] # ReadStorage materialize.public.t10 # # Used Indexes: # - materialize.public.t_x (delta join 1st input (full scan)) # - materialize.public.tt_x (delta join lookup) # # EOF # restore and make sure we're good simple conn=mz_system,user=mz_system ALTER SYSTEM SET optimizer_oneshot_stats_timeout TO '20ms'; ---- COMPLETE 0 simple conn=mz_system,user=mz_system SHOW optimizer_oneshot_stats_timeout; ---- 20 ms COMPLETE 1 statement ok SELECT mz_unsafe.mz_sleep(5) query T multiline EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t JOIN t2 ON t.x = t2.x JOIN t3 ON t.x = t3.x JOIN t4 ON t.x = t4.x JOIN t5 ON t.x = t5.x JOIN t6 ON t.x = t6.x JOIN t7 ON t.x = t7.x JOIN t8 ON t.x = t8.x JOIN t9 ON t.x = t9.x JOIN t10 ON t.x = t10.x; ---- Explained Query: Project (#0{x}, #1{y}, #0{x}, #3{y}, #0{x}, #5{y}, #0{x}, #7{y}, #0{x}, #9{y}, #0{x}, #11{y}, #0{x}, #13{y}, #0{x}, #15{y}, #0{x}, #17{y}, #0{x}, #19{y}) Join on=(#0{x} = #2{x} = #4{x} = #6{x} = #8{x} = #10{x} = #12{x} = #14{x} = #16{x} = #18{x}) type=delta implementation %0:t » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %1:t2 » %0:t[#0{x}]KA|4| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %2:t3 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %3:t4 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| %4:t5 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %3:t4[#0{x}]K|611| %5:t6 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %6:t7 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %7:t8 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %8:t9 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %9:t10[#0{x}]K|260| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| %9:t10 » %0:t[#0{x}]KA|4| » %1:t2[#0{x}]KA|10000| » %2:t3[#0{x}]K|169| » %8:t9[#0{x}]K|273| » %5:t6[#0{x}]K|299| » %6:t7[#0{x}]K|299| » %7:t8[#0{x}]K|299| » %4:t5[#0{x}]K|494| » %3:t4[#0{x}]K|611| ArrangeBy keys=[[#0{x}]] ReadIndex on=t t_x=[delta join 1st input (full scan)] ArrangeBy keys=[[#0{x}]] ReadIndex on=t2 tt_x=[delta join lookup] ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t3 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t4 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t5 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t6 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t7 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t8 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t9 ArrangeBy keys=[[#0{x}]] ReadStorage materialize.public.t10 Source materialize.public.t3 Source materialize.public.t4 Source materialize.public.t5 Source materialize.public.t6 Source materialize.public.t7 Source materialize.public.t8 Source materialize.public.t9 Source materialize.public.t10 Used Indexes: - materialize.public.t_x (delta join 1st input (full scan)) - materialize.public.tt_x (delta join lookup) Target cluster: quickstart EOF # Regression test for gh issue 27348 (cardinality + WMR panic) query T multiline EXPLAIN OPTIMIZED PLAN WITH(cardinality, humanized expressions) AS VERBOSE TEXT FOR WITH MUTUALLY RECURSIVE t(x int) AS (SELECT 1 UNION ALL SELECT * FROM u), u(x int) AS (SELECT t.x+1 FROM t WHERE x < 7) SELECT * FROM t; ---- Explained Query: With Mutually Recursive cte l0 = Union // { cardinality: "" } Get l1 // { cardinality: "" } Constant // { cardinality: "1" } - (1) cte l1 = Project (#1) // { cardinality: "" } Filter (#0{x} < 7) // { cardinality: "" } Map ((#0{x} + 1)) // { cardinality: "" } Get l0 // { cardinality: "" } Return // { cardinality: "" } Get l0 // { cardinality: "" } Target cluster: mz_catalog_server EOF