Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch query-plan-experiments Excluding Merge-Ins
This is equivalent to a diff from 27deb6e4 to 7473c4df
2014-04-04
| ||
18:20 | Merge changes to the query planner that strive to ensure that any index usage that is a proper subset of some other index usage always has a slightly higher cost. (check-in: 683dd379 user: drh tags: trunk) | |
2014-03-31
| ||
20:05 | Remove an unnecessary conditional. (Closed-Leaf check-in: 7473c4df user: drh tags: query-plan-experiments) | |
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: b7830d23 user: drh tags: query-plan-experiments) | |
13:42 | Avoid a (harmless) buffer overread that is possible on an OOM when MEMSYS5 is engaged. (check-in: b3296267 user: drh tags: trunk) | |
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: 8f869ca7 user: drh tags: query-plan-experiments) | |
2014-03-28
| ||
18:35 | Merge the latest changes from trunk. (check-in: 3047a25f user: drh tags: orderby-planning) | |
14:41 | Disable the wal64k.test script for non-unix systems since it depends on unix-only features. (check-in: 27deb6e4 user: drh tags: trunk) | |
12:56 | Fix a harmless compiler warning. (check-in: a4e47150 user: drh tags: trunk) | |
Changes to src/where.c.
︙ | |||
3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 | 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 3765 3766 3767 3768 3769 3770 3771 3772 3773 3774 3775 3776 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 3787 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 3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 3852 3853 3854 3855 3856 3857 3858 3859 3860 3861 3862 3863 3864 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - + - + - - - + | WhereLoop *p = pWInfo->pLoops; pWInfo->pLoops = p->pNextLoop; whereLoopDelete(db, p); } sqlite3DbFree(db, pWInfo); } } /* ** Return TRUE if the set of WHERE clause terms used by pA is a proper ** subset of the WHERE clause terms used by pB. */ static int whereLoopProperSubset(const WhereLoop *pA, const WhereLoop *pB){ int i, j; assert( pA->nLTerm<pB->nLTerm ); /* Checked by calling function */ for(j=0, i=pA->nLTerm-1; i>=0 && j>=0; i--){ for(j=pB->nLTerm-1; j>=0; j--){ if( pB->aLTerm[j]==pA->aLTerm[i] ) break; } } return j>=0; } /* ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so ** that: ** ** (1) pTemplate costs less than any other WhereLoops that are a proper ** subset of pTemplate ** ** (2) pTemplate costs more than any other WhereLoops for which pTemplate ** is a proper subset. ** ** To say "WhereLoop X is a proper subset of Y" means that X uses fewer ** WHERE clause terms than Y and that every WHERE clause term used by X is ** also used by Y. */ static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){ if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return; for(; p; p=p->pNextLoop){ if( p->iTab!=pTemplate->iTab ) continue; if( (p->wsFlags & WHERE_INDEXED)==0 ) continue; if( p->nLTerm<pTemplate->nLTerm && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun && p->nOut<=pTemplate->nOut)) && whereLoopProperSubset(p, pTemplate) ){ pTemplate->rRun = p->rRun; pTemplate->nOut = p->nOut - 1; }else if( p->nLTerm>pTemplate->nLTerm && (p->rRun>pTemplate->rRun || (p->rRun==pTemplate->rRun && p->nOut>=pTemplate->nOut)) && whereLoopProperSubset(pTemplate, p) ){ 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. ** ** If pX is a WhereLoop that pTemplate can supplant, then return the ** link that points to pX. ** ** If pTemplate cannot supplant any existing element of the list but needs ** to be added to the list, then return a pointer to the tail of the list. */ static WhereLoop **whereLoopFindLesser( WhereLoop **ppPrev, const WhereLoop *pTemplate ){ WhereLoop *p; for(p=(*ppPrev); p; ppPrev=&p->pNextLoop, p=*ppPrev){ if( p->iTab!=pTemplate->iTab || p->iSortIdx!=pTemplate->iSortIdx ){ /* If either the iTab or iSortIdx values for two WhereLoop are different ** then those WhereLoops need to be considered separately. Neither is ** a candidate to replace the other. */ continue; } /* In the current implementation, the rSetup value is either zero ** or the cost of building an automatic index (NlogN) and the NlogN ** is the same for compatible WhereLoops. */ assert( p->rSetup==0 || pTemplate->rSetup==0 || 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. ** ** An existing WhereLoop entry might be overwritten if the new template ** is better and has fewer dependencies. Or the template will be ignored ** and no insert will occur if an existing WhereLoop is faster and has ** fewer dependencies than the template. Otherwise a new WhereLoop is ** added based on the template. ** |
︙ | |||
3757 3758 3759 3760 3761 3762 3763 | 3873 3874 3875 3876 3877 3878 3879 3880 3881 3882 3883 3884 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 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 3945 3946 3947 3948 3949 3950 3951 3952 3953 3954 | - + - - - + + - - - - - + - - - + + - - - + + - - + - - - + + + - - - - - - - - - - - - - - - - - + - - - + + - - - - - - - - - - - - - - - - + - + - + + + + + + + + + + + + + + + + + + - + + + + + - - - - - - - - - - - - | sqlite3DebugPrintf(x?" or-%d: ":" or-X: ", n); whereLoopPrint(pTemplate, pBuilder->pWC); } #endif return SQLITE_OK; } |
︙ |
Added test/whereH.test.