Fascinating again, because the code is identical. The p1 difference in the OpenWrite/IdxInsert/IdxDelete opcodes is just the "file handle" that happens to be being used and doesn't have any real significance (at least I don't think it does). The p3 in the Transaction opcode is merely the schemacookie and not only indicates that the schema has seen 2 changes between these plans being generated (presumably the DROP and CREATE of the index).
I am at a loss to explain the difference in execution speed. -- 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 dirdi >Sent: Thursday, 29 August, 2019 15:10 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Unexpected REINDEX behavior. > >On 8/29/19 9:19 PM, Keith Medcalf wrote: >> Fascinating ... because the optimal query plan would be to do a >table scan of tbl1 and then dip into tbl2 for each row to get the >update value. The only meaningful index then would be on tbl2(int) >(that is idx3) since an index will be unhelpful for the LIKE >constraint. > >I noticed that, too. But I stopped wondering about optimizer >internals, >back when web-browsers started to JIT compile javascript ;) > >> Is the generated code different for the two queries, the one that >runs slow -vs- the one that runs fast? (That is, the output from >EXPLAIN ..., not the output from EXPLAIN QUERY PLAN, or from .eqp >full in the CLI). > >This is the output of EXPLAIN UPDATE before I recreate the index: >> 0 Init 0 73 0 00 >> 1 Null 0 4 5 00 >> 2 OpenRead 0 13 0 0 00 >> 3 Rewind 0 7 0 00 >> 4 Rowid 0 5 0 00 >> 5 RowSetAdd 4 5 0 00 >> 6 Next 0 4 0 01 >> 7 OpenWrite 0 13 0 7 00 >> 8 OpenWrite 1 18 0 k(2,,) 00 >> 9 RowSetRead 4 72 5 00 >> 10 NotExists 0 9 5 00 >> 11 Rowid 0 6 0 00 >> 12 Null 0 7 0 00 >> 13 Null 0 8 0 00 >> 14 Column 0 3 9 00 >> 15 Null 0 10 0 00 >> 16 Null 0 11 0 00 >> 17 Null 0 12 0 00 >> 18 Copy 5 13 0 00 >> 19 Null 0 14 0 00 >> 20 Column 0 1 15 00 >> 21 Column 0 2 16 00 >> 22 Null 0 22 22 00 >> 23 Noop 5 3 0 00 >> 24 Integer 1 23 0 00 >> 25 OpenRead 4 11 0 3 00 >> 26 OpenRead 6 23 0 k(2,,) 00 >> 27 Column 0 2 24 00 >> 28 Affinity 24 1 0 D 00 >> 29 SeekLE 6 39 24 1 00 >> 30 DeferredSeek 6 0 4 00 >> 31 Column 4 1 29 00 >> 32 Concat 29 28 26 00 >> 33 Column 0 1 27 00 >> 34 Function0 0 26 25 like(2) 02 >> 35 IfNot 25 38 1 00 >> 36 IdxRowid 6 22 0 00 >> 37 DecrJumpZero 23 39 0 00 >> 38 Prev 6 30 0 00 >> 39 SCopy 22 17 0 00 >> 40 Column 0 4 18 NULL 00 >> 41 Column 0 5 19 NULL 00 >> 42 Column 0 6 20 0 00 >> 43 Affinity 14 7 0 DBDDBDD 00 >> 44 SCopy 17 2 0 00 >> 45 IntCopy 13 3 0 00 >> 46 MakeRecord 2 2 1 00 >> 47 MakeRecord 14 7 21 00 >> 48 FkIfZero 0 56 0 00 >> 49 IsNull 9 56 0 00 >> 50 SCopy 9 25 0 00 >> 51 MustBeInt 25 55 0 00 >> 52 OpenRead 7 11 0 3 00 >> 53 NotExists 7 55 25 00 >> 54 Goto 0 56 0 00 >> 55 FkCounter 0 -1 0 00 >> 56 Close 7 0 0 00 >> 57 Column 0 3 26 00 >> 58 Rowid 0 27 0 00 >> 59 IdxDelete 1 26 2 00 >> 60 Delete 0 68 13 tbl1 00 >> 61 IsNull 17 68 0 00 >> 62 SCopy 17 25 0 00 >> 63 MustBeInt 25 67 0 00 >> 64 OpenRead 8 11 0 3 00 >> 65 NotExists 8 67 25 00 >> 66 Goto 0 68 0 00 >> 67 FkCounter 0 1 0 00 >> 68 Close 8 0 0 00 >> 69 IdxInsert 1 1 2 2 00 >> 70 Insert 0 21 13 tbl1 05 >> 71 Goto 0 9 0 00 >> 72 Halt 0 0 0 00 >> 73 Transaction 0 1 20 0 01 >> 74 String8 0 28 0 % 00 >> 75 Goto 0 1 0 00 > >... and that is the output afterwards: >> 0 Init 0 73 0 00 >> 1 Null 0 4 5 00 >> 2 OpenRead 0 13 0 0 00 >> 3 Rewind 0 7 0 00 >> 4 Rowid 0 5 0 00 >> 5 RowSetAdd 4 5 0 00 >> 6 Next 0 4 0 01 >> 7 OpenWrite 0 13 0 7 00 >> 8 OpenWrite 2 18 0 k(2,,) 00 >> 9 RowSetRead 4 72 5 00 >> 10 NotExists 0 9 5 00 >> 11 Rowid 0 6 0 00 >> 12 Null 0 7 0 00 >> 13 Null 0 8 0 00 >> 14 Column 0 3 9 00 >> 15 Null 0 10 0 00 >> 16 Null 0 11 0 00 >> 17 Null 0 12 0 00 >> 18 Copy 5 13 0 00 >> 19 Null 0 14 0 00 >> 20 Column 0 1 15 00 >> 21 Column 0 2 16 00 >> 22 Null 0 22 22 00 >> 23 Noop 5 3 0 00 >> 24 Integer 1 23 0 00 >> 25 OpenRead 4 11 0 3 00 >> 26 OpenRead 6 23 0 k(2,,) 00 >> 27 Column 0 2 24 00 >> 28 Affinity 24 1 0 D 00 >> 29 SeekLE 6 39 24 1 00 >> 30 DeferredSeek 6 0 4 00 >> 31 Column 4 1 29 00 >> 32 Concat 29 28 26 00 >> 33 Column 0 1 27 00 >> 34 Function0 0 26 25 like(2) 02 >> 35 IfNot 25 38 1 00 >> 36 IdxRowid 6 22 0 00 >> 37 DecrJumpZero 23 39 0 00 >> 38 Prev 6 30 0 00 >> 39 SCopy 22 17 0 00 >> 40 Column 0 4 18 NULL 00 >> 41 Column 0 5 19 NULL 00 >> 42 Column 0 6 20 0 00 >> 43 Affinity 14 7 0 DBDDBDD 00 >> 44 SCopy 17 2 0 00 >> 45 IntCopy 13 3 0 00 >> 46 MakeRecord 2 2 1 00 >> 47 MakeRecord 14 7 21 00 >> 48 FkIfZero 0 56 0 00 >> 49 IsNull 9 56 0 00 >> 50 SCopy 9 25 0 00 >> 51 MustBeInt 25 55 0 00 >> 52 OpenRead 7 11 0 3 00 >> 53 NotExists 7 55 25 00 >> 54 Goto 0 56 0 00 >> 55 FkCounter 0 -1 0 00 >> 56 Close 7 0 0 00 >> 57 Column 0 3 26 00 >> 58 Rowid 0 27 0 00 >> 59 IdxDelete 2 26 2 00 >> 60 Delete 0 68 13 tbl1 00 >> 61 IsNull 17 68 0 00 >> 62 SCopy 17 25 0 00 >> 63 MustBeInt 25 67 0 00 >> 64 OpenRead 8 11 0 3 00 >> 65 NotExists 8 67 25 00 >> 66 Goto 0 68 0 00 >> 67 FkCounter 0 1 0 00 >> 68 Close 8 0 0 00 >> 69 IdxInsert 2 1 2 2 00 >> 70 Insert 0 21 13 tbl1 05 >> 71 Goto 0 9 0 00 >> 72 Halt 0 0 0 00 >> 73 Transaction 0 1 22 0 01 >> 74 String8 0 28 0 % 00 >> 75 Goto 0 1 0 00 > >Indeed, they are not identical: >> $ diff explain{1,2} >> 9c9 >> < > 8 OpenWrite 1 18 0 k(2,,) 00 >> --- >>> > 8 OpenWrite 2 18 0 k(2,,) 00 >> 60c60 >> < > 59 IdxDelete 1 26 2 00 >> --- >>> > 59 IdxDelete 2 26 2 00 >> 70c70 >> < > 69 IdxInsert 1 1 2 2 00 >> --- >>> > 69 IdxInsert 2 1 2 2 00 >> 74c74 >> < > 73 Transaction 0 1 20 0 01 >> --- >>> > 73 Transaction 0 1 22 0 01 > > >> Also, what version of SQLite3 are you using? > >> SELECT sqlite_version(); > >returns > >> 3.29.0 >Packages installed on my dev machine: >> $ dpkg-query -l "*sqlite*" >> Desired=Unknown/Install/Remove/Purge/Hold >> | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig- >aWait/Trig-pend >> |/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad) >> ||/ Name Version Architecture Description >> +++-=======================-==============-============- >================================= >> ii libqt5sql5-sqlite:amd64 5.11.3+dfsg1-4 amd64 Qt 5 SQLite >3 database driver >> ii libsqlite3-0:amd64 3.29.0-2 amd64 SQLite 3 >shared library >> ii sqlitebrowser 3.11.2-1 amd64 GUI editor >for SQLite databases > >-- >Best regards > >dirdi >_______________________________________________ >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
