SQLITE touches unchanged expression indexes on update

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE touches unchanged expression indexes on update

Deon Brewis
It seems like there is an opportunity for improvement on updates if an index contains expressions.

In the following example:

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooLenZ on Foo(length(z));

explain UPDATE foo SET x=1 WHERE rowid=1;

I see the plan below. Notice it's updating FooLenZ even though the 'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It doesn't try to update FooZ, just FooLenZ, but both should be untouched.

RecNo addr opcode      p1 p2 p3 p4        p5 comment
----- ---- ----------- -- -- -- --------- -- -------
    1 0    Init        0  30 0            00 (null)
    2 1    Null        0  7  8            00 (null)
    3 2    OpenWrite   0  2  0  3         00 (null)  // opening 'Foo' (expected)
    4 3    Integer     1  12 0            00 (null)
    5 4    SeekRowid   0  6  12           00 (null)
    6 5    Rowid       0  8  0            00 (null)
    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening 'FooLenZ' (NOT expected)
    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening 'FooX'   (expected)
    9 8    IsNull      8  29 0            00 (null)
   10 9    Integer     1  9  0            00 (null)
   11 10   Column      0  1  10           00 (null)
   12 11   Column      0  2  11           00 (null)
   13 12   Copy        11 13 0            00 (null)
   14 13   Function0   0  13 2  length(1) 01 (null)
   15 14   IntCopy     8  3  0            00 (null)
   16 15   MakeRecord  2  2  1            00 (null)
   17 16   SCopy       9  5  0            00 (null)
   18 17   IntCopy     8  6  0            00 (null)
   19 18   MakeRecord  5  2  4            00 (null)
   20 19   Copy        11 13 0            00 (null)
   21 20   Function0   0  13 14 length(1) 01 (null)
   22 21   Rowid       0  15 0            00 (null)
   23 22   IdxDelete   1  14 2            00 (null)
   24 23   Column      0  0  14           00 (null)
   25 24   IdxDelete   3  14 2            00 (null)
   26 25   IdxInsert   1  1  2  2         00 (null)  // updating 'FooLenZ' (NOT expected)
   27 26   IdxInsert   3  4  5  2         00 (null)  // updating 'FooX' (expected)
   28 27   MakeRecord  9  3  13           00 (null)
   29 28   Insert      0  13 8  Foo       05 (null)
   30 29   Halt        0  0  0            00 (null)
   31 30   Transaction 0  1  42 0         01 (null)
   32 31   Goto        0  1  0            00 (null)

sqlite_master:
RecNo type  name    tbl_name rootpage sql
----- ----- ------- -------- -------- --------------------------------------
    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on Foo(length(z))



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

David Raymond
Just showing with "explain comments" enabled.


sqlite> explain update foo set x = 1 where rowid = 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     33    0                    00  Start at 33
1     Null           0     7     8                    00  r[7..8]=NULL
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; Foo
3     Explain        3     0     0     SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?)  00
4     Integer        1     12    0                    00  r[12]=1
5     SeekRowid      0     7     12                   00  intkey=r[12]; pk
6     Rowid          0     8     0                    00  r[8]=rowid
7     OpenWrite      1     5     0     k(2,,)         00  root=5 iDb=0; FooLenZ
8     OpenWrite      3     3     0     k(2,,)         00  root=3 iDb=0; FooX
9     IsNull         8     32    0                    00  if r[8]==NULL goto 32
10    Integer        1     9     0                    00  r[9]=1
11    Column         0     1     10                   00  r[10]=Foo.y
12    Column         0     2     11                   00  r[11]=Foo.z
13    Noop           0     0     0                    00  uniqueness check for FooLenZ
14    Copy           11    13    0                    00  r[13]=r[11]
15    PureFunc0      0     13    2     length(1)      01  FooLenZ column 0
16    IntCopy        8     3     0                    00  r[3]=r[8]; rowid
17    MakeRecord     2     2     1                    00  r[1]=mkrec(r[2..3]); for FooLenZ
18    Noop           0     0     0                    00  uniqueness check for FooX
19    SCopy          9     5     0                    00  r[5]=r[9]; x
20    IntCopy        8     6     0                    00  r[6]=r[8]; rowid
21    MakeRecord     5     2     4                    00  r[4]=mkrec(r[5..6]); for FooX
22    Copy           11    13    0                    00  r[13]=r[11]
23    PureFunc0      0     13    14    length(1)      01
24    Rowid          0     15    0                    00  r[15]=rowid
25    IdxDelete      1     14    2                    00  key=r[14..15]
26    Column         0     0     14                   00  r[14]=Foo.x
27    IdxDelete      3     14    2                    00  key=r[14..15]
28    IdxInsert      1     1     2     2              00  key=r[1]
29    IdxInsert      3     4     5     2              00  key=r[4]
30    MakeRecord     9     3     13                   00  r[13]=mkrec(r[9..11])
31    Insert         0     13    8     Foo            05  intkey=r[8] data=r[13]
32    Halt           0     0     0                    00
33    Transaction    0     1     4     0              01  usesStmtJournal=0
34    Goto           0     1     0                    00


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Deon Brewis
Sent: Tuesday, September 11, 2018 8:20 PM
To: SQLite mailing list
Subject: [sqlite] SQLITE touches unchanged expression indexes on update

It seems like there is an opportunity for improvement on updates if an index contains expressions.

In the following example:

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooLenZ on Foo(length(z));

explain UPDATE foo SET x=1 WHERE rowid=1;

I see the plan below. Notice it's updating FooLenZ even though the 'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It doesn't try to update FooZ, just FooLenZ, but both should be untouched.

RecNo addr opcode      p1 p2 p3 p4        p5 comment
----- ---- ----------- -- -- -- --------- -- -------
    1 0    Init        0  30 0            00 (null)
    2 1    Null        0  7  8            00 (null)
    3 2    OpenWrite   0  2  0  3         00 (null)  // opening 'Foo' (expected)
    4 3    Integer     1  12 0            00 (null)
    5 4    SeekRowid   0  6  12           00 (null)
    6 5    Rowid       0  8  0            00 (null)
    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening 'FooLenZ' (NOT expected)
    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening 'FooX'   (expected)
    9 8    IsNull      8  29 0            00 (null)
   10 9    Integer     1  9  0            00 (null)
   11 10   Column      0  1  10           00 (null)
   12 11   Column      0  2  11           00 (null)
   13 12   Copy        11 13 0            00 (null)
   14 13   Function0   0  13 2  length(1) 01 (null)
   15 14   IntCopy     8  3  0            00 (null)
   16 15   MakeRecord  2  2  1            00 (null)
   17 16   SCopy       9  5  0            00 (null)
   18 17   IntCopy     8  6  0            00 (null)
   19 18   MakeRecord  5  2  4            00 (null)
   20 19   Copy        11 13 0            00 (null)
   21 20   Function0   0  13 14 length(1) 01 (null)
   22 21   Rowid       0  15 0            00 (null)
   23 22   IdxDelete   1  14 2            00 (null)
   24 23   Column      0  0  14           00 (null)
   25 24   IdxDelete   3  14 2            00 (null)
   26 25   IdxInsert   1  1  2  2         00 (null)  // updating 'FooLenZ' (NOT expected)
   27 26   IdxInsert   3  4  5  2         00 (null)  // updating 'FooX' (expected)
   28 27   MakeRecord  9  3  13           00 (null)
   29 28   Insert      0  13 8  Foo       05 (null)
   30 29   Halt        0  0  0            00 (null)
   31 30   Transaction 0  1  42 0         01 (null)
   32 31   Goto        0  1  0            00 (null)

sqlite_master:
RecNo type  name    tbl_name rootpage sql
----- ----- ------- -------- -------- --------------------------------------
    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on Foo(length(z))



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Richard Hipp-3
In reply to this post by Deon Brewis
On 9/11/18, Deon Brewis <[hidden email]> wrote:
> It seems like there is an opportunity for improvement on updates if an index
> contains expressions.

Thanks for bringing this to our attention.

We're on lockdown for the 3.25.0 release (bug fixes only) but I did
start looking at this to see how feasible it would be.  I found this
comment:

    https://www.sqlite.org/src/artifact/345ce35eb133?ln=306

So apparently this  has come up before :-)  That comment was inserted
on 2015-09-04.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Keith Medcalf
In reply to this post by David Raymond

Interesting ... I get different output with explain comments enabled:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     41    0                    00  Start at 41
1     Null           0     7     8                    00  r[7..8]=NULL
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; Foo
3     Noop           0     0     0                    00  Begin WHERE-loop0: Foo
4     Integer        1     12    0                    00  r[12]=1
5     SeekRowid      0     9     12                   00  intkey=r[12]
6     Noop           0     0     0                    00  Begin WHERE-core
7     Rowid          0     8     0                    00  r[8]=rowid
8     Noop           0     0     0                    00  End WHERE-core
9     Noop           0     0     0                    00  End WHERE-loop0: Foo
10    OpenWrite      1     5     0     k(2,,)         00  root=5 iDb=0; FooLenZ
11    OpenWrite      3     3     0     k(2,,)         00  root=3 iDb=0; FooX
12    IsNull         8     40    0                    00  if r[8]==NULL goto 40
13    Integer        1     9     0                    00  r[9]=1
14    Column         0     1     10                   00  r[10]=Foo.y
15    Column         0     2     11                   00  r[11]=Foo.z
16    Noop           0     0     0                    00  BEGIN: GenCnstCks(0,1,8,8,0)
17    Noop           0     0     0                    00  uniqueness check for FooLenZ
18    Copy           11    13    0                    00  r[13]=r[11]
19    PureFunc0      0     13    2     length(1)      01  FooLenZ column 0
20    IntCopy        8     3     0                    00  r[3]=r[8]; rowid
21    MakeRecord     2     2     1                    00  r[1]=mkrec(r[2..3]); for FooLenZ
22    Noop           0     0     0                    00  uniqueness check for FooX
23    SCopy          9     5     0                    00  r[5]=r[9]; x
24    IntCopy        8     6     0                    00  r[6]=r[8]; rowid
25    MakeRecord     5     2     4                    00  r[4]=mkrec(r[5..6]); for FooX
26    Noop           0     0     0                    00  END: GenCnstCks(0)
27    Noop           0     0     0                    00  GenRowIdxDel for FooLenZ
28    Column         0     2     13                   40  r[13]=Foo.z
29    PureFunc0      0     13    14    length(1)      01
30    Rowid          0     15    0                    00  r[15]=rowid
31    IdxDelete      1     14    2                    00  key=r[14..15]
32    Noop           0     0     0                    00  GenRowIdxDel for FooX
33    Column         0     0     14                   00  r[14]=Foo.x
34    IdxDelete      3     14    2                    00  key=r[14..15]
35    Delete         0     68    8     Foo            00
36    IdxInsert      1     1     2     2              00  key=r[1]
37    IdxInsert      3     4     5     2              00  key=r[4]
38    MakeRecord     9     3     13                   00  r[13]=mkrec(r[9..11])
39    Insert         0     13    8     Foo            05  intkey=r[8] data=r[13]
40    Halt           0     0     0                    00
41    Transaction    0     1     4     0              01  usesStmtJournal=0
42    Goto           0     1     0                    00



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 07:54
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>Just showing with "explain comments" enabled.
>
>
>sqlite> explain update foo set x = 1 where rowid = 1;
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     33    0                    00  Start at 33
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Explain        3     0     0     SEARCH TABLE foo USING INTEGER
>PRIMARY KEY (rowid=?)  00
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     7     12                   00
>intkey=r[12]; pk
>6     Rowid          0     8     0                    00  r[8]=rowid
>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>9     IsNull         8     32    0                    00  if
>r[8]==NULL goto 32
>10    Integer        1     9     0                    00  r[9]=1
>11    Column         0     1     10                   00  r[10]=Foo.y
>12    Column         0     2     11                   00  r[11]=Foo.z
>13    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>14    Copy           11    13    0                    00  r[13]=r[11]
>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>16    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>17    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>18    Noop           0     0     0                    00  uniqueness
>check for FooX
>19    SCopy          9     5     0                    00  r[5]=r[9];
>x
>20    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>21    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>22    Copy           11    13    0                    00  r[13]=r[11]
>23    PureFunc0      0     13    14    length(1)      01
>24    Rowid          0     15    0                    00  r[15]=rowid
>25    IdxDelete      1     14    2                    00
>key=r[14..15]
>26    Column         0     0     14                   00  r[14]=Foo.x
>27    IdxDelete      3     14    2                    00
>key=r[14..15]
>28    IdxInsert      1     1     2     2              00  key=r[1]
>29    IdxInsert      3     4     5     2              00  key=r[4]
>30    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>31    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>32    Halt           0     0     0                    00
>33    Transaction    0     1     4     0              01
>usesStmtJournal=0
>34    Goto           0     1     0                    00
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Deon Brewis
>Sent: Tuesday, September 11, 2018 8:20 PM
>To: SQLite mailing list
>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>It seems like there is an opportunity for improvement on updates if
>an index contains expressions.
>
>In the following example:
>
>CREATE TABLE Foo(x, y, z);
>CREATE INDEX FooX on Foo(x);
>CREATE INDEX FooZ on Foo(z);
>CREATE INDEX FooLenZ on Foo(length(z));
>
>explain UPDATE foo SET x=1 WHERE rowid=1;
>
>I see the plan below. Notice it's updating FooLenZ even though the
>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It
>doesn't try to update FooZ, just FooLenZ, but both should be
>untouched.
>
>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>----- ---- ----------- -- -- -- --------- -- -------
>    1 0    Init        0  30 0            00 (null)
>    2 1    Null        0  7  8            00 (null)
>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening 'Foo'
>(expected)
>    4 3    Integer     1  12 0            00 (null)
>    5 4    SeekRowid   0  6  12           00 (null)
>    6 5    Rowid       0  8  0            00 (null)
>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>'FooLenZ' (NOT expected)
>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>'FooX'   (expected)
>    9 8    IsNull      8  29 0            00 (null)
>   10 9    Integer     1  9  0            00 (null)
>   11 10   Column      0  1  10           00 (null)
>   12 11   Column      0  2  11           00 (null)
>   13 12   Copy        11 13 0            00 (null)
>   14 13   Function0   0  13 2  length(1) 01 (null)
>   15 14   IntCopy     8  3  0            00 (null)
>   16 15   MakeRecord  2  2  1            00 (null)
>   17 16   SCopy       9  5  0            00 (null)
>   18 17   IntCopy     8  6  0            00 (null)
>   19 18   MakeRecord  5  2  4            00 (null)
>   20 19   Copy        11 13 0            00 (null)
>   21 20   Function0   0  13 14 length(1) 01 (null)
>   22 21   Rowid       0  15 0            00 (null)
>   23 22   IdxDelete   1  14 2            00 (null)
>   24 23   Column      0  0  14           00 (null)
>   25 24   IdxDelete   3  14 2            00 (null)
>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>'FooLenZ' (NOT expected)
>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>'FooX' (expected)
>   28 27   MakeRecord  9  3  13           00 (null)
>   29 28   Insert      0  13 8  Foo       05 (null)
>   30 29   Halt        0  0  0            00 (null)
>   31 30   Transaction 0  1  42 0         01 (null)
>   32 31   Goto        0  1  0            00 (null)
>
>sqlite_master:
>RecNo type  name    tbl_name rootpage sql
>----- ----- ------- -------- -------- -------------------------------
>-------
>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>Foo(length(z))
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

David Raymond
A little weird and definitely differet, what version are you using and how are you compiling it? I checked with the precompiled Windows binary to make sure it wasn't something weird from my compilation, and it looks pretty close to my original, but different. So now I'm wondering where the noop's, explain's, and comments are coming from and what affects them.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Keith Medcalf
Sent: Wednesday, September 12, 2018 11:40 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update


