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

Reply via email to