Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch log-stats Excluding Merge-Ins
This is equivalent to a diff from 1d7b2dc0 to 8e78557a
2013-10-07
| ||
17:32 | Multiply all cursor step cost estimates by the estimated size of the row in bytes, in order to get the query planner ot make use of estimated row sizes. This check-in uses magic numbers in a few places (for example, estimates of the size of output rows) and needs lots of refinement. Consider this a proof-of-concept only. (check-in: cb34cfe5 user: drh tags: row-size-est) | |
16:53 | Try to remember statistics from ANALYZE using LogEst instead of u64. (Leaf check-in: 8e78557a user: drh tags: log-stats) | |
10:48 | Merge bug fixes from trunk. (check-in: 1d7b2dc0 user: drh tags: row-size-est) | |
00:36 | Restore the hexrekey pragma which was accidently deleted during the pragma refactoring. Make sure the hexkey and hexrekey pragmas do not overflow buffers with a over-length key. (check-in: 0aca31e1 user: drh tags: trunk) | |
2013-10-06
| ||
22:12 | Accept the sz=N parameter on table-only lines of sqlite_stat1. (check-in: e9e932aa user: drh tags: row-size-est) | |
Changes to src/analyze.c.
︙ | ︙ | |||
1248 1249 1250 1251 1252 1253 1254 | ** 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( char *zIntArray, /* String containing int array to decode */ int nOut, /* Number of slots in aOut[] */ | | | | | 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 | ** 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( char *zIntArray, /* String containing int array to decode */ int nOut, /* Number of slots in aOut[] */ LogEst *aOut, /* Convert values to 10*log2() and store here */ Index *pIndex /* Handle extra flags for this index, if not NULL */ ){ char *z = zIntArray; int c; int i; u64 v; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( z==0 ) z = ""; #else if( NEVER(z==0) ) z = ""; #endif for(i=0; *z && i<nOut; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } aOut[i] = sqlite3LogEst(v); if( *z==' ' ) z++; } if( pIndex ){ if( strcmp(z, "unordered")==0 ){ pIndex->bUnordered = 1; }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){ int v32 = 0; |
︙ | ︙ | |||
1364 1365 1366 1367 1368 1369 1370 | static void initAvgEq(Index *pIdx){ if( pIdx ){ IndexSample *aSample = pIdx->aSample; IndexSample *pFinal = &aSample[pIdx->nSample-1]; int iCol; for(iCol=0; iCol<pIdx->nColumn; iCol++){ int i; /* Used to iterate through samples */ | | | | | | | | | | 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 | static void initAvgEq(Index *pIdx){ if( pIdx ){ IndexSample *aSample = pIdx->aSample; IndexSample *pFinal = &aSample[pIdx->nSample-1]; int iCol; for(iCol=0; iCol<pIdx->nColumn; iCol++){ int i; /* Used to iterate through samples */ u64 sumEq = 0; /* Sum of the nEq values */ u64 nSum = 0; /* Number of terms contributing to sumEq */ u64 avgEq = 0; u64 nDLt = sqlite3LogEstToInt(pFinal->anDLt[iCol]); /* Set nSum to the number of distinct (iCol+1) field prefixes that ** occur in the stat4 table for this index before pFinal. Set ** sumEq to the sum of the nEq values for column iCol for the same ** set (adding the value only once where there exist dupicate ** prefixes). */ for(i=0; i<(pIdx->nSample-1); i++){ if( aSample[i].anDLt[iCol]!=aSample[i+1].anDLt[iCol] ){ sumEq += sqlite3LogEstToInt(aSample[i].anEq[iCol]); nSum++; } } if( nDLt>nSum ){ avgEq = (sqlite3LogEstToInt(pFinal->anLt[iCol]) - sumEq)/(nDLt - nSum); } if( avgEq<=0 ) avgEq = 1; pIdx->aAvgEq[iCol] = sqlite3LogEst(avgEq); if( pIdx->nSampleCol==1 ) break; } } } /* ** Load the content from either the sqlite_stat4 or sqlite_stat3 table |
︙ | ︙ | |||
1434 1435 1436 1437 1438 1439 1440 | int nAvgCol = 1; /* Number of entries in Index.aAvgEq */ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ int nSample; /* Number of samples */ int nByte; /* Bytes of space required */ int i; /* Bytes of space required */ | | | | | | 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 | int nAvgCol = 1; /* Number of entries in Index.aAvgEq */ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ int nSample; /* Number of samples */ int nByte; /* Bytes of space required */ int i; /* Bytes of space required */ LogEst *pSpace; zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; nSample = sqlite3_column_int(pStmt, 1); pIdx = sqlite3FindIndex(db, zIndex, zDb); assert( pIdx==0 || bStat3 || pIdx->nSample==0 ); /* Index.nSample is non-zero at this point if data has already been ** loaded from the stat4 table. In this case ignore stat3 data. */ if( pIdx==0 || pIdx->nSample ) continue; if( bStat3==0 ){ nIdxCol = pIdx->nColumn+1; nAvgCol = pIdx->nColumn; } pIdx->nSampleCol = nIdxCol; nByte = sizeof(IndexSample) * nSample; nByte += sizeof(LogEst) * nIdxCol * 3 * nSample; nByte += nAvgCol * sizeof(LogEst); /* Space for Index.aAvgEq[] */ pIdx->aSample = sqlite3DbMallocZero(db, nByte); if( pIdx->aSample==0 ){ sqlite3_finalize(pStmt); return SQLITE_NOMEM; } pSpace = (LogEst*)&pIdx->aSample[nSample]; pIdx->aAvgEq = pSpace; pSpace += nAvgCol; for(i=0; i<nSample; i++){ pIdx->aSample[i].anEq = pSpace; pSpace += nIdxCol; pIdx->aSample[i].anLt = pSpace; pSpace += nIdxCol; pIdx->aSample[i].anDLt = pSpace; pSpace += nIdxCol; } assert( ((u8*)pSpace)-nByte==(u8*)(pIdx->aSample) ); |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
875 876 877 878 879 880 881 | pParse->nErr++; goto begin_table_error; } pTable->zName = zName; pTable->iPKey = -1; pTable->pSchema = db->aDb[iDb].pSchema; pTable->nRef = 1; | | | 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 | pParse->nErr++; goto begin_table_error; } pTable->zName = zName; pTable->iPKey = -1; pTable->pSchema = db->aDb[iDb].pSchema; pTable->nRef = 1; pTable->nRowEst = 200; assert( pParse->pNewTable==0 ); pParse->pNewTable = pTable; /* If this is the magic sqlite_sequence table used by autoincrement, ** then record a pointer to this table in the main database structure ** so that INSERT can find the table easily. */ |
︙ | ︙ | |||
2718 2719 2720 2721 2722 2723 2724 | /* ** Allocate the index structure. */ nName = sqlite3Strlen30(zName); nCol = pList->nExpr; pIndex = sqlite3DbMallocZero(db, ROUND8(sizeof(Index)) + /* Index structure */ | | | | | 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 | /* ** Allocate the index structure. */ nName = sqlite3Strlen30(zName); nCol = pList->nExpr; pIndex = sqlite3DbMallocZero(db, ROUND8(sizeof(Index)) + /* Index structure */ ROUND8(sizeof(LogEst)*(nCol+1)) + /* Index.aiRowEst */ sizeof(char *)*nCol + /* Index.azColl */ sizeof(int)*nCol + /* Index.aiColumn */ sizeof(u8)*nCol + /* Index.aSortOrder */ nName + 1 + /* Index.zName */ nExtra /* Collation sequence names */ ); if( db->mallocFailed ){ goto exit_create_index; } zExtra = (char*)pIndex; pIndex->aiRowEst = (LogEst*)&zExtra[ROUND8(sizeof(Index))]; pIndex->azColl = (char**) ((char*)pIndex->aiRowEst + ROUND8(sizeof(LogEst)*nCol+1)); assert( EIGHT_BYTE_ALIGNMENT(pIndex->aiRowEst) ); assert( EIGHT_BYTE_ALIGNMENT(pIndex->azColl) ); pIndex->aiColumn = (int *)(&pIndex->azColl[nCol]); pIndex->aSortOrder = (u8 *)(&pIndex->aiColumn[nCol]); pIndex->zName = (char *)(&pIndex->aSortOrder[nCol]); zExtra = (char *)(&pIndex->zName[nName+1]); memcpy(pIndex->zName, zName, nName+1); |
︙ | ︙ | |||
3010 3011 3012 3013 3014 3015 3016 | ** aiRowEst[N]>=1 ** ** Apart from that, we have little to go on besides intuition as to ** how aiRowEst[] should be initialized. The numbers generated here ** are based on typical values found in actual indices. */ void sqlite3DefaultRowEst(Index *pIdx){ | | | > | | | | | 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 | ** aiRowEst[N]>=1 ** ** Apart from that, we have little to go on besides intuition as to ** how aiRowEst[] should be initialized. The numbers generated here ** are based on typical values found in actual indices. */ void sqlite3DefaultRowEst(Index *pIdx){ LogEst *a = pIdx->aiRowEst; int i; LogEst n; assert( a!=0 ); a[0] = pIdx->pTable->nRowEst; assert( 33==sqlite3LogEst(10) ); if( a[0]<34 ){ a[0] = 34; } n = 34; for(i=1; i<=pIdx->nColumn; i++){ a[i] = n; if( n>24 ) n -= 2; } if( pIdx->onError!=OE_None ){ a[pIdx->nColumn] = 0; } } /* ** This routine will drop an existing named index. This routine ** implements the DROP INDEX statement. */ |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
1476 1477 1478 1479 1480 1481 1482 | return 0; } /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside ** is disabled */ assert( db->lookaside.bEnabled==0 ); pTab->nRef = 1; pTab->zName = 0; | | | 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 | return 0; } /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside ** is disabled */ assert( db->lookaside.bEnabled==0 ); pTab->nRef = 1; pTab->zName = 0; pTab->nRowEst = 200; selectColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol); selectAddColumnTypeAndCollation(pParse, pTab->nCol, pTab->aCol, pSelect); pTab->iPKey = -1; if( db->mallocFailed ){ sqlite3DeleteTable(db, pTab); return 0; } |
︙ | ︙ | |||
3394 3395 3396 3397 3398 3399 3400 | pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return WRC_Abort; pTab->nRef = 1; pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab); while( pSel->pPrior ){ pSel = pSel->pPrior; } selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol); pTab->iPKey = -1; | | | 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 | pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return WRC_Abort; pTab->nRef = 1; pTab->zName = sqlite3MPrintf(db, "sqlite_subquery_%p_", (void*)pTab); while( pSel->pPrior ){ pSel = pSel->pPrior; } selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol); pTab->iPKey = -1; pTab->nRowEst = 200; pTab->tabFlags |= TF_Ephemeral; #endif }else{ /* An ordinary table or view name in the FROM clause */ assert( pFrom->pTab==0 ); pFrom->pTab = pTab = sqlite3LocateTableItem(pParse, 0, pFrom); if( pTab==0 ) return WRC_Abort; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1378 1379 1380 1381 1382 1383 1384 | Index *pIndex; /* List of SQL indexes on this table. */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ #ifndef SQLITE_OMIT_CHECK ExprList *pCheck; /* All CHECK constraints */ #endif | < > | 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 | Index *pIndex; /* List of SQL indexes on this table. */ Select *pSelect; /* NULL for tables. Points to definition if a view. */ FKey *pFKey; /* Linked list of all foreign keys in this table */ char *zColAff; /* String defining the affinity of each column */ #ifndef SQLITE_OMIT_CHECK ExprList *pCheck; /* All CHECK constraints */ #endif int tnum; /* Root BTree node for this table (see note above) */ i16 iPKey; /* If not negative, use aCol[iPKey] as the primary key */ i16 nCol; /* Number of columns in this table */ u16 nRef; /* Number of pointers to this Table */ LogEst nRowEst; /* Estimated number of rows in the table */ LogEst szTabRow; /* Estimated size of each table row in bytes */ u8 tabFlags; /* Mask of TF_* values */ u8 keyConf; /* What to do in case of uniqueness conflict on iPKey */ #ifndef SQLITE_OMIT_ALTERTABLE int addColOffset; /* Offset in CREATE TABLE stmt to add a new column */ #endif #ifndef SQLITE_OMIT_VIRTUALTABLE |
︙ | ︙ | |||
1573 1574 1575 1576 1577 1578 1579 | ** and the value of Index.onError indicate the which conflict resolution ** algorithm to employ whenever an attempt is made to insert a non-unique ** element. */ struct Index { char *zName; /* Name of this index */ int *aiColumn; /* Which columns are used by this index. 1st is 0 */ | | | | | | | 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 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 | ** and the value of Index.onError indicate the which conflict resolution ** algorithm to employ whenever an attempt is made to insert a non-unique ** element. */ struct Index { char *zName; /* Name of this index */ int *aiColumn; /* Which columns are used by this index. 1st is 0 */ LogEst *aiRowEst; /* From ANALYZE: Est. rows selected by each column */ Table *pTable; /* The SQL table being indexed */ char *zColAff; /* String defining the affinity of each column */ 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 */ LogEst szIdxRow; /* Estimated average row size in bytes */ u16 nColumn; /* Number of columns in table used by this index */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned autoIndex:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ LogEst *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ #endif }; /* ** Each sample stored in the sqlite_stat3 table is represented in memory ** using a structure of this type. See documentation at the top of the ** analyze.c source file for additional information. */ struct IndexSample { void *p; /* Pointer to sampled record */ int n; /* Size of record in bytes */ LogEst *anEq; /* Est. number of rows where the key equals this sample */ LogEst *anLt; /* Est. number of rows where key is less than this sample */ LogEst *anDLt; /* Est. number of distinct keys less than this sample */ }; /* ** Each token coming out of the lexer is an instance of ** this structure. Tokens are also used as part of an expression. ** ** Note if Token.z==0 then Token.dyn and Token.n are undefined and |
︙ | ︙ |
Changes to src/util.c.
︙ | ︙ | |||
1209 1210 1211 1212 1213 1214 1215 | } #endif /* ** Find (an approximate) sum of two LogEst values. This computation is ** not a simple "+" operator because LogEst is stored as a logarithmic ** value. | < | 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 | } #endif /* ** Find (an approximate) sum of two LogEst values. This computation is ** not a simple "+" operator because LogEst is stored as a logarithmic ** value. */ LogEst sqlite3LogEstAdd(LogEst a, LogEst b){ static const unsigned char x[] = { 10, 10, /* 0,1 */ 9, 9, /* 2,3 */ 8, 8, /* 4,5 */ 7, 7, 7, /* 6,7,8 */ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2324 2325 2326 2327 2328 2329 2330 | ** Return SQLITE_OK on success. */ static void whereKeyStats( Parse *pParse, /* Database connection */ Index *pIdx, /* Index to consider domain of */ UnpackedRecord *pRec, /* Vector of values to consider */ int roundUp, /* Round up if true. Round down if false */ | | | 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 | ** Return SQLITE_OK on success. */ static void whereKeyStats( Parse *pParse, /* Database connection */ Index *pIdx, /* Index to consider domain of */ UnpackedRecord *pRec, /* Vector of values to consider */ int roundUp, /* Round up if true. Round down if false */ LogEst *aStat /* OUT: stats written here */ ){ IndexSample *aSample = pIdx->aSample; int iCol; /* Index of required stats in anEq[] etc. */ int iMin = 0; /* Smallest sample not yet tested */ int i = pIdx->nSample; /* Smallest sample larger than or equal to pRec */ int iTest; /* Next sample to test */ int res; /* Result of comparison operation */ |
︙ | ︙ | |||
2377 2378 2379 2380 2381 2382 2383 | ** or equal to pVal. Or if i==pIdx->nSample, then all samples are less ** than pVal. If aSample[i]==pVal, then res==0. */ if( res==0 ){ aStat[0] = aSample[i].anLt[iCol]; aStat[1] = aSample[i].anEq[iCol]; }else{ | | > | | | 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 | ** or equal to pVal. Or if i==pIdx->nSample, then all samples are less ** than pVal. If aSample[i]==pVal, then res==0. */ if( res==0 ){ aStat[0] = aSample[i].anLt[iCol]; aStat[1] = aSample[i].anEq[iCol]; }else{ LogEst iLower, iUpper, iGap; if( i==0 ){ iLower = 0; iUpper = aSample[0].anLt[iCol]; }else{ iUpper = i>=pIdx->nSample ? pIdx->aiRowEst[0] : aSample[i].anLt[iCol]; iLower = sqlite3LogEstAdd(aSample[i-1].anEq[iCol], aSample[i-1].anLt[iCol]); } aStat[1] = (pIdx->nColumn>iCol ? pIdx->aAvgEq[iCol] : 0); if( iLower>=iUpper ){ iGap = 0; }else{ iGap = iUpper - iLower; } if( roundUp ){ iGap = (iGap*2)/3; |
︙ | ︙ | |||
2462 2463 2464 2465 2466 2467 2468 | if( p->nSample>0 && nEq==pBuilder->nRecValid && nEq<p->nSampleCol && OptimizationEnabled(pParse->db, SQLITE_Stat3) ){ UnpackedRecord *pRec = pBuilder->pRec; | | | | | | | | | | | < | < | > > | | 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 | if( p->nSample>0 && nEq==pBuilder->nRecValid && nEq<p->nSampleCol && OptimizationEnabled(pParse->db, SQLITE_Stat3) ){ UnpackedRecord *pRec = pBuilder->pRec; LogEst a[2]; u8 aff; /* Variable iLower will be set to the estimate of the number of rows in ** the index that are less than the lower bound of the range query. The ** lower bound being the concatenation of $P and $L, where $P is the ** key-prefix formed by the nEq values matched against the nEq left-most ** columns of the index, and $L is the value in pLower. ** ** Or, if pLower is NULL or $L cannot be extracted from it (because it ** is not a simple variable or literal value), the lower bound of the ** range is $P. Due to a quirk in the way whereKeyStats() works, even ** if $L is available, whereKeyStats() is called for both ($P) and ** ($P:$L) and the larger of the two returned values used. ** ** Similarly, iUpper is to be set to the estimate of the number of rows ** less than the upper bound of the range query. Where the upper bound ** is either ($P) or ($P:$U). Again, even if $U is available, both values ** of iUpper are requested of whereKeyStats() and the smaller used. */ LogEst iLower; LogEst iUpper; if( nEq==p->nColumn ){ aff = SQLITE_AFF_INTEGER; }else{ aff = p->pTable->aCol[p->aiColumn[nEq]].affinity; } /* Determine iLower and iUpper using ($P) only. */ if( nEq==0 ){ iLower = 0; iUpper = p->aiRowEst[0]; }else{ /* Note: this call could be optimized away - since the same values must ** have been requested when testing key $P in whereEqualScanEst(). */ whereKeyStats(pParse, p, pRec, 0, a); iLower = a[0]; iUpper = sqlite3LogEstAdd(a[0],a[1]); } /* If possible, improve on the iLower estimate using ($P:$L). */ if( pLower ){ int bOk; /* True if value is extracted from pExpr */ Expr *pExpr = pLower->pExpr->pRight; assert( (pLower->eOperator & (WO_GT|WO_GE))!=0 ); rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); if( rc==SQLITE_OK && bOk ){ LogEst iNew; whereKeyStats(pParse, p, pRec, 0, a); iNew = sqlite3LogEstAdd(a[0],((pLower->eOperator & WO_GT) ? a[1] : 0)); if( iNew>iLower ) iLower = iNew; nOut--; } } /* If possible, improve on the iUpper estimate using ($P:$U). */ if( pUpper ){ int bOk; /* True if value is extracted from pExpr */ Expr *pExpr = pUpper->pExpr->pRight; assert( (pUpper->eOperator & (WO_LT|WO_LE))!=0 ); rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk); if( rc==SQLITE_OK && bOk ){ LogEst iNew; whereKeyStats(pParse, p, pRec, 1, a); iNew = sqlite3LogEstAdd(a[0],((pUpper->eOperator & WO_LE) ? a[1] : 0)); if( iNew<iUpper ) iUpper = iNew; nOut--; } } pBuilder->pRec = pRec; if( rc==SQLITE_OK ){ if( iUpper<=iLower ) iUpper = iLower+1; nNew = sqlite3LogEst(sqlite3LogEstToInt(iUpper) - sqlite3LogEstToInt(iLower)); if( nNew<nOut ){ nOut = nNew; } *pnOut = (LogEst)nOut; WHERETRACE(0x100, ("range scan regions: %llu..%llu est=%d\n", sqlite3LogEstToInt(iLower), sqlite3LogEstToInt(iUpper), nOut)); return SQLITE_OK; } } #else UNUSED_PARAMETER(pParse); UNUSED_PARAMETER(pBuilder); #endif |
︙ | ︙ | |||
2592 2593 2594 2595 2596 2597 2598 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereEqualScanEst( Parse *pParse, /* Parsing & code generating context */ WhereLoopBuilder *pBuilder, Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ | | | | 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereEqualScanEst( Parse *pParse, /* Parsing & code generating context */ WhereLoopBuilder *pBuilder, Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */ LogEst *pnRow /* Write the revised row estimate here */ ){ Index *p = pBuilder->pNew->u.btree.pIndex; int nEq = pBuilder->pNew->u.btree.nEq; UnpackedRecord *pRec = pBuilder->pRec; u8 aff; /* Column affinity */ int rc; /* Subfunction return code */ LogEst a[2]; /* Statistics */ int bOk; assert( nEq>=1 ); assert( nEq<=(p->nColumn+1) ); assert( p->aSample!=0 ); assert( p->nSample>0 ); assert( pBuilder->nRecValid<nEq ); |
︙ | ︙ | |||
2629 2630 2631 2632 2633 2634 2635 | rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk); pBuilder->pRec = pRec; if( rc!=SQLITE_OK ) return rc; if( bOk==0 ) return SQLITE_NOTFOUND; pBuilder->nRecValid = nEq; whereKeyStats(pParse, p, pRec, 0, a); | | > | 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 | rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk); pBuilder->pRec = pRec; if( rc!=SQLITE_OK ) return rc; if( bOk==0 ) return SQLITE_NOTFOUND; pBuilder->nRecValid = nEq; whereKeyStats(pParse, p, pRec, 0, a); WHERETRACE(0x100,("equality scan regions: %d (%llu)\n", a[1], sqlite3LogEstToInt(a[1]))); *pnRow = a[1]; return rc; } #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 |
︙ | ︙ | |||
2657 2658 2659 2660 2661 2662 2663 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ WhereLoopBuilder *pBuilder, ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ | | | | | > | | | | 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 | ** for a UTF conversion required for comparison. The error is stored ** in the pParse structure. */ static int whereInScanEst( Parse *pParse, /* Parsing & code generating context */ WhereLoopBuilder *pBuilder, ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */ LogEst *pnRow /* Write the revised row estimate here */ ){ Index *p = pBuilder->pNew->u.btree.pIndex; int nRecValid = pBuilder->nRecValid; int rc = SQLITE_OK; /* Subfunction return code */ LogEst nEst; /* Number of rows for a single term */ u64 nRowEst = 0; /* New estimate of the number of rows */ int i; /* Loop counter */ assert( p->aSample!=0 ); for(i=0; rc==SQLITE_OK && i<pList->nExpr; i++){ nEst = p->aiRowEst[0]; rc = whereEqualScanEst(pParse, pBuilder, pList->a[i].pExpr, &nEst); nRowEst += sqlite3LogEstToInt(nEst); pBuilder->nRecValid = nRecValid; } if( rc==SQLITE_OK ){ nEst = sqlite3LogEst(nRowEst); if( nEst > p->aiRowEst[0] ) nEst = p->aiRowEst[0]; *pnRow = nEst; WHERETRACE(0x100,("IN row estimate: est=%d (%llu)\n", nEst, nRowEst)); } assert( pBuilder->nRecValid==nRecValid ); return rc; } #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ /* |
︙ | ︙ | |||
4247 4248 4249 4250 4251 4252 4253 | opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEq<=pProbe->nColumn ); if( pNew->u.btree.nEq < pProbe->nColumn ){ iCol = pProbe->aiColumn[pNew->u.btree.nEq]; | | | | | 4250 4251 4252 4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 4263 4264 4265 4266 4267 4268 4269 4270 4271 4272 4273 4274 4275 4276 4277 4278 | opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEq<=pProbe->nColumn ); if( pNew->u.btree.nEq < pProbe->nColumn ){ iCol = pProbe->aiColumn[pNew->u.btree.nEq]; nRowEst = pProbe->aiRowEst[pNew->u.btree.nEq+1]; if( nRowEst<10 && pProbe->onError==OE_None ) nRowEst = 10; }else{ iCol = -1; nRowEst = 0; } pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, opMask, pProbe); saved_nEq = pNew->u.btree.nEq; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; pNew->rSetup = 0; rLogSize = estLog(pProbe->aiRowEst[0]); for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ int nIn = 0; #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nRecValid = pBuilder->nRecValid; #endif if( (pTerm->eOperator==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) && (iCol<0 || pSrc->pTab->aCol[iCol].notNull) |
︙ | ︙ | |||
4342 4343 4344 4345 4346 4347 4348 | #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( nInMul==0 && pProbe->nSample && pNew->u.btree.nEq<=pProbe->nSampleCol && OptimizationEnabled(db, SQLITE_Stat3) ){ Expr *pExpr = pTerm->pExpr; | | | | < < < | 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 | #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 if( nInMul==0 && pProbe->nSample && pNew->u.btree.nEq<=pProbe->nSampleCol && OptimizationEnabled(db, SQLITE_Stat3) ){ Expr *pExpr = pTerm->pExpr; LogEst nOut = pNew->nOut; if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){ testcase( pTerm->eOperator & WO_EQ ); testcase( pTerm->eOperator & WO_ISNULL ); rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut); }else if( (pTerm->eOperator & WO_IN) && !ExprHasProperty(pExpr, EP_xIsSelect) ){ rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut); } assert( nOut==pNew->nOut || rc==SQLITE_OK ); if( nOut<pNew->nOut ) pNew->nOut = nOut; } #endif if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){ /* Each row involves a step of the index, then a binary search of ** the main table */ pNew->rRun = sqlite3LogEstAdd(pNew->rRun,rLogSize>27 ? rLogSize-17 : 10); } |
︙ | ︙ | |||
4456 4457 4458 4459 4460 4461 4462 | static int whereLoopAddBtree( WhereLoopBuilder *pBuilder, /* WHERE clause information */ Bitmask mExtra /* Extra prerequesites for using this table */ ){ WhereInfo *pWInfo; /* WHERE analysis context */ Index *pProbe; /* An index we are evaluating */ Index sPk; /* A fake index object for the primary key */ | | | 4456 4457 4458 4459 4460 4461 4462 4463 4464 4465 4466 4467 4468 4469 4470 | static int whereLoopAddBtree( WhereLoopBuilder *pBuilder, /* WHERE clause information */ Bitmask mExtra /* Extra prerequesites for using this table */ ){ WhereInfo *pWInfo; /* WHERE analysis context */ Index *pProbe; /* An index we are evaluating */ Index sPk; /* A fake index object for the primary key */ LogEst aiRowEstPk[2]; /* The aiRowEst[] value for the sPk index */ int aiColumnPk = -1; /* The aColumn[] value for the sPk index */ SrcList *pTabList; /* The FROM clause */ struct SrcList_item *pSrc; /* The FROM clause btree term to add */ WhereLoop *pNew; /* Template WhereLoop object */ int rc = SQLITE_OK; /* Return code */ int iSortIdx = 1; /* Index number */ int b; /* A boolean value */ |
︙ | ︙ | |||
4491 4492 4493 4494 4495 4496 4497 | memset(&sPk, 0, sizeof(Index)); sPk.nColumn = 1; sPk.aiColumn = &aiColumnPk; sPk.aiRowEst = aiRowEstPk; sPk.onError = OE_Replace; sPk.pTable = pSrc->pTab; aiRowEstPk[0] = pSrc->pTab->nRowEst; | | | | 4491 4492 4493 4494 4495 4496 4497 4498 4499 4500 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 4514 | memset(&sPk, 0, sizeof(Index)); sPk.nColumn = 1; sPk.aiColumn = &aiColumnPk; sPk.aiRowEst = aiRowEstPk; sPk.onError = OE_Replace; sPk.pTable = pSrc->pTab; aiRowEstPk[0] = pSrc->pTab->nRowEst; aiRowEstPk[1] = 0; pFirst = pSrc->pTab->pIndex; if( pSrc->notIndexed==0 ){ /* The real indices of the table are only considered if the ** NOT INDEXED qualifier is omitted from the FROM clause */ sPk.pNext = pFirst; } pProbe = &sPk; } rSize = pSrc->pTab->nRowEst; rLogSize = estLog(rSize); #ifndef SQLITE_OMIT_AUTOMATIC_INDEX /* Automatic indexes */ if( !pBuilder->pOrSet && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0 && pSrc->pIndex==0 |
︙ | ︙ |