Hello, I am running into an issue while trying to make our code more efficient when talking to Oracle - what is much faster when going to the fat RDBMS is curiously slow on sqlite.
Basically when doing a join like select * from base b join derived d using (id) order by d.id the query plan is abysmal slower than when using select * from base b join derived d using (id) order by b.id I would have expected that b.id is basically an alias for d.id as this is a simple left join. Unfortunately it is not simple to change this in our query as it is created using the SQLAlchemy ORM. In case anybody would be so kind and take a look at this I included a small SQL Script to reproduce. Results here are: ---------- > $ /opt/sqlite3/bin/sqlite3 -version > 3.23.2 2018-05-09 02:23:29 > f139f6f07df094a0a62e0a55ae7e91dc68006d55c9db7b244a945cc8216f55ff > $ /opt/sqlite3/bin/sqlite3 < weird_plan.sql > > This results in a blazingly fast execution: > > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 19 0 00 Start at 19 > 1 Noop 2 4 0 00 > 2 Integer 100 1 0 00 r[1]=100; LIMIT > counter > 3 Integer 0 2 0 00 r[2]=0 > 4 MustBeInt 2 0 0 00 OFFSET counter > 5 OffsetLimit 1 3 2 00 if r[1]>0 then > r[3]=r[1]+max(0,r[2]) else r[3]=(-1); LIMIT+OFFSET > 6 OpenRead 1 4 0 0 00 root=4 iDb=0; > derived > 7 OpenRead 0 2 0 2 00 root=2 iDb=0; base > 8 SeekLT 1 18 4 00 key=r[4]; pk > 9 Rowid 1 5 0 00 r[5]=rowid > 10 SeekRowid 0 17 5 00 intkey=r[5]; pk > 11 Ge 4 17 5 54 if r[5]>=r[4] > goto 17 > 12 IfPos 2 17 1 00 if r[2]>0 then > r[2]-=1, goto 17; OFFSET > 13 Copy 5 7 0 00 r[7]=r[5] > 14 Column 0 1 8 00 > r[8]=base.modification_time > 15 ResultRow 7 2 0 00 output=r[7..8] > 16 DecrJumpZero 1 18 0 00 if (--r[1])==0 > goto 18 > 17 Prev 1 9 0 00 > 18 Halt 0 0 0 00 > 19 Transaction 0 0 4 0 01 usesStmtJournal=0 > 20 Integer 5463052 4 0 00 r[4]=5463052 > 21 Goto 0 1 0 00 > > This takes seconds with real data: > > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 30 0 00 Start at 30 > 1 OpenEphemeral 2 4 0 k(1,-B) 00 nColumn=4 > 2 Integer 100 1 0 00 r[1]=100; LIMIT > counter > 3 Integer 0 2 0 00 r[2]=0 > 4 MustBeInt 2 0 0 00 OFFSET counter > 5 OffsetLimit 1 3 2 00 if r[1]>0 then > r[3]=r[1]+max(0,r[2]) else r[3]=(-1); LIMIT+OFFSET > 6 OpenRead 1 4 0 0 00 root=4 iDb=0; > derived > 7 OpenRead 0 2 0 2 00 root=2 iDb=0; base > 8 Rewind 1 23 0 00 > 9 Integer 5463052 4 0 00 r[4]=5463052 > 10 Rowid 1 5 0 00 r[5]=rowid > 11 Ge 4 23 5 53 if r[5]>=r[4] > goto 23 > 12 SeekRowid 0 22 5 00 intkey=r[5]; pk > 13 Ge 7 22 5 54 if r[5]>=r[7] > goto 22 > 14 Column 0 1 10 00 > r[10]=base.modification_time > 15 Copy 5 8 0 00 r[8]=r[5] > 16 Sequence 2 9 0 00 > r[9]=cursor[2].ctr++ > 17 MakeRecord 8 3 12 00 > r[12]=mkrec(r[8..10]) > 18 IdxInsert 2 12 8 3 00 key=r[12] > 19 IfNotZero 3 22 0 00 if r[3]!=0 then > r[3]--, goto 22 > 20 Last 2 0 0 00 > 21 Delete 2 0 0 00 > 22 Next 1 10 0 00 > 23 Sort 2 29 0 00 > 24 IfPos 2 28 1 00 if r[2]>0 then > r[2]-=1, goto 28; OFFSET > 25 Column 2 2 11 00 > r[11]=base_modification_time > 26 Column 2 0 10 00 r[10]=base_id > 27 ResultRow 10 2 0 00 output=r[10..11] > 28 Next 2 24 0 00 > 29 Halt 0 0 0 00 > 30 Transaction 0 0 4 0 01 usesStmtJournal=0 > 31 Integer 5463052 7 0 00 r[7]=5463052 > 32 Goto 0 1 0 00 ---------- TIA for your support! Greetings, Torsten -- $---+----1----+----2----+----3----+----4----+----5----+----6----+ SCALE GmbH Niederlassung Dresden Torsten Landschoff Pohlandstraße 19 01309 Dresden Tel: +49-351-312002-10 Fax: +49-351-312002-29 SCALE GmbH Registergericht und Sitz: Ingolstadt, HRB 6384 Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
