Please verify that the alternative optimization checked-in at
http://www.sqlite.org/src/info/b7e39851a7 covers all of the cases that you
identify below. Tnx.
On Thu, Jan 2, 2014 at 9:25 AM, Yuriy Kaminskiy <yum...@gmail.com> wrote:
> On 2013/11/04 Yuriy Kaminskiy wrote:
> > On 2012/04/08 Yuriy Kaminskiy wrote:
> >> On 2011/12/06 Yuriy Kaminskiy wrote:
> >>> On 2011/11/03 Yuriy Kaminskiy wrote:
> >>>> On 2011/11/23 Yuriy Kaminskiy wrote:
> >>>>> On 2011/10/23 Yuriy Kaminskiy wrote:
> >>>>>> When WHERE condition is constant, there are no need to evaluate and
> check it for
> >>>>>> each row. It works, but only partially:
> >>>>> ...
> >>>>>> [In fact, you can move out out loop not only *whole* constant
> WHERE, but also
> >>>>>> all constant AND terms of WHERE, like this:
> >>>>>> SELECT * FROM t WHERE const1 AND notconst AND const2 ->
> >>>>>> SELECT * FROM (SELECT * FROM t WHERE notconst) WHERE const1 AND
> const2
> >>>>>> I'll take a shot on that later.]
> >>>>> Here it goes.
> >>>>>
> >>>>> Prerequisite: previous patch.
> >>>>> Passes quick regression test (make test).
> >>>>> Possible problem: short-circuits evaluation. Should not be a
> problem, IMO, as only
> >>>>> constants references? Please verify.
> >>>> Ping.
> >>> Ping.
> >> Ping.
> >> For convenience all 3 patches collected below (needed no change for
> 3.7.11).
> >
> > Ping. Over 2 years passed since this patch series was first posted.
> > Updated patch series for 3.8.1 below.
>
> Ping. Same patch series refreshed for 3.8.2 with minor change.
> --
> The author or authors of this code dedicate any and all copyright interest
> in this code to the public domain. We make this dedication for the benefit
> of the public at large and to the detriment of our heirs and successors.
> We intend this dedication to be an overt act of relinquishment in
> perpetuity
> of all present and future rights to this code under copyright law.
>
> Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>
>
> Part 1: Move whereSplit() to unbreak constant condition elimination.
>
> (this is very obvious fix: only effect of "Special case" is pWhere
> assignment,
> but pWhere value is only used in whereSplit call *above* of "Special
> case"; this
> whole "Special case" is expensive no-op now)
>
> Test case:
> CREATE TABLE t (i, j, k);
> EXPLAIN SELECT * FROM t WHERE 11;
>
> Index: sqlite3-3.8.2/src/where.c
> ===================================================================
> --- sqlite3-3.8.2.orig/src/where.c 2014-01-02 16:18:24.000000000 +0400
> +++ sqlite3-3.8.2/src/where.c 2014-01-02 17:10:24.000000000 +0400
> @@ -5423,7 +5423,6 @@ WhereInfo *sqlite3WhereBegin(
> */
> initMaskSet(pMaskSet);
> whereClauseInit(&pWInfo->sWC, pWInfo);
> - whereSplit(&pWInfo->sWC, pWhere, TK_AND);
> sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto
> */
>
> /* Special case: a WHERE clause that is constant. Evaluate the
> @@ -5434,6 +5433,8 @@ WhereInfo *sqlite3WhereBegin(
> pWhere = 0;
> }
>
> + whereSplit(&pWInfo->sWC, pWhere, TK_AND);
> +
> /* Special case: No FROM clause
> */
> if( nTabList==0 ){
> ===================================================================
>
> Part 2: optimize "WHERE const AND notconst" too
>
> (trivial generalization of "Special case")
>
> Test case:
> EXPLAIN SELECT * FROM t WHERE 11 AND 12 AND i AND 13 AND j AND 14;
>
> Index: sqlite3-3.8.1/src/where.c
> ===================================================================
> --- sqlite3-3.8.1.orig/src/where.c 2013-11-03 23:27:05.000000000 +0400
> +++ sqlite3-3.8.1/src/where.c 2013-11-03 23:27:59.000000000 +0400
> @@ -5739,6 +5739,24 @@ WhereInfo *sqlite3WhereBegin(
>
> whereSplit(&pWInfo->sWC, pWhere, TK_AND);
>
> + {
> + /* Move const in "WHERE const AND notconst" out of internal loop */
> + int i, j;
> + WhereClause * const pWC = &pWInfo->sWC;
> +
> + for(j=i=0; i<pWC->nTerm; i++){
> + if( nTabList==0 || sqlite3ExprIsConstantNotJoin(pWC->a[i].pExpr) ){
> + sqlite3ExprIfFalse(pParse, pWC->a[i].pExpr, pWInfo->iBreak,
> SQLITE_JUMPIFNULL);
> + continue;
> + }
> + if( j!=i )
> + pWC->a[j]=pWC->a[i];
> + j++;
> + }
> + /* XXX if (i != j) memset(&(pWC->a[j]), 0, (i-j)*sizeof(pWC->a[0]));
> */
> + pWC->nTerm -= i-j;
> + }
> +
> /* Special case: No FROM clause
> */
> if( nTabList==0 ){
> ===================================================================
> Part 3: Remove now-redundant sqlite3ExprIsConstantNotJoin call.
>
> Index: sqlite3-3.8.2/src/where.c
> ===================================================================
> --- sqlite3-3.8.2.orig/src/where.c 2014-01-02 17:10:47.000000000 +0400
> +++ sqlite3-3.8.2/src/where.c 2014-01-02 17:11:54.000000000 +0400
> @@ -5423,20 +5423,13 @@ WhereInfo *sqlite3WhereBegin(
> */
> initMaskSet(pMaskSet);
> whereClauseInit(&pWInfo->sWC, pWInfo);
> - sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto
> */
> -
> - /* Special case: a WHERE clause that is constant. Evaluate the
> - ** expression and either jump over all of the code or fall thru.
> - */
> - if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
> - sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
> - pWhere = 0;
> - }
> -
> whereSplit(&pWInfo->sWC, pWhere, TK_AND);
> + sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto
> */
>
> {
> - /* Move const in "WHERE const AND notconst" out of internal loop */
> + /* Special case: AND subterm of WHERE clause that is constant.
> Evaluate the
> + ** expression and either jump over all of the code or fall thru.
> + */
> int i, j;
> WhereClause * const pWC = &pWInfo->sWC;
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
