github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/limit (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y INT, s STRING, d DECIMAL NOT NULL, UNIQUE (s DESC, d)) 3 ---- 4 5 exec-ddl 6 ALTER TABLE a INJECT STATISTICS '[ 7 { 8 "columns": ["x"], 9 "created_at": "2018-01-01 1:00:00.00000+00:00", 10 "row_count": 2000, 11 "distinct_count": 2000 12 }, 13 { 14 "columns": ["x","y"], 15 "created_at": "2018-01-01 1:30:00.00000+00:00", 16 "row_count": 2000, 17 "distinct_count": 2000 18 }, 19 { 20 "columns": ["s"], 21 "created_at": "2018-01-01 1:30:00.00000+00:00", 22 "row_count": 2000, 23 "distinct_count": 10 24 }, 25 { 26 "columns": ["s","y"], 27 "created_at": "2018-01-01 1:40:00.00000+00:00", 28 "row_count": 2000, 29 "distinct_count": 100 30 } 31 ]' 32 ---- 33 34 build 35 SELECT * FROM a WHERE s = 'foo' LIMIT 5 36 ---- 37 limit 38 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 39 ├── cardinality: [0 - 5] 40 ├── stats: [rows=5] 41 ├── key: (1) 42 ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 43 ├── select 44 │ ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 45 │ ├── stats: [rows=200, distinct(3)=1, null(3)=0] 46 │ ├── key: (1) 47 │ ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 48 │ ├── limit hint: 5.00 49 │ ├── scan a 50 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 51 │ │ ├── stats: [rows=2000, distinct(1)=2000, null(1)=0, distinct(3)=10, null(3)=0, distinct(4)=200, null(4)=0] 52 │ │ ├── key: (1) 53 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 54 │ │ └── limit hint: 50.00 55 │ └── filters 56 │ └── s:3 = 'foo' [type=bool, outer=(3), constraints=(/3: [/'foo' - /'foo']; tight), fd=()-->(3)] 57 └── 5 [type=int] 58 59 build 60 SELECT * FROM a WHERE s = 'foo' LIMIT (SELECT 5 AS c) 61 ---- 62 limit 63 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 64 ├── immutable, side-effects 65 ├── stats: [rows=200] 66 ├── key: (1) 67 ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 68 ├── select 69 │ ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 70 │ ├── stats: [rows=200, distinct(3)=1, null(3)=0] 71 │ ├── key: (1) 72 │ ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 73 │ ├── scan a 74 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 75 │ │ ├── stats: [rows=2000, distinct(1)=2000, null(1)=0, distinct(3)=10, null(3)=0, distinct(4)=200, null(4)=0] 76 │ │ ├── key: (1) 77 │ │ └── fd: (1)-->(2-4), (3,4)~~>(1,2) 78 │ └── filters 79 │ └── s:3 = 'foo' [type=bool, outer=(3), constraints=(/3: [/'foo' - /'foo']; tight), fd=()-->(3)] 80 └── subquery [type=int] 81 └── max1-row 82 ├── columns: c:5(int!null) 83 ├── error: "more than one row returned by a subquery used as an expression" 84 ├── cardinality: [1 - 1] 85 ├── stats: [rows=1] 86 ├── key: () 87 ├── fd: ()-->(5) 88 └── project 89 ├── columns: c:5(int!null) 90 ├── cardinality: [1 - 1] 91 ├── stats: [rows=1] 92 ├── key: () 93 ├── fd: ()-->(5) 94 ├── values 95 │ ├── cardinality: [1 - 1] 96 │ ├── stats: [rows=1] 97 │ ├── key: () 98 │ └── () [type=tuple] 99 └── projections 100 └── 5 [as=c:5, type=int] 101 102 build 103 SELECT * FROM (SELECT * FROM a ORDER BY s LIMIT 5) WHERE s = 'foo' 104 ---- 105 select 106 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 107 ├── cardinality: [0 - 5] 108 ├── stats: [rows=1.26952228, distinct(3)=1, null(3)=0] 109 ├── key: (1) 110 ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 111 ├── limit 112 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 113 │ ├── internal-ordering: +3 114 │ ├── cardinality: [0 - 5] 115 │ ├── stats: [rows=5, distinct(1)=5, null(1)=0, distinct(3)=3.93848936, null(3)=0, distinct(4)=4.94412336, null(4)=0] 116 │ ├── key: (1) 117 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 118 │ ├── sort 119 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 120 │ │ ├── stats: [rows=2000, distinct(1)=2000, null(1)=0, distinct(3)=10, null(3)=0, distinct(4)=200, null(4)=0] 121 │ │ ├── key: (1) 122 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 123 │ │ ├── ordering: +3 124 │ │ ├── limit hint: 5.00 125 │ │ └── scan a 126 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 127 │ │ ├── stats: [rows=2000, distinct(1)=2000, null(1)=0, distinct(3)=10, null(3)=0, distinct(4)=200, null(4)=0] 128 │ │ ├── key: (1) 129 │ │ └── fd: (1)-->(2-4), (3,4)~~>(1,2) 130 │ └── 5 [type=int] 131 └── filters 132 └── s:3 = 'foo' [type=bool, outer=(3), constraints=(/3: [/'foo' - /'foo']; tight), fd=()-->(3)] 133 134 # Bump up null counts. 135 exec-ddl 136 ALTER TABLE a INJECT STATISTICS '[ 137 { 138 "columns": ["x"], 139 "created_at": "2018-01-01 1:00:00.00000+00:00", 140 "row_count": 2000, 141 "distinct_count": 2000 142 }, 143 { 144 "columns": ["x","y"], 145 "created_at": "2018-01-01 1:30:00.00000+00:00", 146 "row_count": 2000, 147 "distinct_count": 2000, 148 "null_count": 1000 149 }, 150 { 151 "columns": ["y"], 152 "created_at": "2018-01-01 1:30:00.00000+00:00", 153 "row_count": 2000, 154 "distinct_count": 501, 155 "null_count": 1000 156 }, 157 { 158 "columns": ["s"], 159 "created_at": "2018-01-01 1:30:00.00000+00:00", 160 "row_count": 2000, 161 "distinct_count": 11, 162 "null_count": 1000 163 } 164 ]' 165 ---- 166 167 build colstat=2 colstat=3 colstat=(2,3) 168 SELECT * FROM a LIMIT 5 169 ---- 170 limit 171 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 172 ├── cardinality: [0 - 5] 173 ├── stats: [rows=5, distinct(2)=4.98133092, null(2)=2.5, distinct(3)=4.02187199, null(3)=2.5, distinct(2,3)=5, null(2,3)=1.25] 174 ├── key: (1) 175 ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 176 ├── scan a 177 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 178 │ ├── stats: [rows=2000, distinct(2)=501, null(2)=1000, distinct(3)=11, null(3)=1000, distinct(2,3)=2000, null(2,3)=500] 179 │ ├── key: (1) 180 │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 181 │ └── limit hint: 5.00 182 └── 5 [type=int] 183 184 build colstat=2 colstat=3 colstat=(2,3) 185 SELECT * FROM a WHERE s = 'foo' LIMIT 5 186 ---- 187 limit 188 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 189 ├── cardinality: [0 - 5] 190 ├── stats: [rows=5, distinct(2)=4.99009771, null(2)=2.5, distinct(3)=0.994079471, null(3)=0, distinct(2,3)=4.99009771, null(2,3)=0] 191 ├── key: (1) 192 ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 193 ├── select 194 │ ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) 195 │ ├── stats: [rows=100, distinct(2)=92.7652197, null(2)=50, distinct(3)=1, null(3)=0, distinct(2,3)=92.7652197, null(2,3)=0] 196 │ ├── key: (1) 197 │ ├── fd: ()-->(3), (1)-->(2,4), (4)-->(1,2) 198 │ ├── limit hint: 5.00 199 │ ├── scan a 200 │ │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) 201 │ │ ├── stats: [rows=2000, distinct(1)=2000, null(1)=0, distinct(2)=501, null(2)=1000, distinct(3)=11, null(3)=1000, distinct(4)=200, null(4)=0, distinct(2,3)=2000, null(2,3)=500] 202 │ │ ├── key: (1) 203 │ │ ├── fd: (1)-->(2-4), (3,4)~~>(1,2) 204 │ │ └── limit hint: 100.00 205 │ └── filters 206 │ └── s:3 = 'foo' [type=bool, outer=(3), constraints=(/3: [/'foo' - /'foo']; tight), fd=()-->(3)] 207 └── 5 [type=int] 208 209 exec-ddl 210 CREATE TABLE b (x int) 211 ---- 212 213 # Regression test for #32578. Ensure that we don't estimate 0 rows for the 214 # offset. 215 opt colstat=1 216 SELECT * FROM b ORDER BY x LIMIT 1 OFFSET 9999 217 ---- 218 offset 219 ├── columns: x:1(int) 220 ├── internal-ordering: +1 221 ├── cardinality: [0 - 1] 222 ├── stats: [rows=1, distinct(1)=0.995511979, null(1)=0.01] 223 ├── ordering: +1 224 ├── limit 225 │ ├── columns: x:1(int) 226 │ ├── internal-ordering: +1 227 │ ├── cardinality: [0 - 10000] 228 │ ├── stats: [rows=1000, distinct(1)=100, null(1)=10] 229 │ ├── ordering: +1 230 │ ├── sort 231 │ │ ├── columns: x:1(int) 232 │ │ ├── stats: [rows=1000, distinct(1)=100, null(1)=10] 233 │ │ ├── ordering: +1 234 │ │ ├── limit hint: 10000.00 235 │ │ └── scan b 236 │ │ ├── columns: x:1(int) 237 │ │ └── stats: [rows=1000, distinct(1)=100, null(1)=10] 238 │ └── 10000 [type=int] 239 └── 9999 [type=int]