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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
ee31c04353cd75ea4bbadee2994c30d3 |
| User & Date: | drh 2017-10-15 22:16:25.229 |
Context
|
2017-10-16
| ||
| 11:50 | Improved comments, including a typo fix, on the whereLoopCheaperProperSubset() function. (check-in: 700a3c6944 user: drh tags: trunk) | |
|
2017-10-15
| ||
| 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. (check-in: ee31c04353 user: drh tags: trunk) | |
|
2017-10-14
| ||
| 19:54 | Updates to the Makefiles for MSVC. (check-in: ac8786f3f9 user: mistachkin tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
1881 1882 1883 1884 1885 1886 1887 | } } /* ** Return TRUE if all of the following are true: ** ** (1) X has the same or lower cost that Y | | > | > | < < < | | 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 |
}
}
/*
** 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 skip-scan 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 */
){
|
| ︙ | ︙ | |||
1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 |
}
for(i=pX->nLTerm-1; i>=0; i--){
if( pX->aLTerm[i]==0 ) continue;
for(j=pY->nLTerm-1; 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:
| > > > > | 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 |
}
for(i=pX->nLTerm-1; i>=0; i--){
if( pX->aLTerm[i]==0 ) continue;
for(j=pY->nLTerm-1; 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 |
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
} {
| > > > | | 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 2017-10-15, 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<?)}
}
|
| ︙ | ︙ |