Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch index-scan-rate Excluding Merge-Ins
This is equivalent to a diff from 087af29e to de78250a
2013-10-06
| ||
22:52 | Remove an incorrect debugging assert() that was accidently added during the STAT4 enhancement. (check-in: 2bb7f74b user: drh tags: trunk) | |
2013-10-05
| ||
18:16 | Begin an experimental refactoring to estimate the average number of bytes in table and index rows and to use that information in query planner. Begin by renaming WhereCost to LogEst and making that type and its conversion routines available outside of where.c. (check-in: 66c4a251 user: drh tags: row-size-est) | |
02:56 | In the index_list pragma, make sure the "r" column is the same on output as it was on input in the sqlite_stat1 table. (Closed-Leaf check-in: de78250a user: drh tags: index-scan-rate) | |
2013-10-04
| ||
20:39 | Merge trunk changes. (check-in: c6ac80ed user: drh tags: index-scan-rate) | |
18:17 | If an "INSERT INTO ... SELECT" can use the xfer optimization, pass the OPFLAG_BULKCSR hint to btree cursors used to update indices. This results in a tighter key packing. (check-in: 087af29e user: dan tags: trunk) | |
00:00 | Make sure the count(*) optimization works correctly even when partial indices are present. Ticket [a5c8ed66cae]. (check-in: 9f2f4c0a user: drh tags: trunk) | |
Changes to src/analyze.c.
︙ | |||
1246 1247 1248 1249 1250 1251 1252 | 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 | - - - - + + + + - - + - - + + + + + + + + + + + + + + | /* ** The first argument points to a nul-terminated string containing a ** list of space separated integers. Read the first nOut of these into ** the array aOut[]. */ static void decodeIntArray( |
︙ | |||
1312 1313 1314 1315 1316 1317 1318 | 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 | - - + - | pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); }else{ pIndex = 0; } z = argv[2]; if( pIndex ){ |
︙ |
Changes to src/build.c.
︙ | |||
1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 | 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 | + | pCol->zName = z; /* If there is no type specified, columns have the default affinity ** 'NONE'. If there is a type specified, then sqlite3AddColumnType() will ** be called next to set pCol->affinity correctly. */ pCol->affinity = SQLITE_AFF_NONE; pCol->szEst = 1; p->nCol++; } /* ** This routine is called by the parser while in the middle of ** parsing a CREATE TABLE statement. A "NOT NULL" constraint has ** been seen on a column. This routine sets the notNull flag on |
︙ | |||
1063 1064 1065 1066 1067 1068 1069 | 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 | - + + + - + - + + + + + + + + + + + + + + + + + + + + + + + + + | ** 'REAL' | SQLITE_AFF_REAL ** 'FLOA' | SQLITE_AFF_REAL ** 'DOUB' | SQLITE_AFF_REAL ** ** If none of the substrings in the above table are found, ** SQLITE_AFF_NUMERIC is returned. */ |
︙ | |||
1117 1118 1119 1120 1121 1122 1123 | 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 | - + | Column *pCol; p = pParse->pNewTable; if( p==0 || NEVER(p->nCol<1) ) return; pCol = &p->aCol[p->nCol-1]; assert( pCol->zType==0 ); pCol->zType = sqlite3NameFromToken(pParse->db, pType); |
︙ | |||
1465 1466 1467 1468 1469 1470 1471 | 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 | - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | testcase( pCol->affinity==SQLITE_AFF_NUMERIC ); testcase( pCol->affinity==SQLITE_AFF_INTEGER ); testcase( pCol->affinity==SQLITE_AFF_REAL ); zType = azType[pCol->affinity - SQLITE_AFF_TEXT]; len = sqlite3Strlen30(zType); assert( pCol->affinity==SQLITE_AFF_NONE |
︙ | |||
1500 1501 1502 1503 1504 1505 1506 | 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 | - - - + + + + + + + + + + + | */ void sqlite3EndTable( Parse *pParse, /* Parse context */ Token *pCons, /* The ',' token after the last column defn. */ Token *pEnd, /* The final ')' token in the CREATE TABLE */ Select *pSelect /* Select from a "CREATE ... AS SELECT" */ ){ |
︙ | |||
2480 2481 2482 2483 2484 2485 2486 | 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 | + - - - + + + | DbFixer sFix; /* For assigning database names to pTable */ int sortOrderMask; /* 1 to honor DESC in index. 0 to ignore. */ sqlite3 *db = pParse->db; Db *pDb; /* The specific table containing the indexed database */ int iDb; /* Index of the database that is being written */ Token *pName = 0; /* Unqualified name of the index to create */ struct ExprList_item *pListItem; /* For looping over pList */ const Column *pTabCol; /* A column in the table */ |
︙ | |||
2709 2710 2711 2712 2713 2714 2715 | 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 | - | ** more than once within the same index. Only the first instance of ** the column will ever be used by the optimizer. Note that using the ** same column more than once cannot be an error because that would ** break backwards compatibility - it needs to be a warning. */ for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){ const char *zColName = pListItem->zName; |
︙ | |||
2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 | 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 | + + + | } pIndex->azColl[i] = zColl; requestedSortOrder = pListItem->sortOrder & sortOrderMask; pIndex->aSortOrder[i] = (u8)requestedSortOrder; if( pTab->aCol[j].notNull==0 ) pIndex->uniqNotNull = 0; } sqlite3DefaultRowEst(pIndex); if( pParse->pNewTable==0 ){ setIndexScanRatio(pIndex, estimatedTableWidth(pTab)); } if( pTab==pParse->pNewTable ){ /* This routine has been called to create an automatic index as a ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or ** a PRIMARY KEY or UNIQUE clause following the column definitions. ** i.e. one of: ** |
︙ |
Changes to src/expr.c.
︙ | |||
37 38 39 40 41 42 43 | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | - + | if( op==TK_SELECT ){ assert( pExpr->flags&EP_xIsSelect ); return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr); } #ifndef SQLITE_OMIT_CAST if( op==TK_CAST ){ assert( !ExprHasProperty(pExpr, EP_IntValue) ); |
︙ | |||
2457 2458 2459 2460 2461 2462 2463 | 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 | - + | } #ifndef SQLITE_OMIT_CAST case TK_CAST: { /* Expressions of the form: CAST(pLeft AS token) */ int aff, to_op; inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target); assert( !ExprHasProperty(pExpr, EP_IntValue) ); |
︙ | |||
3124 3125 3126 3127 3128 3129 3130 | 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 | - + | sqlite3ExplainExpr(pOut, pExpr->pLeft); break; } #ifndef SQLITE_OMIT_CAST case TK_CAST: { /* Expressions of the form: CAST(pLeft AS token) */ const char *zAff = "unk"; |
︙ |
Changes to src/pragma.c.
︙ | |||
1451 1452 1453 1454 1455 1456 1457 | 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 | - - + + + + - + | Table *pTab; pTab = sqlite3FindTable(db, zRight, zDb); if( pTab ){ v = sqlite3GetVdbe(pParse); pIdx = pTab->pIndex; if( pIdx ){ int i = 0; |
︙ |
Changes to src/select.c.
︙ | |||
4593 4594 4595 4596 4597 4598 4599 | 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 4613 4614 4615 4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 | - + - - - - - - + - + - + | KeyInfo *pKeyInfo = 0; /* Keyinfo for scanned index */ Index *pBest = 0; /* Best index found so far */ int iRoot = pTab->tnum; /* Root page of scanned b-tree */ sqlite3CodeVerifySchema(pParse, iDb); sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
1193 1194 1195 1196 1197 1198 1199 | 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 | + - + | char *zName; /* Name of this column */ Expr *pDflt; /* Default value of this column */ char *zDflt; /* Original text of the default value */ char *zType; /* Data type for this column */ char *zColl; /* Collating sequence. If NULL, use the default */ u8 notNull; /* An OE_ code for handling a NOT NULL constraint */ char affinity; /* One of the SQLITE_AFF_... values */ u8 szEst; /* Estimated size of this column. INT==1 */ |
︙ | |||
1468 1469 1470 1471 1472 1473 1474 | 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 | - + | #define OE_Replace 5 /* Delete existing record, then do INSERT or UPDATE */ #define OE_Restrict 6 /* OE_Abort for IMMEDIATE, OE_Rollback for DEFERRED */ #define OE_SetNull 7 /* Set the foreign key value to NULL */ #define OE_SetDflt 8 /* Set the foreign key value to its default */ #define OE_Cascade 9 /* Cascade the changes */ |
︙ | |||
1556 1557 1558 1559 1560 1561 1562 | 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 | + - + | Index *pNext; /* The next index associated with the same table */ Schema *pSchema; /* Schema containing this index */ u8 *aSortOrder; /* for each column: True==DESC, False==ASC */ char **azColl; /* Array of collation sequence names for index */ Expr *pPartIdxWhere; /* WHERE clause for partial indices */ int tnum; /* DB Page containing root of this index */ u16 nColumn; /* Number of columns in table used by this index */ u8 iScanRatio; /* Scan rate relative to the main table */ |
︙ | |||
3077 3078 3079 3080 3081 3082 3083 | 3079 3080 3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 | - + | void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*); void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*); int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*); void sqlite3ColumnDefault(Vdbe *, Table *, int, int); void sqlite3AlterFinishAddColumn(Parse *, Token *); void sqlite3AlterBeginAddColumn(Parse *, SrcList *); CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*); |
︙ |
Changes to src/where.c.
︙ | |||
4665 4666 4667 4668 4669 4670 4671 | 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 | - - + + - - + - - + + + - - - + + - - - - + | if( pProbe->tnum<=0 ){ /* Integer primary key index */ pNew->wsFlags = WHERE_IPK; /* Full table scan */ pNew->iSortIdx = b ? iSortIdx : 0; /* TUNING: Cost of full table scan is 3*(N + log2(N)). |
︙ |
Changes to test/analyze6.test.
︙ | |||
26 27 28 29 30 31 32 | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | - + - - - - - + + + + + | proc eqp {sql {db db}} { uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db } do_test analyze6-1.0 { db eval { |
︙ |
Changes to test/eqp.test.
︙ | |||
29 30 31 32 33 34 35 | 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | - + - - + + | # ... # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). # proc det {args} { uplevel do_eqp_test $args } do_execsql_test 1.1 { |
︙ | |||
118 119 120 121 122 123 124 | 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | - + - + | } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { |
︙ | |||
370 371 372 373 374 375 376 | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | - + | # drop_all_tables # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b # FROM t1 WHERE a=1; # 0|0|0|SCAN TABLE t1 # |
︙ | |||
398 399 400 401 402 403 404 | 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 | - - + + - + - + | } # EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|1|SCAN TABLE t2 # |
︙ | |||
481 482 483 484 485 486 487 | 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 | - + - + - + | } # EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) # 0|1|1|SCAN TABLE t1 # |
︙ | |||
544 545 546 547 548 549 550 | 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 | - + - - + + - - + + - - - + + + | set data [read $fd] close $fd set data }] [list $res] } do_peqp_test 6.1 { |
︙ |
Changes to test/pragma.test.
︙ | |||
570 571 572 573 574 575 576 | 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 | - + | pragma foreign_key_list(t5); } } {} do_test pragma-6.4 { execsql { pragma index_list(t3); } |
︙ | |||
643 644 645 646 647 648 649 | 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | - + | do_test pragma-7.1.1 { # Make sure a pragma knows to read the schema if it needs to db close sqlite3 db test.db execsql { pragma index_list(t3); } |
︙ | |||
1657 1658 1659 1660 1661 1662 1663 | 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 | - + | db2 eval {PRAGMA index_info(i2)} } {0 2 c 1 3 d 2 1 b} do_test 23.3 { db eval { CREATE INDEX i3 ON t1(d,b,c); } db2 eval {PRAGMA index_list(t1)} |
︙ |
Changes to test/subquery.test.
︙ | |||
241 242 243 244 245 246 247 | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 | - + | } {10.0} do_test subquery-2.5.3.2 { # Verify that the t4i index was not used in the previous query execsql { EXPLAIN QUERY PLAN SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); } |
︙ |
Changes to test/tkt-78e04e52ea.test.
︙ | |||
14 15 16 17 18 19 20 | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | - + - + - + - + - + - + | # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt-78e04-1.0 { execsql { |
︙ |
Changes to test/where.test.
︙ | |||
1121 1122 1123 1124 1125 1126 1127 | 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 | - - - + + + | # # When optimizing out ORDER BY clauses, make sure that trailing terms # of the ORDER BY clause do not reference other tables in a join. # if {[permutation] != "no_optimization"} { do_test where-14.1 { execsql { |
︙ |
Changes to test/where2.test.
︙ | |||
310 311 312 313 314 315 316 | 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 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 | - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + + - + + - + + - + | if {[permutation] != "no_optimization"} { # Ticket #2249. Make sure the OR optimization is not attempted if # comparisons between columns of different affinities are needed. # do_test where2-6.7 { execsql { |
︙ | |||
699 700 701 702 703 704 705 | 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 | - + | } } {4 8 10} # Verify that the OR clause is used in an outer loop even when # the OR clause scores slightly better on an inner loop. if {[permutation] != "no_optimization"} { do_execsql_test where2-12.1 { |