Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Allow tracing of whereLoopInsert() when the 0x8 bit is set on ".wheretrace". Remove the use of sqlite_query_plan from where2.test. Fix a bug in the code generator for OR clause optimizations. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
| Files: | files | file ages | folders |
| SHA1: | 707f0323264c35be14847a6adc49a0dc5eaf4ad2 |
| User & Date: | drh 2013-05-31 15:18:46 |
Context
|
2013-05-31
| ||
| 15:50 | Remove the definitions of objects that are no longer used: WhereCost, WherePlan, and WhereBestIdx. check-in: 816f8add user: drh tags: nextgen-query-plan-exp | |
| 15:18 | Allow tracing of whereLoopInsert() when the 0x8 bit is set on ".wheretrace". Remove the use of sqlite_query_plan from where2.test. Fix a bug in the code generator for OR clause optimizations. check-in: 707f0323 user: drh tags: nextgen-query-plan-exp | |
| 14:31 | Enhance the shell to provide more flexibility when entering numeric arguments on dot-commands. In particular, allow hex arguments to .wheretrace. check-in: b9578c37 user: drh tags: nextgen-query-plan-exp | |
Changes
Changes to src/where.c.
3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767 .... 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 .... 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 .... 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 .... 3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 .... 4152 4153 4154 4155 4156 4157 4158 4159 4160 4161 4162 4163 4164 4165 4166 .... 4769 4770 4771 4772 4773 4774 4775 4776 4777 4778 4779 4780 4781 4782 4783 4784 .... 4788 4789 4790 4791 4792 4793 4794 4795 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 .... 5235 5236 5237 5238 5239 5240 5241 5242 5243 5244 5245 5246 5247 5248 5249 5250 5251 |
sqlite3DebugPrintf("%c %2d.%0*llx.%0*llx", p->cId,
p->iTab, nb, p->maskSelf, nb, p->prereq);
sqlite3DebugPrintf(" %8s",
pItem->zAlias ? pItem->zAlias : pTab->zName);
if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
if( p->u.btree.pIndex ){
const char *zName = p->u.btree.pIndex->zName;
if( strncmp(zName, "sqlite_autoindex_", 17)==0 ){
int i = sqlite3Strlen30(zName) - 1;
while( zName[i]!='_' ) i--;
zName += i;
}
sqlite3DebugPrintf(".%-12s %2d", zName, p->u.btree.nEq);
}else{
................................................................................
** best WhereLoop. pBuilder->pBest->maskSelf==0 indicates that no
** prior WhereLoops have been evaluated and that the current pTemplate
** is therefore the first and hence the best and should be retained.
*/
if( (p = pBuilder->pBest)!=0 ){
if( p->maskSelf!=0 ){
if( p->rRun+p->rSetup < pTemplate->rRun+pTemplate->rSetup ){
return SQLITE_OK;
}
if( p->rRun+p->rSetup == pTemplate->rRun+pTemplate->rSetup
&& p->prereq <= pTemplate->prereq ){
return SQLITE_OK;
}
}
*p = *pTemplate;
p->aTerm = 0;
p->u.vtab.needFree = 0;
return SQLITE_OK;
}
/* Search for an existing WhereLoop to overwrite, or which takes
** priority over pTemplate.
*/
for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){
................................................................................
}
if( (p->prereq & pTemplate->prereq)==p->prereq
&& p->rSetup<=pTemplate->rSetup
&& p->rRun<=pTemplate->rRun
){
/* Already holding an equal or better WhereLoop.
** Return without changing or adding anything */
return SQLITE_OK;
}
if( (p->prereq & pTemplate->prereq)==pTemplate->prereq
&& p->rSetup>=pTemplate->rSetup
&& p->rRun>=pTemplate->rRun
){
/* Overwrite an existing WhereLoop with a better one */
pNext = p->pNextLoop;
................................................................................
}
}
/* If we reach this point it means that either p[] should be overwritten
** with pTemplate[] if p[] exists, or if p==NULL then allocate a new
** WhereLoop and insert it.
*/
if( p==0 ){
p = pToFree = sqlite3DbMallocRaw(db, sizeof(WhereLoop));
if( p==0 ) return SQLITE_NOMEM;
}
if( pTemplate->nTerm ){
paTerm = sqlite3DbMallocRaw(db, pTemplate->nTerm*sizeof(p->aTerm[0]));
if( paTerm==0 ){
................................................................................
if( pIndex && pIndex->tnum==0 ){
p->u.btree.pIndex = 0;
}
}else{
pTemplate->u.vtab.needFree = 0;
}
return SQLITE_OK;
}
/*
** We have so far matched pBuilder->pNew->u.btree.nEq terms of the index pIndex.
** Try to match one more.
**
** If pProbe->tnum==0, that means pIndex is a fake index used for the
................................................................................
WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
if( termCanDriveIndex(pTerm, pSrc, 0) ){
pNew->u.btree.nEq = 1;
pNew->u.btree.pIndex = 0;
pNew->nTerm = 1;
pNew->aTerm[0] = pTerm;
pNew->rSetup = 2*rLogSize*pSrc->pTab->nRowEst;
pNew->nOut = (double)10;
pNew->rRun = rLogSize + pNew->nOut;
pNew->wsFlags = WHERE_TEMP_INDEX;
pNew->prereq = mExtra | pTerm->prereqRight;
rc = whereLoopInsert(pBuilder, pNew);
}
}
................................................................................
for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
if( pTo->maskLoop==maskNew && pTo->isOrderedValid==isOrderedValid ){
break;
}
}
if( jj>=nTo ){
if( nTo>=mxChoice && rCost>=mxCost ){
#ifdef WHERETRACE_ENABLE
if( sqlite3WhereTrace>=3 ){
sqlite3DebugPrintf("Skip %s cost=%-7.2g order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
continue;
}
................................................................................
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>=3 ){
sqlite3DebugPrintf("New %s cost=%-7.2g order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
}else{
if( pTo->rCost<=rCost ){
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace>=3 ){
sqlite3DebugPrintf(
"Skip %s cost=%-7.2g order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" vs %s cost=%-7.2g order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
}
#endif
continue;
}
/* A new and better score for a previously created equivalent path */
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace>=3 ){
sqlite3DebugPrintf(
"Update %s cost=%-7.2g order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" was %s cost=%-7.2g order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
................................................................................
if( (pLoop->wsFlags & WHERE_TEMP_INDEX)!=0 ){
constructAutomaticIndex(pParse, sWBI.pWC, pTabItem, notReady, pLevel);
}else
#endif
if( pLoop->u.btree.pIndex!=0 ){
Index *pIx = pLoop->u.btree.pIndex;
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
/* FIXME: Might need to be the iIdxCur parameter. As an optimization
** use pTabItem->iCursor if WHERE_IDX_ONLY */
int iIndexCur = pLevel->iIdxCur = pParse->nTab++;
assert( pIx->pSchema==pTab->pSchema );
assert( iIndexCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenRead, iIndexCur, pIx->tnum, iDb,
(char*)pKey, P4_KEYINFO_HANDOFF);
VdbeComment((v, "%s", pIx->zName));
}
sqlite3CodeVerifySchema(pParse, iDb);
|
> | | > > > > > > | > > > > > > > > > > > > > > > > > > > > | | | | | | < | | |
3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767 3768 .... 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 .... 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 .... 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944 .... 3958 3959 3960 3961 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 .... 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 .... 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 .... 4815 4816 4817 4818 4819 4820 4821 4822 4823 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 4837 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 .... 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 |
sqlite3DebugPrintf("%c %2d.%0*llx.%0*llx", p->cId,
p->iTab, nb, p->maskSelf, nb, p->prereq);
sqlite3DebugPrintf(" %8s",
pItem->zAlias ? pItem->zAlias : pTab->zName);
if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
if( p->u.btree.pIndex ){
const char *zName = p->u.btree.pIndex->zName;
if( zName==0 ) zName = "ipk";
if( strncmp(zName, "sqlite_autoindex_", 17)==0 ){
int i = sqlite3Strlen30(zName) - 1;
while( zName[i]!='_' ) i--;
zName += i;
}
sqlite3DebugPrintf(".%-12s %2d", zName, p->u.btree.nEq);
}else{
................................................................................
** best WhereLoop. pBuilder->pBest->maskSelf==0 indicates that no
** prior WhereLoops have been evaluated and that the current pTemplate
** is therefore the first and hence the best and should be retained.
*/
if( (p = pBuilder->pBest)!=0 ){
if( p->maskSelf!=0 ){
if( p->rRun+p->rSetup < pTemplate->rRun+pTemplate->rSetup ){
goto whereLoopInsert_noop;
}
if( p->rRun+p->rSetup == pTemplate->rRun+pTemplate->rSetup
&& p->prereq <= pTemplate->prereq ){
goto whereLoopInsert_noop;
}
}
*p = *pTemplate;
p->aTerm = 0;
p->u.vtab.needFree = 0;
#if WHERETRACE_ENABLED
if( sqlite3WhereTrace & 0x8 ){
sqlite3DebugPrintf("ins-best: ");
whereLoopPrint(pTemplate, pBuilder->pTabList);
}
#endif
return SQLITE_OK;
}
/* Search for an existing WhereLoop to overwrite, or which takes
** priority over pTemplate.
*/
for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){
................................................................................
}
if( (p->prereq & pTemplate->prereq)==p->prereq
&& p->rSetup<=pTemplate->rSetup
&& p->rRun<=pTemplate->rRun
){
/* Already holding an equal or better WhereLoop.
** Return without changing or adding anything */
goto whereLoopInsert_noop;
}
if( (p->prereq & pTemplate->prereq)==pTemplate->prereq
&& p->rSetup>=pTemplate->rSetup
&& p->rRun>=pTemplate->rRun
){
/* Overwrite an existing WhereLoop with a better one */
pNext = p->pNextLoop;
................................................................................
}
}
/* If we reach this point it means that either p[] should be overwritten
** with pTemplate[] if p[] exists, or if p==NULL then allocate a new
** WhereLoop and insert it.
*/
#if WHERETRACE_ENABLED
if( sqlite3WhereTrace & 0x8 ){
if( p!=0 ){
sqlite3DebugPrintf("ins-del: ");
whereLoopPrint(p, pBuilder->pTabList);
}
sqlite3DebugPrintf("ins-new: ");
whereLoopPrint(pTemplate, pBuilder->pTabList);
}
#endif
if( p==0 ){
p = pToFree = sqlite3DbMallocRaw(db, sizeof(WhereLoop));
if( p==0 ) return SQLITE_NOMEM;
}
if( pTemplate->nTerm ){
paTerm = sqlite3DbMallocRaw(db, pTemplate->nTerm*sizeof(p->aTerm[0]));
if( paTerm==0 ){
................................................................................
if( pIndex && pIndex->tnum==0 ){
p->u.btree.pIndex = 0;
}
}else{
pTemplate->u.vtab.needFree = 0;
}
return SQLITE_OK;
/* Jump here if the insert is a no-op */
whereLoopInsert_noop:
#if WHERETRACE_ENABLED
if( sqlite3WhereTrace & 0x8 ){
sqlite3DebugPrintf("ins-noop: ");
whereLoopPrint(pTemplate, pBuilder->pTabList);
}
#endif
return SQLITE_OK;
}
/*
** We have so far matched pBuilder->pNew->u.btree.nEq terms of the index pIndex.
** Try to match one more.
**
** If pProbe->tnum==0, that means pIndex is a fake index used for the
................................................................................
WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
if( termCanDriveIndex(pTerm, pSrc, 0) ){
pNew->u.btree.nEq = 1;
pNew->u.btree.pIndex = 0;
pNew->nTerm = 1;
pNew->aTerm[0] = pTerm;
pNew->rSetup = 20*rLogSize*pSrc->pTab->nRowEst;
pNew->nOut = (double)10;
pNew->rRun = rLogSize + pNew->nOut;
pNew->wsFlags = WHERE_TEMP_INDEX;
pNew->prereq = mExtra | pTerm->prereqRight;
rc = whereLoopInsert(pBuilder, pNew);
}
}
................................................................................
for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
if( pTo->maskLoop==maskNew && pTo->isOrderedValid==isOrderedValid ){
break;
}
}
if( jj>=nTo ){
if( nTo>=mxChoice && rCost>=mxCost ){
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf("Skip %s cost=%-7.2g order=%c\n",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
}
#endif
continue;
}
................................................................................
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=%-7.2g 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=%-7.2g order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" vs %s cost=%-7.2g order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
}
#endif
continue;
}
/* A new and better score for a previously created equivalent path */
#ifdef WHERETRACE_ENABLED
if( sqlite3WhereTrace&0x4 ){
sqlite3DebugPrintf(
"Update %s cost=%-7.2g order=%c",
wherePathName(pFrom, iLoop, pWLoop), rCost,
isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
sqlite3DebugPrintf(" was %s cost=%-7.2g order=%c\n",
wherePathName(pTo, iLoop+1, 0), pTo->rCost,
pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
................................................................................
if( (pLoop->wsFlags & WHERE_TEMP_INDEX)!=0 ){
constructAutomaticIndex(pParse, sWBI.pWC, pTabItem, notReady, pLevel);
}else
#endif
if( pLoop->u.btree.pIndex!=0 ){
Index *pIx = pLoop->u.btree.pIndex;
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
/* FIXME: As an optimization use pTabItem->iCursor if WHERE_IDX_ONLY */
int iIndexCur = pLevel->iIdxCur = iIdxCur ? iIdxCur : pParse->nTab++;
assert( pIx->pSchema==pTab->pSchema );
assert( iIndexCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenRead, iIndexCur, pIx->tnum, iDb,
(char*)pKey, P4_KEYINFO_HANDOFF);
VdbeComment((v, "%s", pIx->zName));
}
sqlite3CodeVerifySchema(pParse, iDb);
|
Changes to test/where2.test.
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
# it appends the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
set ::sqlite_sort_count 0
set data [execsql $sql]
if {$::sqlite_sort_count} {set x sort} {set x nosort}
lappend data $x
return [concat $data $::sqlite_query_plan]
}
# Prefer a UNIQUE index over another index.
#
do_test where2-1.1 {
queryplan {
|
> > > > > > > > > > > | |
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
# it appends the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
set ::sqlite_sort_count 0
set data [execsql $sql]
if {$::sqlite_sort_count} {set x sort} {set x nosort}
lappend data $x
set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
# puts eqp=$eqp
foreach {a b c x} $eqp {
if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
$x all as tab idx]} {
lappend data $tab $idx
} elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
lappend data $tab *
}
}
return $data
# return [concat $data $::sqlite_query_plan]
}
# Prefer a UNIQUE index over another index.
#
do_test where2-1.1 {
queryplan {
|