SQLite

Check-in [ee31c04353]
Login

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: ee31c04353cd75ea4bbadee2994c30d3808b696a4f680187502d104902988a5d
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
Side-by-Side Diff Show Whitespace Changes Patch
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
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 is a proper subset of Y
**   (3)  X skips at least as many columns as 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
**
** By "proper subset" we mean that X uses fewer WHERE clause terms
** Conditions (2) and (3) mean that X is a "proper subset" of Y.
** 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
** 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
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
1056

1057
1058
1059
1060
1061
1062
1063
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 INDEX i41 (e=? AND c=? AND b=? AND a<?)}
  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<?)}
}