Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment:  In the query planner, do not consider index X to be a proper subset of index Y if X is a covering index but Y is not. 

Downloads:  Tarball  ZIP archive  SQL archive 
Timelines:  family  ancestors  descendants  both  trunk 
Files:  files  file ages  folders 
SHA3256: 
ee31c04353cd75ea4bbadee2994c30d3 
User & Date:  drh 20171015 22:16:25 
Context
20171016
 
11:50  Improved comments, including a typo fix, on the whereLoopCheaperProperSubset() function. checkin: 700a3c69 user: drh tags: trunk  
20171015
 
22:16  In the query planner, do not consider index X to be a proper subset of index Y if X is a covering index but Y is not. checkin: ee31c043 user: drh tags: trunk  
20171014
 
19:54  Updates to the Makefiles for MSVC. checkin: ac8786f3 user: mistachkin tags: trunk  
Changes
Changes to src/where.c.
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
....
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926

} } /* ** Return TRUE if all of the following are true: ** ** (1) X has the same or lower cost that Y ** (2) X is a proper subset of Y ** (3) X skips at least as many columns as Y ** ** By "proper subset" we mean that X uses fewer WHERE clause terms ** than Y and that every WHERE clause term used by X is also used ** by Y. ** ** If X is a proper subset of Y then Y is a better choice and ought ** to have a lower cost. This routine returns TRUE when that cost ** relationship is inverted and needs to be adjusted. The third rule ** was added because if X uses skipscan less than Y it still might ** deserve a lower cost even if it is a proper subset of Y. */ static int whereLoopCheaperProperSubset( const WhereLoop *pX, /* First WhereLoop to compare */ const WhereLoop *pY /* Compare against this WhereLoop */ ){ ................................................................................ } for(i=pX>nLTerm1; i>=0; i){ if( pX>aLTerm[i]==0 ) continue; for(j=pY>nLTerm1; j>=0; j){ if( pY>aLTerm[j]==pX>aLTerm[i] ) break; } if( j<0 ) return 0; /* X not a subset of Y since term X[i] not used by Y */ } return 1; /* All conditions meet */ } /* ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so ** that: 

>

>
<
<

<

>
>
>
>

1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
....
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929

} } /* ** Return TRUE if all of the following are true: ** ** (1) X has the same or lower cost that Y ** (2) X users fewer WHERE clause terms than Y ** (3) Every WHERE clause term used by X is also used by Y ** (4) X skips at least as many columns as Y ** (5) If X is a covering index, than Y is too ** ** Conditions (2) and (3) mean that X is a "proper subset" of Y. ** If X is a proper subset of Y then Y is a better choice and ought ** to have a lower cost. This routine returns TRUE when that cost ** relationship is inverted and needs to be adjusted. Constraint (4) ** was added because if X uses skipscan less than Y it still might ** deserve a lower cost even if it is a proper subset of Y. */ static int whereLoopCheaperProperSubset( const WhereLoop *pX, /* First WhereLoop to compare */ const WhereLoop *pY /* Compare against this WhereLoop */ ){ ................................................................................ } for(i=pX>nLTerm1; i>=0; i){ if( pX>aLTerm[i]==0 ) continue; for(j=pY>nLTerm1; j>=0; j){ if( pY>aLTerm[j]==pX>aLTerm[i] ) break; } if( j<0 ) return 0; /* X not a subset of Y since term X[i] not used by Y */ } if( (pX>wsFlags&WHERE_IDX_ONLY)!=0 && (pY>wsFlags&WHERE_IDX_ONLY)==0 ){ return 0; /* Constraint (5) */ } return 1; /* All conditions meet */ } /* ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so ** that: 
Changes to test/analyze9.test.
1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 
INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
ANALYZE;
} {}
do_eqp_test 23.1 {
SELECT * FROM t4 WHERE
(e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300
} {
0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)}
}
do_eqp_test 23.2 {
SELECT * FROM t4 WHERE
(e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300
} {
0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)}
}

> > >  
1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 
INSERT INTO t4 SELECT a, b, c, d, e, f FROM data; ANALYZE; } {} do_eqp_test 23.1 { SELECT * FROM t4 WHERE (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300  Formerly used index i41. But i41 is not a covering index whereas  the PRIMARY KEY is a covering index, and so as of 20171015, the  PRIMARY KEY is preferred. } { 0 0 0 {SEARCH TABLE t4 USING PRIMARY KEY (c=? AND b=? AND a<?)} } do_eqp_test 23.2 { SELECT * FROM t4 WHERE (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300 } { 0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)} } 