SQLite Forum

v3.40.0 very slow with UNIONs (or so I think)
Login

v3.40.0 very slow with UNIONs (or so I think)

(1) By jose isaias cabrera (jicman) on 2022-11-22 21:51:04 [source]

Greetings!

I have query that Mr. Medcalf helped me create which returns all changes for a field for a given project. This query,

        SELECT ProjID,
               Updated_By,
               InsertDate,
               var,
               oldv,
               newv
        FROM
        (
        
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Finish_Date' as var,
                   (
                      SELECT
                        coalesce(Finish_Date,'') FROM Project_List
                      WHERE ProjID = o.ProjID
                      AND InsertDate < o.InsertDate
                      ORDER BY InsertDate DESC
                      LIMIT 1
                    ) AS oldv,
                   coalesce(Finish_Date,'') as newv
                   FROM Project_List as o
            UNION
        
              SELECT ProjID,
                     Updated_By,
                     InsertDate,
                     'Ann_CapexP' as var,
                     (
                        SELECT
                          replace(round(Ann_CapexP),'.0','') FROM Project_List WHERE
                        ProjID = o.ProjID
                        AND InsertDate < o.InsertDate
                        ORDER BY InsertDate DESC
                        LIMIT 1
                      ) AS oldv,
                      replace(round(Ann_CapexP),'.0','') as newv
                     FROM Project_List as o
              
      )
      WHERE oldv <> newv 
      AND ProjID = 'PR0000020614' 
ORDER BY InsertDate ASC;

Grabs 8 records and takes 0.01 secs to execute. On v3.40.0, the same query grabs 8 records, but it takes 4.44 secs. This query is composed of just two fields. When I add all the desired fields for a record, v3.39.4 grabs 125 records and takes 0.02 secs to execute, while v3.40.0 grabs 125 records and takes 379.41 secs. I ran analyze, thinking that it would fix things, but, it does not. If you give me a spot I will try to get you the db, but everything is so locked up here that sometimes I have to do 'unorthodox' maneuvering to get things out. I had to revert back to 3.39.4. SQL versions being used are vanilla SQLite Windows DLL from the download site.

SQLiteVer: 3.39.4 2022-09-29 15:55:41
SQLiteVer: 3.40.0 2022-11-16 12:10:08

Thanks for the support.

josé

(2) By anonymous on 2022-11-22 22:30:34 in reply to 1 [link] [source]

Can you check the query plan for each, and then compare them? Seems like the query planner is choosing something less than optimal (obviously).

EXPLAIN ....

and

EXPLAIN QUERY PLAN ...

on the two versions.

(also, making sure the 3.40 version compile options are similar to your earlier configuration is worthwhile).

(3) By jose isaias cabrera (jicman) on 2022-11-23 02:53:29 in reply to 2 [link] [source]

Explain with SQLite version 3.39.4 2022-09-29 15:55:41

sqlite> explain
   ...>         SELECT ProjID,
   ...>                Updated_By,
   ...>                InsertDate,
   ...>                var,
   ...>                oldv,
   ...>                newv
   ...>         FROM
   ...>         (
   ...>
   ...>             SELECT ProjID,
   ...>                    Updated_By,
   ...>                    InsertDate,
   ...>                    'Finish_Date' as var,
   ...>                    (
   ...>                       SELECT
   ...>                         coalesce(Finish_Date,'') FROM Project_List
   ...>                       WHERE ProjID = o.ProjID
   ...>                       AND InsertDate < o.InsertDate
   ...>                       ORDER BY InsertDate DESC
   ...>                       LIMIT 1
   ...>                     ) AS oldv,
   ...>                    coalesce(Finish_Date,'') as newv
   ...>                    FROM Project_List as o
   ...>             UNION
   ...>
   ...>               SELECT ProjID,
   ...>                      Updated_By,
   ...>                      InsertDate,
   ...>                      'Ann_CapexP' as var,
   ...>                      (
   ...>                         SELECT
   ...>                           replace(round(Ann_CapexP),'.0','') FROM Project_List WHERE
   ...>                         ProjID = o.ProjID
   ...>                         AND InsertDate < o.InsertDate
   ...>                         ORDER BY InsertDate DESC
   ...>                         LIMIT 1
   ...>                       ) AS oldv,
   ...>                       replace(round(Ann_CapexP),'.0','') as newv
   ...>                      FROM Project_List as o
   ...>
   ...>       )
   ...>       WHERE oldv <> newv
   ...>       AND ProjID = 'PR0000020614'
   ...> ORDER BY InsertDate ASC;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     181   0                    0   Start at 181
1     InitCoroutine  1     152   2                    0   (subquery-4)
2     OpenEphemeral  5     6     0     k(6,B,B,B,B,B,B)  0   nColumn=6
3     OpenRead       3     164394  0     56             0   root=164394 iDb=0; Project_List
4     OpenRead       6     110918  0     k(3,,,)        2   root=110918 iDb=0; PL_ProjID_BL_Start
5     String8        0     2     0     PR0000020614   0   r[2]='PR0000020614'
6     SeekGE         6     72    2     1              0   key=r[2]
7       IdxGT          6     72    2     1              0   key=r[2]
8       DeferredSeek   6     0     3                    0   Move 3 to 6.rowid if needed
9       BeginSubrtn    0     4     0                    0   r[4]=NULL
10        Null           0     5     5                    0   r[5..5]=NULL; Init subquery result
11        Noop           7     3     0                    0
12        Integer        1     6     0                    0   r[6]=1
13        Ne             8     15    7                    67  if r[7]!=r[8] goto 15
14        ZeroOrNull     7     6     8                    0   r[6] = 0 OR NULL
15        MustBeInt      6     0     0                    0   LIMIT counter
16        IfNot          6     32    0                    0
17        OpenRead       4     164394  0     56             0   root=164394 iDb=0; Project_List
18        OpenRead       8     207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
19        String8        0     9     0     PR0000020614   0   r[9]='PR0000020614'
20        IsNull         9     32    0                    0   if r[9]==NULL goto 32
21        Column         3     55    10                   0   r[10]=Project_List.InsertDate
22        IsNull         10    32    0                    0   if r[10]==NULL goto 32
23        SeekLT         8     32    9     2              0   key=r[9..10]
24        Null           0     10    0                    0   r[10]=NULL
25          IdxLE          8     32    9     2              0   key=r[9..10]
26          DeferredSeek   8     0     4                    0   Move 4 to 8.rowid if needed
27          Column         4     7     5                    0   r[5]=Project_List.Finish_Date
28          NotNull        5     30    0                    0   if r[5]!=NULL goto 30
29          String8        0     5     0                    0   r[5]=''
30          DecrJumpZero   6     32    0                    0   if (--r[6])==0 goto 32
31        Prev           8     25    0                    0
32      Return         4     10    1                    0
33      Column         3     7     3                    0   r[3]=Project_List.Finish_Date
34      NotNull        3     36    0                    0   if r[3]!=NULL goto 36
35      String8        0     3     0                    0   r[3]=''
36      Eq             3     71    5     BINARY-8       80  if r[5]==r[3] goto 71
37      Column         6     0     11                   0   r[11]=Project_List.ProjID
38      Column         3     25    12                   0   r[12]=Project_List.Updated_By
39      Column         3     55    13                   0   r[13]=Project_List.InsertDate
40      String8        0     14    0     Finish_Date    0   r[14]='Finish_Date'
41      BeginSubrtn    0     17    0                    0   r[17]=NULL
42        Null           0     18    18                   0   r[18..18]=NULL; Init subquery result
43        Noop           9     3     0                    0
44        Integer        1     19    0                    0   r[19]=1
45        Ne             8     47    7                    67  if r[7]!=r[8] goto 47
46        ZeroOrNull     7     19    8                    0   r[19] = 0 OR NULL
47        MustBeInt      19    0     0                    0   LIMIT counter
48        IfNot          19    64    0                    0
49        OpenRead       4     164394  0     56             0   root=164394 iDb=0; Project_List
50        OpenRead       10    207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
51        Column         6     0     20                   0   r[20]=Project_List.ProjID
52        IsNull         20    64    0                    0   if r[20]==NULL goto 64
53        Column         3     55    21                   0   r[21]=Project_List.InsertDate
54        IsNull         21    64    0                    0   if r[21]==NULL goto 64
55        SeekLT         10    64    20    2              0   key=r[20..21]
56        Null           0     21    0                    0   r[21]=NULL
57          IdxLE          10    64    20    2              0   key=r[20..21]
58          DeferredSeek   10    0     4                    0   Move 4 to 10.rowid if needed
59          Column         4     7     18                   0   r[18]=Project_List.Finish_Date
60          NotNull        18    62    0                    0   if r[18]!=NULL goto 62
61          String8        0     18    0                    0   r[18]=''
62          DecrJumpZero   19    64    0                    0   if (--r[19])==0 goto 64
63        Prev           10    57    0                    0
64      Return         17    42    1                    0
65      SCopy          18    15    0                    0   r[15]=r[18]
66      Column         3     7     16                   0   r[16]=Project_List.Finish_Date
67      NotNull        16    69    0                    0   if r[16]!=NULL goto 69
68      String8        0     16    0                    0   r[16]=''
69      MakeRecord     11    6     22                   0   r[22]=mkrec(r[11..16])
70      IdxInsert      5     22    11    6              0   key=r[22]
71    Next           6     7     0                    0
72    OpenRead       1     164394  0     56             0   root=164394 iDb=0; Project_List
73    OpenRead       11    110918  0     k(3,,,)        2   root=110918 iDb=0; PL_ProjID_BL_Start
74    String8        0     23    0     PR0000020614   0   r[23]='PR0000020614'
75    SeekGE         11    141   23    1              0   key=r[23]
76      IdxGT          11    141   23    1              0   key=r[23]
77      DeferredSeek   11    0     1                    0   Move 1 to 11.rowid if needed
78      BeginSubrtn    0     24    0                    0   r[24]=NULL
79        Null           0     25    25                   0   r[25..25]=NULL; Init subquery result
80        Noop           12    3     0                    0
81        Integer        1     26    0                    0   r[26]=1
82        Ne             8     84    7                    67  if r[7]!=r[8] goto 84
83        ZeroOrNull     7     26    8                    0   r[26] = 0 OR NULL
84        MustBeInt      26    0     0                    0   LIMIT counter
85        IfNot          26    101   0                    0
86        OpenRead       2     164394  0     56             0   root=164394 iDb=0; Project_List
87        OpenRead       13    207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
88        String8        0     27    0     PR0000020614   0   r[27]='PR0000020614'
89        IsNull         27    101   0                    0   if r[27]==NULL goto 101
90        Column         1     55    28                   0   r[28]=Project_List.InsertDate
91        IsNull         28    101   0                    0   if r[28]==NULL goto 101
92        SeekLT         13    101   27    2              0   key=r[27..28]
93        Null           0     28    0                    0   r[28]=NULL
94          IdxLE          13    101   27    2              0   key=r[27..28]
95          DeferredSeek   13    0     2                    0   Move 2 to 13.rowid if needed
96          Column         2     35    32                   0   r[32]=Project_List.Ann_CapexP
97          Function       0     32    29    round(1)       0   r[29]=func(r[32])
98          Function       6     29    25    replace(3)     0   r[25]=func(r[29..31])
99          DecrJumpZero   26    101   0                    0   if (--r[26])==0 goto 101
100       Prev           13    94    0                    0
101     Return         24    79    1                    0
102     Column         1     35    36                   0   r[36]=Project_List.Ann_CapexP
103     Function       0     36    33    round(1)       0   r[33]=func(r[36])
104     Function       6     33    22    replace(3)     0   r[22]=func(r[33..35])
105     Eq             22    140   25    BINARY-8       80  if r[25]==r[22] goto 140
106     Column         11    0     11                   0   r[11]=Project_List.ProjID
107     Column         1     25    12                   0   r[12]=Project_List.Updated_By
108     Column         1     55    13                   0   r[13]=Project_List.InsertDate
109     String8        0     14    0     Ann_CapexP     0   r[14]='Ann_CapexP'
110     BeginSubrtn    0     37    0                    0   r[37]=NULL
111       Null           0     38    38                   0   r[38..38]=NULL; Init subquery result
112       Noop           14    3     0                    0
113       Integer        1     39    0                    0   r[39]=1
114       Ne             8     116   7                    67  if r[7]!=r[8] goto 116
115       ZeroOrNull     7     39    8                    0   r[39] = 0 OR NULL
116       MustBeInt      39    0     0                    0   LIMIT counter
117       IfNot          39    133   0                    0
118       OpenRead       2     164394  0     56             0   root=164394 iDb=0; Project_List
119       OpenRead       15    207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
120       Column         11    0     40                   0   r[40]=Project_List.ProjID
121       IsNull         40    133   0                    0   if r[40]==NULL goto 133
122       Column         1     55    41                   0   r[41]=Project_List.InsertDate
123       IsNull         41    133   0                    0   if r[41]==NULL goto 133
124       SeekLT         15    133   40    2              0   key=r[40..41]
125       Null           0     41    0                    0   r[41]=NULL
126         IdxLE          15    133   40    2              0   key=r[40..41]
127         DeferredSeek   15    0     2                    0   Move 2 to 15.rowid if needed
128         Column         2     35    22                   0   r[22]=Project_List.Ann_CapexP
129         Function       0     22    42    round(1)       0   r[42]=func(r[22])
130         Function       6     42    38    replace(3)     0   r[38]=func(r[42..44])
131         DecrJumpZero   39    133   0                    0   if (--r[39])==0 goto 133
132       Prev           15    126   0                    0
133     Return         37    111   1                    0
134     SCopy          38    15    0                    0   r[15]=r[38]
135     Column         1     35    48                   0   r[48]=Project_List.Ann_CapexP
136     Function       0     48    45    round(1)       0   r[45]=func(r[48])
137     Function       6     45    16    replace(3)     0   r[16]=func(r[45..47])
138     MakeRecord     11    6     48                   0   r[48]=mkrec(r[11..16])
139     IdxInsert      5     48    11    6              0   key=r[48]
140   Next           11    76    0                    0
141   Rewind         5     150   0                    0
142     Column         5     0     49                   0   r[49]=ProjID
143     Column         5     1     50                   0   r[50]=Updated_By
144     Column         5     2     51                   0   r[51]=InsertDate
145     Column         5     3     52                   0   r[52]=var
146     Column         5     4     53                   0   r[53]=oldv
147     Column         5     5     54                   0   r[54]=newv
148     Yield          1     0     0                    0
149   Next           5     142   0                    0
150   Close          5     0     0                    0
151   EndCoroutine   1     0     0                    0
152   SorterOpen     16    8     0     k(1,B)         0
153   InitCoroutine  1     0     2                    0
154     Yield          1     169   0                    0   next row of (subquery-4)
155     Copy           53    55    0                    2   r[55]=r[53]; (subquery-4).oldv
156     Copy           54    56    0                    2   r[56]=r[54]; (subquery-4).newv
157     Eq             56    168   55    BINARY-8       80  if r[55]==r[56] goto 168
158     Copy           49    56    0                    2   r[56]=r[49]; (subquery-4).ProjID
159     Ne             57    168   56    BINARY-8       81  if r[56]!=r[57] goto 168
160     Copy           49    59    0                    2   r[59]=r[49]; (subquery-4).ProjID
161     Copy           50    60    0                    2   r[60]=r[50]; (subquery-4).Updated_By
162     Copy           52    61    0                    2   r[61]=r[52]; (subquery-4).var
163     Copy           53    62    0                    2   r[62]=r[53]; (subquery-4).oldv
164     Copy           54    63    0                    2   r[63]=r[54]; (subquery-4).newv
165     Copy           51    58    0                    2   r[58]=r[51]; (subquery-4).InsertDate
166     MakeRecord     58    6     65                   0   r[65]=mkrec(r[58..63])
167     SorterInsert   16    65    58    6              0   key=r[65]
168   Goto           0     154   0                    0
169   OpenPseudo     17    66    8                    0   8 columns in r[66]
170   SorterSort     16    180   0                    0
171     SorterData     16    66    17                   0   r[66]=data
172     Column         17    5     64                   0   r[64]=newv
173     Column         17    4     63                   0   r[63]=oldv
174     Column         17    3     62                   0   r[62]=var
175     Column         17    0     61                   0   r[61]=InsertDate
176     Column         17    2     60                   0   r[60]=Updated_By
177     Column         17    1     59                   0   r[59]=ProjID
178     ResultRow      59    6     0                    0   output=r[59..64]
179   SorterNext     16    171   0                    0
180   Halt           0     0     0                    0
181   Transaction    0     0     1155  0              1   usesStmtJournal=0
182   Integer        1     7     0                    0   r[7]=1
183   Integer        0     8     0                    0   r[8]=0
184   String8        0     30    0     .0             0   r[30]='.0'
185   String8        0     31    0                    0   r[31]=''
186   String8        0     34    0     .0             0   r[34]='.0'
187   String8        0     35    0                    0   r[35]=''
188   String8        0     43    0     .0             0   r[43]='.0'
189   String8        0     44    0                    0   r[44]=''
190   String8        0     46    0     .0             0   r[46]='.0'
191   String8        0     47    0                    0   r[47]=''
192   String8        0     57    0     PR0000020614   0   r[57]='PR0000020614'
193   Goto           0     1     0                    0
Run Time: real 0.316 user 0.031250 sys 0.078125