Interesting ... I get different output with explain comments enabled:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     41    0                    00  Start at 41
1     Null           0     7     8                    00  r[7..8]=NULL
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; Foo
3     Noop           0     0     0                    00  Begin WHERE-loop0: Foo
4     Integer        1     12    0                    00  r[12]=1
5     SeekRowid      0     9     12                   00  intkey=r[12]
6     Noop           0     0     0                    00  Begin WHERE-core
7     Rowid          0     8     0                    00  r[8]=rowid
8     Noop           0     0     0                    00  End WHERE-core
9     Noop           0     0     0                    00  End WHERE-loop0: Foo
10    OpenWrite      1     5     0     k(2,,)         00  root=5 iDb=0; FooLenZ
11    OpenWrite      3     3     0     k(2,,)         00  root=3 iDb=0; FooX
12    IsNull         8     40    0                    00  if r[8]==NULL goto 40
13    Integer        1     9     0                    00  r[9]=1
14    Column         0     1     10                   00  r[10]=Foo.y
15    Column         0     2     11                   00  r[11]=Foo.z
16    Noop           0     0     0                    00  BEGIN: GenCnstCks(0,1,8,8,0)
17    Noop           0     0     0                    00  uniqueness check for FooLenZ
18    Copy           11    13    0                    00  r[13]=r[11]
19    PureFunc0      0     13    2     length(1)      01  FooLenZ column 0
20    IntCopy        8     3     0                    00  r[3]=r[8]; rowid
21    MakeRecord     2     2     1                    00  r[1]=mkrec(r[2..3]); for FooLenZ
22    Noop           0     0     0                    00  uniqueness check for FooX
23    SCopy          9     5     0                    00  r[5]=r[9]; x
24    IntCopy        8     6     0                    00  r[6]=r[8]; rowid
25    MakeRecord     5     2     4                    00  r[4]=mkrec(r[5..6]); for FooX
26    Noop           0     0     0                    00  END: GenCnstCks(0)
27    Noop           0     0     0                    00  GenRowIdxDel for FooLenZ
28    Column         0     2     13                   40  r[13]=Foo.z
29    PureFunc0      0     13    14    length(1)      01
30    Rowid          0     15    0                    00  r[15]=rowid
31    IdxDelete      1     14    2                    00  key=r[14..15]
32    Noop           0     0     0                    00  GenRowIdxDel for FooX
33    Column         0     0     14                   00  r[14]=Foo.x
34    IdxDelete      3     14    2                    00  key=r[14..15]
35    Delete         0     68    8     Foo            00
36    IdxInsert      1     1     2     2              00  key=r[1]
37    IdxInsert      3     4     5     2              00  key=r[4]
38    MakeRecord     9     3     13                   00  r[13]=mkrec(r[9..11])
39    Insert         0     13    8     Foo            05  intkey=r[8] data=r[13]
40    Halt           0     0     0                    00
41    Transaction    0     1     4     0              01  usesStmtJournal=0
42    Goto           0     1     0                    00



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 07:54
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>Just showing with "explain comments" enabled.
>
>
>sqlite> explain update foo set x = 1 where rowid = 1;
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     33    0                    00  Start at 33
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Explain        3     0     0     SEARCH TABLE foo USING INTEGER
>PRIMARY KEY (rowid=?)  00
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     7     12                   00
>intkey=r[12]; pk
>6     Rowid          0     8     0                    00  r[8]=rowid
>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>9     IsNull         8     32    0                    00  if
>r[8]==NULL goto 32
>10    Integer        1     9     0                    00  r[9]=1
>11    Column         0     1     10                   00  r[10]=Foo.y
>12    Column         0     2     11                   00  r[11]=Foo.z
>13    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>14    Copy           11    13    0                    00  r[13]=r[11]
>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>16    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>17    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>18    Noop           0     0     0                    00  uniqueness
>check for FooX
>19    SCopy          9     5     0                    00  r[5]=r[9];
>x
>20    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>21    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>22    Copy           11    13    0                    00  r[13]=r[11]
>23    PureFunc0      0     13    14    length(1)      01
>24    Rowid          0     15    0                    00  r[15]=rowid
>25    IdxDelete      1     14    2                    00
>key=r[14..15]
>26    Column         0     0     14                   00  r[14]=Foo.x
>27    IdxDelete      3     14    2                    00
>key=r[14..15]
>28    IdxInsert      1     1     2     2              00  key=r[1]
>29    IdxInsert      3     4     5     2              00  key=r[4]
>30    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>31    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>32    Halt           0     0     0                    00
>33    Transaction    0     1     4     0              01
>usesStmtJournal=0
>34    Goto           0     1     0                    00
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Deon Brewis
>Sent: Tuesday, September 11, 2018 8:20 PM
>To: SQLite mailing list
>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>It seems like there is an opportunity for improvement on updates if
>an index contains expressions.
>
>In the following example:
>
>CREATE TABLE Foo(x, y, z);
>CREATE INDEX FooX on Foo(x);
>CREATE INDEX FooZ on Foo(z);
>CREATE INDEX FooLenZ on Foo(length(z));
>
>explain UPDATE foo SET x=1 WHERE rowid=1;
>
>I see the plan below. Notice it's updating FooLenZ even though the
>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It
>doesn't try to update FooZ, just FooLenZ, but both should be
>untouched.
>
>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>----- ---- ----------- -- -- -- --------- -- -------
>    1 0    Init        0  30 0            00 (null)
>    2 1    Null        0  7  8            00 (null)
>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening 'Foo'
>(expected)
>    4 3    Integer     1  12 0            00 (null)
>    5 4    SeekRowid   0  6  12           00 (null)
>    6 5    Rowid       0  8  0            00 (null)
>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>'FooLenZ' (NOT expected)
>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>'FooX'   (expected)
>    9 8    IsNull      8  29 0            00 (null)
>   10 9    Integer     1  9  0            00 (null)
>   11 10   Column      0  1  10           00 (null)
>   12 11   Column      0  2  11           00 (null)
>   13 12   Copy        11 13 0            00 (null)
>   14 13   Function0   0  13 2  length(1) 01 (null)
>   15 14   IntCopy     8  3  0            00 (null)
>   16 15   MakeRecord  2  2  1            00 (null)
>   17 16   SCopy       9  5  0            00 (null)
>   18 17   IntCopy     8  6  0            00 (null)
>   19 18   MakeRecord  5  2  4            00 (null)
>   20 19   Copy        11 13 0            00 (null)
>   21 20   Function0   0  13 14 length(1) 01 (null)
>   22 21   Rowid       0  15 0            00 (null)
>   23 22   IdxDelete   1  14 2            00 (null)
>   24 23   Column      0  0  14           00 (null)
>   25 24   IdxDelete   3  14 2            00 (null)
>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>'FooLenZ' (NOT expected)
>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>'FooX' (expected)
>   28 27   MakeRecord  9  3  13           00 (null)
>   29 28   Insert      0  13 8  Foo       05 (null)
>   30 29   Halt        0  0  0            00 (null)
>   31 30   Transaction 0  1  42 0         01 (null)
>   32 31   Goto        0  1  0            00 (null)
>
>sqlite_master:
>RecNo type  name    tbl_name rootpage sql
>----- ----- ------- -------- -------- -------------------------------
>-------
>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>Foo(length(z))
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Keith Medcalf

The version is the current tip of trunk, compiler is MinGW GCC 8.1.0 (on Windows 10) ... with the following options defined:

#define _WIN32_WINNT 0x0600
#define WINVER _WIN32_WINNT
#define SQLITE_DEFAULT_CACHE_SIZE          262144           // 1 GB
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE     262144           // 1 GB default: 500 pages
#define SQLITE_DEFAULT_FOREIGN_KEYS         1               // default: 0
#define SQLITE_DEFAULT_PAGE_SIZE            4096            // default: 4096 max: 65536
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256             // default: 1000 pages
#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755            // default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1               // default: 0
#define SQLITE_DEFAULT_SHARED_CACHE         0               // default: 0
#define SQLITE_DEFAULT_MMAP_SIZE            0               // default: 0
#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#define SQLITE_INTROSPECTION_PRAGMAS 1                      // Add Instropsection Pragmas
#define SQLITE_ENABLE_8_3_NAMES 1
#define SQLITE_ENABLE_API_ARMOR 1                           // Enable API Armour
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define SQLITE_ENABLE_COSTMULT 1
#define SQLITE_ENABLE_CURSOR_HINTS 1
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1
#define SQLITE_ENABLE_DBPAGE_VTAB 1
#define SQLITE_ENABLE_DBSTAT_VTAB 1
#define SQLITE_ENABLE_DESERIALIZE 1
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
#define SQLITE_ENABLE_FTS4 1
#define SQLITE_ENABLE_FTS5 1
#define SQLITE_ENABLE_JSON1 1                               // Enable JSON1 -- when standard extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#define SQLITE_ENABLE_LOCKING_STYLE 1
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1                   // Enable Memory Management (sqlite3_release_memory)
#define SQLITE_ENABLE_MODULE_COMMENTS 1
#define SQLITE_ENABLE_PREUPDATE_HOOK 1
#define SQLITE_ENABLE_RTREE 1
#define SQLITE_ENABLE_SORTER_REFERENCES 1                   // Enable Sorter References
#define SQLITE_ENABLE_STAT_VTAB 1                           // Enable dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1
#define SQLITE_ENABLE_STAT2 1
#define SQLITE_ENABLE_STAT3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_ENABLE_STMTVTAB 1                            // Enable Stmt VTAB
#define SQLITE_ENABLE_UNIONVTAB 1                           // Enable unionvtab
#define SQLITE_STAT4_SAMPLES 64                             // default: 24 samples
#define SQLITE_SOUNDEX 1
#define SQLITE_THREADSAFE 1                                 // 0 = Single Threaded, 1 = Serialized, 2 = Multithreaded
#define SQLITE_TEMP_STORE 2                                 // 0 = Files Always, 1 = Files, 2 = Memory, 3 Memory Always
#define SQLITE_USE_URI 1                                    // Enable URI Filenames
#define SQLITE_ALLOW_URI_AUTHORITY 1                        // Allow Authority (Host) in URI
#define SQLITE_MAX_ATTACHED                 15              // default: 10          max: 62
#define SQLITE_OS_WIN 1
#define SQLITE_OS_WINNT 1
#define SQLITE_NOW_STABILITY_STMT 1                         // Make 'now' stable within a statement, not only for a step
#define WHERE_PATH_SIMPLE 50                                // Paths to remember for  2-way joins
#define WHERE_PATH_COMPLEX 100                              // Paths to remember for >2-way joins
#define SQLITE_USE_PRECISE_TIME 1                           // Use GetSystemTimePreciseAsFileTime
#define SQLITE_DATETIME_NEW 1                               // Use New Datetime Functions
#define SQLITE_WIN32_FILE_RANDOM 1                          // Force Windows RANDOM access cache behaviour
#define SQLITE_DEFAULT_WAL_SYNCHRONOUS      1                   // Reduce Synchronous to NORMAL in WAL mode
#define SQLITE_DIRECT_OVERFLOW_READ         1                   // Do not cache overflow pages in SQLite pagecache
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1                   // Disable LIKE matching for BLOBS
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#define UNICODE_STRING_MAX_BYTES ((WORD)65534)
#define UNICODE_STRING_MAX_CHARS (32766)
#define HAVE_ISNAN 1
#define SQLITE_USE_MALLOC_H 1
#define SQLITE_USE_MSIZE 1
#define LONGDOUBLE_TYPE __float128

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 10:21
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>A little weird and definitely differet, what version are you using
>and how are you compiling it? I checked with the precompiled Windows
>binary to make sure it wasn't something weird from my compilation,
>and it looks pretty close to my original, but different. So now I'm
>wondering where the noop's, explain's, and comments are coming from
>and what affects them.
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Wednesday, September 12, 2018 11:40 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>
>Interesting ... I get different output with explain comments enabled:
>
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     41    0                    00  Start at 41
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Noop           0     0     0                    00  Begin
>WHERE-loop0: Foo
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     9     12                   00
>intkey=r[12]
>6     Noop           0     0     0                    00  Begin
>WHERE-core
>7     Rowid          0     8     0                    00  r[8]=rowid
>8     Noop           0     0     0                    00  End WHERE-
>core
>9     Noop           0     0     0                    00  End WHERE-
>loop0: Foo
>10    OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>11    OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>12    IsNull         8     40    0                    00  if
>r[8]==NULL goto 40
>13    Integer        1     9     0                    00  r[9]=1
>14    Column         0     1     10                   00  r[10]=Foo.y
>15    Column         0     2     11                   00  r[11]=Foo.z
>16    Noop           0     0     0                    00  BEGIN:
>GenCnstCks(0,1,8,8,0)
>17    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>18    Copy           11    13    0                    00  r[13]=r[11]
>19    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>20    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>21    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>22    Noop           0     0     0                    00  uniqueness
>check for FooX
>23    SCopy          9     5     0                    00  r[5]=r[9];
>x
>24    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>25    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>26    Noop           0     0     0                    00  END:
>GenCnstCks(0)
>27    Noop           0     0     0                    00
>GenRowIdxDel for FooLenZ
>28    Column         0     2     13                   40  r[13]=Foo.z
>29    PureFunc0      0     13    14    length(1)      01
>30    Rowid          0     15    0                    00  r[15]=rowid
>31    IdxDelete      1     14    2                    00
>key=r[14..15]
>32    Noop           0     0     0                    00
>GenRowIdxDel for FooX
>33    Column         0     0     14                   00  r[14]=Foo.x
>34    IdxDelete      3     14    2                    00
>key=r[14..15]
>35    Delete         0     68    8     Foo            00
>36    IdxInsert      1     1     2     2              00  key=r[1]
>37    IdxInsert      3     4     5     2              00  key=r[4]
>38    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>39    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>40    Halt           0     0     0                    00
>41    Transaction    0     1     4     0              01
>usesStmtJournal=0
>42    Goto           0     1     0                    00
>
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of David Raymond
>>Sent: Wednesday, 12 September, 2018 07:54
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>Just showing with "explain comments" enabled.
>>
>>
>>sqlite> explain update foo set x = 1 where rowid = 1;
>>addr  opcode         p1    p2    p3    p4             p5  comment
>>----  -------------  ----  ----  ----  -------------  --  ----------
>-
>>--
>>0     Init           0     33    0                    00  Start at
>33
>>1     Null           0     7     8                    00
>>r[7..8]=NULL
>>2     OpenWrite      0     2     0     3              00  root=2
>>iDb=0; Foo
>>3     Explain        3     0     0     SEARCH TABLE foo USING
>INTEGER
>>PRIMARY KEY (rowid=?)  00
>>4     Integer        1     12    0                    00  r[12]=1
>>5     SeekRowid      0     7     12                   00
>>intkey=r[12]; pk
>>6     Rowid          0     8     0                    00  r[8]=rowid
>>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>>iDb=0; FooLenZ
>>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>>iDb=0; FooX
>>9     IsNull         8     32    0                    00  if
>>r[8]==NULL goto 32
>>10    Integer        1     9     0                    00  r[9]=1
>>11    Column         0     1     10                   00
>r[10]=Foo.y
>>12    Column         0     2     11                   00
>r[11]=Foo.z
>>13    Noop           0     0     0                    00  uniqueness
>>check for FooLenZ
>>14    Copy           11    13    0                    00
>r[13]=r[11]
>>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>column 0
>>16    IntCopy        8     3     0                    00  r[3]=r[8];
>>rowid
>>17    MakeRecord     2     2     1                    00
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>18    Noop           0     0     0                    00  uniqueness
>>check for FooX
>>19    SCopy          9     5     0                    00  r[5]=r[9];
>>x
>>20    IntCopy        8     6     0                    00  r[6]=r[8];
>>rowid
>>21    MakeRecord     5     2     4                    00
>>r[4]=mkrec(r[5..6]); for FooX
>>22    Copy           11    13    0                    00
>r[13]=r[11]
>>23    PureFunc0      0     13    14    length(1)      01
>>24    Rowid          0     15    0                    00
>r[15]=rowid
>>25    IdxDelete      1     14    2                    00
>>key=r[14..15]
>>26    Column         0     0     14                   00
>r[14]=Foo.x
>>27    IdxDelete      3     14    2                    00
>>key=r[14..15]
>>28    IdxInsert      1     1     2     2              00  key=r[1]
>>29    IdxInsert      3     4     5     2              00  key=r[4]
>>30    MakeRecord     9     3     13                   00
>>r[13]=mkrec(r[9..11])
>>31    Insert         0     13    8     Foo            05
>intkey=r[8]
>>data=r[13]
>>32    Halt           0     0     0                    00
>>33    Transaction    0     1     4     0              01
>>usesStmtJournal=0
>>34    Goto           0     1     0                    00
>>
>>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Deon Brewis
>>Sent: Tuesday, September 11, 2018 8:20 PM
>>To: SQLite mailing list
>>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>It seems like there is an opportunity for improvement on updates if
>>an index contains expressions.
>>
>>In the following example:
>>
>>CREATE TABLE Foo(x, y, z);
>>CREATE INDEX FooX on Foo(x);
>>CREATE INDEX FooZ on Foo(z);
>>CREATE INDEX FooLenZ on Foo(length(z));
>>
>>explain UPDATE foo SET x=1 WHERE rowid=1;
>>
>>I see the plan below. Notice it's updating FooLenZ even though the
>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It
>>doesn't try to update FooZ, just FooLenZ, but both should be
>>untouched.
>>
>>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>>----- ---- ----------- -- -- -- --------- -- -------
>>    1 0    Init        0  30 0            00 (null)
>>    2 1    Null        0  7  8            00 (null)
>>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening
>'Foo'
>>(expected)
>>    4 3    Integer     1  12 0            00 (null)
>>    5 4    SeekRowid   0  6  12           00 (null)
>>    6 5    Rowid       0  8  0            00 (null)
>>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>>'FooLenZ' (NOT expected)
>>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>>'FooX'   (expected)
>>    9 8    IsNull      8  29 0            00 (null)
>>   10 9    Integer     1  9  0            00 (null)
>>   11 10   Column      0  1  10           00 (null)
>>   12 11   Column      0  2  11           00 (null)
>>   13 12   Copy        11 13 0            00 (null)
>>   14 13   Function0   0  13 2  length(1) 01 (null)
>>   15 14   IntCopy     8  3  0            00 (null)
>>   16 15   MakeRecord  2  2  1            00 (null)
>>   17 16   SCopy       9  5  0            00 (null)
>>   18 17   IntCopy     8  6  0            00 (null)
>>   19 18   MakeRecord  5  2  4            00 (null)
>>   20 19   Copy        11 13 0            00 (null)
>>   21 20   Function0   0  13 14 length(1) 01 (null)
>>   22 21   Rowid       0  15 0            00 (null)
>>   23 22   IdxDelete   1  14 2            00 (null)
>>   24 23   Column      0  0  14           00 (null)
>>   25 24   IdxDelete   3  14 2            00 (null)
>>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>>'FooLenZ' (NOT expected)
>>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>>'FooX' (expected)
>>   28 27   MakeRecord  9  3  13           00 (null)
>>   29 28   Insert      0  13 8  Foo       05 (null)
>>   30 29   Halt        0  0  0            00 (null)
>>   31 30   Transaction 0  1  42 0         01 (null)
>>   32 31   Goto        0  1  0            00 (null)
>>
>>sqlite_master:
>>RecNo type  name    tbl_name rootpage sql
>>----- ----- ------- -------- -------- ------------------------------
>-
>>-------
>>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>>Foo(length(z))
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Deon Brewis
In reply to this post by Richard Hipp-3
Thanks a lot!

