github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/trading (about) 1 # ============================================================================= 2 # This schema is based on a business buying/selling online trading cards. This 3 # file simulates queries taking place while schema changes are *not* taking 4 # place. See the trading-mutation file for the same queries running against 5 # tables with simulated schema changes in progress. 6 # ============================================================================= 7 8 # -------------------------------------------------- 9 # Schema Definitions 10 # -------------------------------------------------- 11 12 # Cards is the catalog of all cards that can be traded. 13 exec-ddl 14 CREATE TABLE Cards 15 ( 16 id INT NOT NULL, 17 name VARCHAR(128) NOT NULL, 18 rarity VARCHAR(1) NULL, 19 setname VARCHAR(5) NULL, 20 number INT NOT NULL, 21 isfoil BIT NOT NULL, 22 CONSTRAINT CardsPrimaryKey PRIMARY KEY 23 ( 24 id ASC 25 ), 26 CONSTRAINT CardsNameSetNumber UNIQUE 27 ( 28 name ASC, 29 setname ASC, 30 number ASC 31 ) 32 ) 33 ---- 34 35 exec-ddl 36 ALTER TABLE Cards INJECT STATISTICS '[ 37 { 38 "columns": ["id"], 39 "distinct_count": 57000, 40 "null_count": 0, 41 "row_count": 57000, 42 "created_at": "2020-01-01 0:00:00.00000+00:00" 43 }, 44 { 45 "columns": ["name"], 46 "distinct_count": 39000, 47 "null_count": 0, 48 "row_count": 57000, 49 "created_at": "2020-01-01 0:00:00.00000+00:00" 50 }, 51 { 52 "columns": ["setname"], 53 "distinct_count": 162, 54 "null_count": 0, 55 "row_count": 57000, 56 "created_at": "2020-01-01 0:00:00.00000+00:00" 57 }, 58 { 59 "columns": ["number"], 60 "distinct_count": 829, 61 "null_count": 0, 62 "row_count": 57000, 63 "created_at": "2020-01-01 0:00:00.00000+00:00" 64 }, 65 { 66 "columns": ["name", "setname"], 67 "distinct_count": 56700, 68 "null_count": 0, 69 "row_count": 57000, 70 "created_at": "2020-01-01 0:00:00.00000+00:00" 71 }, 72 { 73 "columns": ["name", "setname", "number"], 74 "distinct_count": 57000, 75 "null_count": 0, 76 "row_count": 57000, 77 "created_at": "2020-01-01 0:00:00.00000+00:00" 78 } 79 ]' 80 ---- 81 82 # CardsInfo tracks current inventory of each card, as well as current buy/sell 83 # price. It is partitioned on dealerid, which represents multiple licensees 84 # (dealers) using the trading software. 85 exec-ddl 86 CREATE TABLE CardsInfo 87 ( 88 dealerid OID NOT NULL, 89 cardid INT NOT NULL, 90 buyprice DECIMAL(10,4) NOT NULL, 91 sellprice DECIMAL(10,4) NOT NULL, 92 discount DECIMAL(10,4) NOT NULL, 93 desiredinventory INT NOT NULL, 94 actualinventory INT NOT NULL, 95 maxinventory INT NOT NULL, 96 version DECIMAL NOT NULL DEFAULT (cluster_logical_timestamp()), 97 CONSTRAINT CardsInfoPrimaryKey PRIMARY KEY 98 ( 99 dealerid ASC, 100 cardid ASC 101 ), 102 CONSTRAINT CardsInfoCardIdKey FOREIGN KEY (cardid) 103 REFERENCES Cards (id), 104 UNIQUE INDEX CardsInfoVersionIndex (dealerid ASC, version ASC) 105 ) 106 ---- 107 108 exec-ddl 109 ALTER TABLE CardsInfo INJECT STATISTICS '[ 110 { 111 "columns": ["dealerid"], 112 "distinct_count": 12, 113 "null_count": 0, 114 "row_count": 700000, 115 "created_at": "2020-01-01 0:00:00.00000+00:00" 116 }, 117 { 118 "columns": ["cardid"], 119 "distinct_count": 57000, 120 "null_count": 0, 121 "row_count": 700000, 122 "created_at": "2020-01-01 0:00:00.00000+00:00" 123 }, 124 { 125 "columns": ["version"], 126 "distinct_count": 700000, 127 "null_count": 0, 128 "row_count": 700000, 129 "created_at": "2020-01-01 0:00:00.00000+00:00", 130 "histo_col_type": "decimal", 131 "histo_buckets": [ 132 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "1426741777604892000"}, 133 {"num_eq": 0, "num_range": 350000, "distinct_range": 350000, "upper_bound": "1505581778104892000"}, 134 {"num_eq": 0, "num_range": 350000, "distinct_range": 350000, "upper_bound": "1584421778604892000"} 135 ] 136 }, 137 { 138 "columns": ["dealerid", "cardid"], 139 "distinct_count": 700000, 140 "null_count": 0, 141 "row_count": 700000, 142 "created_at": "2020-01-01 0:00:00.00000+00:00" 143 }, 144 { 145 "columns": ["dealerid", "version"], 146 "distinct_count": 700000, 147 "null_count": 0, 148 "row_count": 700000, 149 "created_at": "2020-01-01 0:00:00.00000+00:00" 150 } 151 ]' 152 ---- 153 154 # InventoryDetails stores the quantity and location of all the cards. 155 exec-ddl 156 CREATE TABLE InventoryDetails 157 ( 158 dealerid OID NOT NULL, 159 cardid INT NOT NULL, 160 accountname VARCHAR(128) NOT NULL, 161 quantity INT NOT NULL, 162 version DECIMAL NOT NULL DEFAULT (cluster_logical_timestamp()), 163 CONSTRAINT InventoryDetailsPrimaryKey PRIMARY KEY 164 ( 165 dealerid ASC, 166 cardid ASC, 167 accountname ASC 168 ), 169 CONSTRAINT InventoryDetailsCardIdKey FOREIGN KEY (cardid) 170 REFERENCES Cards (id) 171 ) 172 ---- 173 174 exec-ddl 175 ALTER TABLE InventoryDetails INJECT STATISTICS '[ 176 { 177 "columns": ["dealerid"], 178 "distinct_count": 12, 179 "null_count": 0, 180 "row_count": 1700000, 181 "created_at": "2020-01-01 0:00:00.00000+00:00" 182 }, 183 { 184 "columns": ["cardid"], 185 "distinct_count": 50000, 186 "null_count": 0, 187 "row_count": 1700000, 188 "created_at": "2020-01-01 0:00:00.00000+00:00" 189 }, 190 { 191 "columns": ["accountname"], 192 "distinct_count": 150, 193 "null_count": 0, 194 "row_count": 1700000, 195 "created_at": "2020-01-01 0:00:00.00000+00:00" 196 }, 197 { 198 "columns": ["dealerid", "cardid"], 199 "distinct_count": 250000, 200 "null_count": 0, 201 "row_count": 1700000, 202 "created_at": "2020-01-01 0:00:00.00000+00:00" 203 }, 204 { 205 "columns": ["dealerid", "cardid", "accountname"], 206 "distinct_count": 170000, 207 "null_count": 0, 208 "row_count": 1700000, 209 "created_at": "2020-01-01 0:00:00.00000+00:00" 210 } 211 ]' 212 ---- 213 214 # Transactions records all buy/sell trading activity. 215 # 216 # NOTE: The TransactionsOpIndex is meant to be a partial index, only containing 217 # non-NULL values. The operationid column is for idempotency, and 218 # older values get set to NULL after X hours to save space. 99%+ of values 219 # are NULL. 220 exec-ddl 221 CREATE TABLE Transactions 222 ( 223 dealerid OID NOT NULL, 224 isbuy BOOL NOT NULL, 225 date TIMESTAMPTZ NOT NULL, 226 accountname VARCHAR(128) NOT NULL, 227 customername VARCHAR(128) NOT NULL, 228 operationid UUID, 229 version DECIMAL NOT NULL DEFAULT (cluster_logical_timestamp()), 230 CONSTRAINT TransactionsPrimaryKey PRIMARY KEY 231 ( 232 dealerid ASC, 233 isbuy ASC, 234 date ASC 235 ), 236 UNIQUE INDEX TransactionsOpIndex (dealerid ASC, operationid ASC) 237 --WHERE operationid IS NOT NULL 238 ) 239 ---- 240 241 exec-ddl 242 ALTER TABLE Transactions INJECT STATISTICS '[ 243 { 244 "columns": ["dealerid"], 245 "distinct_count": 10, 246 "null_count": 0, 247 "row_count": 20000000, 248 "created_at": "2020-01-01 0:00:00.00000+00:00" 249 }, 250 { 251 "columns": ["isbuy"], 252 "distinct_count": 2, 253 "null_count": 0, 254 "row_count": 20000000, 255 "created_at": "2020-01-01 0:00:00.00000+00:00" 256 }, 257 { 258 "columns": ["date"], 259 "distinct_count": 20000000, 260 "null_count": 0, 261 "row_count": 20000000, 262 "created_at": "2020-01-01 0:00:00.00000+00:00" 263 }, 264 { 265 "columns": ["operationid"], 266 "distinct_count": 4000, 267 "null_count": 19996000, 268 "row_count": 20000000, 269 "created_at": "2020-01-01 0:00:00.00000+00:00" 270 }, 271 { 272 "columns": ["dealerid", "isbuy"], 273 "distinct_count": 15, 274 "null_count": 0, 275 "row_count": 20000000, 276 "created_at": "2020-01-01 0:00:00.00000+00:00" 277 }, 278 { 279 "columns": ["dealerid", "isbuy", "date"], 280 "distinct_count": 20000000, 281 "null_count": 0, 282 "row_count": 20000000, 283 "created_at": "2020-01-01 0:00:00.00000+00:00" 284 } 285 ]' 286 ---- 287 288 # TransactionDetails records line items of each Transaction. 289 exec-ddl 290 CREATE TABLE TransactionDetails 291 ( 292 dealerid OID NOT NULL, 293 isbuy BOOL NOT NULL, 294 transactiondate TIMESTAMPTZ NOT NULL, 295 cardid INT NOT NULL, 296 quantity INT NOT NULL, 297 sellprice DECIMAL(10,4) NOT NULL, 298 buyprice DECIMAL(10,4) NOT NULL, 299 version DECIMAL NOT NULL DEFAULT (cluster_logical_timestamp()), 300 CONSTRAINT DetailsPrimaryKey PRIMARY KEY 301 ( 302 dealerid ASC, 303 isbuy ASC, 304 transactiondate ASC, 305 cardid ASC, 306 quantity ASC 307 ), 308 CONSTRAINT DetailsDealerDateKey FOREIGN KEY (dealerid, isbuy, transactiondate) 309 REFERENCES Transactions (dealerid, isbuy, date), 310 CONSTRAINT DetailsCardIdKey FOREIGN KEY (cardid) 311 REFERENCES Cards (id), 312 INDEX DetailsCardIdIndex (dealerid ASC, isbuy ASC, cardid ASC) 313 ) 314 ---- 315 316 exec-ddl 317 ALTER TABLE TransactionDetails INJECT STATISTICS '[ 318 { 319 "columns": ["dealerid"], 320 "distinct_count": 10, 321 "null_count": 0, 322 "row_count": 180000000, 323 "created_at": "2020-01-01 0:00:00.00000+00:00" 324 }, 325 { 326 "columns": ["isbuy"], 327 "distinct_count": 2, 328 "null_count": 0, 329 "row_count": 180000000, 330 "created_at": "2020-01-01 0:00:00.00000+00:00" 331 }, 332 { 333 "columns": ["transactiondate"], 334 "distinct_count": 180000000, 335 "null_count": 0, 336 "row_count": 180000000, 337 "created_at": "2020-01-01 0:00:00.00000+00:00" 338 }, 339 { 340 "columns": ["cardid"], 341 "distinct_count": 57000, 342 "null_count": 0, 343 "row_count": 180000000, 344 "created_at": "2020-01-01 0:00:00.00000+00:00" 345 }, 346 { 347 "columns": ["dealerid", "isbuy"], 348 "distinct_count": 15, 349 "null_count": 0, 350 "row_count": 180000000, 351 "created_at": "2020-01-01 0:00:00.00000+00:00" 352 }, 353 { 354 "columns": ["dealerid", "isbuy", "transactiondate"], 355 "distinct_count": 20000000, 356 "null_count": 0, 357 "row_count": 180000000, 358 "created_at": "2020-01-01 0:00:00.00000+00:00" 359 }, 360 { 361 "columns": ["dealerid", "isbuy", "transactiondate", "cardid"], 362 "distinct_count": 180000000, 363 "null_count": 0, 364 "row_count": 180000000, 365 "created_at": "2020-01-01 0:00:00.00000+00:00" 366 }, 367 { 368 "columns": ["dealerid", "isbuy", "transactiondate", "cardid", "quantity"], 369 "distinct_count": 180000000, 370 "null_count": 0, 371 "row_count": 180000000, 372 "created_at": "2020-01-01 0:00:00.00000+00:00" 373 }, 374 { 375 "columns": ["dealerid", "isbuy", "cardid"], 376 "distinct_count": 350000, 377 "null_count": 0, 378 "row_count": 180000000, 379 "created_at": "2020-01-01 0:00:00.00000+00:00" 380 } 381 ]' 382 ---- 383 384 # PriceDetails records price history for each card. 385 exec-ddl 386 CREATE TABLE PriceDetails 387 ( 388 dealerid OID NOT NULL, 389 cardid INT NOT NULL, 390 pricedate TIMESTAMPTZ NOT NULL, 391 pricedby VARCHAR(128) NOT NULL, 392 buyprice DECIMAL(10,4) NOT NULL, 393 sellprice DECIMAL(10,4) NOT NULL, 394 discount DECIMAL(10,4) NOT NULL, 395 version DECIMAL NOT NULL, 396 CONSTRAINT PriceDetailsPrimaryKey PRIMARY KEY 397 ( 398 dealerid ASC, 399 cardid ASC, 400 pricedate ASC 401 ), 402 CONSTRAINT PriceDetailsCardIdKey FOREIGN KEY (cardid) 403 REFERENCES Cards (id) 404 ) 405 ---- 406 407 exec-ddl 408 ALTER TABLE PriceDetails INJECT STATISTICS '[ 409 { 410 "columns": ["dealerid"], 411 "distinct_count": 2, 412 "null_count": 0, 413 "row_count": 40000000, 414 "created_at": "2020-01-01 0:00:00.00000+00:00" 415 }, 416 { 417 "columns": ["cardid"], 418 "distinct_count": 57000, 419 "null_count": 0, 420 "row_count": 40000000, 421 "created_at": "2020-01-01 0:00:00.00000+00:00" 422 }, 423 { 424 "columns": ["pricedate"], 425 "distinct_count": 40000000, 426 "null_count": 0, 427 "row_count": 40000000, 428 "created_at": "2020-01-01 0:00:00.00000+00:00" 429 }, 430 { 431 "columns": ["dealerid", "cardid"], 432 "distinct_count": 90000, 433 "null_count": 0, 434 "row_count": 40000000, 435 "created_at": "2020-01-01 0:00:00.00000+00:00" 436 }, 437 { 438 "columns": ["dealerid", "cardid", "pricedate"], 439 "distinct_count": 40000000, 440 "null_count": 0, 441 "row_count": 40000000, 442 "created_at": "2020-01-01 0:00:00.00000+00:00" 443 } 444 ]' 445 ---- 446 447 # NOTE: These views should not be checking for the constant dealerid = 1. 448 # Instead, the dealerid should be derived from the current user, like this: 449 # 450 # dealerid = (SELECT oid FROM pg_roles WHERE rolname = current_user); 451 # 452 # However, the optimizer and execution engine are not smart enough to do a good 453 # job with this. The following needs to be done: 454 # 455 # 1. Optimizer needs access to key information about pg_roles so that it can 456 # infer that there will be at most one row that matches the predicate. 457 # 2. Optimizer needs to replace "current_user" with constant after the query 458 # is prepared, as if it were a parameter. 459 # 3. Execution engine should allow "push down" into virtual tables, or else 460 # this query will need to enumerate every user and role in order to find 461 # the requested rolname. 462 # 463 464 exec-ddl 465 CREATE VIEW CardsView AS 466 SELECT id AS Id, name AS Name, rarity AS Rarity, setname AS SetName, number AS Number, isfoil AS IsFoil, 467 buyprice AS BuyPrice, sellprice AS SellPrice, discount AS Discount, 468 desiredinventory AS DesiredInventory, actualinventory AS ActualInventory, 469 maxinventory AS MaxInventory, version AS Version 470 FROM Cards 471 JOIN CardsInfo 472 ON id = cardid 473 WHERE dealerid = 1 474 ---- 475 476 exec-ddl 477 CREATE VIEW TransactionsView AS 478 SELECT 479 date AS Date, accountname AS AccountName, customername AS CustomerName, 480 isbuy AS IsBuy, operationid AS OperationId 481 FROM Transactions 482 WHERE dealerid = 1 483 ---- 484 485 exec-ddl 486 CREATE VIEW TransactionDetailsView AS 487 SELECT isbuy AS IsBuy, transactiondate AS TransactionDate, cardid AS CardId, quantity AS Quantity, 488 sellprice AS SellPrice, buyprice AS BuyPrice 489 FROM TransactionDetails 490 WHERE dealerid = 1 491 ---- 492 493 exec-ddl 494 CREATE VIEW PriceDetailsView AS 495 SELECT cardid AS CardId, pricedate AS PriceDate, pricedby AS PricedBy, buyprice AS BuyPrice, 496 sellprice AS SellPrice, 497 ( 498 CASE 499 WHEN dealerid <> 1 500 THEN 0::DECIMAL(10,4) 501 ELSE discount 502 END 503 ) AS Discount 504 FROM PriceDetails 505 WHERE dealerid = 1 506 ---- 507 508 exec-ddl 509 CREATE VIEW GlobalInventoryView AS 510 SELECT cardid, min(buyprice) AS BuyPrice, max(sellprice) AS SellPrice, max(discount) AS Discount, 511 sum(desiredinventory) AS DesiredInventory, 512 sum 513 ( 514 CASE 515 WHEN dealerid = 2 AND actualinventory > 24 THEN 24 516 WHEN dealerid <> 1 AND actualinventory > maxinventory THEN maxinventory 517 ELSE actualinventory 518 END 519 ) AS ActualInventory, 520 sum(maxinventory) AS MaxInventory, 521 max(version) AS Version 522 FROM CardsInfo 523 INNER JOIN Cards 524 ON cardid = id 525 WHERE (dealerid = 1 OR dealerid = 2 OR dealerid = 3 OR dealerid = 4) 526 GROUP BY cardid 527 ---- 528 529 exec-ddl 530 CREATE VIEW GlobalCardsView AS 531 SELECT c.id AS Id, c.name AS Name, c.rarity AS Rarity, c.setname AS SetName, c.number AS Number, c.isfoil AS IsFoil, 532 inv.BuyPrice, inv.SellPrice, inv.Discount, inv.DesiredInventory, inv.ActualInventory, 533 inv.MaxInventory, inv.Version 534 FROM Cards c 535 INNER JOIN GlobalInventoryView inv 536 ON c.id = inv.cardid 537 ---- 538 539 # -------------------------------------------------- 540 # SELECT Queries 541 # -------------------------------------------------- 542 543 # Find all cards that have been modified in the last 5 seconds. 544 opt format=show-stats 545 SELECT 546 Id, Name, Rarity, SetName, Number, IsFoil, BuyPrice, SellPrice, 547 DesiredInventory, ActualInventory, Version, Discount, MaxInventory 548 FROM CardsView WHERE Version > 1584421773604892000.0000000000 549 ---- 550 project 551 ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null buyprice:9!null sellprice:10!null desiredinventory:12!null actualinventory:13!null version:15!null discount:11!null maxinventory:14!null 552 ├── stats: [rows=1] 553 ├── key: (15) 554 ├── fd: (1)-->(2-6,9-15), (2,4,5)~~>(1,3,6), (15)-->(1-6,9-14) 555 └── inner-join (lookup cards) 556 ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null dealerid:7!null cardid:8!null buyprice:9!null sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null version:15!null 557 ├── key columns: [8] = [1] 558 ├── lookup columns are key 559 ├── stats: [rows=1, distinct(1)=0.0201621393, null(1)=0, distinct(8)=0.0201621393, null(8)=0] 560 ├── key: (8) 561 ├── fd: ()-->(7), (1)-->(2-6), (2,4,5)~~>(1,3,6), (8)-->(9-15), (15)-->(8-14), (1)==(8), (8)==(1) 562 ├── index-join cardsinfo 563 │ ├── columns: dealerid:7!null cardid:8!null buyprice:9!null sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null version:15!null 564 │ ├── stats: [rows=0.0201621426, distinct(7)=0.0201621426, null(7)=0, distinct(8)=0.0201621393, null(8)=0, distinct(9)=0.02016214, null(9)=0, distinct(10)=0.02016214, null(10)=0, distinct(11)=0.02016214, null(11)=0, distinct(12)=0.02016214, null(12)=0, distinct(13)=0.02016214, null(13)=0, distinct(14)=0.02016214, null(14)=0, distinct(15)=0.0201621426, null(15)=0, distinct(7,15)=0.0201621426, null(7,15)=0] 565 │ │ histogram(15)= 0 0 0.020162 0 566 │ │ <--- 1584421773604892000.0000000000 ---------- 1584421778604892000 567 │ ├── key: (8) 568 │ ├── fd: ()-->(7), (8)-->(9-15), (15)-->(8-14) 569 │ └── scan cardsinfo@cardsinfoversionindex 570 │ ├── columns: dealerid:7!null cardid:8!null version:15!null 571 │ ├── constraint: /7/15: (/1/1584421773604892000.0000000000 - /1] 572 │ ├── stats: [rows=0.0201621426, distinct(7)=0.0201621426, null(7)=0, distinct(15)=0.0201621426, null(15)=0, distinct(7,15)=0.0201621426, null(7,15)=0] 573 │ │ histogram(15)= 0 0 0.020162 0 574 │ │ <--- 1584421773604892000.0000000000 ---------- 1584421778604892000 575 │ ├── key: (8) 576 │ └── fd: ()-->(7), (8)-->(15), (15)-->(8) 577 └── filters (true) 578 579 # Get version of last card that was changed. 580 # 581 opt format=show-stats 582 SELECT coalesce(max(Version), 0) FROM GlobalCardsView 583 ---- 584 project 585 ├── columns: coalesce:31 586 ├── cardinality: [1 - 1] 587 ├── stats: [rows=1] 588 ├── key: () 589 ├── fd: ()-->(31) 590 ├── scalar-group-by 591 │ ├── columns: max:30 592 │ ├── cardinality: [1 - 1] 593 │ ├── stats: [rows=1] 594 │ ├── key: () 595 │ ├── fd: ()-->(30) 596 │ ├── scan cardsinfo@cardsinfoversionindex 597 │ │ ├── columns: dealerid:7!null version:15!null 598 │ │ ├── constraint: /7/15: [/1 - /4] 599 │ │ ├── stats: [rows=233333.333, distinct(7)=4, null(7)=0] 600 │ │ └── key: (7,15) 601 │ └── aggregations 602 │ └── max [as=max:30, outer=(15)] 603 │ └── version:15 604 └── projections 605 └── COALESCE(max:30, 0) [as=coalesce:31, outer=(30)] 606 607 # Show last 20 transactions for a particular card. 608 # 609 # Problems: 610 # 1. Wrong join-type is selected (hash instead of lookup). This is because 611 # "NOT IsBuy" needs to be mapped to "IsBuy = FALSE". 612 # 2. Index join should be applied after join between TransactionsView and 613 # TransactionDetailsView. 614 # 615 opt format=show-stats 616 SELECT 617 d.IsBuy, TransactionDate, CardId, Quantity, SellPrice, BuyPrice, 618 t.IsBuy AS IsBuy2, Date, AccountName, CustomerName 619 FROM TransactionDetailsView d 620 INNER JOIN TransactionsView t 621 ON t.Date = d.TransactionDate 622 WHERE (d.CardId = 21953) AND NOT d.IsBuy AND NOT t.IsBuy 623 ORDER BY TransactionDate DESC 624 LIMIT 20 625 ---- 626 project 627 ├── columns: isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null sellprice:6!null buyprice:7!null isbuy2:10!null date:11!null accountname:12!null customername:13!null 628 ├── cardinality: [0 - 20] 629 ├── stats: [rows=20] 630 ├── key: (5,11) 631 ├── fd: ()-->(2,4,10), (3,5)-->(6,7), (11)-->(12,13), (3)==(11), (11)==(3) 632 ├── ordering: -(3|11) opt(2,4,10) [actual: -3] 633 └── limit 634 ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null sellprice:6!null buyprice:7!null transactions.dealerid:9!null transactions.isbuy:10!null date:11!null accountname:12!null customername:13!null 635 ├── internal-ordering: -(3|11) opt(1,2,4,9,10) 636 ├── cardinality: [0 - 20] 637 ├── stats: [rows=20] 638 ├── key: (5,11) 639 ├── fd: ()-->(1,2,4,9,10), (3,5)-->(6,7), (11)-->(12,13), (3)==(11), (11)==(3) 640 ├── ordering: -(3|11) opt(1,2,4,9,10) [actual: -3] 641 ├── inner-join (lookup transactions) 642 │ ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null sellprice:6!null buyprice:7!null transactions.dealerid:9!null transactions.isbuy:10!null date:11!null accountname:12!null customername:13!null 643 │ ├── key columns: [16 17 3] = [9 10 11] 644 │ ├── lookup columns are key 645 │ ├── stats: [rows=478.646617, distinct(3)=478.646617, null(3)=0, distinct(11)=478.646617, null(11)=0] 646 │ ├── key: (5,11) 647 │ ├── fd: ()-->(1,2,4,9,10), (3,5)-->(6,7), (11)-->(12,13), (3)==(11), (11)==(3) 648 │ ├── ordering: -(3|11) opt(1,2,4,9,10) [actual: -3] 649 │ ├── limit hint: 20.00 650 │ ├── project 651 │ │ ├── columns: "project_const_col_@9":16!null "project_const_col_@10":17!null transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null sellprice:6!null buyprice:7!null 652 │ │ ├── stats: [rows=478.646617] 653 │ │ ├── key: (3,5) 654 │ │ ├── fd: ()-->(1,2,4,16,17), (3,5)-->(6,7) 655 │ │ ├── ordering: -3 opt(1,2,4) [actual: -3] 656 │ │ ├── limit hint: 100.00 657 │ │ ├── index-join transactiondetails 658 │ │ │ ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null sellprice:6!null buyprice:7!null 659 │ │ │ ├── stats: [rows=478.646617, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=478.646617, null(3)=0, distinct(4)=1, null(4)=0, distinct(5)=478.640889, null(5)=0, distinct(6)=478.640889, null(6)=0, distinct(7)=478.640889, null(7)=0, distinct(1,2,4)=1, null(1,2,4)=0] 660 │ │ │ ├── key: (3,5) 661 │ │ │ ├── fd: ()-->(1,2,4), (3,5)-->(6,7) 662 │ │ │ ├── ordering: -3 opt(1,2,4) [actual: -3] 663 │ │ │ ├── limit hint: 100.00 664 │ │ │ └── scan transactiondetails@detailscardidindex,rev 665 │ │ │ ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null 666 │ │ │ ├── constraint: /1/2/4/3/5: [/1/false/21953 - /1/false/21953] 667 │ │ │ ├── stats: [rows=478.646617, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(4)=1, null(4)=0, distinct(1,2,4)=1, null(1,2,4)=0] 668 │ │ │ ├── key: (3,5) 669 │ │ │ ├── fd: ()-->(1,2,4) 670 │ │ │ ├── ordering: -3 opt(1,2,4) [actual: -3] 671 │ │ │ └── limit hint: 100.00 672 │ │ └── projections 673 │ │ ├── 1 [as="project_const_col_@9":16] 674 │ │ └── false [as="project_const_col_@10":17] 675 │ └── filters (true) 676 └── 20 677 678 # Show last 20 prices for a card. 679 opt format=show-stats 680 SELECT CardId, PriceDate, PricedBy, BuyPrice, SellPrice 681 FROM PriceDetailsView 682 WHERE CardId = 12345 683 ORDER BY PriceDate DESC 684 LIMIT 10 685 ---- 686 project 687 ├── columns: cardid:2!null pricedate:3!null pricedby:4!null buyprice:5!null sellprice:6!null 688 ├── cardinality: [0 - 10] 689 ├── stats: [rows=10] 690 ├── key: (3) 691 ├── fd: ()-->(2), (3)-->(4-6) 692 ├── ordering: -3 opt(2) [actual: -3] 693 └── scan pricedetails,rev 694 ├── columns: dealerid:1!null cardid:2!null pricedate:3!null pricedby:4!null buyprice:5!null sellprice:6!null 695 ├── constraint: /1/2/3: [/1/12345 - /1/12345] 696 ├── limit: 10(rev) 697 ├── stats: [rows=10] 698 ├── key: (3) 699 ├── fd: ()-->(1,2), (3)-->(4-6) 700 └── ordering: -3 opt(1,2) [actual: -3] 701 702 # Show next page of 50 cards. 703 # 704 # Problems: 705 # 1. The TransactionDate comparisons should be the last 2 days from the 706 # current timestamp. However, the current timestamp is not treated as a 707 # constant as it should be. 708 # 2. Missing rule to push "LIMIT 50" into GroupBy->RightJoin complex. This 709 # would need to be an exploration rule since it involves an ordering. 710 # Or we could push down the "limit hint" into GroupBy->RightJoin (and 711 # further into the InnerJoin). 712 # 3. Wrong join-type (probably due to #2 above). Should be LookupJoin. 713 # 714 opt format=show-stats 715 SELECT 716 Id, Name, Rarity, SetName, Number, IsFoil, BuyPrice, SellPrice, 717 DesiredInventory, ActualInventory, Version, Discount, MaxInventory, Value AS TwoDaySales 718 FROM 719 ( 720 SELECT *, 721 coalesce(( 722 SELECT sum(Quantity) 723 FROM TransactionDetailsView d 724 WHERE 725 d.CardId = c.Id AND 726 d.IsBuy = FALSE AND 727 d.TransactionDate BETWEEN '2020-03-01'::TIMESTAMPTZ - INTERVAL '2 days' AND '2020-03-01'::TIMESTAMPTZ 728 ), 0) AS Value 729 FROM CardsView c 730 ) AS c 731 WHERE (Name, SetName, Number) > ('Shock', '7E', 248) 732 ORDER BY Name, SetName, Number 733 LIMIT 50 734 ---- 735 project 736 ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null buyprice:9!null sellprice:10!null desiredinventory:12!null actualinventory:13!null version:15!null discount:11!null maxinventory:14!null twodaysales:25 737 ├── cardinality: [0 - 50] 738 ├── stats: [rows=50] 739 ├── key: (15,25) 740 ├── fd: (1)-->(2-6,9-15), (2,4,5)~~>(1,3,6), (15)-->(1-6,9-14) 741 ├── ordering: +2,+4,+5 742 ├── limit 743 │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null cardsinfo.cardid:8!null cardsinfo.buyprice:9!null cardsinfo.sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null cardsinfo.version:15!null sum:24 744 │ ├── internal-ordering: +2,+4,+5 745 │ ├── cardinality: [0 - 50] 746 │ ├── stats: [rows=50] 747 │ ├── key: (8) 748 │ ├── fd: (1)-->(2-6), (2,4,5)~~>(1,3,6), (8)-->(1-6,9-15,24), (15)-->(8-14), (1)==(8), (8)==(1) 749 │ ├── ordering: +2,+4,+5 750 │ ├── sort 751 │ │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null cardsinfo.cardid:8!null cardsinfo.buyprice:9!null cardsinfo.sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null cardsinfo.version:15!null sum:24 752 │ │ ├── stats: [rows=19000, distinct(8)=19000, null(8)=0] 753 │ │ ├── key: (8) 754 │ │ ├── fd: (1)-->(2-6), (2,4,5)~~>(1,3,6), (8)-->(1-6,9-15,24), (15)-->(8-14), (1)==(8), (8)==(1) 755 │ │ ├── ordering: +2,+4,+5 756 │ │ ├── limit hint: 50.00 757 │ │ └── group-by 758 │ │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null cardsinfo.cardid:8!null cardsinfo.buyprice:9!null cardsinfo.sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null cardsinfo.version:15!null sum:24 759 │ │ ├── grouping columns: cardsinfo.cardid:8!null 760 │ │ ├── stats: [rows=19000, distinct(8)=19000, null(8)=0] 761 │ │ ├── key: (8) 762 │ │ ├── fd: (1)-->(2-6), (2,4,5)~~>(1,3,6), (8)-->(1-6,9-15,24), (15)-->(8-14), (1)==(8), (8)==(1) 763 │ │ ├── right-join (hash) 764 │ │ │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null cardsinfo.dealerid:7!null cardsinfo.cardid:8!null cardsinfo.buyprice:9!null cardsinfo.sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null cardsinfo.version:15!null transactiondetails.dealerid:16 isbuy:17 transactiondate:18 transactiondetails.cardid:19 quantity:20 765 │ │ │ ├── stats: [rows=5523583.18, distinct(8)=19000, null(8)=0, distinct(19)=19000, null(19)=0] 766 │ │ │ ├── key: (8,18-20) 767 │ │ │ ├── fd: ()-->(7), (1)-->(2-6), (2,4,5)~~>(1,3,6), (8)-->(9-15), (15)-->(8-14), (1)==(8), (8)==(1), (8,18-20)-->(16,17) 768 │ │ │ ├── scan transactiondetails 769 │ │ │ │ ├── columns: transactiondetails.dealerid:16!null isbuy:17!null transactiondate:18!null transactiondetails.cardid:19!null quantity:20!null 770 │ │ │ │ ├── constraint: /16/17/18/19/20: [/1/false/'2020-02-28 00:00:00+00:00' - /1/false/'2020-03-01 00:00:00+00:00'] 771 │ │ │ │ ├── stats: [rows=10630000, distinct(16)=1, null(16)=0, distinct(17)=1, null(17)=0, distinct(18)=10630000, null(18)=0, distinct(19)=57000, null(19)=0, distinct(16,17)=1, null(16,17)=0, distinct(16-18)=10630000, null(16-18)=0] 772 │ │ │ │ ├── key: (18-20) 773 │ │ │ │ └── fd: ()-->(16,17) 774 │ │ │ ├── inner-join (merge) 775 │ │ │ │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null cardsinfo.dealerid:7!null cardsinfo.cardid:8!null cardsinfo.buyprice:9!null cardsinfo.sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null cardsinfo.version:15!null 776 │ │ │ │ ├── left ordering: +1 777 │ │ │ │ ├── right ordering: +8 778 │ │ │ │ ├── stats: [rows=29618.4611, distinct(1)=19000, null(1)=0, distinct(2)=11668.1409, null(2)=0, distinct(5)=829, null(5)=0, distinct(6)=5572.85686, null(6)=0, distinct(7)=1, null(7)=0, distinct(8)=19000, null(8)=0, distinct(9)=21037.9959, null(9)=0, distinct(10)=21037.9959, null(10)=0, distinct(11)=21037.9959, null(11)=0, distinct(12)=21037.9959, null(12)=0, distinct(13)=21037.9959, null(13)=0, distinct(14)=21037.9959, null(14)=0, distinct(15)=23225.5851, null(15)=0] 779 │ │ │ │ ├── key: (8) 780 │ │ │ │ ├── fd: ()-->(7), (1)-->(2-6), (2,4,5)~~>(1,3,6), (8)-->(9-15), (15)-->(8-14), (1)==(8), (8)==(1) 781 │ │ │ │ ├── select 782 │ │ │ │ │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null 783 │ │ │ │ │ ├── stats: [rows=19000, distinct(1)=19000, null(1)=0, distinct(2)=13000, null(2)=0, distinct(5)=829, null(5)=0, distinct(6)=5601.15328, null(6)=0] 784 │ │ │ │ │ ├── key: (1) 785 │ │ │ │ │ ├── fd: (1)-->(2-6), (2,4,5)~~>(1,3,6) 786 │ │ │ │ │ ├── ordering: +1 787 │ │ │ │ │ ├── scan cards 788 │ │ │ │ │ │ ├── columns: id:1!null name:2!null rarity:3 setname:4 number:5!null isfoil:6!null 789 │ │ │ │ │ │ ├── stats: [rows=57000, distinct(1)=57000, null(1)=0, distinct(2)=39000, null(2)=0, distinct(5)=829, null(5)=0, distinct(6)=5700, null(6)=0] 790 │ │ │ │ │ │ ├── key: (1) 791 │ │ │ │ │ │ ├── fd: (1)-->(2-6), (2,4,5)~~>(1,3,6) 792 │ │ │ │ │ │ └── ordering: +1 793 │ │ │ │ │ └── filters 794 │ │ │ │ │ └── (name:2, setname:4, number:5) > ('Shock', '7E', 248) [outer=(2,4,5), constraints=(/2/4/5: [/'Shock'/'7E'/249 - ]; tight)] 795 │ │ │ │ ├── scan cardsinfo 796 │ │ │ │ │ ├── columns: cardsinfo.dealerid:7!null cardsinfo.cardid:8!null cardsinfo.buyprice:9!null cardsinfo.sellprice:10!null discount:11!null desiredinventory:12!null actualinventory:13!null maxinventory:14!null cardsinfo.version:15!null 797 │ │ │ │ │ ├── constraint: /7/8: [/1 - /1] 798 │ │ │ │ │ ├── stats: [rows=58333.3333, distinct(7)=1, null(7)=0, distinct(8)=37420.3552, null(8)=0, distinct(9)=40676.7278, null(9)=0, distinct(10)=40676.7278, null(10)=0, distinct(11)=40676.7278, null(11)=0, distinct(12)=40676.7278, null(12)=0, distinct(13)=40676.7278, null(13)=0, distinct(14)=40676.7278, null(14)=0, distinct(15)=58333.3333, null(15)=0] 799 │ │ │ │ │ ├── key: (8) 800 │ │ │ │ │ ├── fd: ()-->(7), (8)-->(9-15), (15)-->(8-14) 801 │ │ │ │ │ └── ordering: +8 opt(7) [actual: +8] 802 │ │ │ │ └── filters (true) 803 │ │ │ └── filters 804 │ │ │ └── transactiondetails.cardid:19 = id:1 [outer=(1,19), constraints=(/1: (/NULL - ]; /19: (/NULL - ]), fd=(1)==(19), (19)==(1)] 805 │ │ └── aggregations 806 │ │ ├── sum [as=sum:24, outer=(20)] 807 │ │ │ └── quantity:20 808 │ │ ├── const-agg [as=id:1, outer=(1)] 809 │ │ │ └── id:1 810 │ │ ├── const-agg [as=name:2, outer=(2)] 811 │ │ │ └── name:2 812 │ │ ├── const-agg [as=rarity:3, outer=(3)] 813 │ │ │ └── rarity:3 814 │ │ ├── const-agg [as=setname:4, outer=(4)] 815 │ │ │ └── setname:4 816 │ │ ├── const-agg [as=number:5, outer=(5)] 817 │ │ │ └── number:5 818 │ │ ├── const-agg [as=isfoil:6, outer=(6)] 819 │ │ │ └── isfoil:6 820 │ │ ├── const-agg [as=cardsinfo.buyprice:9, outer=(9)] 821 │ │ │ └── cardsinfo.buyprice:9 822 │ │ ├── const-agg [as=cardsinfo.sellprice:10, outer=(10)] 823 │ │ │ └── cardsinfo.sellprice:10 824 │ │ ├── const-agg [as=discount:11, outer=(11)] 825 │ │ │ └── discount:11 826 │ │ ├── const-agg [as=desiredinventory:12, outer=(12)] 827 │ │ │ └── desiredinventory:12 828 │ │ ├── const-agg [as=actualinventory:13, outer=(13)] 829 │ │ │ └── actualinventory:13 830 │ │ ├── const-agg [as=maxinventory:14, outer=(14)] 831 │ │ │ └── maxinventory:14 832 │ │ └── const-agg [as=cardsinfo.version:15, outer=(15)] 833 │ │ └── cardsinfo.version:15 834 │ └── 50 835 └── projections 836 └── COALESCE(sum:24, 0) [as=value:25, outer=(24)] 837 838 # Daily transaction query. 839 # 840 # Problems: 841 # 1. CardsView is actually a join between Cards and CardsInfo tables. But the 842 # optimizer is missing join elimination rules. If those were available, we 843 # could eliminate the join to Cards (because of FK). 844 # 2. Inequality predicate terms (accountname / customername) are too 845 # selective. 846 # 3. The Date comparisons should be the last 7 days from the current 847 # timestamp. However, the current timestamp is not treated as a constant as 848 # it should be. 849 # 4. The row count estimate for the constrained scan of transactions is too 850 # large, causing us to choose the incorrect join algorithm (we should 851 # choose a lookup join). Collecting small histograms on all columns would 852 # fix the issue. 853 # 854 opt format=show-stats 855 SELECT 856 extract(day from d.TransactionDate), 857 sum(d.SellPrice * d.Quantity) AS TotalSell, 858 sum(d.BuyPrice * d.Quantity) AS TotalBuy, 859 sum((d.SellPrice - d.BuyPrice) * d.Quantity) AS TotalProfit 860 FROM TransactionDetailsView d, TransactionsView t, CardsView c 861 WHERE 862 d.TransactionDate = t.Date AND 863 c.Id = d.CardId AND 864 NOT d.IsBuy AND 865 NOT t.IsBuy AND 866 t.Date BETWEEN '2020-03-01'::TIMESTAMPTZ - INTERVAL '7 days' AND '2020-03-01'::TIMESTAMPTZ AND 867 t.AccountName <> 'someaccount' AND 868 t.customername <> 'somecustomer' 869 GROUP BY extract(day from d.TransactionDate) 870 ORDER BY extract(day from d.TransactionDate) 871 ---- 872 sort 873 ├── columns: extract:37 totalsell:32!null totalbuy:34!null totalprofit:36!null 874 ├── stable 875 ├── stats: [rows=1171234.57, distinct(37)=1171234.57, null(37)=0] 876 ├── key: (37) 877 ├── fd: (37)-->(32,34,36) 878 ├── ordering: +37 879 └── group-by 880 ├── columns: sum:32!null sum:34!null sum:36!null column37:37 881 ├── grouping columns: column37:37 882 ├── stable 883 ├── stats: [rows=1171234.57, distinct(37)=1171234.57, null(37)=0] 884 ├── key: (37) 885 ├── fd: (37)-->(32,34,36) 886 ├── project 887 │ ├── columns: column31:31!null column33:33!null column35:35!null column37:37 888 │ ├── stable 889 │ ├── stats: [rows=1198631.87, distinct(37)=1171234.57, null(37)=0] 890 │ ├── inner-join (hash) 891 │ │ ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null transactiondetails.cardid:4!null quantity:5!null transactiondetails.sellprice:6!null transactiondetails.buyprice:7!null transactions.dealerid:9!null transactions.isbuy:10!null date:11!null accountname:12!null customername:13!null id:16!null cardsinfo.dealerid:22!null cardsinfo.cardid:23!null 892 │ │ ├── stats: [rows=1198631.87, distinct(3)=1171234.57, null(3)=0, distinct(4)=37420.3552, null(4)=0, distinct(11)=1171234.57, null(11)=0, distinct(16)=37420.3552, null(16)=0] 893 │ │ ├── key: (5,11,23) 894 │ │ ├── fd: ()-->(1,2,9,10,22), (3-5)-->(6,7), (11)-->(12,13), (16)==(4,23), (23)==(4,16), (3)==(11), (11)==(3), (4)==(16,23) 895 │ │ ├── inner-join (hash) 896 │ │ │ ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null transactiondetails.cardid:4!null quantity:5!null transactiondetails.sellprice:6!null transactiondetails.buyprice:7!null transactions.dealerid:9!null transactions.isbuy:10!null date:11!null accountname:12!null customername:13!null 897 │ │ │ ├── stats: [rows=1171234.57, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=1171234.57, null(3)=0, distinct(4)=56999.9999, null(4)=0, distinct(5)=1091498.71, null(5)=0, distinct(6)=1091498.71, null(6)=0, distinct(7)=1091498.71, null(7)=0, distinct(9)=1, null(9)=0, distinct(10)=1, null(10)=0, distinct(11)=1171234.57, null(11)=0, distinct(12)=551608.449, null(12)=0, distinct(13)=551608.449, null(13)=0] 898 │ │ │ ├── key: (4,5,11) 899 │ │ │ ├── fd: ()-->(1,2,9,10), (11)-->(12,13), (3-5)-->(6,7), (3)==(11), (11)==(3) 900 │ │ │ ├── scan transactiondetails 901 │ │ │ │ ├── columns: transactiondetails.dealerid:1!null transactiondetails.isbuy:2!null transactiondate:3!null transactiondetails.cardid:4!null quantity:5!null transactiondetails.sellprice:6!null transactiondetails.buyprice:7!null 902 │ │ │ │ ├── constraint: /1/2/3/4/5: [/1/false/'2020-02-23 00:00:00+00:00' - /1/false/'2020-03-01 00:00:00+00:00'] 903 │ │ │ │ ├── stats: [rows=10630000, distinct(1)=1, null(1)=0, distinct(2)=1, null(2)=0, distinct(3)=10630000, null(3)=0, distinct(4)=57000, null(4)=0, distinct(5)=8207077.23, null(5)=0, distinct(6)=8207077.23, null(6)=0, distinct(7)=8207077.23, null(7)=0, distinct(1,2)=1, null(1,2)=0, distinct(1-3)=10630000, null(1-3)=0] 904 │ │ │ │ ├── key: (3-5) 905 │ │ │ │ └── fd: ()-->(1,2), (3-5)-->(6,7) 906 │ │ │ ├── select 907 │ │ │ │ ├── columns: transactions.dealerid:9!null transactions.isbuy:10!null date:11!null accountname:12!null customername:13!null 908 │ │ │ │ ├── stats: [rows=1171234.57, distinct(9)=1, null(9)=0, distinct(10)=1, null(10)=0, distinct(11)=1171234.57, null(11)=0, distinct(12)=666666.667, null(12)=0, distinct(13)=666666.667, null(13)=0, distinct(9,10)=1, null(9,10)=0, distinct(9-13)=1171234.57, null(9-13)=0] 909 │ │ │ │ ├── key: (11) 910 │ │ │ │ ├── fd: ()-->(9,10), (11)-->(12,13) 911 │ │ │ │ ├── scan transactions 912 │ │ │ │ │ ├── columns: transactions.dealerid:9!null transactions.isbuy:10!null date:11!null accountname:12!null customername:13!null 913 │ │ │ │ │ ├── constraint: /9/10/11: [/1/false/'2020-02-23 00:00:00+00:00' - /1/false/'2020-03-01 00:00:00+00:00'] 914 │ │ │ │ │ ├── stats: [rows=1181111.11, distinct(9)=1, null(9)=0, distinct(10)=1, null(10)=0, distinct(11)=1181111.11, null(11)=0, distinct(9,10)=1, null(9,10)=0, distinct(9-11)=1181111.11, null(9-11)=0] 915 │ │ │ │ │ ├── key: (11) 916 │ │ │ │ │ └── fd: ()-->(9,10), (11)-->(12,13) 917 │ │ │ │ └── filters 918 │ │ │ │ ├── accountname:12 != 'someaccount' [outer=(12), constraints=(/12: (/NULL - /'someaccount') [/e'someaccount\x00' - ]; tight)] 919 │ │ │ │ └── customername:13 != 'somecustomer' [outer=(13), constraints=(/13: (/NULL - /'somecustomer') [/e'somecustomer\x00' - ]; tight)] 920 │ │ │ └── filters 921 │ │ │ └── transactiondate:3 = date:11 [outer=(3,11), constraints=(/3: (/NULL - ]; /11: (/NULL - ]), fd=(3)==(11), (11)==(3)] 922 │ │ ├── inner-join (hash) 923 │ │ │ ├── columns: id:16!null cardsinfo.dealerid:22!null cardsinfo.cardid:23!null 924 │ │ │ ├── stats: [rows=58333.3333, distinct(16)=37420.3552, null(16)=0, distinct(22)=1, null(22)=0, distinct(23)=37420.3552, null(23)=0] 925 │ │ │ ├── key: (23) 926 │ │ │ ├── fd: ()-->(22), (16)==(23), (23)==(16) 927 │ │ │ ├── scan cardsinfo@cardsinfoversionindex 928 │ │ │ │ ├── columns: cardsinfo.dealerid:22!null cardsinfo.cardid:23!null 929 │ │ │ │ ├── constraint: /22/30: [/1 - /1] 930 │ │ │ │ ├── stats: [rows=58333.3333, distinct(22)=1, null(22)=0, distinct(23)=37420.3552, null(23)=0] 931 │ │ │ │ ├── key: (23) 932 │ │ │ │ └── fd: ()-->(22) 933 │ │ │ ├── scan cards@cardsnamesetnumber 934 │ │ │ │ ├── columns: id:16!null 935 │ │ │ │ ├── stats: [rows=57000, distinct(16)=57000, null(16)=0] 936 │ │ │ │ └── key: (16) 937 │ │ │ └── filters 938 │ │ │ └── id:16 = cardsinfo.cardid:23 [outer=(16,23), constraints=(/16: (/NULL - ]; /23: (/NULL - ]), fd=(16)==(23), (23)==(16)] 939 │ │ └── filters 940 │ │ └── id:16 = transactiondetails.cardid:4 [outer=(4,16), constraints=(/4: (/NULL - ]; /16: (/NULL - ]), fd=(4)==(16), (16)==(4)] 941 │ └── projections 942 │ ├── transactiondetails.sellprice:6 * quantity:5 [as=column31:31, outer=(5,6)] 943 │ ├── transactiondetails.buyprice:7 * quantity:5 [as=column33:33, outer=(5,7)] 944 │ ├── quantity:5 * (transactiondetails.sellprice:6 - transactiondetails.buyprice:7) [as=column35:35, outer=(5-7)] 945 │ └── extract('day', transactiondate:3) [as=column37:37, outer=(3), stable] 946 └── aggregations 947 ├── sum [as=sum:32, outer=(31)] 948 │ └── column31:31 949 ├── sum [as=sum:34, outer=(33)] 950 │ └── column33:33 951 └── sum [as=sum:36, outer=(35)] 952 └── column35:35 953 954 # Check if transaction was already inserted, for idempotency. 955 # 956 # Problems: 957 # 1. Missing rule to transform AnyOp into ExistsOp when both the scalar 958 # inclusion value and subquery column are non-NULL. 959 # 960 # NOTE: This looks awkward, but it's adapted from stored procedure code. 961 opt 962 SELECT 963 ( 964 '70F03EB1-4F58-4C26-B72D-C524A9D537DD'::UUID IN 965 ( 966 SELECT coalesce(OperationId, '00000000-0000-0000-0000-000000000000'::UUID) 967 FROM TransactionsView 968 WHERE IsBuy = FALSE 969 ) 970 ) AS AlreadyInserted 971 ---- 972 values 973 ├── columns: alreadyinserted:9 974 ├── cardinality: [1 - 1] 975 ├── key: () 976 ├── fd: ()-->(9) 977 └── tuple 978 └── any: eq 979 ├── project 980 │ ├── columns: coalesce:8 981 │ ├── scan transactions 982 │ │ ├── columns: dealerid:1!null isbuy:2!null operationid:6 983 │ │ ├── constraint: /1/2/3: [/1/false - /1/false] 984 │ │ ├── lax-key: (6) 985 │ │ └── fd: ()-->(1,2) 986 │ └── projections 987 │ └── COALESCE(operationid:6, '00000000-0000-0000-0000-000000000000') [as=coalesce:8, outer=(6)] 988 └── '70f03eb1-4f58-4c26-b72d-c524a9d537dd' 989 990 # Get account locations of a list of cards. 991 opt 992 WITH CardsToFind AS 993 ( 994 SELECT (IdAndQuantity).@1 AS Id, (IdAndQuantity).@2 AS Quantity 995 FROM unnest(ARRAY[(42948, 3), (24924, 4)]) AS IdAndQuantity 996 ) 997 SELECT AccountName, sum(Quantity) AS Quantity 998 FROM 999 ( 1000 SELECT Id, AccountName, (CASE WHEN needed.Quantity < have.Quantity THEN needed.Quantity ELSE have.Quantity END) Quantity 1001 FROM CardsToFind AS needed 1002 INNER JOIN LATERAL 1003 ( 1004 SELECT AccountName, Quantity 1005 FROM InventoryDetails 1006 WHERE (dealerid = 1 OR dealerid = 2 OR dealerid = 3 OR dealerid = 4 OR dealerid = 5) AND 1007 CardId = Id AND AccountName = ANY ARRAY['account-1', 'account-2', 'account-3'] 1008 ) AS have 1009 ON TRUE 1010 ) AS allData 1011 GROUP BY AccountName 1012 ORDER BY sum(Quantity) DESC 1013 LIMIT 1000 1014 ---- 1015 limit 1016 ├── columns: accountname:10!null quantity:14!null 1017 ├── internal-ordering: -14 1018 ├── cardinality: [0 - 1000] 1019 ├── key: (10) 1020 ├── fd: (10)-->(14) 1021 ├── ordering: -14 1022 ├── sort 1023 │ ├── columns: accountname:10!null sum:14!null 1024 │ ├── key: (10) 1025 │ ├── fd: (10)-->(14) 1026 │ ├── ordering: -14 1027 │ ├── limit hint: 1000.00 1028 │ └── group-by 1029 │ ├── columns: accountname:10!null sum:14!null 1030 │ ├── grouping columns: accountname:10!null 1031 │ ├── key: (10) 1032 │ ├── fd: (10)-->(14) 1033 │ ├── project 1034 │ │ ├── columns: quantity:13!null accountname:10!null 1035 │ │ ├── inner-join (lookup inventorydetails) 1036 │ │ │ ├── columns: id:6!null quantity:7!null dealerid:8!null cardid:9!null accountname:10!null inventorydetails.quantity:11!null 1037 │ │ │ ├── key columns: [8 9 10] = [8 9 10] 1038 │ │ │ ├── lookup columns are key 1039 │ │ │ ├── fd: (8-10)-->(11), (6)==(9), (9)==(6) 1040 │ │ │ ├── inner-join (lookup inventorydetails@inventorydetails_auto_index_inventorydetailscardidkey) 1041 │ │ │ │ ├── columns: id:6!null quantity:7!null dealerid:8!null cardid:9!null accountname:10!null 1042 │ │ │ │ ├── key columns: [6] = [9] 1043 │ │ │ │ ├── fd: (6)==(9), (9)==(6) 1044 │ │ │ │ ├── values 1045 │ │ │ │ │ ├── columns: id:6!null quantity:7!null 1046 │ │ │ │ │ ├── cardinality: [2 - 2] 1047 │ │ │ │ │ ├── (42948, 3) 1048 │ │ │ │ │ └── (24924, 4) 1049 │ │ │ │ └── filters 1050 │ │ │ │ ├── ((((dealerid:8 = 1) OR (dealerid:8 = 2)) OR (dealerid:8 = 3)) OR (dealerid:8 = 4)) OR (dealerid:8 = 5) [outer=(8), constraints=(/8: [/1 - /1] [/2 - /2] [/3 - /3] [/4 - /4] [/5 - /5]; tight)] 1051 │ │ │ │ └── accountname:10 IN ('account-1', 'account-2', 'account-3') [outer=(10), constraints=(/10: [/'account-1' - /'account-1'] [/'account-2' - /'account-2'] [/'account-3' - /'account-3']; tight)] 1052 │ │ │ └── filters (true) 1053 │ │ └── projections 1054 │ │ └── CASE WHEN quantity:7 < inventorydetails.quantity:11 THEN quantity:7 ELSE inventorydetails.quantity:11 END [as=quantity:13, outer=(7,11)] 1055 │ └── aggregations 1056 │ └── sum [as=sum:14, outer=(13)] 1057 │ └── quantity:13 1058 └── 1000 1059 1060 # Get buy/sell volume of a particular card in the last 2 days. 1061 # 1062 # Problems: 1063 # 1. Multiple duplicate predicates. Scan is already constraining CardId, 1064 # IsBuy, and TransactionDate. But filters still contain those checks. 1065 # 1066 opt 1067 SELECT coalesce(( 1068 SELECT sum(Quantity) AS Volume 1069 FROM 1070 ( 1071 SELECT d.Quantity 1072 FROM TransactionDetails d 1073 INNER JOIN Transactions t 1074 ON d.dealerid = t.dealerid AND d.isbuy = t.isbuy AND d.transactiondate = t.date 1075 WHERE 1076 (d.dealerid = 1 OR d.dealerid = 2 OR d.dealerid = 3 OR d.dealerid = 4 OR d.dealerid = 5) AND 1077 d.isbuy IN (TRUE, FALSE) AND 1078 d.cardid = 19483 AND 1079 d.transactiondate BETWEEN '2020-03-01'::TIMESTAMPTZ - INTERVAL '2 days' AND '2020-03-01'::TIMESTAMPTZ 1080 ORDER BY TransactionDate DESC 1081 LIMIT 100 1082 ) t 1083 ), 0) 1084 ---- 1085 values 1086 ├── columns: coalesce:17 1087 ├── cardinality: [1 - 1] 1088 ├── key: () 1089 ├── fd: ()-->(17) 1090 └── tuple 1091 └── coalesce 1092 ├── subquery 1093 │ └── scalar-group-by 1094 │ ├── columns: sum:16 1095 │ ├── cardinality: [1 - 1] 1096 │ ├── key: () 1097 │ ├── fd: ()-->(16) 1098 │ ├── limit 1099 │ │ ├── columns: d.dealerid:1!null d.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null t.dealerid:9!null t.isbuy:10!null date:11!null 1100 │ │ ├── internal-ordering: -(3|11) opt(4) 1101 │ │ ├── cardinality: [0 - 100] 1102 │ │ ├── key: (5,9-11) 1103 │ │ ├── fd: ()-->(4), (1)==(9), (9)==(1), (2)==(10), (10)==(2), (3)==(11), (11)==(3) 1104 │ │ ├── inner-join (lookup transactions) 1105 │ │ │ ├── columns: d.dealerid:1!null d.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null t.dealerid:9!null t.isbuy:10!null date:11!null 1106 │ │ │ ├── key columns: [1 2 3] = [9 10 11] 1107 │ │ │ ├── lookup columns are key 1108 │ │ │ ├── key: (5,9-11) 1109 │ │ │ ├── fd: ()-->(4), (1)==(9), (9)==(1), (2)==(10), (10)==(2), (3)==(11), (11)==(3) 1110 │ │ │ ├── ordering: -(3|11) opt(4) [actual: -3] 1111 │ │ │ ├── limit hint: 100.00 1112 │ │ │ ├── sort 1113 │ │ │ │ ├── columns: d.dealerid:1!null d.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null 1114 │ │ │ │ ├── key: (1-3,5) 1115 │ │ │ │ ├── fd: ()-->(4) 1116 │ │ │ │ ├── ordering: -3 opt(4) [actual: -3] 1117 │ │ │ │ ├── limit hint: 1100.00 1118 │ │ │ │ └── scan d@detailscardidindex 1119 │ │ │ │ ├── columns: d.dealerid:1!null d.isbuy:2!null transactiondate:3!null cardid:4!null quantity:5!null 1120 │ │ │ │ ├── constraint: /1/2/4/3/5 1121 │ │ │ │ │ ├── [/1/false/19483/'2020-02-28 00:00:00+00:00' - /1/false/19483/'2020-03-01 00:00:00+00:00'] 1122 │ │ │ │ │ ├── [/1/true/19483/'2020-02-28 00:00:00+00:00' - /1/true/19483/'2020-03-01 00:00:00+00:00'] 1123 │ │ │ │ │ ├── [/2/false/19483/'2020-02-28 00:00:00+00:00' - /2/false/19483/'2020-03-01 00:00:00+00:00'] 1124 │ │ │ │ │ ├── [/2/true/19483/'2020-02-28 00:00:00+00:00' - /2/true/19483/'2020-03-01 00:00:00+00:00'] 1125 │ │ │ │ │ ├── [/3/false/19483/'2020-02-28 00:00:00+00:00' - /3/false/19483/'2020-03-01 00:00:00+00:00'] 1126 │ │ │ │ │ ├── [/3/true/19483/'2020-02-28 00:00:00+00:00' - /3/true/19483/'2020-03-01 00:00:00+00:00'] 1127 │ │ │ │ │ ├── [/4/false/19483/'2020-02-28 00:00:00+00:00' - /4/false/19483/'2020-03-01 00:00:00+00:00'] 1128 │ │ │ │ │ ├── [/4/true/19483/'2020-02-28 00:00:00+00:00' - /4/true/19483/'2020-03-01 00:00:00+00:00'] 1129 │ │ │ │ │ ├── [/5/false/19483/'2020-02-28 00:00:00+00:00' - /5/false/19483/'2020-03-01 00:00:00+00:00'] 1130 │ │ │ │ │ └── [/5/true/19483/'2020-02-28 00:00:00+00:00' - /5/true/19483/'2020-03-01 00:00:00+00:00'] 1131 │ │ │ │ ├── key: (1-3,5) 1132 │ │ │ │ └── fd: ()-->(4) 1133 │ │ │ └── filters 1134 │ │ │ ├── (date:11 >= '2020-02-28 00:00:00+00:00') AND (date:11 <= '2020-03-01 00:00:00+00:00') [outer=(11), constraints=(/11: [/'2020-02-28 00:00:00+00:00' - /'2020-03-01 00:00:00+00:00']; tight)] 1135 │ │ │ ├── ((((t.dealerid:9 = 1) OR (t.dealerid:9 = 2)) OR (t.dealerid:9 = 3)) OR (t.dealerid:9 = 4)) OR (t.dealerid:9 = 5) [outer=(9), constraints=(/9: [/1 - /1] [/2 - /2] [/3 - /3] [/4 - /4] [/5 - /5]; tight)] 1136 │ │ │ └── t.isbuy:10 IN (false, true) [outer=(10), constraints=(/10: [/false - /false] [/true - /true]; tight)] 1137 │ │ └── 100 1138 │ └── aggregations 1139 │ └── sum [as=sum:16, outer=(5)] 1140 │ └── quantity:5 1141 └── 0 1142 1143 # -------------------------------------------------- 1144 # INSERT/UPDATE/DELETE/UPSERT Queries 1145 # -------------------------------------------------- 1146 1147 # Insert buy or sell transaction. 1148 opt 1149 INSERT INTO Transactions (dealerid, isbuy, date, accountname, customername, operationid) 1150 VALUES (1, FALSE, '2020-03-01', 'the-account', 'the-customer', '70F03EB1-4F58-4C26-B72D-C524A9D537DD') 1151 ---- 1152 insert transactions 1153 ├── columns: <none> 1154 ├── insert-mapping: 1155 │ ├── column1:8 => dealerid:1 1156 │ ├── column2:9 => isbuy:2 1157 │ ├── column3:10 => date:3 1158 │ ├── column4:11 => accountname:4 1159 │ ├── column5:12 => customername:5 1160 │ ├── column6:13 => operationid:6 1161 │ └── column14:14 => version:7 1162 ├── cardinality: [0 - 0] 1163 ├── volatile, side-effects, mutations 1164 └── values 1165 ├── columns: column1:8!null column2:9!null column3:10!null column4:11!null column5:12!null column6:13!null column14:14 1166 ├── cardinality: [1 - 1] 1167 ├── volatile, side-effects 1168 ├── key: () 1169 ├── fd: ()-->(8-14) 1170 └── (1, false, '2020-03-01 00:00:00+00:00', 'the-account', 'the-customer', '70f03eb1-4f58-4c26-b72d-c524a9d537dd', cluster_logical_timestamp()) 1171 1172 # Upsert buy or sell transaction. 1173 opt 1174 UPSERT INTO Transactions (dealerid, isbuy, date, accountname, customername, operationid) 1175 VALUES (1, FALSE, '2020-03-01', 'the-account', 'the-customer', '70F03EB1-4F58-4C26-B72D-C524A9D537DD') 1176 ---- 1177 upsert transactions 1178 ├── columns: <none> 1179 ├── canary column: 15 1180 ├── fetch columns: dealerid:15 isbuy:16 date:17 accountname:18 customername:19 operationid:20 version:21 1181 ├── insert-mapping: 1182 │ ├── column1:8 => dealerid:1 1183 │ ├── column2:9 => isbuy:2 1184 │ ├── column3:10 => date:3 1185 │ ├── column4:11 => accountname:4 1186 │ ├── column5:12 => customername:5 1187 │ ├── column6:13 => operationid:6 1188 │ └── column14:14 => version:7 1189 ├── update-mapping: 1190 │ ├── column4:11 => accountname:4 1191 │ ├── column5:12 => customername:5 1192 │ └── column6:13 => operationid:6 1193 ├── cardinality: [0 - 0] 1194 ├── volatile, side-effects, mutations 1195 └── left-join (cross) 1196 ├── columns: column1:8!null column2:9!null column3:10!null column4:11!null column5:12!null column6:13!null column14:14 dealerid:15 isbuy:16 date:17 accountname:18 customername:19 operationid:20 version:21 1197 ├── cardinality: [1 - 1] 1198 ├── volatile, side-effects 1199 ├── key: () 1200 ├── fd: ()-->(8-21) 1201 ├── values 1202 │ ├── columns: column1:8!null column2:9!null column3:10!null column4:11!null column5:12!null column6:13!null column14:14 1203 │ ├── cardinality: [1 - 1] 1204 │ ├── volatile, side-effects 1205 │ ├── key: () 1206 │ ├── fd: ()-->(8-14) 1207 │ └── (1, false, '2020-03-01 00:00:00+00:00', 'the-account', 'the-customer', '70f03eb1-4f58-4c26-b72d-c524a9d537dd', cluster_logical_timestamp()) 1208 ├── scan transactions 1209 │ ├── columns: dealerid:15!null isbuy:16!null date:17!null accountname:18!null customername:19!null operationid:20 version:21!null 1210 │ ├── constraint: /15/16/17: [/1/false/'2020-03-01 00:00:00+00:00' - /1/false/'2020-03-01 00:00:00+00:00'] 1211 │ ├── cardinality: [0 - 1] 1212 │ ├── key: () 1213 │ └── fd: ()-->(15-21) 1214 └── filters (true) 1215 1216 # Insert structured data (c=CardId, q=Quantity, s=SellPrice, b=BuyPrice) 1217 # represented as JSON into TransactionDetails table. 1218 opt 1219 WITH updates AS (SELECT jsonb_array_elements('[ 1220 {"c": 49833, "q": 4, "s": 2.89, "b": 2.29}, 1221 {"c": 29483, "q": 2, "s": 18.93, "b": 17.59} 1222 ]'::JSONB 1223 ) AS Detail) 1224 UPSERT INTO TransactionDetails 1225 (dealerid, isbuy, transactiondate, cardid, quantity, sellprice, buyprice) 1226 SELECT 1227 1, FALSE, current_timestamp(), (Detail->'c')::TEXT::INT, (Detail->'q')::TEXT::INT, 1228 (Detail->'s')::TEXT::DECIMAL(10,4), (Detail->'b')::TEXT::DECIMAL(10,4) 1229 FROM updates 1230 ---- 1231 upsert transactiondetails 1232 ├── columns: <none> 1233 ├── canary column: 21 1234 ├── fetch columns: transactiondetails.dealerid:21 transactiondetails.isbuy:22 transactiondate:23 cardid:24 quantity:25 transactiondetails.sellprice:26 transactiondetails.buyprice:27 transactiondetails.version:28 1235 ├── insert-mapping: 1236 │ ├── "?column?":11 => transactiondetails.dealerid:2 1237 │ ├── bool:12 => transactiondetails.isbuy:3 1238 │ ├── current_timestamp:13 => transactiondate:4 1239 │ ├── int8:14 => cardid:5 1240 │ ├── int8:15 => quantity:6 1241 │ ├── sellprice:29 => transactiondetails.sellprice:7 1242 │ ├── buyprice:30 => transactiondetails.buyprice:8 1243 │ └── column18:18 => transactiondetails.version:9 1244 ├── update-mapping: 1245 │ ├── sellprice:29 => transactiondetails.sellprice:7 1246 │ └── buyprice:30 => transactiondetails.buyprice:8 1247 ├── input binding: &2 1248 ├── cardinality: [0 - 0] 1249 ├── stable+volatile, side-effects, mutations 1250 ├── project 1251 │ ├── columns: upsert_dealerid:31 upsert_isbuy:32 upsert_transactiondate:33 upsert_cardid:34 sellprice:29 buyprice:30 "?column?":11!null bool:12!null current_timestamp:13 int8:14 int8:15 column18:18 transactiondetails.dealerid:21 transactiondetails.isbuy:22 transactiondate:23 cardid:24 quantity:25 transactiondetails.sellprice:26 transactiondetails.buyprice:27 transactiondetails.version:28 1252 │ ├── cardinality: [1 - ] 1253 │ ├── stable+volatile, side-effects 1254 │ ├── lax-key: (13-15,21-25) 1255 │ ├── fd: ()-->(11,12), (13-15)~~>(18), (21-25)-->(26-28), (21)-->(31), (21,22)-->(32), (13,21,23)-->(33), (14,21,24)-->(34), (13-15,21-25)~~>(18,29,30) 1256 │ ├── left-join (lookup transactiondetails) 1257 │ │ ├── columns: "?column?":11!null bool:12!null current_timestamp:13 int8:14 int8:15 column18:18 sellprice:19 buyprice:20 transactiondetails.dealerid:21 transactiondetails.isbuy:22 transactiondate:23 cardid:24 quantity:25 transactiondetails.sellprice:26 transactiondetails.buyprice:27 transactiondetails.version:28 1258 │ │ ├── key columns: [11 12 13 14 15] = [21 22 23 24 25] 1259 │ │ ├── lookup columns are key 1260 │ │ ├── cardinality: [1 - ] 1261 │ │ ├── stable+volatile, side-effects 1262 │ │ ├── lax-key: (13-15,21-25) 1263 │ │ ├── fd: ()-->(11,12), (13-15)~~>(18-20), (21-25)-->(26-28) 1264 │ │ ├── ensure-upsert-distinct-on 1265 │ │ │ ├── columns: "?column?":11!null bool:12!null current_timestamp:13 int8:14 int8:15 column18:18 sellprice:19 buyprice:20 1266 │ │ │ ├── grouping columns: current_timestamp:13 int8:14 int8:15 1267 │ │ │ ├── error: "UPSERT or INSERT...ON CONFLICT command cannot affect row a second time" 1268 │ │ │ ├── cardinality: [1 - 2] 1269 │ │ │ ├── stable+volatile, side-effects 1270 │ │ │ ├── lax-key: (13-15) 1271 │ │ │ ├── fd: ()-->(11,12), (13-15)~~>(11,12,18-20) 1272 │ │ │ ├── project 1273 │ │ │ │ ├── columns: sellprice:19 buyprice:20 column18:18 "?column?":11!null bool:12!null current_timestamp:13 int8:14 int8:15 1274 │ │ │ │ ├── cardinality: [2 - 2] 1275 │ │ │ │ ├── stable+volatile, side-effects 1276 │ │ │ │ ├── fd: ()-->(11,12) 1277 │ │ │ │ ├── values 1278 │ │ │ │ │ ├── columns: detail:10!null 1279 │ │ │ │ │ ├── cardinality: [2 - 2] 1280 │ │ │ │ │ ├── ('{"b": 2.29, "c": 49833, "q": 4, "s": 2.89}',) 1281 │ │ │ │ │ └── ('{"b": 17.59, "c": 29483, "q": 2, "s": 18.93}',) 1282 │ │ │ │ └── projections 1283 │ │ │ │ ├── crdb_internal.round_decimal_values((detail:10->'s')::STRING::DECIMAL(10,4), 4) [as=sellprice:19, outer=(10), immutable] 1284 │ │ │ │ ├── crdb_internal.round_decimal_values((detail:10->'b')::STRING::DECIMAL(10,4), 4) [as=buyprice:20, outer=(10), immutable] 1285 │ │ │ │ ├── cluster_logical_timestamp() [as=column18:18, volatile, side-effects] 1286 │ │ │ │ ├── 1 [as="?column?":11] 1287 │ │ │ │ ├── false [as=bool:12] 1288 │ │ │ │ ├── current_timestamp() [as=current_timestamp:13, stable, side-effects] 1289 │ │ │ │ ├── (detail:10->'c')::STRING::INT8 [as=int8:14, outer=(10)] 1290 │ │ │ │ └── (detail:10->'q')::STRING::INT8 [as=int8:15, outer=(10)] 1291 │ │ │ └── aggregations 1292 │ │ │ ├── first-agg [as=sellprice:19, outer=(19)] 1293 │ │ │ │ └── sellprice:19 1294 │ │ │ ├── first-agg [as=buyprice:20, outer=(20)] 1295 │ │ │ │ └── buyprice:20 1296 │ │ │ ├── first-agg [as=column18:18, outer=(18)] 1297 │ │ │ │ └── column18:18 1298 │ │ │ ├── const-agg [as="?column?":11, outer=(11)] 1299 │ │ │ │ └── "?column?":11 1300 │ │ │ └── const-agg [as=bool:12, outer=(12)] 1301 │ │ │ └── bool:12 1302 │ │ └── filters (true) 1303 │ └── projections 1304 │ ├── CASE WHEN transactiondetails.dealerid:21 IS NULL THEN "?column?":11 ELSE transactiondetails.dealerid:21 END [as=upsert_dealerid:31, outer=(11,21)] 1305 │ ├── CASE WHEN transactiondetails.dealerid:21 IS NULL THEN bool:12 ELSE transactiondetails.isbuy:22 END [as=upsert_isbuy:32, outer=(12,21,22)] 1306 │ ├── CASE WHEN transactiondetails.dealerid:21 IS NULL THEN current_timestamp:13 ELSE transactiondate:23 END [as=upsert_transactiondate:33, outer=(13,21,23)] 1307 │ ├── CASE WHEN transactiondetails.dealerid:21 IS NULL THEN int8:14 ELSE cardid:24 END [as=upsert_cardid:34, outer=(14,21,24)] 1308 │ ├── crdb_internal.round_decimal_values(sellprice:19, 4) [as=sellprice:29, outer=(19), immutable] 1309 │ └── crdb_internal.round_decimal_values(buyprice:20, 4) [as=buyprice:30, outer=(20), immutable] 1310 └── f-k-checks 1311 ├── f-k-checks-item: transactiondetails(dealerid,isbuy,transactiondate) -> transactions(dealerid,isbuy,date) 1312 │ └── anti-join (lookup transactions) 1313 │ ├── columns: upsert_dealerid:37 upsert_isbuy:38 upsert_transactiondate:39 1314 │ ├── key columns: [37 38 39] = [40 41 42] 1315 │ ├── lookup columns are key 1316 │ ├── with-scan &2 1317 │ │ ├── columns: upsert_dealerid:37 upsert_isbuy:38 upsert_transactiondate:39 1318 │ │ ├── mapping: 1319 │ │ │ ├── upsert_dealerid:31 => upsert_dealerid:37 1320 │ │ │ ├── upsert_isbuy:32 => upsert_isbuy:38 1321 │ │ │ └── upsert_transactiondate:33 => upsert_transactiondate:39 1322 │ │ └── cardinality: [1 - ] 1323 │ └── filters (true) 1324 └── f-k-checks-item: transactiondetails(cardid) -> cards(id) 1325 └── anti-join (lookup cards) 1326 ├── columns: upsert_cardid:47 1327 ├── key columns: [47] = [48] 1328 ├── lookup columns are key 1329 ├── with-scan &2 1330 │ ├── columns: upsert_cardid:47 1331 │ ├── mapping: 1332 │ │ └── upsert_cardid:34 => upsert_cardid:47 1333 │ └── cardinality: [1 - ] 1334 └── filters (true) 1335 1336 # Delete inventory detail rows to reflect card transfers. 1337 opt 1338 DELETE FROM InventoryDetails 1339 WHERE dealerid = 1 AND accountname = 'some-account' AND cardid = ANY ARRAY[29483, 1793, 294] 1340 ---- 1341 delete inventorydetails 1342 ├── columns: <none> 1343 ├── fetch columns: dealerid:6 cardid:7 accountname:8 1344 ├── cardinality: [0 - 0] 1345 ├── volatile, side-effects, mutations 1346 └── scan inventorydetails@inventorydetails_auto_index_inventorydetailscardidkey 1347 ├── columns: dealerid:6!null cardid:7!null accountname:8!null 1348 ├── constraint: /7/6/8 1349 │ ├── [/294/1/'some-account' - /294/1/'some-account'] 1350 │ ├── [/1793/1/'some-account' - /1793/1/'some-account'] 1351 │ └── [/29483/1/'some-account' - /29483/1/'some-account'] 1352 ├── cardinality: [0 - 3] 1353 ├── key: (7) 1354 └── fd: ()-->(6,8) 1355 1356 # Update CardsInfo inventory numbers (by CardId, Quantity) to reflect card 1357 # transfers. 1358 opt 1359 WITH Updates AS 1360 ( 1361 SELECT (Detail).@1 AS c, (Detail).@2 AS q 1362 FROM unnest(ARRAY[(42948, 3), (24924, 4)]) AS Detail 1363 ) 1364 UPDATE CardsInfo ci 1365 SET actualinventory = (SELECT coalesce(sum_INT(quantity), 0) 1366 FROM InventoryDetails id 1367 WHERE dealerid = 1 AND id.cardid = ci.cardid) 1368 FROM Updates 1369 WHERE ci.cardid = Updates.c AND ci.dealerid = 1 1370 ---- 1371 update ci 1372 ├── columns: <none> 1373 ├── fetch columns: ci.dealerid:15 ci.cardid:16 buyprice:17 sellprice:18 discount:19 desiredinventory:20 actualinventory:21 maxinventory:22 ci.version:23 1374 ├── update-mapping: 1375 │ └── actualinventory_new:33 => actualinventory:12 1376 ├── cardinality: [0 - 0] 1377 ├── volatile, side-effects, mutations 1378 └── project 1379 ├── columns: actualinventory_new:33 ci.dealerid:15!null ci.cardid:16!null buyprice:17!null sellprice:18!null discount:19!null desiredinventory:20!null actualinventory:21!null maxinventory:22!null ci.version:23!null c:24!null q:25!null 1380 ├── key: (16) 1381 ├── fd: ()-->(15), (16)-->(17-25,33), (23)-->(16-22), (16)==(24), (24)==(16) 1382 ├── group-by 1383 │ ├── columns: ci.dealerid:15!null ci.cardid:16!null buyprice:17!null sellprice:18!null discount:19!null desiredinventory:20!null actualinventory:21!null maxinventory:22!null ci.version:23!null c:24!null q:25!null sum_int:31 1384 │ ├── grouping columns: ci.cardid:16!null 1385 │ ├── key: (16) 1386 │ ├── fd: ()-->(15), (16)-->(15,17-25,31), (23)-->(16-22), (16)==(24), (24)==(16) 1387 │ ├── left-join (lookup inventorydetails) 1388 │ │ ├── columns: ci.dealerid:15!null ci.cardid:16!null buyprice:17!null sellprice:18!null discount:19!null desiredinventory:20!null actualinventory:21!null maxinventory:22!null ci.version:23!null c:24!null q:25!null id.dealerid:26 id.cardid:27 quantity:29 1389 │ │ ├── key columns: [37 16] = [26 27] 1390 │ │ ├── fd: ()-->(15), (16)-->(17-25), (23)-->(16-22), (16)==(24), (24)==(16) 1391 │ │ ├── project 1392 │ │ │ ├── columns: "project_const_col_@26":37!null ci.dealerid:15!null ci.cardid:16!null buyprice:17!null sellprice:18!null discount:19!null desiredinventory:20!null actualinventory:21!null maxinventory:22!null ci.version:23!null c:24!null q:25!null 1393 │ │ │ ├── key: (16) 1394 │ │ │ ├── fd: ()-->(15,37), (16)-->(17-25), (23)-->(16-22), (16)==(24), (24)==(16) 1395 │ │ │ ├── distinct-on 1396 │ │ │ │ ├── columns: ci.dealerid:15!null ci.cardid:16!null buyprice:17!null sellprice:18!null discount:19!null desiredinventory:20!null actualinventory:21!null maxinventory:22!null ci.version:23!null c:24!null q:25!null 1397 │ │ │ │ ├── grouping columns: ci.cardid:16!null 1398 │ │ │ │ ├── key: (16) 1399 │ │ │ │ ├── fd: ()-->(15), (16)-->(15,17-25), (23)-->(16-22), (16)==(24), (24)==(16) 1400 │ │ │ │ ├── inner-join (lookup cardsinfo) 1401 │ │ │ │ │ ├── columns: ci.dealerid:15!null ci.cardid:16!null buyprice:17!null sellprice:18!null discount:19!null desiredinventory:20!null actualinventory:21!null maxinventory:22!null ci.version:23!null c:24!null q:25!null 1402 │ │ │ │ │ ├── key columns: [34 24] = [15 16] 1403 │ │ │ │ │ ├── lookup columns are key 1404 │ │ │ │ │ ├── fd: ()-->(15), (16)-->(17-23), (23)-->(16-22), (16)==(24), (24)==(16) 1405 │ │ │ │ │ ├── project 1406 │ │ │ │ │ │ ├── columns: "project_const_col_@15":34!null c:24!null q:25!null 1407 │ │ │ │ │ │ ├── cardinality: [2 - 2] 1408 │ │ │ │ │ │ ├── fd: ()-->(34) 1409 │ │ │ │ │ │ ├── values 1410 │ │ │ │ │ │ │ ├── columns: c:24!null q:25!null 1411 │ │ │ │ │ │ │ ├── cardinality: [2 - 2] 1412 │ │ │ │ │ │ │ ├── (42948, 3) 1413 │ │ │ │ │ │ │ └── (24924, 4) 1414 │ │ │ │ │ │ └── projections 1415 │ │ │ │ │ │ └── 1 [as="project_const_col_@15":34] 1416 │ │ │ │ │ └── filters (true) 1417 │ │ │ │ └── aggregations 1418 │ │ │ │ ├── first-agg [as=buyprice:17, outer=(17)] 1419 │ │ │ │ │ └── buyprice:17 1420 │ │ │ │ ├── first-agg [as=sellprice:18, outer=(18)] 1421 │ │ │ │ │ └── sellprice:18 1422 │ │ │ │ ├── first-agg [as=discount:19, outer=(19)] 1423 │ │ │ │ │ └── discount:19 1424 │ │ │ │ ├── first-agg [as=desiredinventory:20, outer=(20)] 1425 │ │ │ │ │ └── desiredinventory:20 1426 │ │ │ │ ├── first-agg [as=actualinventory:21, outer=(21)] 1427 │ │ │ │ │ └── actualinventory:21 1428 │ │ │ │ ├── first-agg [as=maxinventory:22, outer=(22)] 1429 │ │ │ │ │ └── maxinventory:22 1430 │ │ │ │ ├── first-agg [as=ci.version:23, outer=(23)] 1431 │ │ │ │ │ └── ci.version:23 1432 │ │ │ │ ├── first-agg [as=c:24, outer=(24)] 1433 │ │ │ │ │ └── c:24 1434 │ │ │ │ ├── first-agg [as=q:25, outer=(25)] 1435 │ │ │ │ │ └── q:25 1436 │ │ │ │ └── const-agg [as=ci.dealerid:15, outer=(15)] 1437 │ │ │ │ └── ci.dealerid:15 1438 │ │ │ └── projections 1439 │ │ │ └── 1 [as="project_const_col_@26":37] 1440 │ │ └── filters (true) 1441 │ └── aggregations 1442 │ ├── sum-int [as=sum_int:31, outer=(29)] 1443 │ │ └── quantity:29 1444 │ ├── const-agg [as=ci.dealerid:15, outer=(15)] 1445 │ │ └── ci.dealerid:15 1446 │ ├── const-agg [as=buyprice:17, outer=(17)] 1447 │ │ └── buyprice:17 1448 │ ├── const-agg [as=sellprice:18, outer=(18)] 1449 │ │ └── sellprice:18 1450 │ ├── const-agg [as=discount:19, outer=(19)] 1451 │ │ └── discount:19 1452 │ ├── const-agg [as=desiredinventory:20, outer=(20)] 1453 │ │ └── desiredinventory:20 1454 │ ├── const-agg [as=actualinventory:21, outer=(21)] 1455 │ │ └── actualinventory:21 1456 │ ├── const-agg [as=maxinventory:22, outer=(22)] 1457 │ │ └── maxinventory:22 1458 │ ├── const-agg [as=ci.version:23, outer=(23)] 1459 │ │ └── ci.version:23 1460 │ ├── const-agg [as=c:24, outer=(24)] 1461 │ │ └── c:24 1462 │ └── const-agg [as=q:25, outer=(25)] 1463 │ └── q:25 1464 └── projections 1465 └── COALESCE(sum_int:31, 0) [as=actualinventory_new:33, outer=(31)]