Explain with SQLite version 3.40.0 2022-11-16 12:10:08

sqlite> explain
   ...>         SELECT ProjID,
   ...>                Updated_By,
   ...>                InsertDate,
   ...>                var,
   ...>                oldv,
   ...>                newv
   ...>         FROM
   ...>         (
   ...>
   ...>             SELECT ProjID,
   ...>                    Updated_By,
   ...>                    InsertDate,
   ...>                    'Finish_Date' as var,
   ...>                    (
   ...>                       SELECT
   ...>                         coalesce(Finish_Date,'') FROM Project_List
   ...>                       WHERE ProjID = o.ProjID
   ...>                       AND InsertDate < o.InsertDate
   ...>                       ORDER BY InsertDate DESC
   ...>                       LIMIT 1
   ...>                     ) AS oldv,
   ...>                    coalesce(Finish_Date,'') as newv
   ...>                    FROM Project_List as o
   ...>             UNION
   ...>
   ...>               SELECT ProjID,
   ...>                      Updated_By,
   ...>                      InsertDate,
   ...>                      'Ann_CapexP' as var,
   ...>                      (
   ...>                         SELECT
   ...>                           replace(round(Ann_CapexP),'.0','') FROM Project_List WHERE
   ...>                         ProjID = o.ProjID
   ...>                         AND InsertDate < o.InsertDate
   ...>                         ORDER BY InsertDate DESC
   ...>                         LIMIT 1
   ...>                       ) AS oldv,
   ...>                       replace(round(Ann_CapexP),'.0','') as newv
   ...>                      FROM Project_List as o
   ...>
   ...>       )
   ...>       WHERE oldv <> newv
   ...>       AND ProjID = 'PR0000020614'
   ...> ORDER BY InsertDate ASC;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     117   0                    0   Start at 117
1     InitCoroutine  1     88    2                    0   (subquery-4)
2     OpenEphemeral  5     6     0     k(6,B,B,B,B,B,B)  0   nColumn=6
3     OpenRead       3     164394  0     56             0   root=164394 iDb=0; Project_List
4     Rewind         3     40    0                    0
5       Column         3     0     2                    0   r[2]= cursor 3 column 0
6       Column         3     25    3                    0   r[3]= cursor 3 column 25
7       Column         3     55    4                    0   r[4]= cursor 3 column 55
8       String8        0     5     0     Finish_Date    0   r[5]='Finish_Date'
9       BeginSubrtn    0     8     0                    0   r[8]=NULL
10        Null           0     9     9                    0   r[9..9]=NULL; Init subquery result
11        Noop           6     3     0                    0
12        Integer        1     10    0                    0   r[10]=1
13        Ne             12    15    11                   67  if r[11]!=r[12] goto 15
14        ZeroOrNull     11    10    12                   0   r[10] = 0 OR NULL
15        MustBeInt      10    0     0                    0   LIMIT counter
16        IfNot          10    32    0                    0
17        OpenRead       4     164394  0     56             0   root=164394 iDb=0; Project_List
18        OpenRead       7     207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
19        Column         3     0     13                   0   r[13]= cursor 3 column 0
20        IsNull         13    32    0                    0   if r[13]==NULL goto 32
21        Column         3     55    14                   0   r[14]= cursor 3 column 55
22        IsNull         14    32    0                    0   if r[14]==NULL goto 32
23        SeekLT         7     32    13    2              0   key=r[13..14]
24        Null           0     14    0                    0   r[14]=NULL
25          IdxLE          7     32    13    2              0   key=r[13..14]
26          DeferredSeek   7     0     4                    0   Move 4 to 7.rowid if needed
27          Column         4     7     9                    0   r[9]= cursor 4 column 7
28          NotNull        9     30    0                    0   if r[9]!=NULL goto 30
29          String8        0     9     0                    0   r[9]=''
30          DecrJumpZero   10    32    0                    0   if (--r[10])==0 goto 32
31        Prev           7     25    0                    0
32      Return         8     10    1                    0
33      SCopy          9     6     0                    0   r[6]=r[9]
34      Column         3     7     7                    0   r[7]= cursor 3 column 7
35      NotNull        7     37    0                    0   if r[7]!=NULL goto 37
36      String8        0     7     0                    0   r[7]=''
37      MakeRecord     2     6     15                   0   r[15]=mkrec(r[2..7])
38      IdxInsert      5     15    2     6              0   key=r[15]
39    Next           3     5     0                    1
40    OpenRead       1     164394  0     56             0   root=164394 iDb=0; Project_List
41    Rewind         1     77    0                    0
42      Column         1     0     2                    0   r[2]= cursor 1 column 0
43      Column         1     25    3                    0   r[3]= cursor 1 column 25
44      Column         1     55    4                    0   r[4]= cursor 1 column 55
45      String8        0     5     0     Ann_CapexP     0   r[5]='Ann_CapexP'
46      BeginSubrtn    0     16    0                    0   r[16]=NULL
47        Null           0     17    17                   0   r[17..17]=NULL; Init subquery result
48        Noop           8     3     0                    0
49        Integer        1     18    0                    0   r[18]=1
50        Ne             12    52    11                   67  if r[11]!=r[12] goto 52
51        ZeroOrNull     11    18    12                   0   r[18] = 0 OR NULL
52        MustBeInt      18    0     0                    0   LIMIT counter
53        IfNot          18    69    0                    0
54        OpenRead       2     164394  0     56             0   root=164394 iDb=0; Project_List
55        OpenRead       9     207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
56        Column         1     0     19                   0   r[19]= cursor 1 column 0
57        IsNull         19    69    0                    0   if r[19]==NULL goto 69
58        Column         1     55    20                   0   r[20]= cursor 1 column 55
59        IsNull         20    69    0                    0   if r[20]==NULL goto 69
60        SeekLT         9     69    19    2              0   key=r[19..20]
61        Null           0     20    0                    0   r[20]=NULL
62          IdxLE          9     69    19    2              0   key=r[19..20]
63          DeferredSeek   9     0     2                    0   Move 2 to 9.rowid if needed
64          Column         2     35    15                   0   r[15]= cursor 2 column 35
65          Function       0     15    21    round(1)       0   r[21]=func(r[15])
66          Function       6     21    17    replace(3)     0   r[17]=func(r[21..23])
67          DecrJumpZero   18    69    0                    0   if (--r[18])==0 goto 69
68        Prev           9     62    0                    0
69      Return         16    47    1                    0
70      SCopy          17    6     0                    0   r[6]=r[17]
71      Column         1     35    27                   0   r[27]= cursor 1 column 35
72      Function       0     27    24    round(1)       0   r[24]=func(r[27])
73      Function       6     24    7     replace(3)     0   r[7]=func(r[24..26])
74      MakeRecord     2     6     27                   0   r[27]=mkrec(r[2..7])
75      IdxInsert      5     27    2     6              0   key=r[27]
76    Next           1     42    0                    1
77    Rewind         5     86    0                    0
78      Column         5     0     28                   0   r[28]=ProjID
79      Column         5     1     29                   0   r[29]=Updated_By
80      Column         5     2     30                   0   r[30]=InsertDate
81      Column         5     3     31                   0   r[31]=var
82      Column         5     4     32                   0   r[32]=oldv
83      Column         5     5     33                   0   r[33]=newv
84      Yield          1     0     0                    0
85    Next           5     78    0                    0
86    Close          5     0     0                    0
87    EndCoroutine   1     0     0                    0
88    SorterOpen     10    8     0     k(1,B)         0
89    InitCoroutine  1     0     2                    0
90      Yield          1     105   0                    0   next row of (subquery-4)
91      Copy           32    34    0                    2   r[34]=r[32]
92      Copy           33    35    0                    2   r[35]=r[33]
93      Eq             35    104   34    BINARY-8       80  if r[34]==r[35] goto 104
94      Copy           28    35    0                    2   r[35]=r[28]
95      Ne             36    104   35    BINARY-8       81  if r[35]!=r[36] goto 104
96      Copy           28    38    0                    2   r[38]=r[28]
97      Copy           29    39    0                    2   r[39]=r[29]
98      Copy           31    40    0                    2   r[40]=r[31]
99      Copy           32    41    0                    2   r[41]=r[32]
100     Copy           33    42    0                    2   r[42]=r[33]
101     Copy           30    37    0                    2   r[37]=r[30]
102     MakeRecord     37    6     44                   0   r[44]=mkrec(r[37..42])
103     SorterInsert   10    44    37    6              0   key=r[44]
104   Goto           0     90    0                    0
105   OpenPseudo     11    45    8                    0   8 columns in r[45]
106   SorterSort     10    116   0                    0
107     SorterData     10    45    11                   0   r[45]=data
108     Column         11    5     43                   0   r[43]=newv
109     Column         11    4     42                   0   r[42]=oldv
110     Column         11    3     41                   0   r[41]=var
111     Column         11    0     40                   0   r[40]=InsertDate
112     Column         11    2     39                   0   r[39]=Updated_By
113     Column         11    1     38                   0   r[38]=ProjID
114     ResultRow      38    6     0                    0   output=r[38..43]
115   SorterNext     10    107   0                    0
116   Halt           0     0     0                    0
117   Transaction    0     0     1155  0              1   usesStmtJournal=0
118   Integer        1     11    0                    0   r[11]=1
119   Integer        0     12    0                    0   r[12]=0
120   String8        0     22    0     .0             0   r[22]='.0'
121   String8        0     23    0                    0   r[23]=''
122   String8        0     25    0     .0             0   r[25]='.0'
123   String8        0     26    0                    0   r[26]=''
124   String8        0     36    0     PR0000020614   0   r[36]='PR0000020614'
125   Goto           0     1     0                    0
Run Time: real 0.197 user 0.031250 sys 0.078125

