SQLite

Check-in [7c2d3406]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Avoid computing the values for unused result-set columns in subqueries. Performance optimization request [baa5bb76c35a124c].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7c2d3406000dc8ac5a99cc205b036356b67e4b0b94738592ffc5680749696904
User & Date: drh 2023-02-22 21:11:34
References
2023-08-31
18:00
Do not disable unused columns in a UNION ALL sub-query if any component of the sub-query is DISTINCT. Problem introduced by [7c2d3406000dc8ac] and reported by forum post aeae62275ebbf584. (check-in: c84d5602 user: dan tags: trunk)
2023-02-26
11:52
In the omit-unused-subquery-columns optimization, be sure to remove the EP_Skip and EP_Unlikely flags from the result set expressions that get nulled-out. dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15. Also fix an incorrect ".selecttrace" code block. (check-in: 83a7f13e user: drh tags: branch-3.28)
11:36
In the omit-unused-subquery-columns optimization, be sure to remove the EP_Skip and EP_Unlikely flags from the result set expressions that get nulled-out. dbsqlfuzz bf1d3ed6e0e0dd8766027797d43db40c776d2b15. (check-in: 21aec65e user: drh tags: trunk)
Context
2023-02-22
21:47
Enable the count-of-view optimization by default. Enhancement request [eaed8e36ce888f1e]. (check-in: a4aacdd3 user: drh tags: trunk)
21:11
Avoid computing the values for unused result-set columns in subqueries. Performance optimization request [baa5bb76c35a124c]. (check-in: 7c2d3406 user: drh tags: trunk)
20:50
Update the version number to 3.42.0 to begin the next development cycle. (check-in: 65910216 user: drh tags: trunk)
2023-02-16
19:41
A few simple test cases for the omit-unused-subquery-column optimization. (Closed-Leaf check-in: cf8f57c5 user: drh tags: omit-unused-subquery-columns)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

5222
5223
5224
5225
5226
5227
5228






































































5229
5230
5231
5232
5233
5234
5235
      }
      pSubq = pSubq->pPrior;
    }
  }
  return nChng;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */







































































/*
** The pFunc is the only aggregate function in the query.  Check to see
** if the query is a candidate for the min/max optimization. 
**
** If the query is a candidate for the min/max optimization, then set
** *ppMinMax to be an ORDER BY clause to be used for the optimization







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







5222
5223
5224
5225
5226
5227
5228
5229
5230
5231
5232
5233
5234
5235
5236
5237
5238
5239
5240
5241
5242
5243
5244
5245
5246
5247
5248
5249
5250
5251
5252
5253
5254
5255
5256
5257
5258
5259
5260
5261
5262
5263
5264
5265
5266
5267
5268
5269
5270
5271
5272
5273
5274
5275
5276
5277
5278
5279
5280
5281
5282
5283
5284
5285
5286
5287
5288
5289
5290
5291
5292
5293
5294
5295
5296
5297
5298
5299
5300
5301
5302
5303
5304
5305
      }
      pSubq = pSubq->pPrior;
    }
  }
  return nChng;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

/*
** Check to see if a subquery contains result-set columns that are
** never used.  If it does, change the value of those result-set columns
** to NULL so that they do not cause unnecessary work to compute.
**
** Return the number of column that were changed to NULL.
*/
static int disableUnusedSubqueryResultColumns(SrcItem *pItem){
  int nCol;
  Select *pSub;      /* The subquery to be simplified */
  Select *pX;        /* For looping over compound elements of pSub */
  Table *pTab;       /* The table that describes the subquery */
  int j;             /* Column number */
  int nChng = 0;     /* Number of columns converted to NULL */
  Bitmask colUsed;   /* Columns that may not be NULLed out */

  assert( pItem!=0 );
  if( pItem->fg.isCorrelated || pItem->fg.isCte ){
    return 0;
  }
  assert( pItem->pTab!=0 );
  pTab = pItem->pTab;
  assert( pItem->pSelect!=0 );
  pSub = pItem->pSelect;
  assert( pSub->pEList->nExpr==pTab->nCol );
  if( (pSub->selFlags & (SF_Distinct|SF_Aggregate))!=0 ){
    testcase( pSub->selFlags & SF_Distinct );
    testcase( pSub->selFlags & SF_Aggregate );
    return 0;
  }
  for(pX=pSub; pX; pX=pX->pPrior){
    if( pX->pPrior && pX->op!=TK_ALL ){
      /* This optimization does not work for compound subqueries that
      ** use UNION, INTERSECT, or EXCEPT.  Only UNION ALL is allowed. */
      return 0;
    }
    if( pX->pWin ){
      /* This optimization does not work for subqueries that use window
      ** functions. */
      return 0;
    }
  }
 colUsed = pItem->colUsed;
  if( pSub->pOrderBy ){
    ExprList *pList = pSub->pOrderBy;
    for(j=0; j<pList->nExpr; j++){
      u16 iCol = pList->a[j].u.x.iOrderByCol;
      if( iCol>0 ){
        iCol--;
        colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
      }
    }
  }
  nCol = pTab->nCol;
  for(j=0; j<nCol; j++){
    Select *pX;
    Bitmask m = j<BMS-1 ? MASKBIT(j) : TOPBIT;
    if( (m & colUsed)!=0 ) continue;
    for(pX=pSub; pX; pX=pX->pPrior) {
      Expr *pY = pX->pEList->a[j].pExpr;
      if( pY->op==TK_NULL ) continue;
      pY->op = TK_NULL;
      pX->selFlags |= SF_PushDown;
      nChng++;
    }
  }
  return nChng;
}


