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