Thanks for the support.

(4) By jose isaias cabrera (jicman) on 2022-11-23 03:02:18 in reply to 2 [link] [source]

Explain query plan with SQLite version 3.39.4 2022-09-29 15:55:41

sqlite> explain query plan
   ...>         SELECT ProjID,
   ...>                Updated_By,
   ...>                InsertDate,
   ...>                var,
   ...>                oldv,
   ...>                newv
   ...>         FROM
   ...>         (
   ...>
   ...>             SELECT ProjID,
   ...>                    Updated_By,
   ...>                    InsertDate,
   ...>                    'Finish_Date' as var,
   ...>                    (
   ...>                       SELECT
   ...>                         coalesce(Finish_Date,'') FROM Project_List
   ...>                       WHERE ProjID = o.ProjID
   ...>                       AND InsertDate < o.InsertDate
   ...>                       ORDER BY InsertDate DESC
   ...>                       LIMIT 1
   ...>                     ) AS oldv,
   ...>                    coalesce(Finish_Date,'') as newv
   ...>                    FROM Project_List as o
   ...>             UNION
   ...>
   ...>               SELECT ProjID,
   ...>                      Updated_By,
   ...>                      InsertDate,
   ...>                      'Ann_CapexP' as var,
   ...>                      (
   ...>                         SELECT
   ...>                           replace(round(Ann_CapexP),'.0','') FROM Project_List WHERE
   ...>                         ProjID = o.ProjID
   ...>                         AND InsertDate < o.InsertDate
   ...>                         ORDER BY InsertDate DESC
   ...>                         LIMIT 1
   ...>                       ) AS oldv,
   ...>                       replace(round(Ann_CapexP),'.0','') as newv
   ...>                      FROM Project_List as o
   ...>
   ...>       )
   ...>       WHERE oldv <> newv
   ...>       AND ProjID = 'PR0000020614'
   ...> ORDER BY InsertDate ASC;
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)
|     |  |--CORRELATED SCALAR SUBQUERY 1
|     |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)
|        |--CORRELATED SCALAR SUBQUERY 3
|        |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY
Run Time: real 0.012 user 0.000000 sys 0.000000
sqlite>

Explain query plan with SQLite version 3.40.0 2022-11-16 12:10:08

sqlite> explain query plan
   ...>         SELECT ProjID,
   ...>                Updated_By,
   ...>                InsertDate,
   ...>                var,
   ...>                oldv,
   ...>                newv
   ...>         FROM
   ...>         (
   ...>
   ...>             SELECT ProjID,
   ...>                    Updated_By,
   ...>                    InsertDate,
   ...>                    'Finish_Date' as var,
   ...>                    (
   ...>                       SELECT
   ...>                         coalesce(Finish_Date,'') FROM Project_List
   ...>                       WHERE ProjID = o.ProjID
   ...>                       AND InsertDate < o.InsertDate
   ...>                       ORDER BY InsertDate DESC
   ...>                       LIMIT 1
   ...>                     ) AS oldv,
   ...>                    coalesce(Finish_Date,'') as newv
   ...>                    FROM Project_List as o
   ...>             UNION
   ...>
   ...>               SELECT ProjID,
   ...>                      Updated_By,
   ...>                      InsertDate,
   ...>                      'Ann_CapexP' as var,
   ...>                      (
   ...>                         SELECT
   ...>                           replace(round(Ann_CapexP),'.0','') FROM Project_List WHERE
   ...>                         ProjID = o.ProjID
   ...>                         AND InsertDate < o.InsertDate
   ...>                         ORDER BY InsertDate DESC
   ...>                         LIMIT 1
   ...>                       ) AS oldv,
   ...>                       replace(round(Ann_CapexP),'.0','') as newv
   ...>                      FROM Project_List as o
   ...>
   ...>       )
   ...>       WHERE oldv <> newv
   ...>       AND ProjID = 'PR0000020614'
   ...> ORDER BY InsertDate ASC;
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SCAN o
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SCAN o
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY
Run Time: real 0.009 user 0.000000 sys 0.000000
sqlite>

Thanks for the support.

(5) By anonymous on 2022-11-23 03:54:24 in reply to 4 [link] [source]

Not an expert, yet obviously, 3.39.4 is using indexes (SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)) that 3.40 is not (scan o).

Assume you have read https://www.sqlite.org/queryplanner-ng.html#howtofix

IMO:

You might try inserting INDEXED BY clauses on table o sub-queries as in https://www.sqlite.org/lang_indexedby.html

As per the first doc, if that works, then figure out how to write the query without the INDEXED BY clauses. In otherwords, using it as a debug tool isn't bad if you use it to find out why the query planner is getting confused (and scanning instead).

(6) By anonymous on 2022-11-23 04:44:31 in reply to 4 [link] [source]

You might also experiment with OFFSET 0 (added to the LIMIT 1) to avoid having the query flattened and see if that helps.

also check out the doc https://www.sqlite.org/optoverview.html , particularly section 11. An OFFSET 0 (even when only in the sub-query) will avoid flattening, rather than LIMIT which must be in both inner an outer queries to avoid flattening).

If adding OFFSET 0 to those LIMIT 1 clauses helps then I suspect the slowdown has to do with query flattening and then the query planner choosing a scan rather than the previous behaviour.

It will be interesting to see what you come up with.

(7) By anonymous on 2022-11-23 05:03:43 in reply to 4 [link] [source]

One last item (as if you didn't have enough to check already!):

refer to https://www.sqlite.org/changes.html

Particularly at query planner changes from the change log item 3-d, and 3-e.

My hunch, presuming analyze was run properly, is that it's within those changes.

It's just a guess though.. so I'm interested what you find.

(9) By jose isaias cabrera (jicman) on 2022-11-23 13:40:35 in reply to 7 [link] [source]

Thanks for all the explanations, but, what I expect from a library or software is that if something is correctly written, syntax-wise, and it was working well in a previous versions, the next version should be very close to the same. I would even expect, as in this situation to lose a few hundreds of a second, because of a fix, but to go from 0.08 seconds to 379.41 seconds, that's a huge difference. I will keep using 3.39.4 until a new version gives me close to what I have now. Thanks for your input.

(10) By Richard Hipp (drh) on 2022-11-23 13:51:54 in reply to 9 [link] [source]

It is unlikely to be "fixed" unless you provide us with a test case. A suitable test case might be either of these:

  1. A database file together with a query that runs significantly slower in 3.40.

  2. An SQL script that first constructs a database from scratch, and then runs a query that is significantly slower in 3.40.

The second option is preferred.

(11) By jose isaias cabrera (jicman) on 2022-11-23 16:05:09 in reply to 10 [link] [source]

I tried creating a script, but, it's not working. Probably because the . Also, there are a lot of records, and I was only trying to use 100 records, and that may not be sufficient to cause the slow down for the query. Option one is the choice that I can provide. Where can I place the DB? It's 827MB. Thanks.

josé

(14) By anonymous on 2022-11-23 18:16:52 in reply to 11 [link] [source]

While I well understand your frustration, following the advice of Dr Hipp (and the other well known forum members) is recommended. That extra effort will not only increase the chance of others putting in effort, but it is one of ways open source can actually work.

For your own purposes, you will want the ability to generate a db from scratch, preferably with test data. If you do not already have that, now (or yesterday) is a great time to set it up.

keith noted the WHERE was not being pushed to the outer select (i presume that was what was previously using an index and a scan in 3.40). You might look at placing one there as a debugging effort.

in any event, Dr Hipp and Keith are the best of the best, so my advice is follow their advice.

(16) By jose isaias cabrera (jicman) on 2022-11-23 19:20:23 in reply to 14 [link] [source]

Thanks for the understanding. :-)

I have been using SQLite since 2006, and I had never had a regression. Actually I've seen queries response faster. But, when I ran this report and it just hang, I thought something was really wrong. :-) I was not frustrated. I was just trying to get to the bottom of it. I even downsized the DB (from 847MB to 243) to provide a sample DB that has the problem to Dr. Hipp and the developers. So, if the developers are still interested, I can provide the DB with the scripts that will show the problem right away.

Right now, I am happy with the UNION ALL change. I had to compile a new version of the front-end program to replace the UNION vs UNION ALL, but that is a piece of cake. :-) Thanks for hanging in there with your support. :-)

josé

(8.1) By Keith Medcalf (kmedcalf) on 2022-11-23 07:50:43 edited from 8.0 in reply to 4 [link] [source]

The later version is not pushing the outer WHERE clause into the branches of the UNION.

(12) By Keith Medcalf (kmedcalf) on 2022-11-23 18:10:15 in reply to 1 [link] [source]