I've just noticed it also has the same behavior for partial indexes.

Here is an explain with a partial index (and using a later build this time for my output...).

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooLenZ on Foo(length(z));
CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42;
explain UPDATE foo SET x=1 WHERE rowid=1;

sqlite> explain UPDATE foo SET x=1 WHERE rowid=1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     45    0                    00  Start at 45
1     Null           0     10    11                   00  r[10..11]=NULL
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; Foo
3     Integer        1     15    0                    00  r[15]=1
4     SeekRowid      0     6     15                   00  intkey=r[15]; pk
5     Rowid          0     11    0                    00  r[11]=rowid
6     OpenWrite      1     6     0     k(2,,)         00  root=6 iDb=0; FooPartialZ
7     OpenWrite      2     5     0     k(2,,)         00  root=5 iDb=0; FooLenZ
8     OpenWrite      4     3     0     k(2,,)         00  root=3 iDb=0; FooX
9     IsNull         11    44    0                    00  if r[11]==NULL goto 44
10    Integer        1     12    0                    00  r[12]=1
11    Column         0     1     13                   00  r[13]=Foo.y
12    Column         0     2     14                   00  r[14]=Foo.z
13    Noop           0     0     0                    00  uniqueness check for FooPartialZ
14    Null           0     1     0                    00  r[1]=NULL
15    Le             17    19    14    (BINARY)       51  if r[14]<=r[17] goto 19
16    SCopy          14    2     0                    00  r[2]=r[14]; z
17    IntCopy        11    3     0                    00  r[3]=r[11]; rowid
18    MakeRecord     2     2     1                    00  r[1]=mkrec(r[2..3]); for FooPartialZ
19    Noop           0     0     0                    00  uniqueness check for FooLenZ
20    Copy           14    16    0                    00  r[16]=r[14]
21    PureFunc0      0     16    5     length(1)      01  FooLenZ column 0
22    IntCopy        11    6     0                    00  r[6]=r[11]; rowid
23    MakeRecord     5     2     4                    00  r[4]=mkrec(r[5..6]); for FooLenZ
24    Noop           0     0     0                    00  uniqueness check for FooX
25    SCopy          12    8     0                    00  r[8]=r[12]; x
26    IntCopy        11    9     0                    00  r[9]=r[11]; rowid
27    MakeRecord     8     2     7                    00  r[7]=mkrec(r[8..9]); for FooX
28    Le             17    32    14    (BINARY)       51  if r[14]<=r[17] goto 32
29    Column         0     2     18                   00  r[18]=Foo.z
30    Rowid          0     19    0                    00  r[19]=rowid
31    IdxDelete      1     18    2                    00  key=r[18..19]
32    Copy           14    16    0                    00  r[16]=r[14]
33    PureFunc0      0     16    18    length(1)      01
34    Rowid          0     19    0                    00  r[19]=rowid
35    IdxDelete      2     18    2                    00  key=r[18..19]
36    Column         0     0     18                   00  r[18]=Foo.x
37    IdxDelete      4     18    2                    00  key=r[18..19]
38    IsNull         1     40    0                    00  if r[1]==NULL goto 40
39    IdxInsert      1     1     2     2              00  key=r[1]
40    IdxInsert      2     4     5     2              00  key=r[4]
41    IdxInsert      4     7     8     2              00  key=r[7]
42    MakeRecord     12    3     16                   00  r[16]=mkrec(r[12..14])
43    Insert         0     16    11    Foo            05  intkey=r[11] data=r[16]
44    Halt           0     0     0                    00
45    Transaction    0     1     5     0              01  usesStmtJournal=0
46    Integer        42    17    0                    00  r[17]=42
47    Goto           0     1     0                    00

- Deon

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Richard Hipp
Sent: Wednesday, September 12, 2018 7:05 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update

On 9/11/18, Deon Brewis <[hidden email]> wrote:
> It seems like there is an opportunity for improvement on updates if an
> index contains expressions.

Thanks for bringing this to our attention.

We're on lockdown for the 3.25.0 release (bug fixes only) but I did start looking at this to see how feasible it would be.  I found this
comment:

    https://www.sqlite.org/src/artifact/345ce35eb133?ln=306

