Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure that an index that covers a proper superset of the WHERE clause terms of some other index has a lower cost than the other index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | query-plan-experiments |
Files: | files | file ages | folders |
SHA1: |
ea8b0910040198751551b0b960e6b783 |
User & Date: | drh 2014-03-31 18:24:18.867 |
Context
2014-03-31
| ||
19:49 | Also make sure an index that is a proper subset of some other index has a higher cost than that other index. Add test cases. (check-in: b7830d232b user: drh tags: query-plan-experiments) | |
18:24 | Make sure that an index that covers a proper superset of the WHERE clause terms of some other index has a lower cost than the other index. (check-in: ea8b091004 user: drh tags: query-plan-experiments) | |
2014-03-29
| ||
21:16 | Experiments in picking better query plans, especially when the usage of one index is a subset of another. (check-in: 8f869ca7a6 user: drh tags: query-plan-experiments) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 | WhereLoop *p = pWInfo->pLoops; pWInfo->pLoops = p->pNextLoop; whereLoopDelete(db, p); } sqlite3DbFree(db, pWInfo); } } /* ** Search the list of WhereLoops in *ppPrev looking for one that can be ** supplanted by pTemplate. ** ** Return NULL if the WhereLoop list contains an entry that can supplant ** pTemplate, in other words if pTemplate does not belong on the list. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 3732 3733 3734 3735 3736 3737 3738 3739 3740 3741 3742 3743 3744 3745 3746 3747 3748 3749 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 | WhereLoop *p = pWInfo->pLoops; pWInfo->pLoops = p->pNextLoop; whereLoopDelete(db, p); } sqlite3DbFree(db, pWInfo); } } /* ** Compare every WhereLoop X on the list p against pTemplate. If: ** ** (1) both X and pTemplate refer to the same table, and ** (2) both X and pTemplate use a single index, and ** (3) pTemplate uses all the same WHERE clause terms as X plus ** at least one more term, ** ** then make sure the cost pTemplate is less than the cost of X, adjusting ** the cost of pTemplate downward if necessary. ** ** Example: When computing the query plan for the SELECT below: ** ** CREATE TABLE t1(a,b,c,d); ** CREATE INDEX t1abc ON t1(a,b,c); ** CREATE INDEX t1bc ON t1(b,c); ** SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c; ** ** Make sure the cost of using three three columns of index t1abc is less ** than the cost of using both columns of t1bc. */ static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){ if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return; if( pTemplate->nLTerm==0 ) return; for(; p; p=p->pNextLoop){ if( p->iTab==pTemplate->iTab && (p->wsFlags & WHERE_INDEXED)!=0 && p->nLTerm<pTemplate->nLTerm && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun && p->nOut<=pTemplate->nOut)) ){ int i, j; for(j=0, i=p->nLTerm-1; i>=0 && j>=0; i--){ for(j=pTemplate->nLTerm-1; j>=0; j--){ if( pTemplate->aLTerm[j]==p->aLTerm[i] ) break; } } if( j>=0 ){ pTemplate->rRun = p->rRun; pTemplate->nOut = p->nOut - 1; } } } } /* ** Search the list of WhereLoops in *ppPrev looking for one that can be ** supplanted by pTemplate. ** ** Return NULL if the WhereLoop list contains an entry that can supplant ** pTemplate, in other words if pTemplate does not belong on the list. |
︙ | ︙ | |||
3743 3744 3745 3746 3747 3748 3749 | || p->rSetup==pTemplate->rSetup ); /* whereLoopAddBtree() always generates and inserts the automatic index ** case first. Hence compatible candidate WhereLoops never have a larger ** rSetup. Call this SETUP-INVARIANT */ assert( p->rSetup>=pTemplate->rSetup ); | > > > > > | | | | < < < < < < < < < < < < < < < | | | > > > > > | | | < < < < | | 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 3815 3816 3817 3818 3819 3820 3821 3822 3823 3824 3825 | || p->rSetup==pTemplate->rSetup ); /* whereLoopAddBtree() always generates and inserts the automatic index ** case first. Hence compatible candidate WhereLoops never have a larger ** rSetup. Call this SETUP-INVARIANT */ assert( p->rSetup>=pTemplate->rSetup ); /* If existing WhereLoop p is better than pTemplate, pTemplate can be ** discarded. WhereLoop p is better if: ** (1) p has no more dependencies than pTemplate, and ** (2) p has an equal or lower cost than pTemplate */ if( (p->prereq & pTemplate->prereq)==p->prereq /* (1) */ && p->rSetup<=pTemplate->rSetup /* (2a) */ && p->rRun<=pTemplate->rRun /* (2b) */ && p->nOut<=pTemplate->nOut /* (2c) */ ){ return 0; /* Discard pTemplate */ } /* If pTemplate is always better than p, then cause p to be overwritten ** with pTemplate. pTemplate is better than p if: ** (1) pTemplate has no more dependences than p, and ** (2) pTemplate has an equal or lower cost than p. */ if( (p->prereq & pTemplate->prereq)==pTemplate->prereq /* (1) */ && p->rRun>=pTemplate->rRun /* (2a) */ && p->nOut>=pTemplate->nOut /* (2b) */ ){ assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */ break; /* Cause p to be overwritten by pTemplate */ } } return ppPrev; } /* ** Insert or replace a WhereLoop entry using the template supplied. |
︙ | ︙ | |||
3797 3798 3799 3800 3801 3802 3803 | ** If pBuilder->pOrSet is not NULL then we care about only the ** prerequisites and rRun and nOut costs of the N best loops. That ** information is gathered in the pBuilder->pOrSet object. This special ** processing mode is used only for OR clause processing. ** ** When accumulating multiple loops (when pBuilder->pOrSet is NULL) we ** still might overwrite similar loops with the new template if the | | < < | 3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 | ** If pBuilder->pOrSet is not NULL then we care about only the ** prerequisites and rRun and nOut costs of the N best loops. That ** information is gathered in the pBuilder->pOrSet object. This special ** processing mode is used only for OR clause processing. ** ** When accumulating multiple loops (when pBuilder->pOrSet is NULL) we ** still might overwrite similar loops with the new template if the ** new template is better. Loops may be overwritten if the following ** conditions are met: ** ** (1) They have the same iTab. ** (2) They have the same iSortIdx. ** (3) The template has same or fewer dependencies than the current loop ** (4) The template has the same or lower cost than the current loop */ static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){ WhereLoop **ppPrev, *p; WhereInfo *pWInfo = pBuilder->pWInfo; sqlite3 *db = pWInfo->pParse->db; /* If pBuilder->pOrSet is defined, then only keep track of the costs |
︙ | ︙ | |||
3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 | } #endif return SQLITE_OK; } /* Look for an existing WhereLoop to replace with pTemplate */ ppPrev = whereLoopFindLesser(&pWInfo->pLoops, pTemplate); if( ppPrev==0 ){ /* There already exists a WhereLoop on the list that is better ** than pTemplate, so just ignore pTemplate */ #if WHERETRACE_ENABLED /* 0x8 */ if( sqlite3WhereTrace & 0x8 ){ | > | 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 3879 3880 3881 | } #endif return SQLITE_OK; } /* Look for an existing WhereLoop to replace with pTemplate */ whereLoopAdjustCost(pWInfo->pLoops, pTemplate); ppPrev = whereLoopFindLesser(&pWInfo->pLoops, pTemplate); if( ppPrev==0 ){ /* There already exists a WhereLoop on the list that is better ** than pTemplate, so just ignore pTemplate */ #if WHERETRACE_ENABLED /* 0x8 */ if( sqlite3WhereTrace & 0x8 ){ |
︙ | ︙ |