Try this one which pushes the where clause down by manually ...

  SELECT ProjID,
         Updated_By,
         InsertDate,
         var,
         oldv,
         newv
    FROM (
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Finish_Date' as var,
                   (
                      SELECT coalesce(Finish_Date,'')
                        FROM Project_List
                       WHERE ProjID = o.ProjID
                         AND InsertDate < o.InsertDate
                    ORDER BY InsertDate DESC
                       LIMIT 1
                   ) AS oldv,
                   coalesce(Finish_Date,'') as newv
              FROM Project_List as o
             WHERE ProjID = 'PR0000020614'
          UNION
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Ann_CapexP' as var,
                   (
                       SELECT replace(round(Ann_CapexP),'.0','')
                         FROM Project_List
                        WHERE ProjID = o.ProjID
                          AND InsertDate < o.InsertDate
                     ORDER BY InsertDate DESC
                        LIMIT 1
                   ) AS oldv,
                   replace(round(Ann_CapexP),'.0','') as newv
              FROM Project_List as o
             WHERE ProjID = 'PR0000020614'
         )
   WHERE oldv <> newv
ORDER BY InsertDate ASC;

You could also push down the WHERE oldv <> newv into each branch as AND oldv <> newv

See what happens.

(13) By Keith Medcalf (kmedcalf) on 2022-11-23 18:15:15 in reply to 12 [link] [source]

Or this:

  SELECT ProjID,
         Updated_By,
         InsertDate,
         var,
         oldv,
         newv
    FROM (
          (SELECT * FROM (
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Finish_Date' as var,
                   (
                      SELECT coalesce(Finish_Date,'')
                        FROM Project_List
                       WHERE ProjID = o.ProjID
                         AND InsertDate < o.InsertDate
                    ORDER BY InsertDate DESC
                       LIMIT 1
                   ) AS oldv,
                   coalesce(Finish_Date,'') as newv
              FROM Project_List as o
             WHERE ProjID = 'PR0000020614'
                         )
                   WHERE newv <> oldv
          )
          UNION ALL
          (SELECT * FROM (
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Ann_CapexP' as var,
                   (
                       SELECT replace(round(Ann_CapexP),'.0','')
                         FROM Project_List
                        WHERE ProjID = o.ProjID
                          AND InsertDate < o.InsertDate
                     ORDER BY InsertDate DESC
                        LIMIT 1
                   ) AS oldv,
                   replace(round(Ann_CapexP),'.0','') as newv
              FROM Project_List as o
             WHERE ProjID = 'PR0000020614'
                         )
                   WHERE newv <> oldv
          )
         )
ORDER BY InsertDate ASC;

(17) By anonymous on 2022-11-24 00:40:34 in reply to 13 [link] [source]

Keith, is the reason that the union all (vs union) does the trick in this case due to transient indices not being used on UNION ALL? ( section 2.8 of https://www.sqlite.org/tempfiles.html#transient_indices ).

If it's not too much to explain, could you describe how your UNION ALL version accomplishes the pushing out of the WHERE, and if/why is that solution preferable?

Also, any guidance on when to reach for such a solution would be educational as well.

thanks!

(18) By Keith Medcalf (kmedcalf) on 2022-11-24 01:20:09 in reply to 17 [link] [source]

A UNION B merges A and B returning only DISTINCT rows.
A UNION ALL B merges A and B returning all rows.

UNION uses a B-Tree so that it can tell if the row is a duplicate.
UNION ALL does not.

I don't know why (A UNION B) acts as an optimization barrier while (A UNION ALL B) does not.

Richard may be able to answer that one.

(19) By jose isaias cabrera (jicman) on 2022-11-24 02:53:52 in reply to 18 [link] [source]

This is the explain for the UNION ALL on 3.40.0

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     173   0                    0   Start at 173
1     InitCoroutine  1     73    2                    0   left SELECT
2     Noop           5     8     0                    0
3     OpenRead       3     164394  0     56             0   root=164394 iDb=0; Project_List
4     OpenRead       6     207716  0     k(3,,,)        2   root=207716 iDb=0; PL_ProjID_InsertDate_New
5     String8        0     5     0     PR0000020614   0   r[5]='PR0000020614'
6     SeekGE         6     72    5     1              0   key=r[5]
7       IdxGT          6     72    5     1              0   key=r[5]
8       DeferredSeek   6     0     3                    0   Move 3 to 6.rowid if needed
9       BeginSubrtn    0     7     0                    0   r[7]=NULL
10        Null           0     8     8                    0   r[8..8]=NULL; Init subquery result
11        Noop           7     3     0                    0
12        Integer        1     9     0                    0   r[9]=1
13        Ne             11    15    10                   67  if r[10]!=r[11] goto 15
14        ZeroOrNull     10    9     11                   0   r[9] = 0 OR NULL
15        MustBeInt      9     0     0                    0   LIMIT counter
16        IfNot          9     32    0                    0
17        OpenRead       4     164394  0     56             0   root=164394 iDb=0; Project_List
18        OpenRead       8     207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
19        String8        0     12    0     PR0000020614   0   r[12]='PR0000020614'
20        IsNull         12    32    0                    0   if r[12]==NULL goto 32
21        Column         6     1     13                   0   r[13]= cursor 6 column 1
22        IsNull         13    32    0                    0   if r[13]==NULL goto 32
23        SeekLT         8     32    12    2              0   key=r[12..13]
24        Null           0     13    0                    0   r[13]=NULL
25          IdxLE          8     32    12    2              0   key=r[12..13]
26          DeferredSeek   8     0     4                    0   Move 4 to 8.rowid if needed
27          Column         4     7     8                    0   r[8]= cursor 4 column 7
28          NotNull        8     30    0                    0   if r[8]!=NULL goto 30
29          String8        0     8     0                    0   r[8]=''
30          DecrJumpZero   9     32    0                    0   if (--r[9])==0 goto 32
31        Prev           8     25    0                    0
32      Return         7     10    1                    0
33      Column         3     7     6                    0   r[6]= cursor 3 column 7
34      NotNull        6     36    0                    0   if r[6]!=NULL goto 36
35      String8        0     6     0                    0   r[6]=''
36      Eq             6     71    8     BINARY-8       80  if r[8]==r[6] goto 71
37      Column         6     0     14                   0   r[14]= cursor 6 column 0
38      Column         3     25    15                   0   r[15]= cursor 3 column 25
39      Column         6     1     16                   0   r[16]= cursor 6 column 1
40      String8        0     17    0     Finish_Date    0   r[17]='Finish_Date'
41      BeginSubrtn    0     20    0                    0   r[20]=NULL
42        Null           0     21    21                   0   r[21..21]=NULL; Init subquery result
43        Noop           9     3     0                    0
44        Integer        1     22    0                    0   r[22]=1
45        Ne             11    47    10                   67  if r[10]!=r[11] goto 47
46        ZeroOrNull     10    22    11                   0   r[22] = 0 OR NULL
47        MustBeInt      22    0     0                    0   LIMIT counter
48        IfNot          22    64    0                    0
49        OpenRead       4     164394  0     56             0   root=164394 iDb=0; Project_List
50        OpenRead       10    207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
51        Column         6     0     23                   0   r[23]= cursor 6 column 0
52        IsNull         23    64    0                    0   if r[23]==NULL goto 64
53        Column         6     1     24                   0   r[24]= cursor 6 column 1
54        IsNull         24    64    0                    0   if r[24]==NULL goto 64
55        SeekLT         10    64    23    2              0   key=r[23..24]
56        Null           0     24    0                    0   r[24]=NULL
57          IdxLE          10    64    23    2              0   key=r[23..24]
58          DeferredSeek   10    0     4                    0   Move 4 to 10.rowid if needed
59          Column         4     7     21                   0   r[21]= cursor 4 column 7
60          NotNull        21    62    0                    0   if r[21]!=NULL goto 62
61          String8        0     21    0                    0   r[21]=''
62          DecrJumpZero   22    64    0                    0   if (--r[22])==0 goto 64
63        Prev           10    57    0                    0
64      Return         20    42    1                    0
65      Copy           21    18    0                    0   r[18]=r[21]
66      Column         3     7     19                   0   r[19]= cursor 3 column 7
67      NotNull        19    69    0                    0   if r[19]!=NULL goto 69
68      String8        0     19    0                    0   r[19]=''
69      ClrSubtype     19    0     0                    0   r[19].subtype = 0
70      Yield          1     0     0                    0
71    Next           6     7     0                    0
72    EndCoroutine   1     0     0                    0
73    InitCoroutine  2     167   74                   0   right SELECT
74    Noop           11    8     0                    0
75    OpenRead       1     164394  0     56             0   root=164394 iDb=0; Project_List
76    OpenRead       12    207716  0     k(3,,,)        2   root=207716 iDb=0; PL_ProjID_InsertDate_New
77    String8        0     25    0     PR0000020614   0   r[25]='PR0000020614'
78    SeekGE         12    144   25    1              0   key=r[25]
79      IdxGT          12    144   25    1              0   key=r[25]
80      DeferredSeek   12    0     1                    0   Move 1 to 12.rowid if needed
81      BeginSubrtn    0     27    0                    0   r[27]=NULL
82        Null           0     28    28                   0   r[28..28]=NULL; Init subquery result
83        Noop           13    3     0                    0
84        Integer        1     29    0                    0   r[29]=1
85        Ne             11    87    10                   67  if r[10]!=r[11] goto 87
86        ZeroOrNull     10    29    11                   0   r[29] = 0 OR NULL
87        MustBeInt      29    0     0                    0   LIMIT counter
88        IfNot          29    104   0                    0
89        OpenRead       2     164394  0     56             0   root=164394 iDb=0; Project_List
90        OpenRead       14    207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
91        String8        0     30    0     PR0000020614   0   r[30]='PR0000020614'
92        IsNull         30    104   0                    0   if r[30]==NULL goto 104
93        Column         12    1     31                   0   r[31]= cursor 12 column 1
94        IsNull         31    104   0                    0   if r[31]==NULL goto 104
95        SeekLT         14    104   30    2              0   key=r[30..31]
96        Null           0     31    0                    0   r[31]=NULL
97          IdxLE          14    104   30    2              0   key=r[30..31]
98          DeferredSeek   14    0     2                    0   Move 2 to 14.rowid if needed
99          Column         2     35    35                   0   r[35]= cursor 2 column 35
100         Function       0     35    32    round(1)       0   r[32]=func(r[35])
101         Function       6     32    28    replace(3)     0   r[28]=func(r[32..34])
102         DecrJumpZero   29    104   0                    0   if (--r[29])==0 goto 104
103       Prev           14    97    0                    0
104     Return         27    82    1                    0
105     Column         1     35    39                   0   r[39]= cursor 1 column 35
106     Function       0     39    36    round(1)       0   r[36]=func(r[39])
107     Function       6     36    26    replace(3)     0   r[26]=func(r[36..38])
108     Eq             26    143   28    BINARY-8       80  if r[28]==r[26] goto 143
109     Column         12    0     40                   0   r[40]= cursor 12 column 0
110     Column         1     25    41                   0   r[41]= cursor 1 column 25
111     Column         12    1     42                   0   r[42]= cursor 12 column 1
112     String8        0     43    0     Ann_CapexP     0   r[43]='Ann_CapexP'
113     BeginSubrtn    0     46    0                    0   r[46]=NULL
114       Null           0     47    47                   0   r[47..47]=NULL; Init subquery result
115       Noop           15    3     0                    0
116       Integer        1     48    0                    0   r[48]=1
117       Ne             11    119   10                   67  if r[10]!=r[11] goto 119
118       ZeroOrNull     10    48    11                   0   r[48] = 0 OR NULL
119       MustBeInt      48    0     0                    0   LIMIT counter
120       IfNot          48    136   0                    0
121       OpenRead       2     164394  0     56             0   root=164394 iDb=0; Project_List
122       OpenRead       16    207716  0     k(3,,,)        0   root=207716 iDb=0; PL_ProjID_InsertDate_New
123       Column         12    0     49                   0   r[49]= cursor 12 column 0
124       IsNull         49    136   0                    0   if r[49]==NULL goto 136
125       Column         12    1     50                   0   r[50]= cursor 12 column 1
126       IsNull         50    136   0                    0   if r[50]==NULL goto 136
127       SeekLT         16    136   49    2              0   key=r[49..50]
128       Null           0     50    0                    0   r[50]=NULL
129         IdxLE          16    136   49    2              0   key=r[49..50]
130         DeferredSeek   16    0     2                    0   Move 2 to 16.rowid if needed
131         Column         2     35    26                   0   r[26]= cursor 2 column 35
132         Function       0     26    51    round(1)       0   r[51]=func(r[26])
133         Function       6     51    47    replace(3)     0   r[47]=func(r[51..53])
134         DecrJumpZero   48    136   0                    0   if (--r[48])==0 goto 136
135       Prev           16    129   0                    0
136     Return         46    114   1                    0
137     Copy           47    44    0                    0   r[44]=r[47]
138     Column         1     35    57                   0   r[57]= cursor 1 column 35
139     Function       0     57    54    round(1)       0   r[54]=func(r[57])
140     Function       6     54    45    replace(3)     0   r[45]=func(r[54..56])
141     ClrSubtype     45    0     0                    0   r[45].subtype = 0
142     Yield          2     0     0                    0
143   Next           12    79    0                    0
144   EndCoroutine   2     0     0                    0
145   Noop           0     0     0                    0   Output routine for A
146   ResultRow      14    6     0                    0   output=r[14..19]
147   Return         3     0     0                    0
148   Noop           0     0     0                    0   Output routine for B
149   ResultRow      40    6     0                    0   output=r[40..45]
150   Return         4     0     0                    0
151   Noop           0     0     0                    0   eof-A subroutine
152   Gosub          4     149   0                    0
153   Yield          2     172   0                    0
154   Goto           0     152   0                    0
155   Noop           0     0     0                    0   eof-B subroutine
156   Gosub          3     146   0                    0
157   Yield          1     172   0                    0
158   Goto           0     156   0                    0
159   Noop           0     0     0                    0   A-lt-B subroutine
160   Gosub          3     146   0                    0
161   Yield          1     152   0                    0
162   Goto           0     169   0                    0
163   Noop           0     0     0                    0   A-gt-B subroutine
164   Gosub          4     149   0                    0
165   Yield          2     156   0                    0
166   Goto           0     169   0                    0
167   Yield          1     153   0                    0
168   Yield          2     156   0                    0
169   Permutation    0     0     0     [2]            0
170   Compare        14    40    1     k(2,B,)        1   r[14] <-> r[40]
171   Jump           160   160   164                  0
172   Halt           0     0     0                    0
173   Transaction    0     0     1157  0              1   usesStmtJournal=0
174   Integer        1     10    0                    0   r[10]=1
175   Integer        0     11    0                    0   r[11]=0
176   String8        0     33    0     .0             0   r[33]='.0'
177   String8        0     34    0                    0   r[34]=''
178   String8        0     37    0     .0             0   r[37]='.0'
179   String8        0     38    0                    0   r[38]=''
180   String8        0     52    0     .0             0   r[52]='.0'
181   String8        0     53    0                    0   r[53]=''
182   String8        0     55    0     .0             0   r[55]='.0'
183   String8        0     56    0                    0   r[56]=''
184   Goto           0     1     0                    0

This is the explain query plan with UNION ALL on 3.40.0

QUERY PLAN
`--MERGE (UNION ALL)
   |--LEFT
   |  |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?)
   |  |--CORRELATED SCALAR SUBQUERY 1
   |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
   |  `--CORRELATED SCALAR SUBQUERY 1
   |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
   `--RIGHT
      |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?)
      |--CORRELATED SCALAR SUBQUERY 3
      |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
      `--CORRELATED SCALAR SUBQUERY 3
         `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)

Perhaps these will say something compared with the previous ones.

(20) By anonymous on 2022-11-24 04:24:17 in reply to 19 [link] [source]

That is interesting (by the way, explain query plan seems more understandable at the moment)!

I took all three of your explain query plan outputs to compare and MERGE (UNION ALL) seems to work like a cross join in disabling some of the flattening. (see 7.1  of https://www.sqlite.org/optoverview.html ).    I am wondering why the OFFSET 0 or WHERE clause on outer select didn't seem to effect it (or perhaps it does and I missed that 'memo'?).

I might tackle generating random entries (via series, and random) to make a test db as I would like to see what is happening here. Could you provide a schema sql so I don't have to create one from scratch (I may discard extra columns to reduce complexity)?  I understand the distributions won't be the same, however it might show me what's going on.

The summary of the three explain query plans:

3.40 union all (fast 3.40 query)
QUERY PLAN
`--MERGE (UNION ALL)
   |--LEFT
   |  |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?)
   |  |--CORRELATED SCALAR SUBQUERY 1
   |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
   |  `--CORRELATED SCALAR SUBQUERY 1
   |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
   `--RIGHT
      |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?)
      |--CORRELATED SCALAR SUBQUERY 3
      |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
      `--CORRELATED SCALAR SUBQUERY 3
         `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)