So apparently this  has come up before :-)  That comment was inserted on 2015-09-04.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Deon Brewis
In reply to this post by Keith Medcalf
Sorry guys, I was using 3.17 for the output of the email (we're not actually using that in our product - I accidentally used an old sqlite3.exe to paste it in here).

However, it still repros on 3.24 - it just looks different. See my latest mail to Richard with the updated opcodes and the repro with Partial Indexes as well.

- Deon

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Keith Medcalf
Sent: Wednesday, September 12, 2018 10:04 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update


The version is the current tip of trunk, compiler is MinGW GCC 8.1.0 (on Windows 10) ... with the following options defined:

#define _WIN32_WINNT 0x0600
#define WINVER _WIN32_WINNT
#define SQLITE_DEFAULT_CACHE_SIZE          262144           // 1 GB
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE     262144           // 1 GB default: 500 pages
#define SQLITE_DEFAULT_FOREIGN_KEYS         1               // default: 0
#define SQLITE_DEFAULT_PAGE_SIZE            4096            // default: 4096 max: 65536
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256             // default: 1000 pages
#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755            // default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1               // default: 0
#define SQLITE_DEFAULT_SHARED_CACHE         0               // default: 0
#define SQLITE_DEFAULT_MMAP_SIZE            0               // default: 0
#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#define SQLITE_INTROSPECTION_PRAGMAS 1                      // Add Instropsection Pragmas
#define SQLITE_ENABLE_8_3_NAMES 1
#define SQLITE_ENABLE_API_ARMOR 1                           // Enable API Armour
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define SQLITE_ENABLE_COSTMULT 1
#define SQLITE_ENABLE_CURSOR_HINTS 1
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1 #define SQLITE_ENABLE_DBPAGE_VTAB 1 #define SQLITE_ENABLE_DBSTAT_VTAB 1 #define SQLITE_ENABLE_DESERIALIZE 1 #define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 #define SQLITE_EXPLAIN_ESTIMATED_ROWS 1 #define SQLITE_ENABLE_FTS3 1 #define SQLITE_ENABLE_FTS3_PARENTHESIS 1 #define SQLITE_ENABLE_FTS4 1 #define SQLITE_ENABLE_FTS5 1
#define SQLITE_ENABLE_JSON1 1                               // Enable JSON1 -- when standard extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#define SQLITE_ENABLE_LOCKING_STYLE 1
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1                   // Enable Memory Management (sqlite3_release_memory)
#define SQLITE_ENABLE_MODULE_COMMENTS 1
#define SQLITE_ENABLE_PREUPDATE_HOOK 1
#define SQLITE_ENABLE_RTREE 1
#define SQLITE_ENABLE_SORTER_REFERENCES 1                   // Enable Sorter References
#define SQLITE_ENABLE_STAT_VTAB 1                           // Enable dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1
#define SQLITE_ENABLE_STAT2 1
#define SQLITE_ENABLE_STAT3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_ENABLE_STMTVTAB 1                            // Enable Stmt VTAB
#define SQLITE_ENABLE_UNIONVTAB 1                           // Enable unionvtab
#define SQLITE_STAT4_SAMPLES 64                             // default: 24 samples
#define SQLITE_SOUNDEX 1
#define SQLITE_THREADSAFE 1                                 // 0 = Single Threaded, 1 = Serialized, 2 = Multithreaded
#define SQLITE_TEMP_STORE 2                                 // 0 = Files Always, 1 = Files, 2 = Memory, 3 Memory Always
#define SQLITE_USE_URI 1                                    // Enable URI Filenames
#define SQLITE_ALLOW_URI_AUTHORITY 1                        // Allow Authority (Host) in URI
#define SQLITE_MAX_ATTACHED                 15              // default: 10          max: 62
#define SQLITE_OS_WIN 1
#define SQLITE_OS_WINNT 1
#define SQLITE_NOW_STABILITY_STMT 1                         // Make 'now' stable within a statement, not only for a step
#define WHERE_PATH_SIMPLE 50                                // Paths to remember for  2-way joins
#define WHERE_PATH_COMPLEX 100                              // Paths to remember for >2-way joins
#define SQLITE_USE_PRECISE_TIME 1                           // Use GetSystemTimePreciseAsFileTime
#define SQLITE_DATETIME_NEW 1                               // Use New Datetime Functions
#define SQLITE_WIN32_FILE_RANDOM 1                          // Force Windows RANDOM access cache behaviour
#define SQLITE_DEFAULT_WAL_SYNCHRONOUS      1                   // Reduce Synchronous to NORMAL in WAL mode
#define SQLITE_DIRECT_OVERFLOW_READ         1                   // Do not cache overflow pages in SQLite pagecache
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1                   // Disable LIKE matching for BLOBS
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#define UNICODE_STRING_MAX_BYTES ((WORD)65534) #define UNICODE_STRING_MAX_CHARS (32766) #define HAVE_ISNAN 1 #define SQLITE_USE_MALLOC_H 1 #define SQLITE_USE_MSIZE 1 #define LONGDOUBLE_TYPE __float128

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 10:21
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>A little weird and definitely differet, what version are you using and
>how are you compiling it? I checked with the precompiled Windows binary
>to make sure it wasn't something weird from my compilation, and it
>looks pretty close to my original, but different. So now I'm wondering
>where the noop's, explain's, and comments are coming from and what
>affects them.
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Wednesday, September 12, 2018 11:40 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>
>Interesting ... I get different output with explain comments enabled:
>
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     41    0                    00  Start at 41
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Noop           0     0     0                    00  Begin
>WHERE-loop0: Foo
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     9     12                   00
>intkey=r[12]
>6     Noop           0     0     0                    00  Begin
>WHERE-core
>7     Rowid          0     8     0                    00  r[8]=rowid
>8     Noop           0     0     0                    00  End WHERE-
>core
>9     Noop           0     0     0                    00  End WHERE-
>loop0: Foo
>10    OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>11    OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>12    IsNull         8     40    0                    00  if
>r[8]==NULL goto 40
>13    Integer        1     9     0                    00  r[9]=1
>14    Column         0     1     10                   00  r[10]=Foo.y
>15    Column         0     2     11                   00  r[11]=Foo.z
>16    Noop           0     0     0                    00  BEGIN:
>GenCnstCks(0,1,8,8,0)
>17    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>18    Copy           11    13    0                    00  r[13]=r[11]
>19    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>20    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>21    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>22    Noop           0     0     0                    00  uniqueness
>check for FooX
>23    SCopy          9     5     0                    00  r[5]=r[9];
>x
>24    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>25    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>26    Noop           0     0     0                    00  END:
>GenCnstCks(0)
>27    Noop           0     0     0                    00
>GenRowIdxDel for FooLenZ
>28    Column         0     2     13                   40  r[13]=Foo.z
>29    PureFunc0      0     13    14    length(1)      01
>30    Rowid          0     15    0                    00  r[15]=rowid
>31    IdxDelete      1     14    2                    00
>key=r[14..15]
>32    Noop           0     0     0                    00
>GenRowIdxDel for FooX
>33    Column         0     0     14                   00  r[14]=Foo.x
>34    IdxDelete      3     14    2                    00
>key=r[14..15]
>35    Delete         0     68    8     Foo            00
>36    IdxInsert      1     1     2     2              00  key=r[1]
>37    IdxInsert      3     4     5     2              00  key=r[4]
>38    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>39    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>40    Halt           0     0     0                    00
>41    Transaction    0     1     4     0              01
>usesStmtJournal=0
>42    Goto           0     1     0                    00
>
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of David Raymond
>>Sent: Wednesday, 12 September, 2018 07:54
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>Just showing with "explain comments" enabled.
>>
>>
>>sqlite> explain update foo set x = 1 where rowid = 1;
>>addr  opcode         p1    p2    p3    p4             p5  comment
>>----  -------------  ----  ----  ----  -------------  --  ----------
>-
>>--
>>0     Init           0     33    0                    00  Start at
>33
>>1     Null           0     7     8                    00
>>r[7..8]=NULL
>>2     OpenWrite      0     2     0     3              00  root=2
>>iDb=0; Foo
>>3     Explain        3     0     0     SEARCH TABLE foo USING
>INTEGER
>>PRIMARY KEY (rowid=?)  00
>>4     Integer        1     12    0                    00  r[12]=1
>>5     SeekRowid      0     7     12                   00
>>intkey=r[12]; pk
>>6     Rowid          0     8     0                    00  r[8]=rowid
>>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>>iDb=0; FooLenZ
>>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>>iDb=0; FooX
>>9     IsNull         8     32    0                    00  if
>>r[8]==NULL goto 32
>>10    Integer        1     9     0                    00  r[9]=1
>>11    Column         0     1     10                   00
>r[10]=Foo.y
>>12    Column         0     2     11                   00
>r[11]=Foo.z
>>13    Noop           0     0     0                    00  uniqueness
>>check for FooLenZ
>>14    Copy           11    13    0                    00
>r[13]=r[11]
>>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>column 0
>>16    IntCopy        8     3     0                    00  r[3]=r[8];
>>rowid
>>17    MakeRecord     2     2     1                    00
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>18    Noop           0     0     0                    00  uniqueness
>>check for FooX
>>19    SCopy          9     5     0                    00  r[5]=r[9];
>>x
>>20    IntCopy        8     6     0                    00  r[6]=r[8];
>>rowid
>>21    MakeRecord     5     2     4                    00
>>r[4]=mkrec(r[5..6]); for FooX
>>22    Copy           11    13    0                    00
>r[13]=r[11]
>>23    PureFunc0      0     13    14    length(1)      01
>>24    Rowid          0     15    0                    00
>r[15]=rowid
>>25    IdxDelete      1     14    2                    00
>>key=r[14..15]
>>26    Column         0     0     14                   00
>r[14]=Foo.x
>>27    IdxDelete      3     14    2                    00
>>key=r[14..15]
>>28    IdxInsert      1     1     2     2              00  key=r[1]
>>29    IdxInsert      3     4     5     2              00  key=r[4]
>>30    MakeRecord     9     3     13                   00
>>r[13]=mkrec(r[9..11])
>>31    Insert         0     13    8     Foo            05
>intkey=r[8]
>>data=r[13]
>>32    Halt           0     0     0                    00
>>33    Transaction    0     1     4     0              01
>>usesStmtJournal=0
>>34    Goto           0     1     0                    00
>>
>>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Deon Brewis
>>Sent: Tuesday, September 11, 2018 8:20 PM
>>To: SQLite mailing list
>>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>It seems like there is an opportunity for improvement on updates if an
>>index contains expressions.
>>
>>In the following example:
>>
>>CREATE TABLE Foo(x, y, z);
>>CREATE INDEX FooX on Foo(x);
>>CREATE INDEX FooZ on Foo(z);
>>CREATE INDEX FooLenZ on Foo(length(z));
>>
>>explain UPDATE foo SET x=1 WHERE rowid=1;
>>
>>I see the plan below. Notice it's updating FooLenZ even though the
>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It
>>doesn't try to update FooZ, just FooLenZ, but both should be
>>untouched.
>>
>>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>>----- ---- ----------- -- -- -- --------- -- -------
>>    1 0    Init        0  30 0            00 (null)
>>    2 1    Null        0  7  8            00 (null)
>>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening
>'Foo'
>>(expected)
>>    4 3    Integer     1  12 0            00 (null)
>>    5 4    SeekRowid   0  6  12           00 (null)
>>    6 5    Rowid       0  8  0            00 (null)
>>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>>'FooLenZ' (NOT expected)
>>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>>'FooX'   (expected)
>>    9 8    IsNull      8  29 0            00 (null)
>>   10 9    Integer     1  9  0            00 (null)
>>   11 10   Column      0  1  10           00 (null)
>>   12 11   Column      0  2  11           00 (null)
>>   13 12   Copy        11 13 0            00 (null)
>>   14 13   Function0   0  13 2  length(1) 01 (null)
>>   15 14   IntCopy     8  3  0            00 (null)
>>   16 15   MakeRecord  2  2  1            00 (null)
>>   17 16   SCopy       9  5  0            00 (null)
>>   18 17   IntCopy     8  6  0            00 (null)
>>   19 18   MakeRecord  5  2  4            00 (null)
>>   20 19   Copy        11 13 0            00 (null)
>>   21 20   Function0   0  13 14 length(1) 01 (null)
>>   22 21   Rowid       0  15 0            00 (null)
>>   23 22   IdxDelete   1  14 2            00 (null)
>>   24 23   Column      0  0  14           00 (null)
>>   25 24   IdxDelete   3  14 2            00 (null)
>>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>>'FooLenZ' (NOT expected)
>>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>>'FooX' (expected)
>>   28 27   MakeRecord  9  3  13           00 (null)
>>   29 28   Insert      0  13 8  Foo       05 (null)
>>   30 29   Halt        0  0  0            00 (null)
>>   31 30   Transaction 0  1  42 0         01 (null)
>>   32 31   Goto        0  1  0            00 (null)
>>
>>sqlite_master:
>>RecNo type  name    tbl_name rootpage sql
>>----- ----- ------- -------- -------- ------------------------------
>-
>>-------
>>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>>Foo(length(z))
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

David Raymond
In reply to this post by Keith Medcalf
Whoa, all sorts of undocumented stuff. Where did you find all these and what they do?

Not seen on https://www.sqlite.org/compile.html (But are seen in 3.24 amalgamation):
SQLITE_DEFAULT_PROXYDIR_PERMISSIONS
SQLITE_DEFAULT_RECURSIVE_TRIGGERS
SQLITE_ENABLE_COSTMULT
SQLITE_ENABLE_CURSOR_HINTS
SQLITE_COUNTOFVIEW_OPTIMIZATION
SQLITE_EXPLAIN_ESTIMATED_ROWS
SQLITE_ENABLE_LOAD_EXTENSION
SQLITE_ENABLE_MODULE_COMMENTS
SQLITE_STAT4_SAMPLES
SQLITE_OS_WIN
SQLITE_OS_WINNT
SQLITE_USE_MALLOC_H
SQLITE_USE_MSIZE

Search also not finding it in the 3.24 amalgamation either:
SQLITE_DEFAULT_TEMP_CACHE_SIZE
SQLITE_ENABLE_STAT_VTAB
SQLITE_ENABLE_UNIONVTAB
SQLITE_NOW_STABILITY_STMT
SQLITE_USE_PRECISE_TIME
SQLITE_DATETIME_NEW
SQLITE_WIN32_FILE_RANDOM


For my own I'm on Windows 7, apparently using MinGW-W64 with GCC 7.3.0
Compiling from the 3.24.0 amalgamation, with only a couple tweaks to shell.c to change the default settings. (.timer on, .eqp on, extra newline to start the prompt, etc.)

Apparently enable_explain_comments isn't included in the bit that keeps track of things to report in pragma compile_options.
Also, apparently .version isn't listed in the .help output of the CLI, but works. Wonder what else is missing.


Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

D:\>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .version
SQLite 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca
gcc-7.3.0

sqlite> pragma compile_options;
compile_options
ALLOW_COVERING_INDEX_SCAN
COMPILER=gcc-7.3.0
DEFAULT_AUTOMATIC_INDEX
DEFAULT_CACHE_SIZE=-65536
DEFAULT_FILE_FORMAT=4
DEFAULT_FOREIGN_KEYS
DEFAULT_JOURNAL_SIZE_LIMIT=0
DEFAULT_LOCKING_MODE=0
DEFAULT_MEMSTATUS
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_SYNCHRONOUS=0
DEFAULT_WAL_AUTOCHECKPOINT=1
DEFAULT_WAL_SYNCHRONOUS=0
DEFAULT_WORKER_THREADS=4
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_MEMORY_MANAGEMENT
ENABLE_RTREE
ENABLE_STMT_SCANSTATUS
ENABLE_UNKNOWN_SQL_FUNCTION
LIKE_DOESNT_MATCH_BLOBS
MAX_ATTACHED=125
MAX_EXPR_DEPTH=0
MAX_MMAP_SIZE=0
MAX_WORKER_THREADS=4
OMIT_SHARED_CACHE
STMTJRNL_SPILL=4194304
THREADSAFE=0
USE_ALLOCA
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> CREATE TABLE Foo(x, y, z);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> CREATE INDEX FooX on Foo(x);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> CREATE INDEX FooZ on Foo(z);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> CREATE INDEX FooLenZ on Foo(length(z));
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> explain UPDATE foo SET x=1 WHERE rowid=1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     33    0                    00  Start at 33
1     Null           0     7     8                    00  r[7..8]=NULL
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; Foo
3     Explain        3     0     0     SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?)  00
4     Integer        1     12    0                    00  r[12]=1
5     SeekRowid      0     7     12                   00  intkey=r[12]; pk
6     Rowid          0     8     0                    00  r[8]=rowid
7     OpenWrite      1     5     0     k(2,,)         00  root=5 iDb=0; FooLenZ
8     OpenWrite      3     3     0     k(2,,)         00  root=3 iDb=0; FooX
9     IsNull         8     32    0                    00  if r[8]==NULL goto 32
10    Integer        1     9     0                    00  r[9]=1
11    Column         0     1     10                   00  r[10]=Foo.y
12    Column         0     2     11                   00  r[11]=Foo.z
13    Noop           0     0     0                    00  uniqueness check for FooLenZ
14    Copy           11    13    0                    00  r[13]=r[11]
15    PureFunc0      0     13    2     length(1)      01  FooLenZ column 0
16    IntCopy        8     3     0                    00  r[3]=r[8]; rowid
17    MakeRecord     2     2     1                    00  r[1]=mkrec(r[2..3]); for FooLenZ
18    Noop           0     0     0                    00  uniqueness check for FooX
19    SCopy          9     5     0                    00  r[5]=r[9]; x
20    IntCopy        8     6     0                    00  r[6]=r[8]; rowid
21    MakeRecord     5     2     4                    00  r[4]=mkrec(r[5..6]); for FooX
22    Copy           11    13    0                    00  r[13]=r[11]
23    PureFunc0      0     13    14    length(1)      01
24    Rowid          0     15    0                    00  r[15]=rowid
25    IdxDelete      1     14    2                    00  key=r[14..15]
26    Column         0     0     14                   00  r[14]=Foo.x
27    IdxDelete      3     14    2                    00  key=r[14..15]
28    IdxInsert      1     1     2     2              00  key=r[1]
29    IdxInsert      3     4     5     2              00  key=r[4]
30    MakeRecord     9     3     13                   00  r[13]=mkrec(r[9..11])
31    Insert         0     13    8     Foo            05  intkey=r[8] data=r[13]
32    Halt           0     0     0                    00
33    Transaction    0     1     4     0              01  usesStmtJournal=0
34    Goto           0     1     0                    00
Run Time: real 0.062 user 0.000000 sys 0.015600

sqlite>

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Keith Medcalf
Sent: Wednesday, September 12, 2018 1:04 PM
To: SQLite mailing list
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update


The version is the current tip of trunk, compiler is MinGW GCC 8.1.0 (on Windows 10) ... with the following options defined:

