Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the plan solver to take into account the number of output rows when computing the set of paths to retain for the next cycle. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | unlikely-func |
| Files: | files | file ages | folders |
| SHA1: |
1a46a7242313da96420985fa52e1de3f |
| User & Date: | drh 2013-09-06 17:45:42 |
Context
|
2013-09-07
| ||
| 00:29 | Continuing refinements of the logic to take WHERE clause terms not used for indexing into account when computing the number of output rows from each table. check-in: b65dc534 user: drh tags: unlikely-func | |
|
2013-09-06
| ||
| 17:45 | Enhance the plan solver to take into account the number of output rows when computing the set of paths to retain for the next cycle. check-in: 1a46a724 user: drh tags: unlikely-func | |
| 15:23 | Initial implementation of the unlikely() SQL function used as a hint to the query planner. check-in: 036fc37a user: drh tags: unlikely-func | |
Changes
Changes to src/resolve.c.
701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 |
pExpr->iTable = exprProbability(pList->a[1].pExpr);
if( pExpr->iTable<0 ){
sqlite3ErrorMsg(pParse, "second parameter to unlikely() must be "
"between 0.0 and 1.0");
pNC->nErr++;
}
}else{
pExpr->iTable = 100;
}
}
}
#ifndef SQLITE_OMIT_AUTHORIZATION
if( pDef ){
auth = sqlite3AuthCheck(pParse, SQLITE_FUNCTION, 0, pDef->zName, 0);
if( auth!=SQLITE_OK ){
|
| |
701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 |
pExpr->iTable = exprProbability(pList->a[1].pExpr);
if( pExpr->iTable<0 ){
sqlite3ErrorMsg(pParse, "second parameter to unlikely() must be "
"between 0.0 and 1.0");
pNC->nErr++;
}
}else{
pExpr->iTable = 75;
}
}
}
#ifndef SQLITE_OMIT_AUTHORIZATION
if( pDef ){
auth = sqlite3AuthCheck(pParse, SQLITE_FUNCTION, 0, pDef->zName, 0);
if( auth!=SQLITE_OK ){
|
Changes to src/where.c.
5302 5303 5304 5305 5306 5307 5308 5309 5310 5311 5312 5313 5314 5315 5316 5317 5318 .... 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 5386 .... 5395 5396 5397 5398 5399 5400 5401 5402 5403 5404 5405 5406 5407 5408 5409 5410 5411 5412 5413 5414 5415 5416 5417 5418 5419 5420 5421 5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 5435 5436 5437 5438 5439 5440 5441 5442 5443 5444 5445 5446 5447 5448 5449 5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 5460 5461 5462 5463 5464 5465 5466 5467 5468 5469 5470 5471 5472 5473 5474 5475 5476 5477 5478 5479 5480 5481 5482 5483 .... 5506 5507 5508 5509 5510 5511 5512 5513 5514 5515 5516 5517 5518 5519 5520 5521 5522 5523 5524 5525 |
static int wherePathSolver(WhereInfo *pWInfo, WhereCost nRowEst){
int mxChoice; /* Maximum number of simultaneous paths tracked */
int nLoop; /* Number of terms in the join */
Parse *pParse; /* Parsing context */
sqlite3 *db; /* The database connection */
int iLoop; /* Loop counter over the terms of the join */
int ii, jj; /* Loop counters */
WhereCost rCost; /* Cost of a path */
WhereCost mxCost = 0; /* Maximum cost of a set of paths */
WhereCost rSortCost; /* Cost to do a sort */
int nTo, nFrom; /* Number of valid entries in aTo[] and aFrom[] */
WherePath *aFrom; /* All nFrom paths at the previous level */
WherePath *aTo; /* The nTo best paths at the current level */
WherePath *pFrom; /* An element of aFrom[] that we are working on */
WherePath *pTo; /* An element of aTo[] that we are working on */
WhereLoop *pWLoop; /* One of the WhereLoop objects */
WhereLoop **pX; /* Used to divy up the pSpace memory */
................................................................................
u8 isOrdered = pFrom->isOrdered;
if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue;
if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
/* At this point, pWLoop is a candidate to be the next loop.
** Compute its cost */
rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
rCost = whereCostAdd(rCost, pFrom->rCost);
maskNew = pFrom->maskLoop | pWLoop->maskSelf;
if( !isOrderedValid ){
switch( wherePathSatisfiesOrderBy(pWInfo,
pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags,
iLoop, pWLoop, &revMask) ){
case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */
isOrdered = 1;
................................................................................
break;
}
}else{
revMask = pFrom->revLoop;
}
/* Check to see if pWLoop should be added to the mxChoice best so far */
for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
if( pTo->maskLoop==maskNew && pTo->isOrderedValid==isOrderedValid ){
testcase( jj==nTo-1 );
break;
}
}
if( jj>=nTo ){
if( nTo>=mxChoice && rCost>=mxCost ){
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf("Skip %s cost=%3d order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
continue;
}
/* Add a new Path to the aTo[] set */
if( nTo<mxChoice ){
/* Increase the size of the aTo set by one */
jj = nTo++;
}else{
/* New path replaces the prior worst to keep count below mxChoice */
for(jj=nTo-1; aTo[jj].rCost<mxCost; jj--){ assert(jj>0); }
}
pTo = &aTo[jj];
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf("New %s cost=%-3d order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
}else{
if( pTo->rCost<=rCost ){
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf(
"Skip %s cost=%-3d order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" vs %s cost=%-3d order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
}
#endif
testcase( pTo->rCost==rCost );
continue;
}
testcase( pTo->rCost==rCost+1 );
/* A new and better score for a previously created equivalent path */
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf(
"Update %s cost=%-3d order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" was %s cost=%-3d order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
}
#endif
}
/* pWLoop is a winner. Add it to the set of best so far */
pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
pTo->revLoop = revMask;
pTo->nRow = pFrom->nRow + pWLoop->nOut;
pTo->rCost = rCost;
pTo->isOrderedValid = isOrderedValid;
pTo->isOrdered = isOrdered;
memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
pTo->aLoop[iLoop] = pWLoop;
if( nTo>=mxChoice ){
mxCost = aTo[0].rCost;
for(jj=1, pTo=&aTo[1]; jj<mxChoice; jj++, pTo++){
if( pTo->rCost>mxCost ) mxCost = pTo->rCost;
}
}
}
}
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace>=2 ){
................................................................................
sqlite3ErrorMsg(pParse, "no query solution");
sqlite3DbFree(db, pSpace);
return SQLITE_ERROR;
}
/* Find the lowest cost path. pFrom will be left pointing to that path */
pFrom = aFrom;
assert( nFrom==1 );
#if 0 /* The following is needed if nFrom is ever more than 1 */
for(ii=1; ii<nFrom; ii++){
if( pFrom->rCost>aFrom[ii].rCost ) pFrom = &aFrom[ii];
}
#endif
assert( pWInfo->nLevel==nLoop );
/* Load the lowest cost path into pWInfo */
for(iLoop=0; iLoop<nLoop; iLoop++){
WhereLevel *pLevel = pWInfo->a + iLoop;
pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
pLevel->iFrom = pWLoop->iTab;
pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
|
> | > | > | > > | > > > | > > | | | | | | | | | | | | | | | > > > | > > > < < < |
5302 5303 5304 5305 5306 5307 5308 5309 5310 5311 5312 5313 5314 5315 5316 5317 5318 5319 5320 5321 .... 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 5386 5387 5388 5389 5390 .... 5399 5400 5401 5402 5403 5404 5405 5406 5407 5408 5409 5410 5411 5412 5413 5414 5415 5416 5417 5418 5419 5420 5421 5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 5435 5436 5437 5438 5439 5440 5441 5442 5443 5444 5445 5446 5447 5448 5449 5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 5460 5461 5462 5463 5464 5465 5466 5467 5468 5469 5470 5471 5472 5473 5474 5475 5476 5477 5478 5479 5480 5481 5482 5483 5484 5485 5486 5487 5488 5489 5490 5491 5492 5493 5494 5495 5496 5497 5498 5499 .... 5522 5523 5524 5525 5526 5527 5528 5529 5530 5531 5532 5533 5534 5535 5536 5537 5538 |
static int wherePathSolver(WhereInfo *pWInfo, WhereCost nRowEst){
int mxChoice; /* Maximum number of simultaneous paths tracked */
int nLoop; /* Number of terms in the join */
Parse *pParse; /* Parsing context */
sqlite3 *db; /* The database connection */
int iLoop; /* Loop counter over the terms of the join */
int ii, jj; /* Loop counters */
int mxI = 0; /* Index of next entry to replace */
WhereCost rCost; /* Cost of a path */
WhereCost nOut; /* Number of outputs */
WhereCost mxCost = 0; /* Maximum cost of a set of paths */
WhereCost mxOut = 0; /* Maximum nOut value on the set of paths */
WhereCost rSortCost; /* Cost to do a sort */
int nTo, nFrom; /* Number of valid entries in aTo[] and aFrom[] */
WherePath *aFrom; /* All nFrom paths at the previous level */
WherePath *aTo; /* The nTo best paths at the current level */
WherePath *pFrom; /* An element of aFrom[] that we are working on */
WherePath *pTo; /* An element of aTo[] that we are working on */
WhereLoop *pWLoop; /* One of the WhereLoop objects */
WhereLoop **pX; /* Used to divy up the pSpace memory */
................................................................................
u8 isOrdered = pFrom->isOrdered;
if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue;
if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
/* At this point, pWLoop is a candidate to be the next loop.
** Compute its cost */
rCost = whereCostAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
rCost = whereCostAdd(rCost, pFrom->rCost);
nOut = pFrom->nRow + pWLoop->nOut;
maskNew = pFrom->maskLoop | pWLoop->maskSelf;
if( !isOrderedValid ){
switch( wherePathSatisfiesOrderBy(pWInfo,
pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags,
iLoop, pWLoop, &revMask) ){
case 1: /* Yes. pFrom+pWLoop does satisfy the ORDER BY clause */
isOrdered = 1;
................................................................................
break;
}
}else{
revMask = pFrom->revLoop;
}
/* Check to see if pWLoop should be added to the mxChoice best so far */
for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
if( pTo->maskLoop==maskNew
&& pTo->isOrderedValid==isOrderedValid
&& ((pTo->rCost<=rCost && pTo->nRow<=nOut) ||
(pTo->rCost>=rCost && pTo->nRow>=nOut))
){
testcase( jj==nTo-1 );
break;
}
}
if( jj>=nTo ){
if( nTo>=mxChoice
&& (rCost>mxCost || (rCost==mxCost && nOut>=mxOut))
){
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf("Skip %s cost=%-3d,%3d order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
continue;
}
/* Add a new Path to the aTo[] set */
if( nTo<mxChoice ){
/* Increase the size of the aTo set by one */
jj = nTo++;
}else{
/* New path replaces the prior worst to keep count below mxChoice */
jj = mxI;
}
pTo = &aTo[jj];
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf("New %s cost=%-3d,%3d order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
}else{
if( pTo->rCost<=rCost && pTo->nRow<=nOut ){
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf(
"Skip %s cost=%-3d,%3d order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" vs %s cost=%-3d,%d order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
}
#endif
testcase( pTo->rCost==rCost );
continue;
}
testcase( pTo->rCost==rCost+1 );
/* A new and better score for a previously created equivalent path */
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf(
"Update %s cost=%-3d,%3d order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" was %s cost=%-3d,%3d order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
}
#endif
}
/* pWLoop is a winner. Add it to the set of best so far */
pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
pTo->revLoop = revMask;
pTo->nRow = nOut;
pTo->rCost = rCost;
pTo->isOrderedValid = isOrderedValid;
pTo->isOrdered = isOrdered;
memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
pTo->aLoop[iLoop] = pWLoop;
if( nTo>=mxChoice ){
mxI = 0;
mxCost = aTo[0].rCost;
mxOut = aTo[0].nRow;
for(jj=1, pTo=&aTo[1]; jj<mxChoice; jj++, pTo++){
if( pTo->rCost>mxCost || (pTo->rCost==mxCost && pTo->nRow>mxOut) ){
mxCost = pTo->rCost;
mxOut = pTo->nRow;
mxI = jj;
}
}
}
}
}
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace>=2 ){
................................................................................
sqlite3ErrorMsg(pParse, "no query solution");
sqlite3DbFree(db, pSpace);
return SQLITE_ERROR;
}
/* Find the lowest cost path. pFrom will be left pointing to that path */
pFrom = aFrom;
for(ii=1; ii<nFrom; ii++){
if( pFrom->rCost>aFrom[ii].rCost ) pFrom = &aFrom[ii];
}
assert( pWInfo->nLevel==nLoop );
/* Load the lowest cost path into pWInfo */
for(iLoop=0; iLoop<nLoop; iLoop++){
WhereLevel *pLevel = pWInfo->a + iLoop;
pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
pLevel->iFrom = pWLoop->iTab;
pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
|