3.39.4 (fast query)
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)
|     |  |--CORRELATED SCALAR SUBQUERY 1
|     |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)
|        |--CORRELATED SCALAR SUBQUERY 3
|        |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY

3.40 slow query
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SCAN o
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SCAN o
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY

My general thinking is it might be worth getting to the bottom of this rather than stopping because it now 'works' as it seems a fragile position.  So if you want to help investigate further, a schema sql create script would save me some time.

thanks.

(30) By jose isaias cabrera (jicman) on 2022-11-25 17:03:31 in reply to 20 [link] [source]

Happy belated Thanksgiving to all. I thank God for your wonderful tool, Dr. Hipp, and I am only doing good things with it. :-)

... Could you provide a schema sql so I don't have to create one from scratch (I may discard extra columns to reduce complexity)? ...

This script below will show the EXPLAIN QUERY PLANs differences/problems. It does not show the slowness of the query because there are over 165K of records on the table where the query is ran against. However, if you replicate this data with a script by just adding the same data with a different ProjID (PR0000000001 to PR0000030000), it to create enough data for some of the slowness of the query response.

CREATE TABLE Project_List
(
    ProjID, Finish_Date, BL_Finish, Updated_By, InsertDate,
    PRIMARY KEY (ProjID, Finish_Date, BL_Finish)
);
CREATE INDEX PL_ProjID_InsertDate_New ON "Project_List" (ProjID, InsertDate);

INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Kathy','2021-08-10_07-26-53');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Pamela','2021-08-17_06-28-38');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Barbara','2021-08-17_11-29-18');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Barbara','2021-08-17_13-46-26');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Pamela','2021-08-20_04-44-04');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Pamela','2021-08-25_07-31-24');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Barbara','2021-09-24_03-55-27');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Miguel','2021-10-15_03-59-51');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Miguel','2022-01-04_05-35-28');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Miguel','2022-02-08_13-04-48');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Externo','2022-02-16_04-59-37');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Jose','2022-02-22_08-20-39');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Pamela','2022-03-14_11-37-33');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Esther','2022-04-08_04-27-49');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Barbara','2022-04-25_05-17-31');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Pamela','2022-04-25_12-01-17');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','','Pamela','2022-05-13_04-05-26');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','','Externo','2022-05-20_04-12-13');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','','Miguel','2022-05-26_04-53-14');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','','Esther','2022-06-13_12-14-45');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','','Jose','2022-06-27_11-56-58');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','','Jose','2022-06-29_04-33-34');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Pamela','2022-06-29_10-09-49');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Barbara','2022-07-05_08-56-08');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Daniel','2022-07-06_07-09-57');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Daniel','2022-07-25_05-32-23');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Jose','2022-08-05_04-50-11');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Barbara','2022-08-10_04-15-47');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Barbara','2022-08-22_10-59-48');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Pamela','2022-08-23_11-48-46');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Externo','2022-08-31_05-47-18');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Jose','2022-09-06_05-56-24');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Esther','2022-09-13_09-41-39');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Barbara','2022-09-16_07-25-06');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Pamela','2022-10-03_12-19-50');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Barbara','2022-10-17_07-02-07');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Barbara','2022-10-31_05-09-06');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-08-31','2023-08-31','Jose','2022-11-03_05-57-56');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','2023-08-31','Donna','2022-11-10_08-57-13');
INSERT INTO Project_List(ProjID,Finish_Date,BL_Finish,Updated_By,InsertDate) VALUES('PR0000020614','2023-09-30','2023-09-30','Barbara','2022-11-16_11-48-08');

And use this query,

        SELECT ProjID,
               Updated_By,
               InsertDate,
               var,
               oldv,
               newv
        FROM
        (
        
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'BL_Finish' as var,
                   (
                      SELECT
                        coalesce(BL_Finish,'') FROM Project_List
                      WHERE ProjID = o.ProjID
                      AND InsertDate < o.InsertDate
                      ORDER BY InsertDate DESC
                      LIMIT 1
                    ) AS oldv,
                   coalesce(BL_Finish,'') as newv
                   FROM Project_List as o
            UNION
        
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Finish_Date' as var,
                   (
                      SELECT
                        coalesce(Finish_Date,'') FROM Project_List
                      WHERE ProjID = o.ProjID
                      AND InsertDate < o.InsertDate
                      ORDER BY InsertDate DESC
                      LIMIT 1
                    ) AS oldv,
                   coalesce(Finish_Date,'') as newv
                   FROM Project_List as o
            
      )
      WHERE oldv <> newv 
      AND ProjID = 'PR0000020614' 
ORDER BY InsertDate ASC;

Just change the UNION to UNION ALL, and as Keith also suggested, moving the WHEREs into each SELECT, that also works fast. I am using Windows 10, so, I have downloaded the SQLite tools from the download site, and I have unzipped them, and opened a few command prompts and just CD to the parent directory of each tool and run SQLite3 executable from there and can test them. Just an FYI. Thanks all for your support.

(33) By Keith Medcalf (kmedcalf) on 2022-11-25 17:54:09 in reply to 30 [link] [source]

You are missing something from the primary key because running the script as shown generates many primary key constraint violations. Making the whole caboodle the primary key seems to allow the data to be inserted.

(34) By jose isaias cabrera (jicman) on 2022-11-25 17:58:41 in reply to 33 [link] [source]

Yeah, that is correct. The problem is that there are a bunch of other fields that I took off. And the constraints are ok in the original, but with just these 3 fields, they are popping out. I should have taken those out. Sorry.

(43) By anonymous on 2022-11-25 22:13:18 in reply to 30 [link] [source]

The posts locked in moderation were my (lame) guess at your schema. Once I was able to get the query plans to match your reported behavior I tried to reduce them. I was able duplicate your resulting query plans on the versions you posted.

In the meantime Dr Hipp and Keith (who are always miles ahead) were able to pin point an existing test case (the INTERSECT example). I refactored the reduced query into something more closely matching that, but keeping the same query plan.

At this point the reduced query is moot (now that we have your test rigging).

Thus, not sure how useful any of my previous posts are at this point, but admins can feel free to delete any to all of those posts for clarity (or edit).

regards

(44) By jose isaias cabrera (jicman) on 2022-11-26 01:57:18 in reply to 43 [link] [source]

Dr. Hipp fixed the problem that I was having. Thanks.

josé

(46) By anonymous on 2022-11-26 03:45:09 in reply to 44 [link] [source]

Still seeing anomalies when second part of union is a scan constant row (see my reply to Dr Hipp for an example below).

The example I provided to Dr Hipp was further reduction of your query (beyond my initial crack at it), isolating the to one side of the union. This appears to mean that if you had a union that added a row with only constants, that it reverts to the slow query you experienced (in otherwords, works like 3.40).

I could be wrong (and often am), but I think the story isn't quite finished on this (would love to be wrong).

(21) By anonymous on 2022-11-24 05:52:54 in reply to 19 [link] [source]

I worked up a small demo (you can use it on the fiddle).

I can replicate the query plans you saw, both with UNION ALL, and with UNION.  The  INDEXED BY clause also get's good results.  However the OFFSET 0 does not.

This is the script (the creation script is commented out so I can change the query  on the same dataset).  Also, no analyse has been run.. it operates as you descibed out of the box:

---- script

 create table Project_List (
 ProjID int,
 Updated_By text,
 InsertDate TEXT,
 Finish_Date TEXT,
 Ann_CapexP real);

 create index PL_ProjID_InsertDate_New on Project_List (ProjID, InsertDate);

 insert into Project_List SELECT
 value as ProjID,
 hex(randomblob(10)) as Updated_By,
 abs(random()) % (10000000 - 1) + 1 as Ann_CapexP,
 cast (round(julianday('now')) +3 as integer) as InsertDate,
 cast (round(julianday('now')) +5 as integer) as Finish_Date
 FROM generate_series(1,100,1);

 select count(*) from Project_List;