#define _WIN32_WINNT 0x0600
#define WINVER _WIN32_WINNT
#define SQLITE_DEFAULT_CACHE_SIZE          262144           // 1 GB
#define SQLITE_DEFAULT_TEMP_CACHE_SIZE     262144           // 1 GB default: 500 pages
#define SQLITE_DEFAULT_FOREIGN_KEYS         1               // default: 0
#define SQLITE_DEFAULT_PAGE_SIZE            4096            // default: 4096 max: 65536
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256             // default: 1000 pages
#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755            // default: 0755
#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1               // default: 0
#define SQLITE_DEFAULT_SHARED_CACHE         0               // default: 0
#define SQLITE_DEFAULT_MMAP_SIZE            0               // default: 0
#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
#define SQLITE_INTROSPECTION_PRAGMAS 1                      // Add Instropsection Pragmas
#define SQLITE_ENABLE_8_3_NAMES 1
#define SQLITE_ENABLE_API_ARMOR 1                           // Enable API Armour
#define SQLITE_ENABLE_COLUMN_METADATA 1
#define SQLITE_ENABLE_COSTMULT 1
#define SQLITE_ENABLE_CURSOR_HINTS 1
#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1
#define SQLITE_ENABLE_DBPAGE_VTAB 1
#define SQLITE_ENABLE_DBSTAT_VTAB 1
#define SQLITE_ENABLE_DESERIALIZE 1
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1
#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
#define SQLITE_ENABLE_FTS4 1
#define SQLITE_ENABLE_FTS5 1
#define SQLITE_ENABLE_JSON1 1                               // Enable JSON1 -- when standard extension
#define SQLITE_ENABLE_LOAD_EXTENSION 1
#define SQLITE_ENABLE_LOCKING_STYLE 1
#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1                   // Enable Memory Management (sqlite3_release_memory)
#define SQLITE_ENABLE_MODULE_COMMENTS 1
#define SQLITE_ENABLE_PREUPDATE_HOOK 1
#define SQLITE_ENABLE_RTREE 1
#define SQLITE_ENABLE_SORTER_REFERENCES 1                   // Enable Sorter References
#define SQLITE_ENABLE_STAT_VTAB 1                           // Enable dbstat_register called from shell
#define SQLITE_ENABLE_STAT1 1
#define SQLITE_ENABLE_STAT2 1
#define SQLITE_ENABLE_STAT3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_ENABLE_STMTVTAB 1                            // Enable Stmt VTAB
#define SQLITE_ENABLE_UNIONVTAB 1                           // Enable unionvtab
#define SQLITE_STAT4_SAMPLES 64                             // default: 24 samples
#define SQLITE_SOUNDEX 1
#define SQLITE_THREADSAFE 1                                 // 0 = Single Threaded, 1 = Serialized, 2 = Multithreaded
#define SQLITE_TEMP_STORE 2                                 // 0 = Files Always, 1 = Files, 2 = Memory, 3 Memory Always
#define SQLITE_USE_URI 1                                    // Enable URI Filenames
#define SQLITE_ALLOW_URI_AUTHORITY 1                        // Allow Authority (Host) in URI
#define SQLITE_MAX_ATTACHED                 15              // default: 10          max: 62
#define SQLITE_OS_WIN 1
#define SQLITE_OS_WINNT 1
#define SQLITE_NOW_STABILITY_STMT 1                         // Make 'now' stable within a statement, not only for a step
#define WHERE_PATH_SIMPLE 50                                // Paths to remember for  2-way joins
#define WHERE_PATH_COMPLEX 100                              // Paths to remember for >2-way joins
#define SQLITE_USE_PRECISE_TIME 1                           // Use GetSystemTimePreciseAsFileTime
#define SQLITE_DATETIME_NEW 1                               // Use New Datetime Functions
#define SQLITE_WIN32_FILE_RANDOM 1                          // Force Windows RANDOM access cache behaviour
#define SQLITE_DEFAULT_WAL_SYNCHRONOUS      1                   // Reduce Synchronous to NORMAL in WAL mode
#define SQLITE_DIRECT_OVERFLOW_READ         1                   // Do not cache overflow pages in SQLite pagecache
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1                   // Disable LIKE matching for BLOBS
#define HAVE_FDATASYNC 1
#define HAVE_GMTIME_R 1
#define HAVE_LOCALTIME_S 1
#define HAVE_USLEEP 1
#define HAVE_UTIME 1
#define UNICODE_STRING_MAX_BYTES ((WORD)65534)
#define UNICODE_STRING_MAX_CHARS (32766)
#define HAVE_ISNAN 1
#define SQLITE_USE_MALLOC_H 1
#define SQLITE_USE_MSIZE 1
#define LONGDOUBLE_TYPE __float128

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 10:21
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>A little weird and definitely differet, what version are you using
>and how are you compiling it? I checked with the precompiled Windows
>binary to make sure it wasn't something weird from my compilation,
>and it looks pretty close to my original, but different. So now I'm
>wondering where the noop's, explain's, and comments are coming from
>and what affects them.
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Wednesday, September 12, 2018 11:40 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>
>Interesting ... I get different output with explain comments enabled:
>
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     41    0                    00  Start at 41
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Noop           0     0     0                    00  Begin
>WHERE-loop0: Foo
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     9     12                   00
>intkey=r[12]
>6     Noop           0     0     0                    00  Begin
>WHERE-core
>7     Rowid          0     8     0                    00  r[8]=rowid
>8     Noop           0     0     0                    00  End WHERE-
>core
>9     Noop           0     0     0                    00  End WHERE-
>loop0: Foo
>10    OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>11    OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>12    IsNull         8     40    0                    00  if
>r[8]==NULL goto 40
>13    Integer        1     9     0                    00  r[9]=1
>14    Column         0     1     10                   00  r[10]=Foo.y
>15    Column         0     2     11                   00  r[11]=Foo.z
>16    Noop           0     0     0                    00  BEGIN:
>GenCnstCks(0,1,8,8,0)
>17    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>18    Copy           11    13    0                    00  r[13]=r[11]
>19    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>20    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>21    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>22    Noop           0     0     0                    00  uniqueness
>check for FooX
>23    SCopy          9     5     0                    00  r[5]=r[9];
>x
>24    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>25    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>26    Noop           0     0     0                    00  END:
>GenCnstCks(0)
>27    Noop           0     0     0                    00
>GenRowIdxDel for FooLenZ
>28    Column         0     2     13                   40  r[13]=Foo.z
>29    PureFunc0      0     13    14    length(1)      01
>30    Rowid          0     15    0                    00  r[15]=rowid
>31    IdxDelete      1     14    2                    00
>key=r[14..15]
>32    Noop           0     0     0                    00
>GenRowIdxDel for FooX
>33    Column         0     0     14                   00  r[14]=Foo.x
>34    IdxDelete      3     14    2                    00
>key=r[14..15]
>35    Delete         0     68    8     Foo            00
>36    IdxInsert      1     1     2     2              00  key=r[1]
>37    IdxInsert      3     4     5     2              00  key=r[4]
>38    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>39    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>40    Halt           0     0     0                    00
>41    Transaction    0     1     4     0              01
>usesStmtJournal=0
>42    Goto           0     1     0                    00
>
>
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of David Raymond
>>Sent: Wednesday, 12 September, 2018 07:54
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>Just showing with "explain comments" enabled.
>>
>>
>>sqlite> explain update foo set x = 1 where rowid = 1;
>>addr  opcode         p1    p2    p3    p4             p5  comment
>>----  -------------  ----  ----  ----  -------------  --  ----------
>-
>>--
>>0     Init           0     33    0                    00  Start at
>33
>>1     Null           0     7     8                    00
>>r[7..8]=NULL
>>2     OpenWrite      0     2     0     3              00  root=2
>>iDb=0; Foo
>>3     Explain        3     0     0     SEARCH TABLE foo USING
>INTEGER
>>PRIMARY KEY (rowid=?)  00
>>4     Integer        1     12    0                    00  r[12]=1
>>5     SeekRowid      0     7     12                   00
>>intkey=r[12]; pk
>>6     Rowid          0     8     0                    00  r[8]=rowid
>>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>>iDb=0; FooLenZ
>>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>>iDb=0; FooX
>>9     IsNull         8     32    0                    00  if
>>r[8]==NULL goto 32
>>10    Integer        1     9     0                    00  r[9]=1
>>11    Column         0     1     10                   00
>r[10]=Foo.y
>>12    Column         0     2     11                   00
>r[11]=Foo.z
>>13    Noop           0     0     0                    00  uniqueness
>>check for FooLenZ
>>14    Copy           11    13    0                    00
>r[13]=r[11]
>>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>column 0
>>16    IntCopy        8     3     0                    00  r[3]=r[8];
>>rowid
>>17    MakeRecord     2     2     1                    00
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>18    Noop           0     0     0                    00  uniqueness
>>check for FooX
>>19    SCopy          9     5     0                    00  r[5]=r[9];
>>x
>>20    IntCopy        8     6     0                    00  r[6]=r[8];
>>rowid
>>21    MakeRecord     5     2     4                    00
>>r[4]=mkrec(r[5..6]); for FooX
>>22    Copy           11    13    0                    00
>r[13]=r[11]
>>23    PureFunc0      0     13    14    length(1)      01
>>24    Rowid          0     15    0                    00
>r[15]=rowid
>>25    IdxDelete      1     14    2                    00
>>key=r[14..15]
>>26    Column         0     0     14                   00
>r[14]=Foo.x
>>27    IdxDelete      3     14    2                    00
>>key=r[14..15]
>>28    IdxInsert      1     1     2     2              00  key=r[1]
>>29    IdxInsert      3     4     5     2              00  key=r[4]
>>30    MakeRecord     9     3     13                   00
>>r[13]=mkrec(r[9..11])
>>31    Insert         0     13    8     Foo            05
>intkey=r[8]
>>data=r[13]
>>32    Halt           0     0     0                    00
>>33    Transaction    0     1     4     0              01
>>usesStmtJournal=0
>>34    Goto           0     1     0                    00
>>
>>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Deon Brewis
>>Sent: Tuesday, September 11, 2018 8:20 PM
>>To: SQLite mailing list
>>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>It seems like there is an opportunity for improvement on updates if
>>an index contains expressions.
>>
>>In the following example:
>>
>>CREATE TABLE Foo(x, y, z);
>>CREATE INDEX FooX on Foo(x);
>>CREATE INDEX FooZ on Foo(z);
>>CREATE INDEX FooLenZ on Foo(length(z));
>>
>>explain UPDATE foo SET x=1 WHERE rowid=1;
>>
>>I see the plan below. Notice it's updating FooLenZ even though the
>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all. It
>>doesn't try to update FooZ, just FooLenZ, but both should be
>>untouched.
>>
>>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>>----- ---- ----------- -- -- -- --------- -- -------
>>    1 0    Init        0  30 0            00 (null)
>>    2 1    Null        0  7  8            00 (null)
>>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening
>'Foo'
>>(expected)
>>    4 3    Integer     1  12 0            00 (null)
>>    5 4    SeekRowid   0  6  12           00 (null)
>>    6 5    Rowid       0  8  0            00 (null)
>>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>>'FooLenZ' (NOT expected)
>>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>>'FooX'   (expected)
>>    9 8    IsNull      8  29 0            00 (null)
>>   10 9    Integer     1  9  0            00 (null)
>>   11 10   Column      0  1  10           00 (null)
>>   12 11   Column      0  2  11           00 (null)
>>   13 12   Copy        11 13 0            00 (null)
>>   14 13   Function0   0  13 2  length(1) 01 (null)
>>   15 14   IntCopy     8  3  0            00 (null)
>>   16 15   MakeRecord  2  2  1            00 (null)
>>   17 16   SCopy       9  5  0            00 (null)
>>   18 17   IntCopy     8  6  0            00 (null)
>>   19 18   MakeRecord  5  2  4            00 (null)
>>   20 19   Copy        11 13 0            00 (null)
>>   21 20   Function0   0  13 14 length(1) 01 (null)
>>   22 21   Rowid       0  15 0            00 (null)
>>   23 22   IdxDelete   1  14 2            00 (null)
>>   24 23   Column      0  0  14           00 (null)
>>   25 24   IdxDelete   3  14 2            00 (null)
>>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>>'FooLenZ' (NOT expected)
>>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>>'FooX' (expected)
>>   28 27   MakeRecord  9  3  13           00 (null)
>>   29 28   Insert      0  13 8  Foo       05 (null)
>>   30 29   Halt        0  0  0            00 (null)
>>   31 30   Transaction 0  1  42 0         01 (null)
>>   32 31   Goto        0  1  0            00 (null)
>>
>>sqlite_master:
>>RecNo type  name    tbl_name rootpage sql
>>----- ----- ------- -------- -------- ------------------------------
>-
>>-------
>>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>>Foo(length(z))
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Keith Medcalf

>SQLITE_DEFAULT_PROXYDIR_PERMISSIONS  Applicable to Unix only.  Sets the permissions that will be used when certain kinds of directories are created.

>SQLITE_DEFAULT_RECURSIVE_TRIGGERS  Sets the default for the recursive_triggers pragma (the default is 0 or off).

>SQLITE_ENABLE_COSTMULT  Enables the optimizer to use Cost Multiplier factors in certain operations.

>SQLITE_ENABLE_CURSOR_HINTS  Sends additional information about the cursoring function to the BTREE layer.

>SQLITE_COUNTOFVIEW_OPTIMIZATION  Enables a specific optimization for counting records in a view (see the source documentation)

>SQLITE_EXPLAIN_ESTIMATED_ROWS  Adds information from Query Planner about Estimated Rows to the Explain output

>SQLITE_ENABLE_LOAD_EXTENSION  Set the default load extension API to on.

>SQLITE_ENABLE_MODULE_COMMENTS  Adds additional comments to the explain showing which module generated the code

>SQLITE_STAT4_SAMPLES  Changes the number of buckets in the stat4 histogram created by ANALYZE;

These two force type of Windows being used rather than guessing ...
>SQLITE_OS_WIN
>SQLITE_OS_WINNT

>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE

