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