The query with poor performance:

 explain query plan 
SELECT ProjID,
               Updated_By,
               InsertDate,
               var,
               oldv,
               newv
        FROM
        (
        
            SELECT ProjID,
                   Updated_By,
                   InsertDate,
                   'Finish_Date' as var,
                   (
                      SELECT
                        coalesce(Finish_Date,'') FROM Project_List
                      WHERE ProjID = o.ProjID
                      AND InsertDate < o.InsertDate
                      ORDER BY InsertDate DESC
                      LIMIT 1
                    ) AS oldv,
                   coalesce(Finish_Date,'') as newv
                   FROM Project_List  as o -- INDEXED BY PL_ProjID_InsertDate_New
            UNION  ALL
        
              SELECT ProjID,
                     Updated_By,
                     InsertDate,
                     'Ann_CapexP' as var,
                     (
                        SELECT
                          replace(round(Ann_CapexP),'.0','') FROM Project_List WHERE
                        ProjID = o.ProjID
                        AND InsertDate < o.InsertDate
                        ORDER BY InsertDate DESC
                        LIMIT 1
                      ) AS oldv,
                      replace(round(Ann_CapexP),'.0','') as newv
                     FROM Project_List as o -- INDEXED BY PL_ProjID_InsertDate_New
              
      )
      WHERE oldv <> newv 
      AND ProjID = 'PR0000020614' 
ORDER BY InsertDate ASC;


---- output of 3.40 online fiddle with original sql
--------output -----
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SCAN o
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SCAN o
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY


----- using union all instead of union output (uncomment -- ALL)
QUERY PLAN
`--MERGE (UNION ALL)
   |--LEFT
   |  |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?)
   |  |--CORRELATED SCALAR SUBQUERY 1
   |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
   |  `--CORRELATED SCALAR SUBQUERY 1
   |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
   `--RIGHT
      |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?)
      |--CORRELATED SCALAR SUBQUERY 3
      |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
      `--CORRELATED SCALAR SUBQUERY 3
         `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)


------ using INDEXED BY on two nested sub-queries (uncomment INDEXED BY)
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SCAN o USING INDEX PL_ProjID_InsertDate_New
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SCAN o USING INDEX PL_ProjID_InsertDate_New
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY

So there are few ways to get the query to use the index, perhaps the most heavy handed is INDEXED BY because it makes it clear what you trying to do.  There also are query plan performance differences.  My rough guess from random runs is that the UNION ALL is faster than the INDEXED BY but I only used a small number of records.

Also, INDEXED BY seems to more closely match the 3.39.4 query plan (exactly?).

I can distill the above down to a smaller test case if Dr Hipp thinks it's worthwhile to take a look at it.

(22) By anonymous on 2022-11-24 06:39:24 in reply to 19 [link] [source]

Please ignore the section in my post below about INDEXED BY performance as I noticed the query plan in 3.40 only changed to SCAN USING INDEX rather than SEARCH USING INDEX.

I clearly have more work to do on that query so I'll have another look tomorrow evening.

Once(if?) I have that sorted out I will try to take a crack at a minimised schema and query that shows the same behaviour. And then try some performance comparisons (it is too early for that IMO).

What I appear to be able to assert is that the 3.40 query plan of union all version is different than the 3.39.4 original problematic query plan by OP (The MERGE (UNION ALL) rather than co-routine). I will double check that when I pick this up again.

If someone wants to sort it out before I get to it, please feel welcome! :) I used the online fiddle and scripts I posted.

(23) By Richard Hipp (drh) on 2022-11-24 12:35:36 in reply to 18 [link] [source]

See https://sqlite.org/src/artifact/8a709a8e19?ln=5012-5017 and https://sqlite.org/src/info/346a3b12b861ce7b.

The following is a simplification of the script that the dbsqlfuzz fuzzer found that demonstrated the problem:

CREATE TABLE t1(a,b,c COLLATE NOCASE);
INSERT INTO t1 VALUES(1,'a','a');
INSERT INTO t1 VALUES(9.9000000000000003552,'b','B');
INSERT INTO t1 VALUES(NULL,'C','c');
INSERT INTO t1 VALUES('hello','d','D');
INSERT INTO t1 VALUES(X'616263','e','e');

.mode qbox
.echo on
SELECT a,b,CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE -3.7e+921*11<>b
ORDER BY a,b,c;

SELECT * FROM (SELECT a,b,CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE -3.7e+921*11<>b
ORDER BY a,b,c) WHERE "a" ISNULL AND "b"='C' AND "CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END"='C'

The second query is a wrapper around the first query that attempts to pick off a single row of the first query. However, it returns no rows. Try it using SQLite version 3.39 or earlier and you will get the wrong answer. Do the same on 3.40 and the correct answer comes out. The fix we put in place for this problem was the 346a3b12b861ce7b patch.

(24.1) By Keith Medcalf (kmedcalf) on 2022-11-24 16:14:34 edited from 24.0 in reply to 23 [link] [source]

Very interesting, except both versions 3.39.0 and 3.38.0 return correct responses.

SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
sqlite> .version
SQLite 3.38.0 2022-02-22 18:58:40 40fa792d359f84c3b9e9d6623743e1a59826274e221df1bde8f47086968a1bab
zlib version 1.2.11
gcc-5.2.0
sqlite> CREATE TABLE t1(a,b,c COLLATE NOCASE);
sqlite> INSERT INTO t1 VALUES(1,'a','a');
sqlite> INSERT INTO t1 VALUES(9.9000000000000003552,'b','B');
sqlite> INSERT INTO t1 VALUES(NULL,'C','c');
sqlite> INSERT INTO t1 VALUES('hello','d','D');
sqlite> INSERT INTO t1 VALUES(X'616263','e','e');
sqlite>
sqlite> .mode qbox
sqlite> .eqp on
sqlite>    SELECT a,b,CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END
   ...>      FROM t1
   ...> INTERSECT
   ...>    SELECT a,b,c
   ...>      FROM t1
   ...>     WHERE -3.7e+921*11<>b
   ...> ORDER BY a,b,c;
QUERY PLAN
`--MERGE (INTERSECT)
   |--LEFT
   |  |--SCAN t1
   |  `--USE TEMP B-TREE FOR ORDER BY
   `--RIGHT
      |--SCAN t1
      `--USE TEMP B-TREE FOR ORDER BY
┌───────────┬─────┬───────────────────────────────────────────────────────┐
│     a     │  b  │ CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END │
├───────────┼─────┼───────────────────────────────────────────────────────┤
│ NULL      │ 'C' │ 'C'                                                   │
│ 1         │ 'a' │ 'a'                                                   │
│ 9.9       │ 'b' │ 'b'                                                   │
│ 'hello'   │ 'd' │ 'd'                                                   │
│ x'616263' │ 'e' │ 'e'                                                   │
└───────────┴─────┴───────────────────────────────────────────────────────┘
sqlite>
sqlite> SELECT *
   ...>   FROM (
   ...>            SELECT a,b,CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END
   ...>              FROM t1
   ...>         INTERSECT
   ...>            SELECT a,b,c
   ...>              FROM t1
   ...>             WHERE -3.7e+921*11<>b
   ...>          ORDER BY a,b,c
   ...>        )
   ...>  WHERE "a" ISNULL
   ...>    AND "b"='C'
   ...>    AND "CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END"='C'
   ...> ;
QUERY PLAN
|--CO-ROUTINE SUBQUERY 2
|  `--MERGE (INTERSECT)
|     |--LEFT
|     |  |--SCAN t1
|     |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|     `--RIGHT
|        `--SCAN t1
`--SCAN SUBQUERY 2
┌──────┬─────┬───────────────────────────────────────────────────────┐
│  a   │  b  │ CASE c WHEN 943 THEN 967 WHEN 897 THEN 533 ELSE b END │
├──────┼─────┼───────────────────────────────────────────────────────┤
│ NULL │ 'C' │ 'C'                                                   │
└──────┴─────┴───────────────────────────────────────────────────────┘
sqlite>

Unless, of course, the executables for these versions as located on your website are not actually what they purport to be.

(25.1) By Keith Medcalf (kmedcalf) on 2022-11-24 18:06:44 edited from 25.0 in reply to 23 [link] [source]

Your simplified example is bogus (ie, does not demonstrate any issue). However, the actual testcase does in fact demonstrate an issue in that the specific subquery has a difficulty when a de-collationified expression is pushed down into the subquery.

(26) By Richard Hipp (drh) on 2022-11-24 18:21:57 in reply to 25.1 [link] [source]

The problem was introduced by a different bug-fix that happened earlier in the 3.40 development cycle. So it never appeared in a release. The previous bug-fix was at check-in ed14863dd72e35fa. So the example above only failed for the 69 check-ins over 12 days in mid-October.

I'll investigate and see how long the previous bug was valid for....

(27) By Richard Hipp (drh) on 2022-11-24 18:42:48 in reply to 26 [link] [source]

An alternative demonstration of the problem is this script:

CREATE TABLE t1(a,b,c COLLATE NOCASE);
INSERT INTO t1 VALUES(1,'a','a');
INSERT INTO t1 VALUES(9.9000000000000003552,'b','B');
INSERT INTO t1 VALUES(NULL,'C','c');
INSERT INTO t1 VALUES('hello','d','D');
INSERT INTO t1 VALUES(X'616263','e','e');

.echo on
.mode qbox
SELECT a,b,c FROM t1 INTERSECT SELECT a,b, b FROM t1 WHERE 'eT"3qRkL+oJMJjQ9z0'>=b
ORDER BY a,b,c;

SELECT * FROM (SELECT a,b,c FROM t1 INTERSECT SELECT a,b, b FROM t1 WHERE 'eT"3qRkL+oJMJjQ9z0'>=b
ORDER BY a,b,c) WHERE "a" ISNULL AND "b"='C' AND "c"='c';

The second query should return one row. But it returns zero rows ever since the push-down optimization was added for version 3.8.11 in 2015. Recent enhancements to the dbsqlfuzz fuzzer found this problem and brought it to your attention.

(29) By anonymous on 2022-11-25 06:56:40 in reply to 27 [link] [source]

To show the query plan difference I added:

create index i on t1 (a,b,c);

With the index, from what I checked, the show query plan details the same behaviour of the UNION example across the various sqlite versions and matches closely with the reduced query I posted earlier this evening.

Thus, it appears to me that this is indeed the issue you identified with a push down optimisation being incorrectly applied (as per you post above).

summary: Previously, the push down query optimisation that was in place was incorrect. in 3.40 it fixes correctness, but the queries that depended on that optimization are going to be (much) slower until re-written?

(28) By anonymous on 2022-11-25 06:17:27 in reply to 25.1 [link] [source]

(Apologies ahead of time if this is noise.)

I reduced the original Project_List query to the following:

drop  table if exists t ;
 create table t (a text);
 create index i on t (a);
explain query plan
 SELECT a 
        FROM
        (
        
            SELECT a 
                   FROM t as o 
            UNION
        
              SELECT 1 as a 
       
      )
       WHERE 
        a = 1;

then tested on three versions as follows:


3.34.1:
CO-ROUTINE 2
COMPOUND QUERY
LEFT-MOST SUBQUERY
SEARCH TABLE t AS o USING COVERING INDEX i (a=?)
UNION USING TEMP B-TREE
SCAN CONSTANT ROW

3.39.1:
CO-ROUTINE (subquery-2)
COMPOUND QUERY
LEFT-MOST SUBQUERY
SEARCH o USING COVERING INDEX i (a=?)
UNION USING TEMP B-TREE
SCAN CONSTANT ROW
SCAN (subquery-2)

3.40.0 (online fiddle and a custom 3.40.0 build with QPSG enabled)
QUERY PLAN
|--CO-ROUTINE (subquery-2)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  `--SCAN o
|     `--UNION USING TEMP B-TREE
|        `--SCAN CONSTANT ROW
`--SCAN (subquery-2)

It's clearly NOT pushing the where into the subquery.  What isn't clear is whether it should be.

As Dr Hipp has suggested that it's related to the INTERSECT example , I will look at refactoring the query to INTERSECT and comparing both output, and query plans across the above sqlite versions.

(admins: feel free to delete my partially retracted 'in limbo' post yesterday, the script and query was overly complicated, leading to mistakes and dead end investigations).

(15) By jose isaias cabrera (jicman) on 2022-11-23 19:09:04 in reply to 12 [link] [source]

This change above grabs 125 records in 0.1 seconds using SQLiteVer: 3.40.0 2022-11-16 12:10:08. However, if I use the original one, and replace UNION with UNION ALL, the result is 125 records in 0.09 seconds using the same SQLiteVer: 3.40.0 2022-11-16 12:10:08. So, I will keep the UNION ALL change. Thanks.

(31) By Richard Hipp (drh) on 2022-11-25 17:07:24 in reply to 1 [link] [source]

Can you retry your original query using the latest trunk check-in of SQLite and let me know whether or not the performance issue has been resolved?

(32.1) By jose isaias cabrera (jicman) on 2022-11-25 18:02:02 edited from 32.0 in reply to 31 [link] [source]

Where is sqlite3.c on the trunk? I usually download the snapshots, and in those, the sqlite3.c is right in the top directory. I usually use this command,

i686-w64-mingw32-gcc -shared -static-libgcc sqlite3.c -o sqlite3.dll

But, I can't find the sqlite3.c source.

$ pwd
/home/e608313/b/sqlite/SQLite-adbca344

$ ls -l
total 2.6M
-rw-r--r-- 1 e608313 Domain Users  263 Nov 25 12:05 LICENSE.md
-rw-r--r-- 1 e608313 Domain Users  49K Nov 25 12:36 Makefile
-rw-r--r-- 1 e608313 Domain Users  49K Nov 25 12:05 Makefile.in
-rw-r--r-- 1 e608313 Domain Users 3.4K Nov 25 12:05 Makefile.linux-gcc
-rw-r--r-- 1 e608313 Domain Users  81K Nov 25 12:05 Makefile.msc
-rw-r--r-- 1 e608313 Domain Users  16K Nov 25 12:05 README.md
-rw-r--r-- 1 e608313 Domain Users    7 Nov 25 12:05 VERSION
-rw-r--r-- 1 e608313 Domain Users 276K Nov 25 12:05 aclocal.m4
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 art/
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 autoconf/
-rw-r--r-- 1 e608313 Domain Users  48K Nov 25 12:05 config.guess
-rw-r--r-- 1 e608313 Domain Users  79K Nov 25 12:36 config.log
-rwxr-xr-x 1 e608313 Domain Users  55K Nov 25 12:36 config.status*
-rw-r--r-- 1 e608313 Domain Users  31K Nov 25 12:05 config.sub
-rwxr-xr-x 1 e608313 Domain Users 402K Nov 25 12:05 configure*
-rw-r--r-- 1 e608313 Domain Users  24K Nov 25 12:05 configure.ac
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 contrib/
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 doc/
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 ext/
-rwxr-xr-x 1 e608313 Domain Users 5.5K Nov 25 12:05 install-sh*
-rwxr-xr-x 1 e608313 Domain Users 255K Nov 25 12:36 libtool*
-rw-r--r-- 1 e608313 Domain Users 240K Nov 25 12:05 ltmain.sh
-rw-r--r-- 1 e608313 Domain Users 1.6K Nov 25 12:05 magic.txt
-rw-r--r-- 1 e608313 Domain Users  37K Nov 25 12:05 main.mk
-rw-r--r-- 1 e608313 Domain Users 160K Nov 25 12:05 manifest
-rw-r--r-- 1 e608313 Domain Users   65 Nov 25 12:05 manifest.uuid
-rw-r--r-- 1 e608313 Domain Users  937 Nov 25 12:05 mkso.sh
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 mptest/
-rw-r--r-- 1 e608313 Domain Users 1.8K Nov 25 12:05 spec.template
-rw-r--r-- 1 e608313 Domain Users  258 Nov 25 12:05 sqlite.pc.in
-rw-r--r-- 1 e608313 Domain Users 8.8K Nov 25 12:05 sqlite3.1
-rw-r--r-- 1 e608313 Domain Users  263 Nov 25 12:36 sqlite3.pc
-rw-r--r-- 1 e608313 Domain Users  267 Nov 25 12:05 sqlite3.pc.in
-rw-r--r-- 1 e608313 Domain Users 4.0K Nov 25 12:29 sqlite_cfg.h
-rw-r--r-- 1 e608313 Domain Users 3.7K Nov 25 12:05 sqlite_cfg.h.in
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 src/
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 test/
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 tool/
drwxr-xr-x 1 e608313 Domain Users    0 Nov 25 12:05 vsixtest/

(35) By jose isaias cabrera (jicman) on 2022-11-25 18:24:55 in reply to 32.1 [link] [source]

Never mind... I had to run,

./configure
make

The problem is no longer there. I grabbed 125 records in 0.03 seconds. SQLiteVer: 3.41.0 2022-11-25 17:05:55. I am going to build the sqlite3 tool to send EXPLAIN QUERY PLAN.

(37) By jose isaias cabrera (jicman) on 2022-11-25 18:40:37 in reply to 35 [link] [source]

New EXPLAIN QUERY PLAN on the same query as previously stated using SQLite version 3.41.0 2022-11-25 17:05:55 is:

QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)
|     |  |--CORRELATED SCALAR SUBQUERY 1
|     |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|     `--UNION USING TEMP B-TREE
|        |--SEARCH o USING INDEX PL_ProjID_BL_Start (ProjID=?)
|        |--CORRELATED SCALAR SUBQUERY 3
|        |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?)
|--SCAN (subquery-4)
`--USE TEMP B-TREE FOR ORDER BY

(36) By Keith Medcalf (kmedcalf) on 2022-11-25 18:25:41 in reply to 31 [link] [source]

I get the following:

Without latest checkin:

>sqlite < testit.sql
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SCAN o (~1048576 rows)
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
|     `--UNION USING TEMP B-TREE
|        |--SCAN o (~1048576 rows)
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
|--SCAN (subquery-4) (~524288 rows)
`--USE TEMP B-TREE FOR ORDER BY
┌────────────────┬────────────┬───────────────────────┬───────────────┬──────────────┬──────────────┐
│     ProjID     │ Updated_By │      InsertDate       │      var      │     oldv     │     newv     │
├────────────────┼────────────┼───────────────────────┼───────────────┼──────────────┼──────────────┤
│ 'PR0000020614' │ 'Externo'  │ '2022-05-20_04-12-13' │ 'Finish_Date' │ '2023-09-30' │ '2023-08-31' │
│ 'PR0000020614' │ 'Pamela'   │ '2022-06-29_10-09-49' │ 'BL_Finish'   │ ''           │ '2023-08-31' │
│ 'PR0000020614' │ 'Donna'    │ '2022-11-10_08-57-13' │ 'Finish_Date' │ '2023-08-31' │ '2023-09-30' │
│ 'PR0000020614' │ 'Barbara'  │ '2022-11-16_11-48-08' │ 'BL_Finish'   │ '2023-08-31' │ '2023-09-30' │
└────────────────┴────────────┴───────────────────────┴───────────────┴──────────────┴──────────────┘
VM-steps: 129600104
Run Time: real 6.681 user 6.343750 sys 0.328125
QUERY PLAN
`--MERGE (UNION ALL)
   |--LEFT
   |  |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?) (~9 rows)
   |  |--CORRELATED SCALAR SUBQUERY 1
   |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
   |  `--CORRELATED SCALAR SUBQUERY 1
   |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
   `--RIGHT
      |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?) (~9 rows)
      |--CORRELATED SCALAR SUBQUERY 3
      |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
      `--CORRELATED SCALAR SUBQUERY 3
         `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
