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 |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
Files: | files | file ages | folders |
SHA1: |
707f0323264c35be14847a6adc49a0dc |
User & Date: | drh 2013-05-31 15:18:46.709 |
Context
2013-05-31
| ||
15:50 | Remove the definitions of objects that are no longer used: WhereCost, WherePlan, and WhereBestIdx. (check-in: 816f8add7e 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: 707f032326 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: b9578c371e 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 | 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{ | > | 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767 3768 | 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{ |
︙ | ︙ | |||
3861 3862 3863 3864 3865 3866 3867 | ** 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 ){ | | | > > > > > > | 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 | ** 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){ |
︙ | ︙ | |||
3897 3898 3899 3900 3901 3902 3903 | } 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 */ | | > > > > > > > > > > | 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941 3942 3943 3944 | } 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; whereLoopClear(db, p); break; } } /* 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 ){ |
︙ | ︙ | |||
3941 3942 3943 3944 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 | 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 | > > > > > > > > > > | 3958 3959 3960 3961 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 | 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 |
︙ | ︙ | |||
4152 4153 4154 4155 4156 4157 4158 | 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; | | | 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 | 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); } } |
︙ | ︙ | |||
4769 4770 4771 4772 4773 4774 4775 | 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 ){ | | | | | | | 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 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 | 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; } /* 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=%-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') : '?'); |
︙ | ︙ | |||
5235 5236 5237 5238 5239 5240 5241 | 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); | < | | | 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 5274 5275 5276 5277 | 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 | # 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 | > > > > > > > > > > > | | 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 { |
︙ | ︙ |