These two are necessary because when using the MinGW compiler on Windows the code does not automatically detect that MALLOC USABLE SIZE is available and uses the MSVC semantics of the _msize call to get the size of the allocation from the system arena manager, and cannot easily be convinced to do so, except by adding these two internal defines, and getting there using proper compiler detection is problematic.  The default assumption that system malloc does not track usable size means that mem1.c allocates an extra 8 bytes in each allocation so that it can store the size information at the beginning of the block, then moves the pointer along 8 bytes so that this size is not overwritten and the consumer of the allocation is none the wiser.  This however causes problems because system malloc returns allocations aligned on paragraph (16-byte) boundaries.  The extra 8 bytes allocation and the pointer moving up by 8 bytes means that the returned allocation (to user code) is always misaligned from the paragraph boundary leading to alignment exceptions (hardware exceptions) for items which require paragraph alignment because the builtin assumptions about the alignment of memory returned by malloc no longer match reality (so if you store certain items in sqlite3_malloc'd memory which require paragraph alignment the compiler does not and cannot align them correctly -- you can fix this either by (a) increasing the space used for the block header recording the allocation size to a full paragraph multiple or (b) tell the code that MALLOC USABLE SIZE is available and turning the problem over to the runtime library (or (c) use the win32_alloc allocator which does track usable size by default).  (or you could generate slower code that assumes misaglinment).
>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE


>SQLITE_DEFAULT_TEMP_CACHE_SIZE Sets the default maximum size for the temp db in memory.  Apparently no longer used.

These enable some additional extensions.
>SQLITE_ENABLE_STAT_VTAB
>SQLITE_ENABLE_UNIONVTAB

Once upon a time the sqlite 'now' was step-stable by default and the internal "now" (iCurrentTime in the Vdbe) was reset on each entry into the Vdbe (ie, each sqlite3_step).  This is a change I made that only resets iCurrentTime on "first entry" to the Vdbe (pc=0) thus making 'now' statement stable rather than step stable.  Changes along the line (when the SLOCHNG was introduced, I forget what version that was, to enable determinism for the query planner but not for indexing (when functions were allowed in index expressions)) meaning that for all intents and purposes this is now "suspenders" since the default is that 'now' is statement stable after that version.
>SQLITE_NOW_STABILITY_STMT

>SQLITE_USE_PRECISE_TIME
>SQLITE_DATETIME_NEW
>SQLITE_WIN32_FILE_RANDOM
These enable some custom changes I made.  

>SQLITE_USE_PRECISE_TIME simply replaces the call to the GetSystemTimeAsFileTime API in the win32 vfs so that it uses the GetSystemTimePreciseAsFileTime API instead (which means the code will only run on Windows with that API being supported (Windows 8/Windows Server 2012 kernel or later -- eventually I may make it choose dynamically at runtime to restore the compatibility with all versions of Windows and the define will not be required).  Although both APIs return the system time in huns (hundreds of nanoseconds), the non-Precise version of the call reads from the system clock which is only updated on each timer tick (which is somewhere between 16.5 ms and 0.5 ms), depending on what you have requested the multimedia update frequency be set to (note that the tick frequency is also the minimum sleep interval since the scheduler is only dispatched on each tick).  The Precise version of the API returns the time in huns without reference to the clock tick interval (by using the TSC, so if the CPU is old and has a non-stable TSC then the result may not actually be very Precise after all, however, this is a problem for Microsoft to fix if they choose to do so (it probably will be as part of the change of the underlying OS time semantics from a POSIX base to a UTC base).  This means that the iJD (julian day in milliseconds, which is how sqlite3 tracks time) will always have millisecond precision rather than being limited to the update granularity of the system timer tick.

>SQLITE_DATETIME_NEW turns on some more changes I made to the "datetime" module by changing the semantics to always return the applicable UTC offset as part of the resulting string, even when using "localtime" (and thus bypassing most of the processing for 'utc' since the timestamp with an offset is always a UTC timestamp).  The replacement functions (datetime/date/time) all behave the same and this turns on that behaviour (which requires a new variable in the DateTime structure).  The replacement datetime/date/time functions (which also work without this being defined, though the timestamps are not localized) also permit the use of an IANA standard timezone name in place of 'localtime' (thus requiring the current IANA timezone database be loaded into the sqlite3 database in use in order to work) thus fixing the timezone handling on Windows so that it is accurate (or at least as accurate as the IANA database) for all timezones for all times (Linux and other OSes that use the timezone database in their localtime functions do not need this since they know how to compute localtime properly -- only Windows is horridly broken in this case).

>SQLITE_WIN32_FILE_RANDOM is another local change which forces all file opens to pass the RANDOM flag to the win32 createfile API so as to ensure that the Windows readahead is always disabled even when the OS is not WINCE.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 12:12
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>Whoa, all sorts of undocumented stuff. Where did you find all these
>and what they do?
>
>Not seen on https://www.sqlite.org/compile.html (But are seen in 3.24
>amalgamation):
>SQLITE_DEFAULT_PROXYDIR_PERMISSIONS
>SQLITE_DEFAULT_RECURSIVE_TRIGGERS
>SQLITE_ENABLE_COSTMULT
>SQLITE_ENABLE_CURSOR_HINTS
>SQLITE_COUNTOFVIEW_OPTIMIZATION
>SQLITE_EXPLAIN_ESTIMATED_ROWS
>SQLITE_ENABLE_LOAD_EXTENSION
>SQLITE_ENABLE_MODULE_COMMENTS
>SQLITE_STAT4_SAMPLES
>SQLITE_OS_WIN
>SQLITE_OS_WINNT
>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE
>
>Search also not finding it in the 3.24 amalgamation either:
>SQLITE_DEFAULT_TEMP_CACHE_SIZE
>SQLITE_ENABLE_STAT_VTAB
>SQLITE_ENABLE_UNIONVTAB
>SQLITE_NOW_STABILITY_STMT
>SQLITE_USE_PRECISE_TIME
>SQLITE_DATETIME_NEW
>SQLITE_WIN32_FILE_RANDOM
>
>
>For my own I'm on Windows 7, apparently using MinGW-W64 with GCC
>7.3.0
>Compiling from the 3.24.0 amalgamation, with only a couple tweaks to
>shell.c to change the default settings. (.timer on, .eqp on, extra
>newline to start the prompt, etc.)
>
>Apparently enable_explain_comments isn't included in the bit that
>keeps track of things to report in pragma compile_options.
>Also, apparently .version isn't listed in the .help output of the
>CLI, but works. Wonder what else is missing.
>
>
>Microsoft Windows [Version 6.1.7601]
>Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
>
>D:\>sqlite3
>SQLite version 3.24.0 2018-06-04 19:24:41
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>
>sqlite> .version
>SQLite 3.24.0 2018-06-04 19:24:41
>c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca
>gcc-7.3.0
>
>sqlite> pragma compile_options;
>compile_options
>ALLOW_COVERING_INDEX_SCAN
>COMPILER=gcc-7.3.0
>DEFAULT_AUTOMATIC_INDEX
>DEFAULT_CACHE_SIZE=-65536
>DEFAULT_FILE_FORMAT=4
>DEFAULT_FOREIGN_KEYS
>DEFAULT_JOURNAL_SIZE_LIMIT=0
>DEFAULT_LOCKING_MODE=0
>DEFAULT_MEMSTATUS
>DEFAULT_MMAP_SIZE=0
>DEFAULT_PAGE_SIZE=4096
>DEFAULT_SYNCHRONOUS=0
>DEFAULT_WAL_AUTOCHECKPOINT=1
>DEFAULT_WAL_SYNCHRONOUS=0
>DEFAULT_WORKER_THREADS=4
>ENABLE_COLUMN_METADATA
>ENABLE_DBSTAT_VTAB
>ENABLE_MEMORY_MANAGEMENT
>ENABLE_RTREE
>ENABLE_STMT_SCANSTATUS
>ENABLE_UNKNOWN_SQL_FUNCTION
>LIKE_DOESNT_MATCH_BLOBS
>MAX_ATTACHED=125
>MAX_EXPR_DEPTH=0
>MAX_MMAP_SIZE=0
>MAX_WORKER_THREADS=4
>OMIT_SHARED_CACHE
>STMTJRNL_SPILL=4194304
>THREADSAFE=0
>USE_ALLOCA
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE TABLE Foo(x, y, z);
>QUERY PLAN
>`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE INDEX FooX on Foo(x);
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE INDEX FooZ on Foo(z);
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE INDEX FooLenZ on Foo(length(z));
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> explain UPDATE foo SET x=1 WHERE rowid=1;
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     33    0                    00  Start at 33
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Explain        3     0     0     SEARCH TABLE foo USING INTEGER
>PRIMARY KEY (rowid=?)  00
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     7     12                   00
>intkey=r[12]; pk
>6     Rowid          0     8     0                    00  r[8]=rowid
>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>9     IsNull         8     32    0                    00  if
>r[8]==NULL goto 32
>10    Integer        1     9     0                    00  r[9]=1
>11    Column         0     1     10                   00  r[10]=Foo.y
>12    Column         0     2     11                   00  r[11]=Foo.z
>13    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>14    Copy           11    13    0                    00  r[13]=r[11]
>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>16    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>17    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>18    Noop           0     0     0                    00  uniqueness
>check for FooX
>19    SCopy          9     5     0                    00  r[5]=r[9];
>x
>20    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>21    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>22    Copy           11    13    0                    00  r[13]=r[11]
>23    PureFunc0      0     13    14    length(1)      01
>24    Rowid          0     15    0                    00  r[15]=rowid
>25    IdxDelete      1     14    2                    00
>key=r[14..15]
>26    Column         0     0     14                   00  r[14]=Foo.x
>27    IdxDelete      3     14    2                    00
>key=r[14..15]
>28    IdxInsert      1     1     2     2              00  key=r[1]
>29    IdxInsert      3     4     5     2              00  key=r[4]
>30    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>31    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>32    Halt           0     0     0                    00
>33    Transaction    0     1     4     0              01
>usesStmtJournal=0
>34    Goto           0     1     0                    00
>Run Time: real 0.062 user 0.000000 sys 0.015600
>
>sqlite>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Wednesday, September 12, 2018 1:04 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>
>The version is the current tip of trunk, compiler is MinGW GCC 8.1.0
>(on Windows 10) ... with the following options defined:
>
>#define _WIN32_WINNT 0x0600
>#define WINVER _WIN32_WINNT
>#define SQLITE_DEFAULT_CACHE_SIZE          262144           // 1 GB
>#define SQLITE_DEFAULT_TEMP_CACHE_SIZE     262144           // 1 GB
>default: 500 pages
>#define SQLITE_DEFAULT_FOREIGN_KEYS         1               //
>default: 0
>#define SQLITE_DEFAULT_PAGE_SIZE            4096            //
>default: 4096 max: 65536
>#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256             //
>default: 1000 pages
>#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755            //
>default: 0755
>#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1               //
>default: 0
>#define SQLITE_DEFAULT_SHARED_CACHE         0               //
>default: 0
>#define SQLITE_DEFAULT_MMAP_SIZE            0               //
>default: 0
>#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
>#define SQLITE_INTROSPECTION_PRAGMAS 1                      // Add
>Instropsection Pragmas
>#define SQLITE_ENABLE_8_3_NAMES 1
>#define SQLITE_ENABLE_API_ARMOR 1                           // Enable
>API Armour
>#define SQLITE_ENABLE_COLUMN_METADATA 1
>#define SQLITE_ENABLE_COSTMULT 1
>#define SQLITE_ENABLE_CURSOR_HINTS 1
>#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1
>#define SQLITE_ENABLE_DBPAGE_VTAB 1
>#define SQLITE_ENABLE_DBSTAT_VTAB 1
>#define SQLITE_ENABLE_DESERIALIZE 1
>#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1
>#define SQLITE_ENABLE_FTS3 1
>#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
>#define SQLITE_ENABLE_FTS4 1
>#define SQLITE_ENABLE_FTS5 1
>#define SQLITE_ENABLE_JSON1 1                               // Enable
>JSON1 -- when standard extension
>#define SQLITE_ENABLE_LOAD_EXTENSION 1
>#define SQLITE_ENABLE_LOCKING_STYLE 1
>#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1                   // Enable
>Memory Management (sqlite3_release_memory)
>#define SQLITE_ENABLE_MODULE_COMMENTS 1
>#define SQLITE_ENABLE_PREUPDATE_HOOK 1
>#define SQLITE_ENABLE_RTREE 1
>#define SQLITE_ENABLE_SORTER_REFERENCES 1                   // Enable
>Sorter References
>#define SQLITE_ENABLE_STAT_VTAB 1                           // Enable
>dbstat_register called from shell
>#define SQLITE_ENABLE_STAT1 1
>#define SQLITE_ENABLE_STAT2 1
>#define SQLITE_ENABLE_STAT3 1
>#define SQLITE_ENABLE_STAT4 1
>#define SQLITE_ENABLE_STMTVTAB 1                            // Enable
>Stmt VTAB
>#define SQLITE_ENABLE_UNIONVTAB 1                           // Enable
>unionvtab
>#define SQLITE_STAT4_SAMPLES 64                             //
>default: 24 samples
>#define SQLITE_SOUNDEX 1
>#define SQLITE_THREADSAFE 1                                 // 0 =
>Single Threaded, 1 = Serialized, 2 = Multithreaded
>#define SQLITE_TEMP_STORE 2                                 // 0 =
>Files Always, 1 = Files, 2 = Memory, 3 Memory Always
>#define SQLITE_USE_URI 1                                    // Enable
>URI Filenames
>#define SQLITE_ALLOW_URI_AUTHORITY 1                        // Allow
>Authority (Host) in URI
>#define SQLITE_MAX_ATTACHED                 15              //
>default: 10          max: 62
>#define SQLITE_OS_WIN 1
>#define SQLITE_OS_WINNT 1
>#define SQLITE_NOW_STABILITY_STMT 1                         // Make
>'now' stable within a statement, not only for a step
>#define WHERE_PATH_SIMPLE 50                                // Paths
>to remember for  2-way joins
>#define WHERE_PATH_COMPLEX 100                              // Paths
>to remember for >2-way joins
>#define SQLITE_USE_PRECISE_TIME 1                           // Use
>GetSystemTimePreciseAsFileTime
>#define SQLITE_DATETIME_NEW 1                               // Use
>New Datetime Functions
>#define SQLITE_WIN32_FILE_RANDOM 1                          // Force
>Windows RANDOM access cache behaviour
>#define SQLITE_DEFAULT_WAL_SYNCHRONOUS      1                   //
>Reduce Synchronous to NORMAL in WAL mode
>#define SQLITE_DIRECT_OVERFLOW_READ         1                   // Do
>not cache overflow pages in SQLite pagecache
>#define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1                   //
>Disable LIKE matching for BLOBS
>#define HAVE_FDATASYNC 1
>#define HAVE_GMTIME_R 1
>#define HAVE_LOCALTIME_S 1
>#define HAVE_USLEEP 1
>#define HAVE_UTIME 1
>#define UNICODE_STRING_MAX_BYTES ((WORD)65534)
>#define UNICODE_STRING_MAX_CHARS (32766)
>#define HAVE_ISNAN 1
>#define SQLITE_USE_MALLOC_H 1
>#define SQLITE_USE_MSIZE 1
>#define LONGDOUBLE_TYPE __float128
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of David Raymond
>>Sent: Wednesday, 12 September, 2018 10:21
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>A little weird and definitely differet, what version are you using
>>and how are you compiling it? I checked with the precompiled Windows
>>binary to make sure it wasn't something weird from my compilation,
>>and it looks pretty close to my original, but different. So now I'm
>>wondering where the noop's, explain's, and comments are coming from
>>and what affects them.
>>
>>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Keith Medcalf
>>Sent: Wednesday, September 12, 2018 11:40 AM
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>
>>Interesting ... I get different output with explain comments
>enabled:
>>
>>addr  opcode         p1    p2    p3    p4             p5  comment
>>----  -------------  ----  ----  ----  -------------  --  ----------
>-
>>--
>>0     Init           0     41    0                    00  Start at
>41
>>1     Null           0     7     8                    00
>>r[7..8]=NULL
>>2     OpenWrite      0     2     0     3              00  root=2
>>iDb=0; Foo
>>3     Noop           0     0     0                    00  Begin
>>WHERE-loop0: Foo
>>4     Integer        1     12    0                    00  r[12]=1
>>5     SeekRowid      0     9     12                   00
>>intkey=r[12]
>>6     Noop           0     0     0                    00  Begin
>>WHERE-core
>>7     Rowid          0     8     0                    00  r[8]=rowid
>>8     Noop           0     0     0                    00  End WHERE-
>>core
>>9     Noop           0     0     0                    00  End WHERE-
>>loop0: Foo
>>10    OpenWrite      1     5     0     k(2,,)         00  root=5
>>iDb=0; FooLenZ
>>11    OpenWrite      3     3     0     k(2,,)         00  root=3
>>iDb=0; FooX
>>12    IsNull         8     40    0                    00  if
>>r[8]==NULL goto 40
>>13    Integer        1     9     0                    00  r[9]=1
>>14    Column         0     1     10                   00
>r[10]=Foo.y
>>15    Column         0     2     11                   00
>r[11]=Foo.z
>>16    Noop           0     0     0                    00  BEGIN:
>>GenCnstCks(0,1,8,8,0)
>>17    Noop           0     0     0                    00  uniqueness
>>check for FooLenZ
>>18    Copy           11    13    0                    00
>r[13]=r[11]
>>19    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>column 0
>>20    IntCopy        8     3     0                    00  r[3]=r[8];
>>rowid
>>21    MakeRecord     2     2     1                    00
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>22    Noop           0     0     0                    00  uniqueness
>>check for FooX
>>23    SCopy          9     5     0                    00  r[5]=r[9];
>>x
>>24    IntCopy        8     6     0                    00  r[6]=r[8];
>>rowid
>>25    MakeRecord     5     2     4                    00
>>r[4]=mkrec(r[5..6]); for FooX
>>26    Noop           0     0     0                    00  END:
>>GenCnstCks(0)
>>27    Noop           0     0     0                    00
>>GenRowIdxDel for FooLenZ
>>28    Column         0     2     13                   40
>r[13]=Foo.z
>>29    PureFunc0      0     13    14    length(1)      01
>>30    Rowid          0     15    0                    00
>r[15]=rowid
>>31    IdxDelete      1     14    2                    00
>>key=r[14..15]
>>32    Noop           0     0     0                    00
>>GenRowIdxDel for FooX
>>33    Column         0     0     14                   00
>r[14]=Foo.x
>>34    IdxDelete      3     14    2                    00
>>key=r[14..15]
>>35    Delete         0     68    8     Foo            00
>>36    IdxInsert      1     1     2     2              00  key=r[1]
>>37    IdxInsert      3     4     5     2              00  key=r[4]
>>38    MakeRecord     9     3     13                   00
>>r[13]=mkrec(r[9..11])
>>39    Insert         0     13    8     Foo            05
>intkey=r[8]
>>data=r[13]
>>40    Halt           0     0     0                    00
>>41    Transaction    0     1     4     0              01
>>usesStmtJournal=0
>>42    Goto           0     1     0                    00
>>
>>
>>
>>---
>>The fact that there's a Highway to Hell but only a Stairway to
>Heaven
>>says a lot about anticipated traffic volume.
>>
>>
>>>-----Original Message-----
>>>From: sqlite-users [mailto:sqlite-users-
>>>[hidden email]] On Behalf Of David Raymond
>>>Sent: Wednesday, 12 September, 2018 07:54
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes
>on
>>>update
>>>
>>>Just showing with "explain comments" enabled.
>>>
>>>
>>>sqlite> explain update foo set x = 1 where rowid = 1;
>>>addr  opcode         p1    p2    p3    p4             p5  comment
>>>----  -------------  ----  ----  ----  -------------  --  ---------
>-
>>-
>>>--
>>>0     Init           0     33    0                    00  Start at
>>33
>>>1     Null           0     7     8                    00
>>>r[7..8]=NULL
>>>2     OpenWrite      0     2     0     3              00  root=2
>>>iDb=0; Foo
>>>3     Explain        3     0     0     SEARCH TABLE foo USING
>>INTEGER
>>>PRIMARY KEY (rowid=?)  00
>>>4     Integer        1     12    0                    00  r[12]=1
>>>5     SeekRowid      0     7     12                   00
>>>intkey=r[12]; pk
>>>6     Rowid          0     8     0                    00
>r[8]=rowid
>>>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>>>iDb=0; FooLenZ
>>>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>>>iDb=0; FooX
>>>9     IsNull         8     32    0                    00  if
>>>r[8]==NULL goto 32
>>>10    Integer        1     9     0                    00  r[9]=1
>>>11    Column         0     1     10                   00
>>r[10]=Foo.y
>>>12    Column         0     2     11                   00
>>r[11]=Foo.z
>>>13    Noop           0     0     0                    00
>uniqueness
>>>check for FooLenZ
>>>14    Copy           11    13    0                    00
>>r[13]=r[11]
>>>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>>column 0
>>>16    IntCopy        8     3     0                    00
>r[3]=r[8];
>>>rowid
>>>17    MakeRecord     2     2     1                    00
>>>r[1]=mkrec(r[2..3]); for FooLenZ
>>>18    Noop           0     0     0                    00
>uniqueness
>>>check for FooX
>>>19    SCopy          9     5     0                    00
>r[5]=r[9];
>>>x
>>>20    IntCopy        8     6     0                    00
>r[6]=r[8];
>>>rowid
>>>21    MakeRecord     5     2     4                    00
>>>r[4]=mkrec(r[5..6]); for FooX
>>>22    Copy           11    13    0                    00
>>r[13]=r[11]
>>>23    PureFunc0      0     13    14    length(1)      01
>>>24    Rowid          0     15    0                    00
>>r[15]=rowid
>>>25    IdxDelete      1     14    2                    00
>>>key=r[14..15]
>>>26    Column         0     0     14                   00
>>r[14]=Foo.x
>>>27    IdxDelete      3     14    2                    00
>>>key=r[14..15]
>>>28    IdxInsert      1     1     2     2              00  key=r[1]
>>>29    IdxInsert      3     4     5     2              00  key=r[4]
>>>30    MakeRecord     9     3     13                   00
>>>r[13]=mkrec(r[9..11])
>>>31    Insert         0     13    8     Foo            05
>>intkey=r[8]
>>>data=r[13]
>>>32    Halt           0     0     0                    00
>>>33    Transaction    0     1     4     0              01
>>>usesStmtJournal=0
>>>34    Goto           0     1     0                    00
>>>
>>>
>>>-----Original Message-----
>>>From: sqlite-users [mailto:sqlite-users-
>>>[hidden email]] On Behalf Of Deon Brewis
>>>Sent: Tuesday, September 11, 2018 8:20 PM
>>>To: SQLite mailing list
>>>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>>>update
>>>
>>>It seems like there is an opportunity for improvement on updates if
>>>an index contains expressions.
>>>
>>>In the following example:
>>>
>>>CREATE TABLE Foo(x, y, z);
>>>CREATE INDEX FooX on Foo(x);
>>>CREATE INDEX FooZ on Foo(z);
>>>CREATE INDEX FooLenZ on Foo(length(z));
>>>
>>>explain UPDATE foo SET x=1 WHERE rowid=1;
>>>
>>>I see the plan below. Notice it's updating FooLenZ even though the
>>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all.
>It
>>>doesn't try to update FooZ, just FooLenZ, but both should be
>>>untouched.
>>>
>>>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>>>----- ---- ----------- -- -- -- --------- -- -------
>>>    1 0    Init        0  30 0            00 (null)
>>>    2 1    Null        0  7  8            00 (null)
>>>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening
>>'Foo'
>>>(expected)
>>>    4 3    Integer     1  12 0            00 (null)
>>>    5 4    SeekRowid   0  6  12           00 (null)
>>>    6 5    Rowid       0  8  0            00 (null)
>>>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>>>'FooLenZ' (NOT expected)
>>>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>>>'FooX'   (expected)
>>>    9 8    IsNull      8  29 0            00 (null)
>>>   10 9    Integer     1  9  0            00 (null)
>>>   11 10   Column      0  1  10           00 (null)
>>>   12 11   Column      0  2  11           00 (null)
>>>   13 12   Copy        11 13 0            00 (null)
>>>   14 13   Function0   0  13 2  length(1) 01 (null)
>>>   15 14   IntCopy     8  3  0            00 (null)
>>>   16 15   MakeRecord  2  2  1            00 (null)
>>>   17 16   SCopy       9  5  0            00 (null)
>>>   18 17   IntCopy     8  6  0            00 (null)
>>>   19 18   MakeRecord  5  2  4            00 (null)
>>>   20 19   Copy        11 13 0            00 (null)
>>>   21 20   Function0   0  13 14 length(1) 01 (null)
>>>   22 21   Rowid       0  15 0            00 (null)
>>>   23 22   IdxDelete   1  14 2            00 (null)
>>>   24 23   Column      0  0  14           00 (null)
>>>   25 24   IdxDelete   3  14 2            00 (null)
>>>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>>>'FooLenZ' (NOT expected)
>>>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>>>'FooX' (expected)
>>>   28 27   MakeRecord  9  3  13           00 (null)
>>>   29 28   Insert      0  13 8  Foo       05 (null)
>>>   30 29   Halt        0  0  0            00 (null)
>>>   31 30   Transaction 0  1  42 0         01 (null)
>>>   32 31   Goto        0  1  0            00 (null)
>>>
>>>sqlite_master:
>>>RecNo type  name    tbl_name rootpage sql
>>>----- ----- ------- -------- -------- -----------------------------
>-
>>-
>>>-------
>>>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>>>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>>>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>>>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>>>Foo(length(z))
>>>
>>>
>>>
>>>_______________________________________________
>>>sqlite-users mailing list
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>_______________________________________________
>>>sqlite-users mailing list
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Keith Medcalf
In reply to this post by David Raymond

For more information than you could ever want about how Windows does time and timers, see http://www.windowstimestamp.com/description 


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Wednesday, 12 September, 2018 12:12
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>Whoa, all sorts of undocumented stuff. Where did you find all these
>and what they do?
>
>Not seen on https://www.sqlite.org/compile.html (But are seen in 3.24
>amalgamation):
>SQLITE_DEFAULT_PROXYDIR_PERMISSIONS
>SQLITE_DEFAULT_RECURSIVE_TRIGGERS
>SQLITE_ENABLE_COSTMULT
>SQLITE_ENABLE_CURSOR_HINTS
>SQLITE_COUNTOFVIEW_OPTIMIZATION
>SQLITE_EXPLAIN_ESTIMATED_ROWS
>SQLITE_ENABLE_LOAD_EXTENSION
>SQLITE_ENABLE_MODULE_COMMENTS
>SQLITE_STAT4_SAMPLES
>SQLITE_OS_WIN
>SQLITE_OS_WINNT
>SQLITE_USE_MALLOC_H
>SQLITE_USE_MSIZE
>
>Search also not finding it in the 3.24 amalgamation either:
>SQLITE_DEFAULT_TEMP_CACHE_SIZE
>SQLITE_ENABLE_STAT_VTAB
>SQLITE_ENABLE_UNIONVTAB
>SQLITE_NOW_STABILITY_STMT
>SQLITE_USE_PRECISE_TIME
>SQLITE_DATETIME_NEW
>SQLITE_WIN32_FILE_RANDOM
>
>
>For my own I'm on Windows 7, apparently using MinGW-W64 with GCC
>7.3.0
>Compiling from the 3.24.0 amalgamation, with only a couple tweaks to
>shell.c to change the default settings. (.timer on, .eqp on, extra
>newline to start the prompt, etc.)
>
>Apparently enable_explain_comments isn't included in the bit that
>keeps track of things to report in pragma compile_options.
>Also, apparently .version isn't listed in the .help output of the
>CLI, but works. Wonder what else is missing.
>
>
>Microsoft Windows [Version 6.1.7601]
>Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
>
>D:\>sqlite3
>SQLite version 3.24.0 2018-06-04 19:24:41
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>
>sqlite> .version
>SQLite 3.24.0 2018-06-04 19:24:41
>c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca
>gcc-7.3.0
>
>sqlite> pragma compile_options;
>compile_options
>ALLOW_COVERING_INDEX_SCAN
>COMPILER=gcc-7.3.0
>DEFAULT_AUTOMATIC_INDEX
>DEFAULT_CACHE_SIZE=-65536
>DEFAULT_FILE_FORMAT=4
>DEFAULT_FOREIGN_KEYS
>DEFAULT_JOURNAL_SIZE_LIMIT=0
>DEFAULT_LOCKING_MODE=0
>DEFAULT_MEMSTATUS
>DEFAULT_MMAP_SIZE=0
>DEFAULT_PAGE_SIZE=4096
>DEFAULT_SYNCHRONOUS=0
>DEFAULT_WAL_AUTOCHECKPOINT=1
>DEFAULT_WAL_SYNCHRONOUS=0
>DEFAULT_WORKER_THREADS=4
>ENABLE_COLUMN_METADATA
>ENABLE_DBSTAT_VTAB
>ENABLE_MEMORY_MANAGEMENT
>ENABLE_RTREE
>ENABLE_STMT_SCANSTATUS
>ENABLE_UNKNOWN_SQL_FUNCTION
>LIKE_DOESNT_MATCH_BLOBS
>MAX_ATTACHED=125
>MAX_EXPR_DEPTH=0
>MAX_MMAP_SIZE=0
>MAX_WORKER_THREADS=4
>OMIT_SHARED_CACHE
>STMTJRNL_SPILL=4194304
>THREADSAFE=0
>USE_ALLOCA
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE TABLE Foo(x, y, z);
>QUERY PLAN
>`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE INDEX FooX on Foo(x);
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE INDEX FooZ on Foo(z);
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> CREATE INDEX FooLenZ on Foo(length(z));
>Run Time: real 0.000 user 0.000000 sys 0.000000
>
>sqlite> explain UPDATE foo SET x=1 WHERE rowid=1;
>addr  opcode         p1    p2    p3    p4             p5  comment
>----  -------------  ----  ----  ----  -------------  --  -----------
>--
>0     Init           0     33    0                    00  Start at 33
>1     Null           0     7     8                    00
>r[7..8]=NULL
>2     OpenWrite      0     2     0     3              00  root=2
>iDb=0; Foo
>3     Explain        3     0     0     SEARCH TABLE foo USING INTEGER
>PRIMARY KEY (rowid=?)  00
>4     Integer        1     12    0                    00  r[12]=1
>5     SeekRowid      0     7     12                   00
>intkey=r[12]; pk
>6     Rowid          0     8     0                    00  r[8]=rowid
>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>iDb=0; FooLenZ
>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>iDb=0; FooX
>9     IsNull         8     32    0                    00  if
>r[8]==NULL goto 32
>10    Integer        1     9     0                    00  r[9]=1
>11    Column         0     1     10                   00  r[10]=Foo.y
>12    Column         0     2     11                   00  r[11]=Foo.z
>13    Noop           0     0     0                    00  uniqueness
>check for FooLenZ
>14    Copy           11    13    0                    00  r[13]=r[11]
>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>column 0
>16    IntCopy        8     3     0                    00  r[3]=r[8];
>rowid
>17    MakeRecord     2     2     1                    00
>r[1]=mkrec(r[2..3]); for FooLenZ
>18    Noop           0     0     0                    00  uniqueness
>check for FooX
>19    SCopy          9     5     0                    00  r[5]=r[9];
>x
>20    IntCopy        8     6     0                    00  r[6]=r[8];
>rowid
>21    MakeRecord     5     2     4                    00
>r[4]=mkrec(r[5..6]); for FooX
>22    Copy           11    13    0                    00  r[13]=r[11]
>23    PureFunc0      0     13    14    length(1)      01
>24    Rowid          0     15    0                    00  r[15]=rowid
>25    IdxDelete      1     14    2                    00
>key=r[14..15]
>26    Column         0     0     14                   00  r[14]=Foo.x
>27    IdxDelete      3     14    2                    00
>key=r[14..15]
>28    IdxInsert      1     1     2     2              00  key=r[1]
>29    IdxInsert      3     4     5     2              00  key=r[4]
>30    MakeRecord     9     3     13                   00
>r[13]=mkrec(r[9..11])
>31    Insert         0     13    8     Foo            05  intkey=r[8]
>data=r[13]
>32    Halt           0     0     0                    00
>33    Transaction    0     1     4     0              01
>usesStmtJournal=0
>34    Goto           0     1     0                    00
>Run Time: real 0.062 user 0.000000 sys 0.015600
>
>sqlite>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Wednesday, September 12, 2018 1:04 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>update
>
>
>The version is the current tip of trunk, compiler is MinGW GCC 8.1.0
>(on Windows 10) ... with the following options defined:
>
>#define _WIN32_WINNT 0x0600
>#define WINVER _WIN32_WINNT
>#define SQLITE_DEFAULT_CACHE_SIZE          262144           // 1 GB
>#define SQLITE_DEFAULT_TEMP_CACHE_SIZE     262144           // 1 GB
>default: 500 pages
>#define SQLITE_DEFAULT_FOREIGN_KEYS         1               //
>default: 0
>#define SQLITE_DEFAULT_PAGE_SIZE            4096            //
>default: 4096 max: 65536
>#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT   256             //
>default: 1000 pages
>#define SQLITE_DEFAULT_PROXYDIR_PERMISSIONS 0755            //
>default: 0755
>#define SQLITE_DEFAULT_RECURSIVE_TRIGGERS   1               //
>default: 0
>#define SQLITE_DEFAULT_SHARED_CACHE         0               //
>default: 0
>#define SQLITE_DEFAULT_MMAP_SIZE            0               //
>default: 0
>#define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
>#define SQLITE_INTROSPECTION_PRAGMAS 1                      // Add
>Instropsection Pragmas
>#define SQLITE_ENABLE_8_3_NAMES 1
>#define SQLITE_ENABLE_API_ARMOR 1                           // Enable
>API Armour
>#define SQLITE_ENABLE_COLUMN_METADATA 1
>#define SQLITE_ENABLE_COSTMULT 1
>#define SQLITE_ENABLE_CURSOR_HINTS 1
>#define SQLITE_COUNTOFVIEW_OPTIMIZATION 1
>#define SQLITE_ENABLE_DBPAGE_VTAB 1
>#define SQLITE_ENABLE_DBSTAT_VTAB 1
>#define SQLITE_ENABLE_DESERIALIZE 1
>#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1
>#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1
>#define SQLITE_ENABLE_FTS3 1
>#define SQLITE_ENABLE_FTS3_PARENTHESIS 1
>#define SQLITE_ENABLE_FTS4 1
>#define SQLITE_ENABLE_FTS5 1
>#define SQLITE_ENABLE_JSON1 1                               // Enable
>JSON1 -- when standard extension
>#define SQLITE_ENABLE_LOAD_EXTENSION 1
>#define SQLITE_ENABLE_LOCKING_STYLE 1
>#define SQLITE_ENABLE_MEMORY_MANAGEMENT 1                   // Enable
>Memory Management (sqlite3_release_memory)
>#define SQLITE_ENABLE_MODULE_COMMENTS 1
>#define SQLITE_ENABLE_PREUPDATE_HOOK 1
>#define SQLITE_ENABLE_RTREE 1
>#define SQLITE_ENABLE_SORTER_REFERENCES 1                   // Enable
>Sorter References
>#define SQLITE_ENABLE_STAT_VTAB 1                           // Enable
>dbstat_register called from shell
>#define SQLITE_ENABLE_STAT1 1
>#define SQLITE_ENABLE_STAT2 1
>#define SQLITE_ENABLE_STAT3 1
>#define SQLITE_ENABLE_STAT4 1
>#define SQLITE_ENABLE_STMTVTAB 1                            // Enable
>Stmt VTAB
>#define SQLITE_ENABLE_UNIONVTAB 1                           // Enable
>unionvtab
>#define SQLITE_STAT4_SAMPLES 64                             //
>default: 24 samples
>#define SQLITE_SOUNDEX 1
>#define SQLITE_THREADSAFE 1                                 // 0 =
>Single Threaded, 1 = Serialized, 2 = Multithreaded
>#define SQLITE_TEMP_STORE 2                                 // 0 =
>Files Always, 1 = Files, 2 = Memory, 3 Memory Always
>#define SQLITE_USE_URI 1                                    // Enable
>URI Filenames
>#define SQLITE_ALLOW_URI_AUTHORITY 1                        // Allow
>Authority (Host) in URI
>#define SQLITE_MAX_ATTACHED                 15              //
>default: 10          max: 62
>#define SQLITE_OS_WIN 1
>#define SQLITE_OS_WINNT 1
>#define SQLITE_NOW_STABILITY_STMT 1                         // Make
>'now' stable within a statement, not only for a step
>#define WHERE_PATH_SIMPLE 50                                // Paths
>to remember for  2-way joins
>#define WHERE_PATH_COMPLEX 100                              // Paths
>to remember for >2-way joins
>#define SQLITE_USE_PRECISE_TIME 1                           // Use
>GetSystemTimePreciseAsFileTime
>#define SQLITE_DATETIME_NEW 1                               // Use
>New Datetime Functions
>#define SQLITE_WIN32_FILE_RANDOM 1                          // Force
>Windows RANDOM access cache behaviour
>#define SQLITE_DEFAULT_WAL_SYNCHRONOUS      1                   //
>Reduce Synchronous to NORMAL in WAL mode
>#define SQLITE_DIRECT_OVERFLOW_READ         1                   // Do
>not cache overflow pages in SQLite pagecache
>#define SQLITE_LIKE_DOESNT_MATCH_BLOBS      1                   //
>Disable LIKE matching for BLOBS
>#define HAVE_FDATASYNC 1
>#define HAVE_GMTIME_R 1
>#define HAVE_LOCALTIME_S 1
>#define HAVE_USLEEP 1
>#define HAVE_UTIME 1
>#define UNICODE_STRING_MAX_BYTES ((WORD)65534)
>#define UNICODE_STRING_MAX_CHARS (32766)
>#define HAVE_ISNAN 1
>#define SQLITE_USE_MALLOC_H 1
>#define SQLITE_USE_MSIZE 1
>#define LONGDOUBLE_TYPE __float128
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of David Raymond
>>Sent: Wednesday, 12 September, 2018 10:21
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>A little weird and definitely differet, what version are you using
>>and how are you compiling it? I checked with the precompiled Windows
>>binary to make sure it wasn't something weird from my compilation,
>>and it looks pretty close to my original, but different. So now I'm
>>wondering where the noop's, explain's, and comments are coming from
>>and what affects them.
>>
>>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Keith Medcalf
>>Sent: Wednesday, September 12, 2018 11:40 AM
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on
>>update
>>
>>
>>Interesting ... I get different output with explain comments
>enabled:
>>
>>addr  opcode         p1    p2    p3    p4             p5  comment
>>----  -------------  ----  ----  ----  -------------  --  ----------
>-
>>--
>>0     Init           0     41    0                    00  Start at
>41
>>1     Null           0     7     8                    00
>>r[7..8]=NULL
>>2     OpenWrite      0     2     0     3              00  root=2
>>iDb=0; Foo
>>3     Noop           0     0     0                    00  Begin
>>WHERE-loop0: Foo
>>4     Integer        1     12    0                    00  r[12]=1
>>5     SeekRowid      0     9     12                   00
>>intkey=r[12]
>>6     Noop           0     0     0                    00  Begin
>>WHERE-core
>>7     Rowid          0     8     0                    00  r[8]=rowid
>>8     Noop           0     0     0                    00  End WHERE-
>>core
>>9     Noop           0     0     0                    00  End WHERE-
>>loop0: Foo
>>10    OpenWrite      1     5     0     k(2,,)         00  root=5
>>iDb=0; FooLenZ
>>11    OpenWrite      3     3     0     k(2,,)         00  root=3
>>iDb=0; FooX
>>12    IsNull         8     40    0                    00  if
>>r[8]==NULL goto 40
>>13    Integer        1     9     0                    00  r[9]=1
>>14    Column         0     1     10                   00
>r[10]=Foo.y
>>15    Column         0     2     11                   00
>r[11]=Foo.z
>>16    Noop           0     0     0                    00  BEGIN:
>>GenCnstCks(0,1,8,8,0)
>>17    Noop           0     0     0                    00  uniqueness
>>check for FooLenZ
>>18    Copy           11    13    0                    00
>r[13]=r[11]
>>19    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>column 0
>>20    IntCopy        8     3     0                    00  r[3]=r[8];
>>rowid
>>21    MakeRecord     2     2     1                    00
>>r[1]=mkrec(r[2..3]); for FooLenZ
>>22    Noop           0     0     0                    00  uniqueness
>>check for FooX
>>23    SCopy          9     5     0                    00  r[5]=r[9];
>>x
>>24    IntCopy        8     6     0                    00  r[6]=r[8];
>>rowid
>>25    MakeRecord     5     2     4                    00
>>r[4]=mkrec(r[5..6]); for FooX
>>26    Noop           0     0     0                    00  END:
>>GenCnstCks(0)
>>27    Noop           0     0     0                    00
>>GenRowIdxDel for FooLenZ
>>28    Column         0     2     13                   40
>r[13]=Foo.z
>>29    PureFunc0      0     13    14    length(1)      01
>>30    Rowid          0     15    0                    00
>r[15]=rowid
>>31    IdxDelete      1     14    2                    00
>>key=r[14..15]
>>32    Noop           0     0     0                    00
>>GenRowIdxDel for FooX
>>33    Column         0     0     14                   00
>r[14]=Foo.x
>>34    IdxDelete      3     14    2                    00
>>key=r[14..15]
>>35    Delete         0     68    8     Foo            00
>>36    IdxInsert      1     1     2     2              00  key=r[1]
>>37    IdxInsert      3     4     5     2              00  key=r[4]
>>38    MakeRecord     9     3     13                   00
>>r[13]=mkrec(r[9..11])
>>39    Insert         0     13    8     Foo            05
>intkey=r[8]
>>data=r[13]
>>40    Halt           0     0     0                    00
>>41    Transaction    0     1     4     0              01
>>usesStmtJournal=0
>>42    Goto           0     1     0                    00
>>
>>
>>
>>---
>>The fact that there's a Highway to Hell but only a Stairway to
>Heaven
>>says a lot about anticipated traffic volume.
>>
>>
>>>-----Original Message-----
>>>From: sqlite-users [mailto:sqlite-users-
>>>[hidden email]] On Behalf Of David Raymond
>>>Sent: Wednesday, 12 September, 2018 07:54
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SQLITE touches unchanged expression indexes
>on
>>>update
>>>
>>>Just showing with "explain comments" enabled.
>>>
>>>
>>>sqlite> explain update foo set x = 1 where rowid = 1;
>>>addr  opcode         p1    p2    p3    p4             p5  comment
>>>----  -------------  ----  ----  ----  -------------  --  ---------
>-
>>-
>>>--
>>>0     Init           0     33    0                    00  Start at
>>33
>>>1     Null           0     7     8                    00
>>>r[7..8]=NULL
>>>2     OpenWrite      0     2     0     3              00  root=2
>>>iDb=0; Foo
>>>3     Explain        3     0     0     SEARCH TABLE foo USING
>>INTEGER
>>>PRIMARY KEY (rowid=?)  00
>>>4     Integer        1     12    0                    00  r[12]=1
>>>5     SeekRowid      0     7     12                   00
>>>intkey=r[12]; pk
>>>6     Rowid          0     8     0                    00
>r[8]=rowid
>>>7     OpenWrite      1     5     0     k(2,,)         00  root=5
>>>iDb=0; FooLenZ
>>>8     OpenWrite      3     3     0     k(2,,)         00  root=3
>>>iDb=0; FooX
>>>9     IsNull         8     32    0                    00  if
>>>r[8]==NULL goto 32
>>>10    Integer        1     9     0                    00  r[9]=1
>>>11    Column         0     1     10                   00
>>r[10]=Foo.y
>>>12    Column         0     2     11                   00
>>r[11]=Foo.z
>>>13    Noop           0     0     0                    00
>uniqueness
>>>check for FooLenZ
>>>14    Copy           11    13    0                    00
>>r[13]=r[11]
>>>15    PureFunc0      0     13    2     length(1)      01  FooLenZ
>>>column 0
>>>16    IntCopy        8     3     0                    00
>r[3]=r[8];
>>>rowid
>>>17    MakeRecord     2     2     1                    00
>>>r[1]=mkrec(r[2..3]); for FooLenZ
>>>18    Noop           0     0     0                    00
>uniqueness
>>>check for FooX
>>>19    SCopy          9     5     0                    00
>r[5]=r[9];
>>>x
>>>20    IntCopy        8     6     0                    00
>r[6]=r[8];
>>>rowid
>>>21    MakeRecord     5     2     4                    00
>>>r[4]=mkrec(r[5..6]); for FooX
>>>22    Copy           11    13    0                    00
>>r[13]=r[11]
>>>23    PureFunc0      0     13    14    length(1)      01
>>>24    Rowid          0     15    0                    00
>>r[15]=rowid
>>>25    IdxDelete      1     14    2                    00
>>>key=r[14..15]
>>>26    Column         0     0     14                   00
>>r[14]=Foo.x
>>>27    IdxDelete      3     14    2                    00
>>>key=r[14..15]
>>>28    IdxInsert      1     1     2     2              00  key=r[1]
>>>29    IdxInsert      3     4     5     2              00  key=r[4]
>>>30    MakeRecord     9     3     13                   00
>>>r[13]=mkrec(r[9..11])
>>>31    Insert         0     13    8     Foo            05
>>intkey=r[8]
>>>data=r[13]
>>>32    Halt           0     0     0                    00
>>>33    Transaction    0     1     4     0              01
>>>usesStmtJournal=0
>>>34    Goto           0     1     0                    00
>>>
>>>
>>>-----Original Message-----
>>>From: sqlite-users [mailto:sqlite-users-
>>>[hidden email]] On Behalf Of Deon Brewis
>>>Sent: Tuesday, September 11, 2018 8:20 PM
>>>To: SQLite mailing list
>>>Subject: [sqlite] SQLITE touches unchanged expression indexes on
>>>update
>>>
>>>It seems like there is an opportunity for improvement on updates if
>>>an index contains expressions.
>>>
>>>In the following example:
>>>
>>>CREATE TABLE Foo(x, y, z);
>>>CREATE INDEX FooX on Foo(x);
>>>CREATE INDEX FooZ on Foo(z);
>>>CREATE INDEX FooLenZ on Foo(length(z));
>>>
>>>explain UPDATE foo SET x=1 WHERE rowid=1;
>>>
>>>I see the plan below. Notice it's updating FooLenZ even though the
>>>'UPDATE foo SET x=1' statement doesn't touch the Z column at all.
>It
>>>doesn't try to update FooZ, just FooLenZ, but both should be
>>>untouched.
>>>
>>>RecNo addr opcode      p1 p2 p3 p4        p5 comment
>>>----- ---- ----------- -- -- -- --------- -- -------
>>>    1 0    Init        0  30 0            00 (null)
>>>    2 1    Null        0  7  8            00 (null)
>>>    3 2    OpenWrite   0  2  0  3         00 (null)  // opening
>>'Foo'
>>>(expected)
>>>    4 3    Integer     1  12 0            00 (null)
>>>    5 4    SeekRowid   0  6  12           00 (null)
>>>    6 5    Rowid       0  8  0            00 (null)
>>>    7 6    OpenWrite   1  5  0  k(2,,)    00 (null)  // opening
>>>'FooLenZ' (NOT expected)
>>>    8 7    OpenWrite   3  3  0  k(2,,)    00 (null)  // opening
>>>'FooX'   (expected)
>>>    9 8    IsNull      8  29 0            00 (null)
>>>   10 9    Integer     1  9  0            00 (null)
>>>   11 10   Column      0  1  10           00 (null)
>>>   12 11   Column      0  2  11           00 (null)
>>>   13 12   Copy        11 13 0            00 (null)
>>>   14 13   Function0   0  13 2  length(1) 01 (null)
>>>   15 14   IntCopy     8  3  0            00 (null)
>>>   16 15   MakeRecord  2  2  1            00 (null)
>>>   17 16   SCopy       9  5  0            00 (null)
>>>   18 17   IntCopy     8  6  0            00 (null)
>>>   19 18   MakeRecord  5  2  4            00 (null)
>>>   20 19   Copy        11 13 0            00 (null)
>>>   21 20   Function0   0  13 14 length(1) 01 (null)
>>>   22 21   Rowid       0  15 0            00 (null)
>>>   23 22   IdxDelete   1  14 2            00 (null)
>>>   24 23   Column      0  0  14           00 (null)
>>>   25 24   IdxDelete   3  14 2            00 (null)
>>>   26 25   IdxInsert   1  1  2  2         00 (null)  // updating
>>>'FooLenZ' (NOT expected)
>>>   27 26   IdxInsert   3  4  5  2         00 (null)  // updating
>>>'FooX' (expected)
>>>   28 27   MakeRecord  9  3  13           00 (null)
>>>   29 28   Insert      0  13 8  Foo       05 (null)
>>>   30 29   Halt        0  0  0            00 (null)
>>>   31 30   Transaction 0  1  42 0         01 (null)
>>>   32 31   Goto        0  1  0            00 (null)
>>>
>>>sqlite_master:
>>>RecNo type  name    tbl_name rootpage sql
>>>----- ----- ------- -------- -------- -----------------------------
>-
>>-
>>>-------
>>>    1 table Foo     Foo             2 CREATE TABLE Foo(x, y, z)
>>>    2 index FooX    Foo             3 CREATE INDEX FooX on Foo(x)
>>>    3 index FooZ    Foo             4 CREATE INDEX FooZ on Foo(z)
>>>    4 index FooLenZ Foo             5 CREATE INDEX FooLenZ on
>>>Foo(length(z))
>>>
>>>
>>>
>>>_______________________________________________
>>>sqlite-users mailing list
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>_______________________________________________
>>>sqlite-users mailing list
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Richard Hipp-3
In reply to this post by Deon Brewis
On 9/11/18, Deon Brewis <[hidden email]> wrote:
> It seems like there is an opportunity for improvement on updates if an index
> contains expressions.

This enhancement did not make the cutoff for 3.25.0.  But as 3.25.0 is
now out, I have started the next release cycle and you can find this
enhancement on the latest trunk version of SQLite.  You'll need to
grab a tarball (or clone the Fossil repository) and compile it
yourself.  If you can, please do this and try out the code and let me
know whether or not it works, that will be appreciated.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE touches unchanged expression indexes on update

Deon Brewis
This seems to work perfectly for expression indexes.  Thanks, it makes a HUGE difference for us!

Would you perhaps be able to make a similar fix for partial indexes? i.e. this scenario:

CREATE TABLE Foo(x, y, z);
CREATE INDEX FooX on Foo(x);
CREATE INDEX FooZ on Foo(z);
CREATE INDEX FooPartialZ on Foo(z) WHERE z > 42;

explain UPDATE foo SET x=1 WHERE rowid=1;


addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     36    0                    00  Start at 36
1     Null           0     7     8                    00  r[7..8]=NULL
2     OpenWrite      0     2     0     3              00  root=2 iDb=0; Foo
3     Integer        1     12    0                    00  r[12]=1
4     SeekRowid      0     6     12                   00  intkey=r[12]
5     Rowid          0     8     0                    00  r[8]=rowid
6     OpenWrite      1     5     0     k(2,,)         00  root=5 iDb=0; FooPartialZ   <<<===========================
7     OpenWrite      3     3     0     k(2,,)         00  root=3 iDb=0; FooX
8     IsNull         8     35    0                    00  if r[8]==NULL goto 35
9     Integer        1     9     0                    00  r[9]=1
10    Column         0     1     10                   00  r[10]=Foo.y
11    Column         0     2     11                   00  r[11]=Foo.z
12    Noop           0     0     0                    00  uniqueness check for FooPartialZ  <<<===========================
13    Null           0     1     0                    00  r[1]=NULL
14    Le             14    18    11    (BINARY)       51  if r[11]<=r[14] goto 18
15    SCopy          11    2     0                    00  r[2]=r[11]; z
16    IntCopy        8     3     0                    00  r[3]=r[8]; rowid
17    MakeRecord     2     2     1                    00  r[1]=mkrec(r[2..3]); for FooPartialZ  <<<===========================
18    Noop           0     0     0                    00  uniqueness check for FooX
19    SCopy          9     5     0                    00  r[5]=r[9]; x
20    IntCopy        8     6     0                    00  r[6]=r[8]; rowid
21    MakeRecord     5     2     4                    00  r[4]=mkrec(r[5..6]); for FooX
22    Column         0     2     13                   00  r[13]=Foo.z
23    Le             14    27    13    (BINARY)       51  if r[13]<=r[14] goto 27
24    Column         0     2     15                   00  r[15]=Foo.z
25    Rowid          0     16    0                    00  r[16]=rowid
26    IdxDelete      1     15    2                    00  key=r[15..16]
27    Column         0     0     15                   00  r[15]=Foo.x
28    Rowid          0     16    0                    00  r[16]=rowid
29    IdxDelete      3     15    2                    00  key=r[15..16]
30    IsNull         1     32    0                    00  if r[1]==NULL goto 32
31    IdxInsert      1     1     2     2              00  key=r[1]  <<<===========================
32    IdxInsert      3     4     5     2              00  key=r[4]
33    MakeRecord     9     3     13                   00  r[13]=mkrec(r[9..11])
34    Insert         0     13    8     Foo            05  intkey=r[8] data=r[13]
35    Halt           0     0     0                    00
36    Transaction    0     1     4     0              01  usesStmtJournal=0
37    Integer        42    14    0                    00  r[14]=42
38    Goto           0     1     0                    00

-----Original Message-----
From: [hidden email] <[hidden email]> On Behalf Of Richard Hipp
Sent: Saturday, September 15, 2018 2:46 PM
To: SQLite mailing list <[hidden email]>
Cc: [hidden email]
Subject: Re: [sqlite] SQLITE touches unchanged expression indexes on update

On 9/11/18, Deon Brewis <[hidden email]> wrote:
> It seems like there is an opportunity for improvement on updates if an
> index contains expressions.

This enhancement did not make the cutoff for 3.25.0.  But as 3.25.0 is now out, I have started the next release cycle and you can find this enhancement on the latest trunk version of SQLite.  You'll need to grab a tarball (or clone the Fossil repository) and compile it yourself.  If you can, please do this and try out the code and let me know whether or not it works, that will be appreciated.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users