┌────────────────┬────────────┬───────────────────────┬───────────────┬──────────────┬──────────────┐
│     ProjID     │ Updated_By │      InsertDate       │      var      │     oldv     │     newv     │
├────────────────┼────────────┼───────────────────────┼───────────────┼──────────────┼──────────────┤
│ 'PR0000020614' │ 'Externo'  │ '2022-05-20_04-12-13' │ 'Finish_Date' │ '2023-09-30' │ '2023-08-31' │
│ 'PR0000020614' │ 'Pamela'   │ '2022-06-29_10-09-49' │ 'BL_Finish'   │ ''           │ '2023-08-31' │
│ 'PR0000020614' │ 'Donna'    │ '2022-11-10_08-57-13' │ 'Finish_Date' │ '2023-08-31' │ '2023-09-30' │
│ 'PR0000020614' │ 'Barbara'  │ '2022-11-16_11-48-08' │ 'BL_Finish'   │ '2023-08-31' │ '2023-09-30' │
└────────────────┴────────────┴───────────────────────┴───────────────┴──────────────┴──────────────┘
VM-steps: 3013
Run Time: real 0.011 user 0.000000 sys 0.000000
With the latest checkin:
>sqlite3 < testit.sql
QUERY PLAN
|--CO-ROUTINE (subquery-4)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  |--SEARCH o USING COVERING INDEX sqlite_autoindex_Project_List_1 (ProjID=?) (~9 rows)
|     |  |--CORRELATED SCALAR SUBQUERY 1
|     |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
|     |  `--CORRELATED SCALAR SUBQUERY 1
|     |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
|     `--UNION USING TEMP B-TREE
|        |--SEARCH o USING COVERING INDEX sqlite_autoindex_Project_List_1 (ProjID=?) (~9 rows)
|        |--CORRELATED SCALAR SUBQUERY 3
|        |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
|        `--CORRELATED SCALAR SUBQUERY 3
|           `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
|--SCAN (subquery-4) (~4 rows)
`--USE TEMP B-TREE FOR ORDER BY
┌────────────────┬────────────┬───────────────────────┬───────────────┬──────────────┬──────────────┐
│     ProjID     │ Updated_By │      InsertDate       │      var      │     oldv     │     newv     │
├────────────────┼────────────┼───────────────────────┼───────────────┼──────────────┼──────────────┤
│ 'PR0000020614' │ 'Externo'  │ '2022-05-20_04-12-13' │ 'Finish_Date' │ '2023-09-30' │ '2023-08-31' │
│ 'PR0000020614' │ 'Pamela'   │ '2022-06-29_10-09-49' │ 'BL_Finish'   │ ''           │ '2023-08-31' │
│ 'PR0000020614' │ 'Donna'    │ '2022-11-10_08-57-13' │ 'Finish_Date' │ '2023-08-31' │ '2023-09-30' │
│ 'PR0000020614' │ 'Barbara'  │ '2022-11-16_11-48-08' │ 'BL_Finish'   │ '2023-08-31' │ '2023-09-30' │
└────────────────┴────────────┴───────────────────────┴───────────────┴──────────────┴──────────────┘
VM-steps: 3044
Run Time: real 0.008 user 0.000000 sys 0.000000
QUERY PLAN
`--MERGE (UNION ALL)
   |--LEFT
   |  |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?) (~9 rows)
   |  |--CORRELATED SCALAR SUBQUERY 1
   |  |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
   |  `--CORRELATED SCALAR SUBQUERY 1
   |     `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
   `--RIGHT
      |--SEARCH o USING INDEX PL_ProjID_InsertDate_New (ProjID=?) (~9 rows)
      |--CORRELATED SCALAR SUBQUERY 3
      |  `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
      `--CORRELATED SCALAR SUBQUERY 3
         `--SEARCH Project_List USING INDEX PL_ProjID_InsertDate_New (ProjID=? AND InsertDate<?) (~2 rows)
