# 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. # Requires stable object IDs. reset-server mode cockroach statement ok CREATE TABLE t (a int) # No fast_path_clusters and fast_path_limit when the feature flag is off. query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT * FROM t ---- { "plans": { "raw": { "text": "Get materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } }, "optimized": { "global": { "text": "Explained Query:\n ReadStorage materialize.public.t\n\nSource materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } } } ], "sources": [ { "id": { "User": 1 }, "op": null } ] } }, "fast_path": { "text": "", "json": null } } }, "insights": { "imports": { "u1": { "name": { "database": "materialize", "schema": "public", "item": "t" }, "type": "storage" } }, "fast_path_clusters": {}, "fast_path_limit": null, "persist_count": [] }, "cluster": { "name": "quickstart", "id": { "User": 1 } }, "redacted_sql": "SELECT * FROM [u1 AS materialize.public.t]" } EOF simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_fast_path_plan_insights TO true; ---- COMPLETE 0 # Assert fast_path_limit is true. This must be done before adding the index. query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT * FROM t ---- { "plans": { "raw": { "text": "Get materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } }, "optimized": { "global": { "text": "Explained Query:\n ReadStorage materialize.public.t\n\nSource materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } } } ], "sources": [ { "id": { "User": 1 }, "op": null } ] } }, "fast_path": { "text": "", "json": null } } }, "insights": { "imports": { "u1": { "name": { "database": "materialize", "schema": "public", "item": "t" }, "type": "storage" } }, "fast_path_clusters": {}, "fast_path_limit": 1000, "persist_count": [] }, "cluster": { "name": "quickstart", "id": { "User": 1 } }, "redacted_sql": "SELECT * FROM [u1 AS materialize.public.t]" } EOF query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT * FROM t t1, t t2 ---- { "plans": { "raw": { "text": "CrossJoin\n Get materialize.public.t\n Get materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "Join": { "left": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "right": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "on": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "kind": "Inner" } } }, "optimized": { "global": { "text": "Explained Query:\n With\n cte l0 =\n ArrangeBy keys=[[]]\n ReadStorage materialize.public.t\n Return\n CrossJoin type=differential\n Get l0\n Get l0\n\nSource materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "keys": [ [] ] } }, "body": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } } } } } ], "sources": [ { "id": { "User": 1 }, "op": null } ] } }, "fast_path": { "text": "", "json": null } } }, "insights": { "imports": { "u1": { "name": { "database": "materialize", "schema": "public", "item": "t" }, "type": "storage" } }, "fast_path_clusters": {}, "fast_path_limit": null, "persist_count": [] }, "cluster": { "name": "quickstart", "id": { "User": 1 } }, "redacted_sql": "SELECT * FROM [u1 AS materialize.public.t] AS t1, [u1 AS materialize.public.t] AS t2" } EOF statement ok CREATE DEFAULT INDEX ON t query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT * FROM t ---- { "plans": { "raw": { "text": "Get materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } }, "optimized": { "global": { "text": "Explained Query:\n ReadIndex on=t t_primary_idx=[*** full scan ***]\n\nUsed Indexes:\n - materialize.public.t_primary_idx (*** full scan ***)\n\nTarget cluster: quickstart\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 2 }, "FullScan" ] ] } } } } ], "sources": [] } }, "fast_path": { "text": "Explained Query (fast path):\n →Map/Filter/Project\n →Indexed materialize.public.t (using materialize.public.t_primary_idx)\n\nUsed Indexes:\n - materialize.public.t_primary_idx (*** full scan ***)\n\nTarget cluster: quickstart\n", "json": { "plans": [ { "id": "Explained Query (fast path)", "plan": { "PeekExisting": [ { "User": 1 }, { "User": 2 }, null, { "mfp": { "expressions": [], "predicates": [], "projection": [ 0 ], "input_arity": 1 } } ] } } ], "sources": [] } } } }, "insights": { "imports": { "u2": { "name": { "database": "materialize", "schema": "public", "item": "t_primary_idx" }, "type": "compute" } }, "fast_path_clusters": {}, "fast_path_limit": null, "persist_count": [] }, "cluster": { "name": "quickstart", "id": { "User": 1 } }, "redacted_sql": "SELECT * FROM [u1 AS materialize.public.t]" } EOF query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT * FROM t t1, t t2 ---- { "plans": { "raw": { "text": "CrossJoin\n Get materialize.public.t\n Get materialize.public.t\n\nTarget cluster: quickstart\n", "json": { "Join": { "left": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "right": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "on": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "kind": "Inner" } } }, "optimized": { "global": { "text": "Explained Query:\n With\n cte l0 =\n ArrangeBy keys=[[]]\n ReadIndex on=t t_primary_idx=[*** full scan ***]\n Return\n CrossJoin type=differential\n Get l0\n Get l0\n\nUsed Indexes:\n - materialize.public.t_primary_idx (*** full scan ***)\n\nTarget cluster: quickstart\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "ArrangeBy": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": { "Index": [ [ { "User": 2 }, "FullScan" ] ] } } }, "keys": [ [] ] } }, "body": { "Join": { "inputs": [ { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } }, { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "UnknownOrLocal" } } ], "equivalences": [], "implementation": { "Differential": [ [ 0, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 0 } } ], [ [ 1, [], { "V1": { "unique_key": false, "key_length": 0, "arranged": false, "cardinality": null, "filters": { "literal_equality": false, "like": false, "is_null": false, "literal_inequality": 0, "any_filter": false }, "input": 1 } } ] ] ] } } } } } } ], "sources": [] } }, "fast_path": { "text": "", "json": null } } }, "insights": { "imports": { "u2": { "name": { "database": "materialize", "schema": "public", "item": "t_primary_idx" }, "type": "compute" } }, "fast_path_clusters": {}, "fast_path_limit": null, "persist_count": [] }, "cluster": { "name": "quickstart", "id": { "User": 1 } }, "redacted_sql": "SELECT * FROM [u1 AS materialize.public.t] AS t1, [u1 AS materialize.public.t] AS t2" } EOF statement ok CREATE CLUSTER other SIZE '1' statement ok SET CLUSTER = other; # Ensure fast_path_clusters is set here. query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT * FROM t ---- { "plans": { "raw": { "text": "Get materialize.public.t\n\nTarget cluster: other\n", "json": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } } }, "optimized": { "global": { "text": "Explained Query:\n ReadStorage materialize.public.t\n\nSource materialize.public.t\n\nTarget cluster: other\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } } } ], "sources": [ { "id": { "User": 1 }, "op": null } ] } }, "fast_path": { "text": "", "json": null } } }, "insights": { "imports": { "u1": { "name": { "database": "materialize", "schema": "public", "item": "t" }, "type": "storage" } }, "fast_path_clusters": { "quickstart": { "index": { "database": "materialize", "schema": "public", "item": "t_primary_idx" }, "on": { "database": "materialize", "schema": "public", "item": "t" } } }, "fast_path_limit": 1000, "persist_count": [] }, "cluster": { "name": "other", "id": { "User": 2 } }, "redacted_sql": "SELECT * FROM [u1 AS materialize.public.t]" } EOF # Ensure persist_count is set here. query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT count(*) FROM t ---- { "plans": { "raw": { "text": "Reduce aggregates=[count(*)]\n Get materialize.public.t\n\nTarget cluster: other\n", "json": { "Reduce": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] } } }, "group_key": [], "aggregates": [ { "func": "Count", "expr": { "Literal": [ { "data": [ 2 ] }, { "scalar_type": "Bool", "nullable": false }, null ] }, "distinct": false } ], "expected_group_size": null } } }, "optimized": { "global": { "text": "Explained Query:\n With\n cte l0 =\n Reduce aggregates=[count(*)]\n Project ()\n ReadStorage materialize.public.t\n Return\n Union\n Get l0\n Map (0)\n Union\n Negate\n Project ()\n Get l0\n Constant\n - ()\n\nSource materialize.public.t\n\nTarget cluster: other\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Let": { "id": 0, "value": { "Reduce": { "input": { "Project": { "input": { "Get": { "id": { "Global": { "User": 1 } }, "typ": { "column_types": [ { "scalar_type": "Int32", "nullable": true } ], "keys": [] }, "access_strategy": "Persist" } }, "outputs": [] } }, "group_key": [], "aggregates": [ { "func": "Count", "expr": { "Literal": [ { "Ok": { "data": [ 2 ] } }, { "scalar_type": "Bool", "nullable": false } ] }, "distinct": false } ], "monotonic": false, "expected_group_size": null } }, "body": { "Union": { "base": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int64", "nullable": false } ], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "inputs": [ { "Map": { "input": { "Union": { "base": { "Negate": { "input": { "Project": { "input": { "Get": { "id": { "Local": 0 }, "typ": { "column_types": [ { "scalar_type": "Int64", "nullable": false } ], "keys": [ [] ] }, "access_strategy": "UnknownOrLocal" } }, "outputs": [] } } } }, "inputs": [ { "Constant": { "rows": { "Ok": [ [ { "data": [] }, 1 ] ] }, "typ": { "column_types": [], "keys": [] } } } ] } }, "scalars": [ { "Literal": [ { "Ok": { "data": [ 49 ] } }, { "scalar_type": "Int64", "nullable": false } ] } ] } } ] } } } } } ], "sources": [ { "id": { "User": 1 }, "op": null } ] } }, "fast_path": { "text": "", "json": null } } }, "insights": { "imports": { "u1": { "name": { "database": "materialize", "schema": "public", "item": "t" }, "type": "storage" } }, "fast_path_clusters": {}, "fast_path_limit": null, "persist_count": [ { "database": "materialize", "schema": "public", "item": "t" } ] }, "cluster": { "name": "other", "id": { "User": 2 } }, "redacted_sql": "SELECT pg_catalog.count(*) FROM [u1 AS materialize.public.t]" } EOF # Ensure cluster is null for views. query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR CREATE VIEW V AS SELECT 1 ---- { "plans": { "raw": { "text": "Map (1)\n Constant\n - ()\n", "json": { "Map": { "input": { "Constant": { "rows": [ { "data": [] } ], "typ": { "column_types": [], "keys": [] } } }, "scalars": [ { "Literal": [ { "data": [ 45, 1 ] }, { "scalar_type": "Int32", "nullable": false }, null ] } ] } } }, "optimized": { "global": { "text": "", "json": null }, "fast_path": { "text": "", "json": null } } }, "insights": null, "cluster": null, "redacted_sql": null } EOF # Ensure redacted sql. query T multiline EXPLAIN PLAN INSIGHTS AS JSON FOR SELECT 'abc' ---- { "plans": { "raw": { "text": "Map (\"abc\")\n Constant\n - ()\n\nTarget cluster: mz_catalog_server\n", "json": { "Map": { "input": { "Constant": { "rows": [ { "data": [] } ], "typ": { "column_types": [], "keys": [] } } }, "scalars": [ { "Literal": [ { "data": [ 19, 3, 97, 98, 99 ] }, { "scalar_type": "String", "nullable": false }, null ] } ] } } }, "optimized": { "global": { "text": "Explained Query:\n Constant\n - (\"abc\")\n\nTarget cluster: mz_catalog_server\n", "json": { "plans": [ { "id": "Explained Query", "plan": { "Constant": { "rows": { "Ok": [ [ { "data": [ 19, 3, 97, 98, 99 ] }, 1 ] ] }, "typ": { "column_types": [ { "scalar_type": "String", "nullable": false } ], "keys": [] } } } } ], "sources": [] } }, "fast_path": { "text": "Explained Query (fast path):\n →Constant (1 rows)\n\nTarget cluster: mz_catalog_server\n", "json": { "plans": [ { "id": "Explained Query (fast path)", "plan": { "Constant": [ { "Ok": [ [ { "data": [ 19, 3, 97, 98, 99 ] }, 1 ] ] }, { "column_types": [ { "scalar_type": "String", "nullable": false } ], "keys": [] } ] } } ], "sources": [] } } } }, "insights": { "imports": {}, "fast_path_clusters": {}, "fast_path_limit": null, "persist_count": [] }, "cluster": { "name": "mz_catalog_server", "id": { "System": 2 } }, "redacted_sql": "SELECT ''" } EOF