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- >boun...@mailinglists.sqlite.org] 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- >boun...@mailinglists.sqlite.org] 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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