┌────────────────┬────────────┬───────────────────────┬───────────────┬──────────────┬──────────────┐
│     ProjID     │ Updated_By │      InsertDate       │      var      │     oldv     │     newv     │
├────────────────┼────────────┼───────────────────────┼───────────────┼──────────────┼──────────────┤
│ 'PR0000020614' │ 'Externo'  │ '2022-05-20_04-12-13' │ 'Finish_Date' │ '2023-09-30' │ '2023-08-31' │
│ 'PR0000020614' │ 'Pamela'   │ '2022-06-29_10-09-49' │ 'BL_Finish'   │ ''           │ '2023-08-31' │
│ 'PR0000020614' │ 'Donna'    │ '2022-11-10_08-57-13' │ 'Finish_Date' │ '2023-08-31' │ '2023-09-30' │
│ 'PR0000020614' │ 'Barbara'  │ '2022-11-16_11-48-08' │ 'BL_Finish'   │ '2023-08-31' │ '2023-09-30' │
└────────────────┴────────────┴───────────────────────┴───────────────┴──────────────┴──────────────┘
VM-steps: 3013
Run Time: real 0.007 user 0.000000 sys 0.000000
Using this schema and the data posted above duplicated 30000 times.
CREATE TABLE Project_List
(
    ProjID, Finish_Date, BL_Finish, Updated_By, InsertDate,
    PRIMARY KEY (ProjID, Finish_Date, BL_Finish, Updated_By, InsertDate)
);
CREATE INDEX PL_ProjID_InsertDate_New ON "Project_List" (ProjID, InsertDate);

(38) By jose isaias cabrera (jicman) on 2022-11-25 18:50:39 in reply to 36 [link] [source]

Thanks, Keith. How do you get VM-Steps to show with queries? I have .timer on, but VM-Step is not on mine.

I would have expected that UNION ALL would be slower, or would, at least, have more VM-Steps. Hmmmm. So, UNION ALL is the winner.

(39.1) By Larry Brasfield (larrybr) on 2022-11-25 19:06:24 edited from 39.0 in reply to 38 [link] [source]

Plain UNION has to do extra work to eliminate duplicates. With UNION ALL, that work is avoided. The amount of processing and main memory access is much reduced.

(41) By jose isaias cabrera (jicman) on 2022-11-25 19:53:00 in reply to 39.1 [link] [source]

There you go. I learned 17 new things today, including this one. Thanks Mr. Brasfield.

(40) By Richard Hipp (drh) on 2022-11-25 19:33:11 in reply to 38 [link] [source]

How do you get VM-Steps to show

Use: ".stats vmstep"

(42) By jose isaias cabrera (jicman) on 2022-11-25 19:54:57 in reply to 40 [link] [source]

Ok, 18 new things I have learned today. Adding it to my .sqlite3rc file. Thanks, Dr. Hipp.

(45) By anonymous on 2022-11-26 03:23:28 in reply to 31 [link] [source]

With 3.41.0 built from tarball today, the change resolves the OP test query (and the reduction I posted yesterday, if query typo of UNION ALL is corrected to UNION ).

However, with 3.41.0 (from tarball) we are still seeing the issue in the test query posted yesterday (now out of moderation today):

script:

drop  table if exists t ;
 create table t (a text);
 create index i on t (a);
 insert into t select value as a from  generate_series(1,100,1);

explain query plan
 SELECT a FROM (SELECT a FROM t as o UNION select 1 as o) WHERE a = 1;


results:
1

slow query plan:
QUERY PLAN
|--CO-ROUTINE (subquery-2)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  `--SCAN o
|     `--UNION USING TEMP B-TREE
|        `--SCAN CONSTANT ROW
`--SCAN (subquery-2)

a slight variation:

explain query plan
 SELECT a FROM (SELECT a FROM t as o UNION select 1 as a) WHERE a = 1;

results:
1
1

with identical explain query plan as previous query:

QUERY PLAN
|--CO-ROUTINE (subquery-2)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  `--SCAN o
|     `--UNION USING TEMP B-TREE
|        `--SCAN CONSTANT ROW
`--SCAN (subquery-2)

Was the fix/change intended to handle this case?  It would seem that the scan constant row could allow the same push down as the fix/change implements?

The above two queries were created by reducing the query from the OP, and then isolated to the first part of the union (making the second part of the union the scan constant row) for simplicity.

(47) By anonymous on 2022-11-26 04:56:13 in reply to 31 [link] [source]

update:

I see the column affinity requirement is new (check-in : https://sqlite.org/src/info/1ad41840c5e0fa70).

A good chance that is what's happening on the example with scan constant row.

explain query plan
 SELECT a FROM (SELECT a FROM t as o UNION select cast('1' as text) as o) WHERE a = 1;

results:
1

and has a query plan of:

|--CO-ROUTINE (subquery-2)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  `--SEARCH o USING COVERING INDEX i (a=?)
|     `--UNION USING TEMP B-TREE
|        `--SCAN CONSTANT ROW
`--SCAN (subquery-2)

SELECT a FROM (SELECT a FROM t as o UNION select cast('1' as text) as a) WHERE a = 1;

has identical results.

So this seems to work according to current check-in documentation.

Missed that there is a new requirement of the column affinity.

The only other detail that is a mystery is:

SELECT a FROM (SELECT a FROM t as o UNION select '1' as o) WHERE a = 1;

This seems to fail the new column affinity check.  Apparently '1' isn't considered the same as cast('1' as text).  

This seems to be true even with when the column affinity matches the data type inserted:

  drop  table if exists t ;
  create table t (a text);
  create index i on t (a);
  insert into t select cast (value as text) as a from  generate_series(1,100,1);

the query:

SELECT a FROM (SELECT a FROM t as o UNION select '1' as o) WHERE a = 1;

 still causes a scan, even with both column affinity and inserted as quoted literal being text.

(48.2) By Keith Medcalf (kmedcalf) on 2022-11-26 06:14:17 edited from 48.1 in reply to 47 [link] [source]

Apparently '1' isn't considered the same as cast('1' as text).

That would depend on your definiton of "the same".

'1' is an expression. It has value '1'. It is text. It has no affinity.

cast('1' as text) is an expression. It is text. Its value is '1'. It has text affinity.

You may determine what it means to have an affinity or not have an affinity and what this implies by reading the fine documentation on the concepts of datatypes and affinities here: https://sqlite.org/datatype3.html

1 (bare, by its lonesomne) is an expression. It is an integer. It has no affinity.

cast(1 as integer) is an expression. It is an integer. It has integer affinity.

'1' == 1 is False
cast('1' as text) == 1 is True
'1' == cast(1 as integer) is True
cast('1' as text) == cast(1 as integer) is True

That is to say, with clarity, that if one or the other side of a comparison has an affinity, then the side that has no affinity is converted to the same affinity as the other side. If both sides have affinity, then if one of them is numeric (integer, real, numeric) then it wins and the other side is converted to numeric affinity and a numeric comparison is performed. If neither side has affinity and and not the same type, they are neither IS nor ==.

Similar rules apply to the collation which is used only when the value is of type text (although any datatype may have a collation). That is:

1 (bare, by its lonesome) is an expression with the integer value 1 and has no affinity (and the default collation).

1 collate nocase is an expression with the integer value 1 and has no affinity however, it has the collation sequence nocase in the event that it gets text affinity applied.

(49) By anonymous on 2022-11-26 07:25:58 in reply to 48.2 [link] [source]

Sorry, yes, agree with you on the affinity.  I could have certainly written it up tighter in my description.

I was not suggesting that the items should have different affinity, but that the affinity of the items (or semantically more accurate, lack of affinity) was now affecting the query, where, prior to 3.41/3.40 they did not (in the same way).  By 'considered the same as...' I was referring to the handling by the query planner.  The affinity (and lack of) now effects that query where previously it handled 1, '1' or cast(1 as whatever) and the union worked.  That it now requires cast() is new behavior that will make queries much slower until everyone wraps the constants in cast()s.

It was a surprise that there is a now a restriction on optimization in this check-in (https://sqlite.org/src/info/1ad41840c5e0fa70) regarding column affinity.

In 3.39.4, the query works without the cast() to use the optimization, while  3.40 does not due to rule 8, and in the trunk 3.41 it does not due the push down optimization due to it not meeting the 'new' affinity rule 9 (in check-in cited).

If I understand you correctly, the 'new' requirements for that push down optimization require the 'new' affinity rule due to the collation rules (or perhaps to attempt not to get involved in this 'edge' case).

given table t (a text), index on t (a);

SELECT a FROM (SELECT a FROM t as o UNION select cast('1' as text) as a) WHERE a = 1;

works while

SELECT a FROM (SELECT a FROM t as o UNION select '1' as a) WHERE a = 1;

does not.

In 3.39.4 and prior, both worked (well, both used the push down optimization), so I am simply pointing out there is likely going to be some broken queries out there.  And the break will be a slow down like the OP, no error, just seems to hang.

Or folks can write casts around all unions with constants, matching each column's affinity (if they know where to look for the slow down).  I imagine there are going to be some frustrated folks out there on this.

Seems to me that in the above query, the a scan constant row could be cast to column affinity, but I can guess even that would have issues.  However as it is, a union constant without a cast won't ever meet rule 9 (as you point out, it has no affinity).  That seems like a less than desirable result of rule 9.

We seem to have one foot on Postel's law, and another is more STRICT :)

Anyway, thanks for the explanation and reference (though I've read/referred to that page so many times over the years I think I have it memorised by now).

(50) By anonymous on 2022-11-26 08:52:06 in reply to 48.2 [link] [source]

(2nd response)
Keith,

re: "That is to say, with clarity, that if one or the other side of a comparison has an affinity, then the side that has no affinity is converted to the same affinity as the other side."

That certainly has been the behaviour for a very very long time (since sqlite epoch 0).  And it's why the query (and many like it) worked in previous versions (before 3.40/3.41) and took advantage of the flexible typing of sqlite.

Requiring the select on the union to match the column affinity to use the push down optimization makes flexible typing a lot less useful.  A lot less.  In the OP query, without push down you had to write either a query with potentially different results (union all vs union is not the same set), or do the push down of the where manually.  And the slowdown came to them as a surprise.

Rule 9 also raises the question of how to match affinity on a STRICT table column of affinity/datatype "ANY".  I may be missing something, but can one write a union cast('hi' as ANY) as a? (typeof() says it's integer). And even if we could figure out how to write that to match the column affinity, is that something we want to force instead of 'hi' as a?

consider the following (done with 3.41 created from trunk this afternoon):

create table t (a ANY) strict;
create index i on t(a);
explain query plan select a from (select a from t as o union select cast('1' as ANY) as o) where a = 1;

query plan? you guessed it, left most sub-query is scan o (not search using index i).  In other words those queries are going to be slow but the sql writer will say 'I cast it to the column type!'.

In any event, having outlined what I see as the issue, seems to me a good place to put rule 9 in effect is on strict tables (with handling for ANY), and follow Postel (the pre 3.40 behavior) on the dynamic/flexible typed tables.  Perhaps take a another look at casting the constants (maybe wrap them in casts)on non strict tables.

I hope I am wrong, but I don't think we quite have the post 3.39.4 push down optimisation rules where they need to be to avoid a lot of pain for folks.

I think I've outlined about all I can, so I will leave things at that, as I have confidence in folks like yourself, Dr Hipp and everyone working on sqlite.

regards

(51) By anonymous on 2023-02-23 21:37:31 in reply to 50 [link] [source]

The check in 1ad41840 removes rule 9 (allows the where push down optimisation) and restores usage of an index.  With SQLite made from source tarball SQLite-a4aacdd3 :

create table t (a ANY) strict;
create index i on t(a);

for the two queries:

explain query plan select a from (select a from t as o union select cast('1' as ANY) as o) where a = 1;

and

explain query plan select a from (select a from t union select 1) where a = 1;

the query plans are identical, and now use the available index:

QUERY PLAN
|--CO-ROUTINE (subquery-2)
|  `--COMPOUND QUERY
|     |--LEFT-MOST SUBQUERY
|     |  `--SEARCH o USING COVERING INDEX i (a=?)
|     `--UNION USING TEMP B-TREE
|        `--SCAN CONSTANT ROW
`--SCAN (subquery-2)


This change should prevent/address unanticipated slowdowns post 3.39.4.

A big thanks to Dr Hipp and everyone for making it work AND making it faster.