github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/distsql_automatic_stats (about) 1 # LogicTest: fakedist fakedist-metadata 2 3 # Disable automatic stats 4 statement ok 5 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false 6 7 statement ok 8 CREATE TABLE data (a INT, b INT, c FLOAT, d DECIMAL, PRIMARY KEY (a, b, c), INDEX d_idx (d)) 9 10 # Enable automatic stats 11 statement ok 12 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true 13 14 # Generate all combinations of values 1 to 10. 15 statement ok 16 INSERT INTO data SELECT a, b, c::FLOAT, NULL FROM 17 generate_series(1, 10) AS a(a), 18 generate_series(1, 10) AS b(b), 19 generate_series(1, 10) AS c(c) 20 21 query TTIII colnames,rowsort,retry 22 SELECT statistics_name, column_names, row_count, distinct_count, null_count 23 FROM [SHOW STATISTICS FOR TABLE data] ORDER BY column_names::STRING, created 24 ---- 25 statistics_name column_names row_count distinct_count null_count 26 __auto__ {a,b,c} 1000 1000 0 27 __auto__ {a,b} 1000 100 0 28 __auto__ {a} 1000 10 0 29 __auto__ {b} 1000 10 0 30 __auto__ {c} 1000 10 0 31 __auto__ {d} 1000 1 1000 32 33 # Disable automatic stats 34 statement ok 35 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false 36 37 # Update more than 20% of the table. 38 statement ok 39 UPDATE data SET d = 10 WHERE (a = 1 OR a = 2 OR a = 3) AND b > 1 40 41 # There should be no change to stats. 42 query TTIII colnames,rowsort 43 SELECT statistics_name, column_names, row_count, distinct_count, null_count 44 FROM [SHOW STATISTICS FOR TABLE data] ORDER BY column_names::STRING, created 45 ---- 46 statistics_name column_names row_count distinct_count null_count 47 __auto__ {a,b,c} 1000 1000 0 48 __auto__ {a,b} 1000 100 0 49 __auto__ {a} 1000 10 0 50 __auto__ {b} 1000 10 0 51 __auto__ {c} 1000 10 0 52 __auto__ {d} 1000 1 1000 53 54 # Enable automatic stats 55 statement ok 56 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true 57 58 # Update more than 20% of the table. 59 statement ok 60 UPDATE data SET d = 12 WHERE d = 10 61 62 query TTIII colnames,rowsort,retry 63 SELECT statistics_name, column_names, row_count, distinct_count, null_count 64 FROM [SHOW STATISTICS FOR TABLE data] ORDER BY column_names::STRING, created 65 ---- 66 statistics_name column_names row_count distinct_count null_count 67 __auto__ {a,b,c} 1000 1000 0 68 __auto__ {a,b,c} 1000 1000 0 69 __auto__ {a,b} 1000 100 0 70 __auto__ {a,b} 1000 100 0 71 __auto__ {a} 1000 10 0 72 __auto__ {a} 1000 10 0 73 __auto__ {b} 1000 10 0 74 __auto__ {b} 1000 10 0 75 __auto__ {c} 1000 10 0 76 __auto__ {c} 1000 10 0 77 __auto__ {d} 1000 1 1000 78 __auto__ {d} 1000 2 730 79 80 # Upsert more than 20% of the table. 81 statement ok 82 UPSERT INTO data SELECT a, b, c::FLOAT, 1 FROM 83 generate_series(1, 11) AS a(a), 84 generate_series(1, 10) AS b(b), 85 generate_series(1, 5) AS c(c) 86 87 query TTIII colnames,rowsort,retry 88 SELECT statistics_name, column_names, row_count, distinct_count, null_count 89 FROM [SHOW STATISTICS FOR TABLE data] ORDER BY column_names::STRING, created 90 ---- 91 statistics_name column_names row_count distinct_count null_count 92 __auto__ {a,b,c} 1000 1000 0 93 __auto__ {a,b,c} 1000 1000 0 94 __auto__ {a,b,c} 1050 1050 0 95 __auto__ {a,b} 1000 100 0 96 __auto__ {a,b} 1000 100 0 97 __auto__ {a,b} 1050 110 0 98 __auto__ {a} 1000 10 0 99 __auto__ {a} 1000 10 0 100 __auto__ {a} 1050 11 0 101 __auto__ {b} 1000 10 0 102 __auto__ {b} 1000 10 0 103 __auto__ {b} 1050 10 0 104 __auto__ {c} 1000 10 0 105 __auto__ {c} 1000 10 0 106 __auto__ {c} 1050 10 0 107 __auto__ {d} 1000 1 1000 108 __auto__ {d} 1000 2 730 109 __auto__ {d} 1050 3 365 110 111 # Delete more than 20% of the table. 112 statement ok 113 DELETE FROM data WHERE c > 5 114 115 query TTIII colnames,rowsort,retry 116 SELECT statistics_name, column_names, row_count, distinct_count, null_count 117 FROM [SHOW STATISTICS FOR TABLE data] ORDER BY column_names::STRING, created 118 ---- 119 statistics_name column_names row_count distinct_count null_count 120 __auto__ {a,b,c} 1000 1000 0 121 __auto__ {a,b,c} 1000 1000 0 122 __auto__ {a,b,c} 1050 1050 0 123 __auto__ {a,b,c} 550 550 0 124 __auto__ {a,b} 1000 100 0 125 __auto__ {a,b} 1000 100 0 126 __auto__ {a,b} 1050 110 0 127 __auto__ {a,b} 550 110 0 128 __auto__ {a} 1000 10 0 129 __auto__ {a} 1000 10 0 130 __auto__ {a} 1050 11 0 131 __auto__ {a} 550 11 0 132 __auto__ {b} 1000 10 0 133 __auto__ {b} 1000 10 0 134 __auto__ {b} 1050 10 0 135 __auto__ {b} 550 10 0 136 __auto__ {c} 1000 10 0 137 __auto__ {c} 1000 10 0 138 __auto__ {c} 1050 10 0 139 __auto__ {c} 550 5 0 140 __auto__ {d} 1000 1 1000 141 __auto__ {d} 1000 2 730 142 __auto__ {d} 1050 3 365 143 __auto__ {d} 550 1 0 144 145 # Test CREATE TABLE ... AS 146 statement ok 147 CREATE TABLE copy AS SELECT * FROM data 148 149 # Distinct count for rowid can be flaky, so don't show it. The estimate is 150 # almost always 550, but occasionally it is 549... 151 query TTII colnames,rowsort,retry 152 SELECT statistics_name, column_names, row_count, null_count 153 FROM [SHOW STATISTICS FOR TABLE copy] ORDER BY column_names::STRING, created 154 ---- 155 statistics_name column_names row_count null_count 156 __auto__ {a} 550 0 157 __auto__ {b} 550 0 158 __auto__ {c} 550 0 159 __auto__ {d} 550 0 160 __auto__ {rowid} 550 0 161 162 statement ok 163 CREATE TABLE test_create (x INT PRIMARY KEY, y CHAR) 164 165 query TTIII colnames,rowsort,retry 166 SELECT statistics_name, column_names, row_count, distinct_count, null_count 167 FROM [SHOW STATISTICS FOR TABLE test_create] ORDER BY column_names::STRING, created 168 ---- 169 statistics_name column_names row_count distinct_count null_count 170 __auto__ {x} 0 0 0 171 __auto__ {y} 0 0 0 172 173 # Test fast path delete. 174 statement ok 175 DELETE FROM copy WHERE true 176 177 query TTII colnames,rowsort,retry 178 SELECT statistics_name, column_names, row_count, null_count 179 FROM [SHOW STATISTICS FOR TABLE copy] ORDER BY column_names::STRING, created 180 ---- 181 statistics_name column_names row_count null_count 182 __auto__ {a} 550 0 183 __auto__ {a} 0 0 184 __auto__ {b} 550 0 185 __auto__ {b} 0 0 186 __auto__ {c} 550 0 187 __auto__ {c} 0 0 188 __auto__ {d} 550 0 189 __auto__ {d} 0 0 190 __auto__ {rowid} 550 0 191 __auto__ {rowid} 0 0