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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Keith Medcalf Sent: Wednesday, September 12, 2018 10:04 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 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- >boun...@mailinglists.sqlite.org] 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- >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 _______________________________________________ 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