/*
** The pFunc is the only aggregate function in the query.  Check to see
** if the query is a candidate for the min/max optimization. 
**
** If the query is a candidate for the min/max optimization, then set
** *ppMinMax to be an ORDER BY clause to be used for the optimization
7321
7322
7323
7324
7325
7326
7327
















7328
7329
7330
7331
7332
7333
7334
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
      assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 );
    }else{
      TREETRACE(0x4000,pParse,p,("Push-down not possible\n"));
    }

















    zSavedAuthContext = pParse->zAuthContext;
    pParse->zAuthContext = pItem->zName;

    /* Generate code to implement the subquery
    */
    if( fromClauseTermCanBeCoroutine(pParse, pTabList, i, p->selFlags) ){







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







7391
7392
7393
7394
7395
7396
7397
7398
7399
7400
7401
7402
7403
7404
7405
7406
7407
7408
7409
7410
7411
7412
7413
7414
7415
7416
7417
7418
7419
7420
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
      assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 );
    }else{
      TREETRACE(0x4000,pParse,p,("Push-down not possible\n"));
    }

    /* Convert unused result columns of the subquery into simple NULL
    ** expressions, to avoid unneeded searching and computation.
    */
    if( OptimizationEnabled(db, SQLITE_NullUnusedCols)
     && disableUnusedSubqueryResultColumns(pItem)
    ){
#if TREETRACE_ENABLED
      if( sqlite3TreeTrace & 0x4000 ){
        TREETRACE(0x4000,pParse,p,
            ("Change unused result columns to NULL for subquery %d:\n",
             pSub->selId));
        sqlite3TreeViewSelect(0, p, 0);
      }
#endif
    }

    zSavedAuthContext = pParse->zAuthContext;
    pParse->zAuthContext = pItem->zName;

    /* Generate code to implement the subquery
    */
    if( fromClauseTermCanBeCoroutine(pParse, pTabList, i, p->selFlags) ){

Changes to src/sqliteInt.h.

1835
1836
1837
1838
1839
1840
1841

1842
1843
1844
1845
1846
1847
1848
#define SQLITE_BloomPulldown  0x00100000 /* Run Bloom filters early */
#define SQLITE_BalancedMerge  0x00200000 /* Balance multi-way merges */
#define SQLITE_ReleaseReg     0x00400000 /* Use OP_ReleaseReg for testing */
#define SQLITE_FlttnUnionAll  0x00800000 /* Disable the UNION ALL flattener */
   /* TH3 expects this value  ^^^^^^^^^^ See flatten04.test */
#define SQLITE_IndexedExpr    0x01000000 /* Pull exprs from index when able */
#define SQLITE_Coroutines     0x02000000 /* Co-routines for subqueries */

#define SQLITE_AllOpts        0xffffffff /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)







>







1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
#define SQLITE_BloomPulldown  0x00100000 /* Run Bloom filters early */
#define SQLITE_BalancedMerge  0x00200000 /* Balance multi-way merges */
#define SQLITE_ReleaseReg     0x00400000 /* Use OP_ReleaseReg for testing */
#define SQLITE_FlttnUnionAll  0x00800000 /* Disable the UNION ALL flattener */
   /* TH3 expects this value  ^^^^^^^^^^ See flatten04.test */
#define SQLITE_IndexedExpr    0x01000000 /* Pull exprs from index when able */
#define SQLITE_Coroutines     0x02000000 /* Co-routines for subqueries */
#define SQLITE_NullUnusedCols 0x04000000 /* NULL unused columns in subqueries */
#define SQLITE_AllOpts        0xffffffff /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

Changes to test/selectC.test.

225
226
227
228
229
230
231





232
233
234
235
236
237
238
  insert into t_distinct_bug values ('1', '2', 'e');
  insert into t_distinct_bug values ('1', '3', 'f');
} {}

do_execsql_test selectC-4.2 {
  select a from (select distinct a, b from t_distinct_bug)
} {1 1 1}






do_execsql_test selectC-4.3 {
  select a, udf() from (select distinct a, b from t_distinct_bug)
} {1 1 1 2 1 3}

#-------------------------------------------------------------------------
# Test that the problem in ticket #190c2507 has been fixed.







>
>
>
>
>







225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
  insert into t_distinct_bug values ('1', '2', 'e');
  insert into t_distinct_bug values ('1', '3', 'f');
} {}

do_execsql_test selectC-4.2 {
  select a from (select distinct a, b from t_distinct_bug)
} {1 1 1}

