github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/testdata/telemetry/planning (about) 1 # This file contains telemetry tests for sql.plan.* counters. 2 3 exec 4 CREATE TABLE x (a INT PRIMARY KEY) 5 ---- 6 7 # Tests for EXPLAIN counters. 8 9 feature-whitelist 10 sql.plan.explain 11 sql.plan.explain-analyze 12 sql.plan.explain-opt 13 sql.plan.explain-opt-verbose 14 sql.plan.explain-distsql 15 ---- 16 17 feature-usage 18 EXPLAIN SELECT * FROM x 19 ---- 20 sql.plan.explain 21 22 feature-usage 23 EXPLAIN (DISTSQL) SELECT * FROM x 24 ---- 25 sql.plan.explain-distsql 26 27 feature-usage 28 EXPLAIN ANALYZE SELECT * FROM x 29 ---- 30 sql.plan.explain-analyze 31 32 feature-usage 33 EXPLAIN ANALYZE (DEBUG) SELECT * FROM x 34 ---- 35 36 feature-usage 37 EXPLAIN (OPT) SELECT * FROM x 38 ---- 39 sql.plan.explain-opt 40 41 feature-usage 42 EXPLAIN (OPT, VERBOSE) SELECT * FROM x 43 ---- 44 sql.plan.explain-opt-verbose 45 46 # Tests for hints. 47 48 feature-whitelist 49 sql.plan.hints.* 50 ---- 51 52 feature-usage 53 SELECT x FROM (VALUES (1)) AS a(x) INNER HASH JOIN (VALUES (1)) AS b(y) ON x = y 54 ---- 55 sql.plan.hints.hash-join 56 57 feature-usage 58 SELECT x FROM (VALUES (1)) AS a(x) INNER MERGE JOIN (VALUES (1)) AS b(y) ON x = y 59 ---- 60 sql.plan.hints.merge-join 61 62 feature-usage 63 SELECT a FROM (VALUES (1)) AS b(y) INNER LOOKUP JOIN x ON y = a 64 ---- 65 sql.plan.hints.lookup-join 66 67 feature-usage 68 SELECT * FROM x@primary 69 ---- 70 sql.plan.hints.index 71 sql.plan.hints.index.select 72 73 feature-usage 74 UPDATE x@primary SET a=1 WHERE a>1 75 ---- 76 sql.plan.hints.index 77 sql.plan.hints.index.update 78 79 feature-usage 80 DELETE FROM x@primary WHERE a>1 81 ---- 82 sql.plan.hints.index 83 sql.plan.hints.index.delete 84 85 # Tests for tracking important setting changes. 86 87 feature-whitelist 88 sql.plan.reorder-joins.* 89 sql.plan.automatic-stats.* 90 ---- 91 92 feature-usage 93 SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 0 94 ---- 95 sql.plan.reorder-joins.set-limit-0 96 97 feature-usage 98 SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 3 99 ---- 100 sql.plan.reorder-joins.set-limit-3 101 102 feature-usage 103 SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 6 104 ---- 105 sql.plan.reorder-joins.set-limit-6 106 107 feature-usage 108 SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 20 109 ---- 110 sql.plan.reorder-joins.set-limit-more 111 112 feature-usage 113 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = on 114 ---- 115 sql.plan.automatic-stats.enabled 116 117 feature-usage 118 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = off 119 ---- 120 sql.plan.automatic-stats.disabled 121 122 feature-usage 123 RESET CLUSTER SETTING sql.stats.automatic_collection.enabled 124 ---- 125 sql.plan.automatic-stats.enabled 126 127 # Test telemetry for manual statistics creation. 128 feature-whitelist 129 sql.plan.stats.created 130 ---- 131 132 feature-usage 133 CREATE STATISTICS stats FROM x 134 ---- 135 sql.plan.stats.created 136 137 # Test various planning counters. 138 feature-whitelist 139 sql.plan.cte.* 140 sql.plan.lateral-join 141 sql.plan.subquery.* 142 ---- 143 144 feature-usage 145 WITH a AS (SELECT 1) SELECT * FROM a 146 ---- 147 sql.plan.cte 148 149 feature-usage 150 WITH RECURSIVE a AS (SELECT 1 UNION ALL SELECT * FROM a WHERE false) SELECT * FROM a 151 ---- 152 sql.plan.cte 153 sql.plan.cte.recursive 154 155 feature-usage 156 SELECT * FROM (VALUES (1), (2)) AS a(x), LATERAL (SELECT a.x+1) 157 ---- 158 sql.plan.lateral-join 159 160 feature-usage 161 SELECT * FROM (VALUES (1), (2)) AS a(x) JOIN LATERAL (SELECT a.x+1 AS x) AS b ON a.x < b.x 162 ---- 163 sql.plan.lateral-join 164 165 feature-usage 166 SELECT 1 = (SELECT a FROM x LIMIT 1) 167 ---- 168 sql.plan.subquery 169 170 feature-usage 171 SELECT x FROM (VALUES (1)) AS b(x) WHERE EXISTS(SELECT * FROM (VALUES (1)) AS a(x) WHERE a.x = b.x) 172 ---- 173 sql.plan.subquery.correlated 174 175 # Test some sql.plan.ops counters, using some esoteric operators unlikely to be 176 # executed in background activity). 177 feature-whitelist 178 sql.plan.ops.cast.string::inet 179 sql.plan.ops.bin.jsonb - string 180 sql.plan.ops.array.* 181 ---- 182 183 feature-usage 184 SELECT '1.2.3.4'::STRING::INET 185 ---- 186 sql.plan.ops.cast.string::inet 187 188 feature-usage 189 SELECT '{"a":"b","c":123}'::JSON - 'a' 190 ---- 191 sql.plan.ops.bin.jsonb - string 192 193 feature-usage 194 SELECT ARRAY (SELECT 1)[1] 195 ---- 196 sql.plan.ops.array.flatten 197 sql.plan.ops.array.ind 198 199 feature-usage 200 INSERT INTO x SELECT unnest(ARRAY[9, 10, 11, 12]) 201 ---- 202 sql.plan.ops.array.cons 203 204 # Test a few sql.plan.opt.node counters. 205 feature-whitelist 206 sql.plan.opt.node.project-set 207 sql.plan.opt.node.join.* 208 ---- 209 210 feature-usage 211 SELECT EXISTS(SELECT * FROM generate_series(1,2)) 212 ---- 213 sql.plan.opt.node.project-set 214 215 feature-usage 216 SELECT * FROM x AS x1 NATURAL INNER MERGE JOIN x AS x2 217 ---- 218 sql.plan.opt.node.join.algo.merge 219 sql.plan.opt.node.join.type.inner