github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/customer (about) 1 # ============================================================================== 2 # This file contains schema and queries collected from various customers who 3 # have publicly posted their schema and queries, such as in a Github issue or 4 # one of our other public support channels. The purpose of collecting these 5 # queries is to minimize the chance of performance regression in future versions 6 # of Cockroach DB. 7 # ============================================================================== 8 9 10 # ------------------------------------------------------------------------------ 11 # Github Issue 20334: Ensure that optimizer uses the secondary 12 # edges_auto_index_fk_dst_ref_nodes index so it can then merge join. 13 # ------------------------------------------------------------------------------ 14 exec-ddl 15 CREATE TABLE nodes ( 16 id INT NOT NULL, 17 payload STRING NULL, 18 CONSTRAINT "primary" PRIMARY KEY (id ASC), 19 FAMILY "primary" (id, payload) 20 ) 21 ---- 22 23 exec-ddl 24 CREATE TABLE edges ( 25 src INT NOT NULL, 26 dst INT NOT NULL, 27 payload STRING NULL, 28 CONSTRAINT "primary" PRIMARY KEY (src ASC, dst ASC), 29 CONSTRAINT fk_dst_ref_nodes FOREIGN KEY (dst) REFERENCES nodes (id), 30 INDEX edges_auto_index_fk_dst_ref_nodes (dst ASC), 31 CONSTRAINT fk_src_ref_nodes FOREIGN KEY (src) REFERENCES nodes (id), 32 FAMILY "primary" (src, dst, payload) 33 ) 34 ---- 35 36 opt 37 select nodes.id,dst from nodes join edges on edges.dst=nodes.id 38 ---- 39 inner-join (merge) 40 ├── columns: id:1!null dst:4!null 41 ├── left ordering: +1 42 ├── right ordering: +4 43 ├── fd: (1)==(4), (4)==(1) 44 ├── scan nodes 45 │ ├── columns: id:1!null 46 │ ├── key: (1) 47 │ └── ordering: +1 48 ├── scan edges@edges_auto_index_fk_dst_ref_nodes 49 │ ├── columns: dst:4!null 50 │ └── ordering: +4 51 └── filters (true) 52 53 # ------------------------------------------------------------------------------ 54 # Github Issues 16313/16426: Ensure that STORING index is used to filter unread 55 # articles before index joining to the primary index. 56 # ------------------------------------------------------------------------------ 57 exec-ddl 58 CREATE TABLE article ( 59 id INT NOT NULL DEFAULT unique_rowid(), 60 feed INT NOT NULL, 61 folder INT NOT NULL, 62 hash STRING NULL, 63 title STRING NULL, 64 summary STRING NULL, 65 content STRING NULL, 66 link STRING NULL, 67 read BOOL NULL, 68 date TIMESTAMP WITH TIME ZONE NULL, 69 retrieved TIMESTAMP WITH TIME ZONE NULL, 70 CONSTRAINT "primary" PRIMARY KEY (folder ASC, feed ASC, id ASC), 71 UNIQUE INDEX article_id_key (id ASC), 72 UNIQUE INDEX article_hash_key (hash ASC), 73 UNIQUE INDEX article_idx_read_key (id ASC) STORING (read), 74 FAMILY "primary" (id, feed, folder, hash, title, summary, content, link, read, date, retrieved) 75 ) INTERLEAVE IN PARENT feed (folder, feed) 76 ---- 77 78 opt 79 SELECT id, feed, folder, title, summary, content, link, date 80 FROM article 81 WHERE NOT read and id > 0 order by id limit 50 82 ---- 83 project 84 ├── columns: id:1!null feed:2!null folder:3!null title:5 summary:6 content:7 link:8 date:10 85 ├── cardinality: [0 - 50] 86 ├── key: (1) 87 ├── fd: (1)-->(2,3,5-8,10) 88 ├── ordering: +1 89 └── limit 90 ├── columns: id:1!null feed:2!null folder:3!null title:5 summary:6 content:7 link:8 read:9!null date:10 91 ├── internal-ordering: +1 opt(9) 92 ├── cardinality: [0 - 50] 93 ├── key: (1) 94 ├── fd: ()-->(9), (1)-->(2,3,5-8,10) 95 ├── ordering: +1 opt(9) [actual: +1] 96 ├── index-join article 97 │ ├── columns: id:1!null feed:2!null folder:3!null title:5 summary:6 content:7 link:8 read:9!null date:10 98 │ ├── key: (1) 99 │ ├── fd: ()-->(9), (1)-->(2,3,5-8,10) 100 │ ├── ordering: +1 opt(9) [actual: +1] 101 │ ├── limit hint: 50.00 102 │ └── select 103 │ ├── columns: id:1!null feed:2!null folder:3!null read:9!null 104 │ ├── key: (1) 105 │ ├── fd: ()-->(9), (1)-->(2,3) 106 │ ├── ordering: +1 opt(9) [actual: +1] 107 │ ├── limit hint: 50.00 108 │ ├── scan article@article_idx_read_key 109 │ │ ├── columns: id:1!null feed:2!null folder:3!null read:9 110 │ │ ├── constraint: /1: [/1 - ] 111 │ │ ├── key: (1) 112 │ │ ├── fd: (1)-->(2,3,9) 113 │ │ ├── ordering: +1 opt(9) [actual: +1] 114 │ │ └── limit hint: 101.01 115 │ └── filters 116 │ └── NOT read:9 [outer=(9), constraints=(/9: [/false - /false]; tight), fd=()-->(9)] 117 └── 50 118 119 # Check that forcing the index works as well. 120 opt 121 SELECT id, feed, folder, title, summary, content, link, date 122 FROM article@article_idx_read_key 123 WHERE NOT read and id > 0 order by id limit 50 124 ---- 125 project 126 ├── columns: id:1!null feed:2!null folder:3!null title:5 summary:6 content:7 link:8 date:10 127 ├── cardinality: [0 - 50] 128 ├── key: (1) 129 ├── fd: (1)-->(2,3,5-8,10) 130 ├── ordering: +1 131 └── limit 132 ├── columns: id:1!null feed:2!null folder:3!null title:5 summary:6 content:7 link:8 read:9!null date:10 133 ├── internal-ordering: +1 opt(9) 134 ├── cardinality: [0 - 50] 135 ├── key: (1) 136 ├── fd: ()-->(9), (1)-->(2,3,5-8,10) 137 ├── ordering: +1 opt(9) [actual: +1] 138 ├── index-join article 139 │ ├── columns: id:1!null feed:2!null folder:3!null title:5 summary:6 content:7 link:8 read:9!null date:10 140 │ ├── key: (1) 141 │ ├── fd: ()-->(9), (1)-->(2,3,5-8,10) 142 │ ├── ordering: +1 opt(9) [actual: +1] 143 │ ├── limit hint: 50.00 144 │ └── select 145 │ ├── columns: id:1!null feed:2!null folder:3!null read:9!null 146 │ ├── key: (1) 147 │ ├── fd: ()-->(9), (1)-->(2,3) 148 │ ├── ordering: +1 opt(9) [actual: +1] 149 │ ├── limit hint: 50.00 150 │ ├── scan article@article_idx_read_key 151 │ │ ├── columns: id:1!null feed:2!null folder:3!null read:9 152 │ │ ├── constraint: /1: [/1 - ] 153 │ │ ├── flags: force-index=article_idx_read_key 154 │ │ ├── key: (1) 155 │ │ ├── fd: (1)-->(2,3,9) 156 │ │ ├── ordering: +1 opt(9) [actual: +1] 157 │ │ └── limit hint: 101.01 158 │ └── filters 159 │ └── NOT read:9 [outer=(9), constraints=(/9: [/false - /false]; tight), fd=()-->(9)] 160 └── 50 161 162 # Use only columns covered by the index. 163 opt 164 SELECT id, read FROM article WHERE NOT read and id > 0 order by id limit 5 165 ---- 166 limit 167 ├── columns: id:1!null read:9!null 168 ├── internal-ordering: +1 opt(9) 169 ├── cardinality: [0 - 5] 170 ├── key: (1) 171 ├── fd: ()-->(9) 172 ├── ordering: +1 opt(9) [actual: +1] 173 ├── select 174 │ ├── columns: id:1!null read:9!null 175 │ ├── key: (1) 176 │ ├── fd: ()-->(9) 177 │ ├── ordering: +1 opt(9) [actual: +1] 178 │ ├── limit hint: 5.00 179 │ ├── scan article@article_idx_read_key 180 │ │ ├── columns: id:1!null read:9 181 │ │ ├── constraint: /1: [/1 - ] 182 │ │ ├── key: (1) 183 │ │ ├── fd: (1)-->(9) 184 │ │ ├── ordering: +1 opt(9) [actual: +1] 185 │ │ └── limit hint: 5.27 186 │ └── filters 187 │ └── NOT read:9 [outer=(9), constraints=(/9: [/false - /false]; tight), fd=()-->(9)] 188 └── 5 189 190 # ------------------------------------------------------------------------------ 191 # Github Issue 14241: Ensure that optimizer uses a reverse scan over test_idx 192 # with restrictive spans and no explicit sort operator. 193 # ------------------------------------------------------------------------------ 194 exec-ddl 195 CREATE TABLE IF NOT EXISTS leaderboard_record ( 196 PRIMARY KEY (leaderboard_id, expires_at, owner_id), 197 -- Creating a foreign key constraint and defining indexes that include it 198 -- in the same transaction breaks. See issue cockroachdb/cockroach#13505. 199 -- In this case we prefer the indexes over the constraint. 200 -- FOREIGN KEY (leaderboard_id) REFERENCES leaderboard(id), 201 id BYTEA UNIQUE NOT NULL, 202 leaderboard_id BYTEA NOT NULL, 203 owner_id BYTEA NOT NULL, 204 handle VARCHAR(20) NOT NULL, 205 lang VARCHAR(18) DEFAULT 'en' NOT NULL, 206 location VARCHAR(64), -- e.g. "San Francisco, CA" 207 timezone VARCHAR(64), -- e.g. "Pacific Time (US & Canada)" 208 rank_value BIGINT DEFAULT 0 CHECK (rank_value >= 0) NOT NULL, 209 score BIGINT DEFAULT 0 NOT NULL, 210 num_score INT DEFAULT 0 CHECK (num_score >= 0) NOT NULL, 211 -- FIXME replace with JSONB 212 metadata BYTEA DEFAULT '{}' CHECK (length(metadata) < 16000) NOT NULL, 213 ranked_at INT CHECK (ranked_at >= 0) DEFAULT 0 NOT NULL, 214 updated_at INT CHECK (updated_at > 0) NOT NULL, 215 -- Used to enable proper order in revscan when sorting by score descending. 216 updated_at_inverse INT CHECK (updated_at > 0) NOT NULL, 217 expires_at INT CHECK (expires_at >= 0) DEFAULT 0 NOT NULL, 218 banned_at INT CHECK (expires_at >= 0) DEFAULT 0 NOT NULL, 219 INDEX test_idx(leaderboard_id, expires_at, score, updated_at_inverse, id) 220 ); 221 ---- 222 223 opt 224 SELECT score, expires_at 225 FROM leaderboard_record 226 WHERE leaderboard_id = 'test' 227 AND expires_at = 0 228 AND (score, updated_at_inverse, id) < (100, 500, 'some_id') 229 ORDER BY score desc, updated_at_inverse DESC 230 LIMIT 50 231 ---- 232 project 233 ├── columns: score:9!null expires_at:15!null [hidden: updated_at_inverse:14!null] 234 ├── cardinality: [0 - 50] 235 ├── fd: ()-->(15) 236 ├── ordering: -9,-14 opt(15) [actual: -9,-14] 237 └── scan leaderboard_record@test_idx,rev 238 ├── columns: id:1!null leaderboard_id:2!null score:9!null updated_at_inverse:14!null expires_at:15!null 239 ├── constraint: /2/15/9/14/1/3: [/'\x74657374'/0 - /'\x74657374'/0/100/500/'\x736f6d655f6964') 240 ├── limit: 50(rev) 241 ├── key: (1) 242 ├── fd: ()-->(2,15), (1)-->(9,14) 243 └── ordering: -9,-14 opt(2,15) [actual: -9,-14] 244 245 # ------------------------------------------------------------------------------ 246 # Github Issue 26444: Ensure that optimizer uses a merge join using the 247 # secondary indexes. 248 # ------------------------------------------------------------------------------ 249 exec-ddl 250 CREATE TABLE rides ( 251 id UUID NOT NULL, 252 rider_id UUID NULL, 253 vehicle_id UUID NULL, 254 start_address STRING NULL, 255 end_address STRING NULL, 256 start_time TIMESTAMP NULL, 257 end_time TIMESTAMP NULL, 258 revenue FLOAT NULL, 259 CONSTRAINT "primary" PRIMARY KEY (id ASC), 260 INDEX rides_vehicle_id_idx (vehicle_id ASC), 261 FAMILY "primary" (id, rider_id, vehicle_id, start_address, 262 end_address, start_time, end_time, revenue) 263 ) 264 ---- 265 266 exec-ddl 267 CREATE TABLE vehicles ( 268 id UUID NOT NULL, 269 type STRING NULL, 270 city STRING NOT NULL, 271 owner_id UUID NULL, 272 creation_time TIMESTAMP NULL, 273 status STRING NULL, 274 ext JSON NULL, 275 CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC), 276 INDEX vehicles_id_idx (id ASC) STORING (owner_id), 277 FAMILY "primary" (id, type, city, owner_id, creation_time, status, ext) 278 ) 279 ---- 280 281 opt 282 select v.owner_id, count(*) from rides r, vehicles v where v.id = r.vehicle_id group by v.owner_id 283 ---- 284 group-by 285 ├── columns: owner_id:12 count:16!null 286 ├── grouping columns: owner_id:12 287 ├── key: (12) 288 ├── fd: (12)-->(16) 289 ├── inner-join (merge) 290 │ ├── columns: vehicle_id:3!null v.id:9!null owner_id:12 291 │ ├── left ordering: +3 292 │ ├── right ordering: +9 293 │ ├── fd: (3)==(9), (9)==(3) 294 │ ├── scan r@rides_vehicle_id_idx 295 │ │ ├── columns: vehicle_id:3 296 │ │ └── ordering: +3 297 │ ├── scan v@vehicles_id_idx 298 │ │ ├── columns: v.id:9!null owner_id:12 299 │ │ └── ordering: +9 300 │ └── filters (true) 301 └── aggregations 302 └── count-rows [as=count_rows:16] 303 304 # ------------------------------------------------------------------------------ 305 # Github Issue 24415: Ensure the optimizer uses the covering index. 306 # ------------------------------------------------------------------------------ 307 exec-ddl 308 CREATE TABLE data ( 309 id UUID NULL, 310 value INT NULL, 311 col1 INT NULL, 312 col2 INT NULL, 313 col3 INT NULL, 314 col4 INT NULL, 315 col5 INT NULL, 316 col6 INT NULL, 317 col7 INT NULL, 318 col8 INT NULL, 319 col9 INT NULL, 320 col10 INT NULL, 321 INDEX foo (id ASC) STORING (value) 322 ) 323 ---- 324 325 opt 326 SELECT id, sum(value) FROM data GROUP BY id 327 ---- 328 group-by 329 ├── columns: id:1 sum:14 330 ├── grouping columns: id:1 331 ├── internal-ordering: +1 332 ├── key: (1) 333 ├── fd: (1)-->(14) 334 ├── scan data@foo 335 │ ├── columns: id:1 value:2 336 │ └── ordering: +1 337 └── aggregations 338 └── sum [as=sum:14, outer=(2)] 339 └── value:2 340 341 # ------------------------------------------------------------------------------ 342 # Github Issue 24297: Ensure the optimizer chooses the more selective secondary 343 # index that requires an explicit sort (rather than the less selective secondary 344 # index that avoids the sort). 345 # ------------------------------------------------------------------------------ 346 exec-ddl 347 CREATE TABLE t_sync_data ( 348 remote_id UUID NOT NULL, 349 conflict_remote_id UUID NULL, 350 user_id UUID NOT NULL, 351 last_sync_id INT NOT NULL, 352 data_type STRING NOT NULL, 353 sync_data STRING NULL, 354 deleted BOOL NOT NULL, 355 create_device_id STRING NOT NULL, 356 original_data_id STRING NOT NULL, 357 create_time INT NOT NULL, 358 last_update_time INT NOT NULL, 359 CONSTRAINT “primary” PRIMARY KEY (remote_id ASC), 360 INDEX t_sync_data_user_id_data_type_idx (user_id ASC, data_type ASC), 361 INDEX t_sync_data_last_sync_id_idx (last_sync_id ASC), 362 FAMILY f_meta (remote_id, conflict_remote_id, user_id, last_sync_id, data_type, deleted, create_device_id, original_data_id, create_time, last_update_time), 363 FAMILY f_data (sync_data), 364 INDEX index1 (last_sync_id, user_id, data_type), 365 INDEX index2 (user_id, data_type), 366 INDEX index3 (last_sync_id) 367 ) 368 ---- 369 370 opt 371 SELECT * 372 FROM t_sync_data 373 WHERE last_sync_id>0 374 AND user_id='11cd19e4-837d-4bff-4a76-aefa0ddbec64' 375 AND data_type='a01' 376 ORDER BY last_sync_id ASC 377 LIMIT 50 378 ---- 379 limit 380 ├── columns: remote_id:1!null conflict_remote_id:2 user_id:3!null last_sync_id:4!null data_type:5!null sync_data:6 deleted:7!null create_device_id:8!null original_data_id:9!null create_time:10!null last_update_time:11!null 381 ├── internal-ordering: +4 opt(3,5) 382 ├── cardinality: [0 - 50] 383 ├── key: (1) 384 ├── fd: ()-->(3,5), (1)-->(2,4,6-11) 385 ├── ordering: +4 opt(3,5) [actual: +4] 386 ├── sort 387 │ ├── columns: remote_id:1!null conflict_remote_id:2 user_id:3!null last_sync_id:4!null data_type:5!null sync_data:6 deleted:7!null create_device_id:8!null original_data_id:9!null create_time:10!null last_update_time:11!null 388 │ ├── key: (1) 389 │ ├── fd: ()-->(3,5), (1)-->(2,4,6-11) 390 │ ├── ordering: +4 opt(3,5) [actual: +4] 391 │ ├── limit hint: 50.00 392 │ └── select 393 │ ├── columns: remote_id:1!null conflict_remote_id:2 user_id:3!null last_sync_id:4!null data_type:5!null sync_data:6 deleted:7!null create_device_id:8!null original_data_id:9!null create_time:10!null last_update_time:11!null 394 │ ├── key: (1) 395 │ ├── fd: ()-->(3,5), (1)-->(2,4,6-11) 396 │ ├── index-join t_sync_data 397 │ │ ├── columns: remote_id:1!null conflict_remote_id:2 user_id:3!null last_sync_id:4!null data_type:5!null sync_data:6 deleted:7!null create_device_id:8!null original_data_id:9!null create_time:10!null last_update_time:11!null 398 │ │ ├── key: (1) 399 │ │ ├── fd: ()-->(3,5), (1)-->(2,4,6-11) 400 │ │ └── scan t_sync_data@t_sync_data_user_id_data_type_idx 401 │ │ ├── columns: remote_id:1!null user_id:3!null data_type:5!null 402 │ │ ├── constraint: /3/5/1: [/'11cd19e4-837d-4bff-4a76-aefa0ddbec64'/'a01' - /'11cd19e4-837d-4bff-4a76-aefa0ddbec64'/'a01'] 403 │ │ ├── key: (1) 404 │ │ └── fd: ()-->(3,5) 405 │ └── filters 406 │ └── last_sync_id:4 > 0 [outer=(4), constraints=(/4: [/1 - ]; tight)] 407 └── 50 408 409 # ------------------------------------------------------------------------------ 410 # Github Issue 15649: Use order-matching index even with a high offset/limit. 411 # ------------------------------------------------------------------------------ 412 exec-ddl 413 CREATE TABLE test ( 414 id INT NOT NULL, 415 midname STRING NULL, 416 name STRING NULL, 417 CONSTRAINT "primary" PRIMARY KEY (id ASC), 418 INDEX test_name_idx (name ASC), 419 FAMILY "primary" (id, midname, name) 420 ) 421 ---- 422 423 opt 424 EXPLAIN SELECT id FROM test ORDER BY id asc LIMIT 10 offset 10000; 425 ---- 426 explain 427 ├── columns: tree:4 field:5 description:6 428 └── offset 429 ├── columns: id:1!null 430 ├── internal-ordering: +1 431 ├── cardinality: [0 - 10] 432 ├── key: (1) 433 ├── ordering: +1 434 ├── scan test 435 │ ├── columns: id:1!null 436 │ ├── limit: 10010 437 │ ├── key: (1) 438 │ └── ordering: +1 439 └── 10000 440 441 # ------------------------------------------------------------------------------ 442 # Github Issue 17270: Use the o_ok index rather than the primary index, since 443 # o_ok has only 2 columns (o_orderkey and rowid). 444 # ------------------------------------------------------------------------------ 445 exec-ddl 446 CREATE TABLE orders ( 447 o_orderkey INT NOT NULL, 448 o_custkey INT NOT NULL, 449 o_orderstatus STRING(1) NOT NULL, 450 o_totalprice DECIMAL(15,2) NOT NULL, 451 o_orderdate DATE NOT NULL, 452 o_orderpriority STRING(15) NOT NULL, 453 o_clerk STRING(15) NOT NULL, 454 o_shippriority INT NOT NULL, 455 o_comment STRING(79) NOT NULL, 456 UNIQUE INDEX o_ok (o_orderkey ASC), 457 INDEX o_ck (o_custkey ASC), 458 INDEX o_od (o_orderdate ASC), 459 FAMILY "primary" (o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, rowid) 460 ) 461 ---- 462 463 exec-ddl 464 CREATE TABLE lineitem ( 465 l_orderkey INT NOT NULL, 466 l_partkey INT NOT NULL, 467 l_suppkey INT NOT NULL, 468 l_linenumber INT NOT NULL, 469 l_quantity DECIMAL(15,2) NOT NULL, 470 l_extendedprice DECIMAL(15,2) NOT NULL, 471 l_discount DECIMAL(15,2) NOT NULL, 472 l_tax DECIMAL(15,2) NOT NULL, 473 l_returnflag STRING(1) NOT NULL, 474 l_linestatus STRING(1) NOT NULL, 475 l_shipdate DATE NOT NULL, 476 l_commitdate DATE NOT NULL, 477 l_receiptdate DATE NOT NULL, 478 l_shipinstruct STRING(25) NOT NULL, 479 l_shipmode STRING(10) NOT NULL, 480 l_comment STRING(44) NOT NULL, 481 INDEX l_ok (l_orderkey ASC), 482 INDEX l_pk (l_partkey ASC), 483 INDEX l_sk (l_suppkey ASC), 484 INDEX l_sd (l_shipdate ASC), 485 INDEX l_cd (l_commitdate ASC), 486 INDEX l_rd (l_receiptdate ASC), 487 INDEX l_pk_sk (l_partkey ASC, l_suppkey ASC), 488 INDEX l_sk_pk (l_suppkey ASC, l_partkey ASC), 489 FAMILY "primary" (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment, rowid) 490 ) 491 ---- 492 493 opt 494 SELECT count(l_orderkey) FROM orders, lineitem WHERE orders.o_orderkey = lineitem.l_orderkey 495 ---- 496 scalar-group-by 497 ├── columns: count:28!null 498 ├── cardinality: [1 - 1] 499 ├── key: () 500 ├── fd: ()-->(28) 501 ├── inner-join (merge) 502 │ ├── columns: o_orderkey:1!null l_orderkey:11!null 503 │ ├── left ordering: +1 504 │ ├── right ordering: +11 505 │ ├── fd: (1)==(11), (11)==(1) 506 │ ├── scan orders@o_ok 507 │ │ ├── columns: o_orderkey:1!null 508 │ │ ├── key: (1) 509 │ │ └── ordering: +1 510 │ ├── scan lineitem@l_ok 511 │ │ ├── columns: l_orderkey:11!null 512 │ │ └── ordering: +11 513 │ └── filters (true) 514 └── aggregations 515 └── count-rows [as=count:28] 516 517 # ------------------------------------------------------------------------------ 518 # Ensure we do a lookup join when one side comes from an SRF. 519 # ------------------------------------------------------------------------------ 520 521 exec-ddl 522 CREATE TABLE idtable ( 523 primary_id UUID DEFAULT uuid_v4()::UUID PRIMARY KEY, 524 secondary_id UUID NOT NULL, 525 data JSONB NOT NULL, 526 527 INDEX secondary_id (secondary_id) 528 ) 529 ---- 530 531 opt 532 SELECT 533 elem->>'secondary_id' AS secondary_id, data || jsonb_build_object('primary_id', primary_id) 534 FROM 535 idtable, 536 json_array_elements('[ 537 {"person_id":"8e5dc104-9f38-4255-9283-fd080be16c57", "product_id":"a739c2d3-edec-413b-88d8-9c31d0414b1e"}, 538 {"person_id":"308686c4-7415-4c2d-92d5-25b39a1c84e2", "product_id":"3f12802d-5b0f-43d7-a0d0-12ac8e88cb18"} 539 ]') AS elem 540 WHERE 541 secondary_id = (elem->>'secondary_id')::UUID 542 ---- 543 project 544 ├── columns: secondary_id:6 "?column?":7 545 ├── stable 546 ├── inner-join (lookup idtable) 547 │ ├── columns: primary_id:1!null idtable.secondary_id:2!null data:3!null value:4!null column5:5!null 548 │ ├── key columns: [1] = [1] 549 │ ├── lookup columns are key 550 │ ├── fd: (1)-->(2,3), (4)-->(5), (2)==(5), (5)==(2) 551 │ ├── inner-join (lookup idtable@secondary_id) 552 │ │ ├── columns: primary_id:1!null idtable.secondary_id:2!null value:4!null column5:5!null 553 │ │ ├── key columns: [5] = [2] 554 │ │ ├── fd: (4)-->(5), (1)-->(2), (2)==(5), (5)==(2) 555 │ │ ├── project 556 │ │ │ ├── columns: column5:5 value:4!null 557 │ │ │ ├── cardinality: [2 - 2] 558 │ │ │ ├── fd: (4)-->(5) 559 │ │ │ ├── values 560 │ │ │ │ ├── columns: value:4!null 561 │ │ │ │ ├── cardinality: [2 - 2] 562 │ │ │ │ ├── ('{"person_id": "8e5dc104-9f38-4255-9283-fd080be16c57", "product_id": "a739c2d3-edec-413b-88d8-9c31d0414b1e"}',) 563 │ │ │ │ └── ('{"person_id": "308686c4-7415-4c2d-92d5-25b39a1c84e2", "product_id": "3f12802d-5b0f-43d7-a0d0-12ac8e88cb18"}',) 564 │ │ │ └── projections 565 │ │ │ └── (value:4->>'secondary_id')::UUID [as=column5:5, outer=(4)] 566 │ │ └── filters (true) 567 │ └── filters (true) 568 └── projections 569 ├── value:4->>'secondary_id' [as=secondary_id:6, outer=(4)] 570 └── data:3 || jsonb_build_object('primary_id', primary_id:1) [as="?column?":7, outer=(1,3), stable]