do_execsql_test selectC-4.2b {
  CREATE VIEW v42b AS SELECT DISTINCT a, b FROM t_distinct_bug;
  SELECT a FROM v42b;
} {1 1 1}

do_execsql_test selectC-4.3 {
  select a, udf() from (select distinct a, b from t_distinct_bug)
} {1 1 1 2 1 3}

#-------------------------------------------------------------------------
# Test that the problem in ticket #190c2507 has been fixed.

Added test/selectH.test.





























































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
# 2023-02-16
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test cases for the omit-unused-subquery-column optimization.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix selectH

do_execsql_test 1.1 {
  CREATE TABLE t1(
     c0,  c1,  c2,  c3,  c4,  c5,  c6,  c7,  c8,  c9,
     c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
     c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
     c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
     c40, c41, c42, c43, c44, c45, c46, c47, c48, c49,
     c50, c51, c52, c53, c54, c55, c56, c57, c58, c59,
     c60, c61, c62, c63, c64, c65
  );
  INSERT INTO t1 VALUES(
     0,  1,  2,  3,  4,  5,  6,  7,  8,  9,
     10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
     20, 21, 22, 23, 24, 25, 26, 27, 28, 29,
     30, 31, 32, 33, 34, 35, 36, 37, 38, 39,
     40, 41, 42, 43, 44, 45, 46, 47, 48, 49,
     50, 51, 52, 53, 54, 55, 56, 57, 58, 59,
     60, 61, 62, 63, 64, 65
  );
  CREATE INDEX t1c60 ON t1(c60);
}

# The SQL counter(N) function adjusts the value of the global
# TCL variable ::selectH_cnt by the value N and returns the new
# value.  By putting calls to counter(N) as unused columns in a
# view or subquery, we can check to see if the counter gets incremented,
# and if not that means that the unused column was omitted.
#
unset -nocomplain selectH_cnt
set selectH_cnt 0
proc selectH_counter {amt} {
  global selectH_cnt
  incr selectH_cnt $amt
  return $selectH_cnt
}
db func counter selectH_counter

do_execsql_test 1.2 {
  SELECT DISTINCT c44 FROM (
    SELECT c0 AS a, *, counter(1) FROM t1
    UNION ALL
    SELECT c1 AS a, *, counter(1) FROM t1
  ) WHERE c60=60;
} {44}
do_test 1.3 {
  set ::selectH_cnt
} {0}

do_execsql_test 2.1 {
  SELECT a FROM (
    SELECT counter(1) AS cnt, c15 AS a, *, c62 AS b FROM t1
    UNION ALL
    SELECT counter(1) AS cnt, c16 AS a, *, c61 AS b FROM t1
    ORDER BY b
  );
} {16 15}
do_test 2.2 {
  set ::selectH_cnt
} {0}

do_execsql_test 3.1 {
  CREATE VIEW v1 AS
    SELECT c16 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c17 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c18 AS a, *, counter(1) AS x FROM t1
    UNION ALL
    SELECT c19 AS a, *, counter(1) AS x FROM t1;
  SELECT count(*) FROM v1 WHERE c60=60;
} {4}
do_test 3.2 {
  set ::selectH_cnt
} {0}
do_execsql_test 3.3 {
  SELECT count(a) FROM v1 WHERE c60=60;
} {4}
do_execsql_test 3.4 {
  SELECT a FROM v1 WHERE c60=60;
} {16 17 18 19}
do_test 3.5 {
  set ::selectH_cnt
} {0}
do_execsql_test 3.6 {
  SELECT x FROM v1 WHERE c60=60;
} {1 2 3 4}
do_test 3.7 {
  set ::selectH_cnt
} {4}


finish_test

Changes to test/with1.test.

1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
#-------------------------------------------------------------------------
reset_db
do_execsql_test 24.1 {
  CREATE TABLE t1(a, b, c);
  CREATE VIEW v1 AS SELECT max(a), min(b) FROM t1 GROUP BY c;
}
do_test 24.1 {
  set program [db eval {EXPLAIN SELECT 1 FROM v1,v1,v1}]
  expr [lsearch $program OpenDup]>0
} {1}
do_execsql_test 24.2 {
  ATTACH "" AS aux;
  CREATE VIEW aux.v3 AS VALUES(1);
  CREATE VIEW main.v3 AS VALUES(3);








|







1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
#-------------------------------------------------------------------------
reset_db
do_execsql_test 24.1 {
  CREATE TABLE t1(a, b, c);
  CREATE VIEW v1 AS SELECT max(a), min(b) FROM t1 GROUP BY c;
}
do_test 24.1 {
  set program [db eval {EXPLAIN SELECT * FROM v1 AS aa, v1 AS bb, v1 AS cc}]
  expr [lsearch $program OpenDup]>0
} {1}
do_execsql_test 24.2 {
  ATTACH "" AS aux;
  CREATE VIEW aux.v3 AS VALUES(1);
  CREATE VIEW main.v3 AS VALUES(3);