Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Query planner heuristic update: When doing a full table scan on a table that has an equality constraint on an unindexed column, do not allow the estimated number of output rows to be greater than half the total number of rows in the table. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
73954f93c4c6f880c6e01d0d130e3fed |
User & Date: | drh 2014-09-06 01:35:57.122 |
Context
2014-09-06
| ||
02:00 | Fix a couple of typos in comments. No changes to code. (check-in: a758465e3c user: mistachkin tags: trunk) | |
01:35 | Query planner heuristic update: When doing a full table scan on a table that has an equality constraint on an unindexed column, do not allow the estimated number of output rows to be greater than half the total number of rows in the table. (check-in: 73954f93c4 user: drh tags: trunk) | |
2014-09-05
| ||
05:58 | Fix harmless compiler warning. (check-in: 7331190677 user: mistachkin tags: trunk) | |
Changes
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1155 1156 1157 1158 1159 1160 1161 | #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat3 0x0800 /* Use the SQLITE_STAT3 table */ | < | 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 | #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat3 0x0800 /* Use the SQLITE_STAT3 table */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #ifndef SQLITE_OMIT_BUILTIN_TEST #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
4217 4218 4219 4220 4221 4222 4223 | ** WHERE clause that reference the loop but which are not used by an ** index. ** ** In the current implementation, the first extra WHERE clause term reduces ** the number of output rows by a factor of 10 and each additional term ** reduces the number of output rows by sqrt(2). */ | | > > > > | < < | | > > > > | > > > > > > > > | 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 4240 4241 4242 4243 4244 4245 4246 4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 4263 4264 4265 | ** WHERE clause that reference the loop but which are not used by an ** index. ** ** In the current implementation, the first extra WHERE clause term reduces ** the number of output rows by a factor of 10 and each additional term ** reduces the number of output rows by sqrt(2). */ static void whereLoopOutputAdjust( WhereClause *pWC, /* The WHERE clause */ WhereLoop *pLoop, /* The loop to adjust downward */ LogEst nRow /* Number of rows in the entire table */ ){ WhereTerm *pTerm, *pX; Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf); int i, j; int nEq = 0; /* Number of = constraints not within likely()/unlike() */ for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){ if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break; if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue; if( (pTerm->prereqAll & notAllowed)!=0 ) continue; for(j=pLoop->nLTerm-1; j>=0; j--){ pX = pLoop->aLTerm[j]; if( pX==0 ) continue; if( pX==pTerm ) break; if( pX->iParent>=0 && (&pWC->a[pX->iParent])==pTerm ) break; } if( j<0 ){ if( pTerm->truthProb<=0 ){ pLoop->nOut += pTerm->truthProb; }else{ pLoop->nOut--; if( pTerm->eOperator&WO_EQ ) nEq++; } } } /* TUNING: If there is at least one equality constraint in the WHERE ** clause that does not have a likelihood() explicitly assigned to it ** then do not let the estimated number of output rows exceed half ** the number of rows in the table. */ if( nEq && pLoop->nOut>nRow-10 ){ pLoop->nOut = nRow - 10; } } /* ** Adjust the cost C by the costMult facter T. This only occurs if ** compiled with -DSQLITE_ENABLE_COSTMULT */ |
︙ | ︙ | |||
4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 | u16 saved_nLTerm; /* Original value of pNew->nLTerm */ u16 saved_nEq; /* Original value of pNew->u.btree.nEq */ u16 saved_nSkip; /* Original value of pNew->u.btree.nSkip */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ LogEst saved_nOut; /* Original value of pNew->nOut */ int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ LogEst rLogSize; /* Logarithm of table size */ WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */ pNew = pBuilder->pNew; if( db->mallocFailed ) return SQLITE_NOMEM; assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 ); | > | 4298 4299 4300 4301 4302 4303 4304 4305 4306 4307 4308 4309 4310 4311 4312 | u16 saved_nLTerm; /* Original value of pNew->nLTerm */ u16 saved_nEq; /* Original value of pNew->u.btree.nEq */ u16 saved_nSkip; /* Original value of pNew->u.btree.nSkip */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ LogEst saved_nOut; /* Original value of pNew->nOut */ int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ LogEst rSize; /* Number of rows in the table */ LogEst rLogSize; /* Logarithm of table size */ WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */ pNew = pBuilder->pNew; if( db->mallocFailed ) return SQLITE_NOMEM; assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 ); |
︙ | ︙ | |||
4313 4314 4315 4316 4317 4318 4319 | saved_nEq = pNew->u.btree.nEq; saved_nSkip = pNew->u.btree.nSkip; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; pNew->rSetup = 0; | > | | 4328 4329 4330 4331 4332 4333 4334 4335 4336 4337 4338 4339 4340 4341 4342 4343 | saved_nEq = pNew->u.btree.nEq; saved_nSkip = pNew->u.btree.nSkip; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; pNew->rSetup = 0; rSize = pProbe->aiRowLogEst[0]; rLogSize = estLog(rSize); /* Consider using a skip-scan if there are no WHERE clause constraints ** available for the left-most terms of the index, and if the average ** number of repeats in the left-most terms is at least 18. ** ** The magic number 18 is selected on the basis that scanning 17 rows ** is almost always quicker than an index seek (even though if the index |
︙ | ︙ | |||
4490 4491 4492 4493 4494 4495 4496 | pNew->rRun = sqlite3LogEstAdd(pNew->rRun, pNew->nOut + 16); } ApplyCostMultiplier(pNew->rRun, pProbe->pTable->costMult); nOutUnadjusted = pNew->nOut; pNew->rRun += nInMul + nIn; pNew->nOut += nInMul + nIn; | | | 4506 4507 4508 4509 4510 4511 4512 4513 4514 4515 4516 4517 4518 4519 4520 | pNew->rRun = sqlite3LogEstAdd(pNew->rRun, pNew->nOut + 16); } ApplyCostMultiplier(pNew->rRun, pProbe->pTable->costMult); nOutUnadjusted = pNew->nOut; pNew->rRun += nInMul + nIn; pNew->nOut += nInMul + nIn; whereLoopOutputAdjust(pBuilder->pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ pNew->nOut = saved_nOut; }else{ pNew->nOut = nOutUnadjusted; } |
︙ | ︙ | |||
4744 4745 4746 4747 4748 4749 4750 | pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is (N*3.0). */ pNew->rRun = rSize + 16; ApplyCostMultiplier(pNew->rRun, pTab->costMult); | | | 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 4772 4773 4774 | pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is (N*3.0). */ pNew->rRun = rSize + 16; ApplyCostMultiplier(pNew->rRun, pTab->costMult); whereLoopOutputAdjust(pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; }else{ Bitmask m; if( pProbe->isCovering ){ pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED; |
︙ | ︙ | |||
4780 4781 4782 4783 4784 4785 4786 | ** index and table rows. If this is a non-covering index scan, ** also add the cost of visiting table rows (N*3.0). */ pNew->rRun = rSize + 1 + (15*pProbe->szIdxRow)/pTab->szTabRow; if( m!=0 ){ pNew->rRun = sqlite3LogEstAdd(pNew->rRun, rSize+16); } ApplyCostMultiplier(pNew->rRun, pTab->costMult); | | | 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 | ** index and table rows. If this is a non-covering index scan, ** also add the cost of visiting table rows (N*3.0). */ pNew->rRun = rSize + 1 + (15*pProbe->szIdxRow)/pTab->szTabRow; if( m!=0 ){ pNew->rRun = sqlite3LogEstAdd(pNew->rRun, rSize+16); } ApplyCostMultiplier(pNew->rRun, pTab->costMult); whereLoopOutputAdjust(pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; } } rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); |
︙ | ︙ |
Changes to test/whereJ.test.
︙ | ︙ | |||
369 370 371 372 373 374 375 | AND t3b.id BETWEEN t2b.minChild AND t2b.maxChild AND t4.id BETWEEN t3a.minChild AND t3b.maxChild ORDER BY t4.x; } {~/SCAN/} ############################################################################ | < | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 | AND t3b.id BETWEEN t2b.minChild AND t2b.maxChild AND t4.id BETWEEN t3a.minChild AND t3b.maxChild ORDER BY t4.x; } {~/SCAN/} ############################################################################ # Create and populate table. do_execsql_test 3.1 { CREATE TABLE t1(a, b, c) } for {set i 0} {$i < 32} {incr i 2} { for {set x 0} {$x < 100} {incr x} { execsql { INSERT INTO t1 VALUES($i, $x, $c) } incr c } execsql { INSERT INTO t1 VALUES($i+1, 5, $c) } incr c } do_execsql_test 3.2 { SELECT a, count(*) FROM t1 GROUP BY a HAVING a < 8; } { 0 100 1 1 2 100 3 1 4 100 5 1 6 100 7 1 } do_execsql_test 3.3 { CREATE INDEX idx_ab ON t1(a, b); CREATE INDEX idx_c ON t1(c); ANALYZE; } {} # This one should use index "idx_c". do_eqp_test 3.4 { SELECT * FROM t1 WHERE a = 4 AND b BETWEEN 20 AND 80 -- Matches 80 rows AND c BETWEEN 150 AND 160 -- Matches 10 rows } { 0 0 0 {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)} } # This one should use index "idx_ab". do_eqp_test 3.5 { SELECT * FROM t1 WHERE a = 5 AND b BETWEEN 20 AND 80 -- Matches 1 row AND c BETWEEN 150 AND 160 -- Matches 10 rows } { 0 0 0 {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)} } ########################################################################################### # Reset the database and setup for a test case derived from an actual SQLite users # db close sqlite3 db test.db do_execsql_test 4.1 { CREATE TABLE le( le_id largeint, xid char(31), type smallint, name char(255) DEFAULT '', mtime largeint DEFAULT 0, muuid int DEFAULT 0 ); CREATE TABLE cx( cx_id largeint, code char(31), type smallint, name char(31), description varchar, role smallint, mtime largeint DEFAULT 0, muuid int DEFAULT 0, le_id largeint DEFAULT 0, imco smallint DEFAULT 0 ); CREATE TABLE px( px_id largeint, cx_id largeint, px_tid largeint, name char(31), description varchar DEFAULT '', ia smallint, sl smallint, le_id largeint DEFAULT 0, mtime largeint DEFAULT 0, muuid int DEFAULT 0 ); CREATE INDEX le_id on le (le_id); CREATE INDEX c_id on cx (cx_id); CREATE INDEX c_leid on cx (le_id); CREATE INDEX p_id on px (px_id); CREATE INDEX p_cid0 on px (cx_id); CREATE INDEX p_pt on px (px_tid); CREATE INDEX p_leid on px (le_id); } {} do_execsql_test 4.2 { ANALYZE sqlite_master; INSERT INTO sqlite_stat1 VALUES('le','le_id','1979 1'); INSERT INTO sqlite_stat1 VALUES('cx','c_leid','852 171'); INSERT INTO sqlite_stat1 VALUES('cx','c_id','852 1'); INSERT INTO sqlite_stat1 VALUES('px','p_leid','114443 63'); INSERT INTO sqlite_stat1 VALUES('px','p_pt','114443 22889'); INSERT INTO sqlite_stat1 VALUES('px','p_cid0','114443 181'); INSERT INTO sqlite_stat1 VALUES('px','p_id','114443 1'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','162 162','162 162',X'030202013903fb'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','208 208','208 208',X'0302020253012d'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','219 219','219 219',X'030202025e0131'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','248 248','248 248',X'030202027b014e'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','265 265','265 265',X'030202028c015f'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','358 358','358 358',X'03020202e901bc'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','439 439','439 439',X'030202033a020d'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','657 657','657 657',X'030202041402b4'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','659 659','659 659',X'030202041602b6'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','681 681','681 681',X'030202042c02cc'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','831 831','831 831',X'03020204c20482'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','852 852','852 852',X'03020204d70497'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','870 870','870 870',X'03020204e904a9'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','879 879','879 879',X'03020204f204b2'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1099 1099','1099 1099',X'03020205ce058e'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1273 1273','1273 1273',X'030202067c05a9'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1319 1319','1319 1319',X'03020206e30730'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1330 1330','1330 1330',X'0302020700035b'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1539 1539','1539 1539',X'03020207d105d8'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1603 1603','1603 1603',X'03020208390780'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1759 1759','1759 1759',X'030202092f0618'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1843 1843','1843 1843',X'03020209880650'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1915 1915','1915 1915',X'03020209d0068b'); INSERT INTO sqlite_stat4 VALUES('le','le_id','1 1','1927 1927','1927 1927',X'03020209dc0697'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 94','0 94',X'0308015f'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 189','0 189',X'03080200be'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 284','0 284',X'0308020120'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 379','0 379',X'030802017f'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 474','0 474',X'03080201de'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 569','0 569',X'030802023d'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 664','0 664',X'030802029f'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','846 1','0 759','0 759',X'03080202fe'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','3 1','846 847','1 847',X'0301024500e6'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','1 1','849 849','2 849',X'03010246027e'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','1 1','850 850','3 850',X'0301024700c9'); INSERT INTO sqlite_stat4 VALUES('cx','c_leid','1 1','851 851','4 851',X'03010248027f'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','94 94','94 94',X'03020200b801a8'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','113 113','113 113',X'03020200d101ad'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','171 171','171 171',X'030201011d2a'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','177 177','177 177',X'030202012600f2'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','189 189','189 189',X'030202013501c8'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','206 206','206 206',X'030201014f2d'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','231 231','231 231',X'030202016d00fc'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','284 284','284 284',X'03020201b702d0'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','291 291','291 291',X'03020101c042'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','311 311','311 311',X'03020201d801e7'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','339 339','339 339',X'03020101f74b'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','347 347','347 347',X'03020202030118'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','379 379','379 379',X'030202022f01fa'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','393 393','393 393',X'030201023f55'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','407 407','407 407',X'03020202500201'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','413 413','413 413',X'03020102565a'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','468 468','468 468',X'030201029468'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','474 474','474 474',X'030202029a0211'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','517 517','517 517',X'03020102cc76'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','548 548','548 548',X'03020202f00223'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','569 569','569 569',X'03020203090087'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','664 664','664 664',X'03020203740163'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','759 759','759 759',X'03020203e800b3'); INSERT INTO sqlite_stat4 VALUES('cx','c_id','1 1','803 803','803 803',X'030202041b026f'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 12715','0 12715',X'030802345b'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 25431','0 25431',X'0308026718'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 38147','0 38147',X'030803009a5c'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 50863','0 50863',X'03080300cdbe'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 63579','0 63579',X'0308030100e8'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 76295','0 76295',X'03080301351d'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 89011','0 89011',X'03080301674c'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110728 1','0 101727','0 101727',X'030803019b99'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','28 1','110824 110843','16 110843',X'0301037a0107f1'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','53 1','110873 110875','25 110875',X'0302020095275a'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','32 1','110927 110936','27 110936',X'030203009b009b4a'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','51 1','110980 111017','30 111017',X'03020300a4016c00'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','67 1','111047 111059','38 111059',X'03020200af2611'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','60 1','111136 111156','43 111156',X'03020300bc009aeb'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','42 1','111222 111239','59 111239',X'03020300d200b17b'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','36 1','111264 111266','60 111266',X'03020200d426d6'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','27 1','111733 111757','159 111757',X'030203014e017e1b'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','36 1','111760 111773','160 111773',X'030203014f00a2b9'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','29 1','111822 111833','167 111833',X'0302030176009c22'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','75 1','113031 113095','1190 113095',X'030203068501912c'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','132 1','113230 113263','1252 113263',X'0302030711009ee6'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','110 1','113851 113918','1572 113918',X'03020308e9011ca2'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','78 1','114212 114217','1791 114217',X'03020209e13b24'); INSERT INTO sqlite_stat4 VALUES('px','p_leid','112 1','114303 114351','1799 114351',X'03020309ea0128f2'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 12715','0 12715',X'030802477e'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 25431','0 25431',X'0308027c20'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 38147','0 38147',X'03080300c211'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 50863','0 50863',X'03080300fbe5'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 63579','0 63579',X'0308030140ff'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 76295','0 76295',X'03080301792d'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','89824 1','0 89011','0 89011',X'03080301bb68'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','24217 1','89824 101727','1 101727',X'03090300da12'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','154 1','114041 114154','2 114154',X'0301030200e5e9'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','198 1','114195 114351','3 114351',X'03010303015cb1'); INSERT INTO sqlite_stat4 VALUES('px','p_pt','50 1','114393 114441','4 114441',X'0301030401b2ef'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','3867 1','3 3736','2 3736',X'03010337015c6a'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','4194 1','4177 8209','5 8209',X'0301033b015075'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','4335 1','8371 11129','6 11129',X'0301033d0156fc'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1740 1','12706 12715','7 12715',X'0301023e34b9'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1680 1','14446 15487','8 15487',X'0301033f011694'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','7163 1','20116 25431','32 25431',X'03020300a400ed26'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1525 1','29100 29302','42 29302',X'03020200bb00d1'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','3703 1','30655 33323','45 33323',X'03020300be013fa5'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2612 1','37767 38147','61 38147',X'03020200e32828'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1882 1','40545 41584','63 41584',X'03020300ea01a35a'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','6984 1','44110 50863','73 50863',X'0302030102017467'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1728 1','51230 51680','75 51680',X'030203010400b3e0'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2805 1','55491 57936','95 57936',X'030203014101a004'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2837 1','58934 59506','103 59506',X'030203015900a283'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','94 1','63492 63579','137 63579',X'0302030191016319'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','3573 1','63591 64497','140 64497',X'030203019c00822e'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','5037 1','70917 73033','160 73033',X'03020301c70091d9'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1940 1','75954 76295','161 76295',X'03020201c817f1'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1927 1','83926 84371','209 84371',X'03020202114295'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1522 1','86601 88117','213 88117',X'030203021b01b7b5'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','210 1','88906 89011','226 89011',X'030203022800dbbb'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','6165 1','92125 98066','258 98066',X'030203024d0189ac'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','2900 1','100721 101727','293 101727',X'030203027500cf39'); INSERT INTO sqlite_stat4 VALUES('px','p_cid0','1501 1','110012 110154','503 110154',X'0302020380493a'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','11129 11129','11129 11129',X'03030300d84e014d51'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','12715 12715','12715 12715',X'03030200de816f51'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','13030 13030','13030 13030',X'03030200e05b6fc4'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','25431 25431','25431 25431',X'0303030123df00efb0'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','29302 29302','29302 29302',X'030302013a2812c7'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','35463 35463','35463 35463',X'03030301666e00f866'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','38147 38147','38147 38147',X'030302017a391b74'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','38525 38525','38525 38525',X'030303017c6e00fb58'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','50863 50863','50863 50863',X'03030201b68724dd'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','58461 58461','58461 58461',X'03030201d95b2e1e'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','59506 59506','59506 59506',X'03030301dd7000a0fb'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','63468 63468','63468 63468',X'03030301ecea011405'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','63579 63579','63579 63579',X'03030201ed5932d5'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','64497 64497','64497 64497',X'03030301f0ef00a680'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','73033 73033','73033 73033',X'0303030225b90190e5'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','75650 75650','75650 75650',X'030303023a19019362'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','76295 76295','76295 76295',X'030303023e9801940c'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','79152 79152','79152 79152',X'030303024be50196b9'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','83249 83249','83249 83249',X'0303030261750123b1'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','89011 89011','89011 89011',X'030303027b3900c3af'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','98066 98066','98066 98066',X'03030302a76500ce54'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','101590 101590','101590 101590',X'03030302b63d00d3b5'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','101727 101727','101727 101727',X'03030202b6f24e9b'); INSERT INTO sqlite_stat4 VALUES('px','p_id','1 1','107960 107960','107960 107960',X'03030302d8ce0136ad'); ANALYZE sqlite_master; } {} # The following query should do a ful ltable scan of cx in the outer loop. # It is not correct to search table px using indx p_pt in the outer loop # with cx in the middle loop. Test case from Bloomberg on 2014-09-05. # do_execsql_test 4.2 { EXPLAIN QUERY PLAN SELECT px.name, px.description FROM le, cx, px WHERE cx.code = '2990' AND cx.type=2 AND px.cx_id = cx.cx_id AND px.px_tid = 0 AND px.le_id = le.le_id; } {/.*SCAN TABLE cx.*SEARCH TABLE px.*SEARCH TABLE le.*/} finish_test |