There is no LEFT join visible to anyone except someone who is in love with LEFT joins without knowing what one is.
Your query is misformed. You proper query ought to be: select * from base b join derived d using (id) order by id; There is no b.id nor d.id in the output row. It is a figment of your imagination. The output row contains an "id". Of course the problem arises because of all the other people who also like to write broken SQL and expect it it work. Why do you expect that your brokenness should win out over someone else's brokenness? --- 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 Torsten Landschoff >Sent: Tuesday, 15 May, 2018 16:13 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Weird query plan when sorting by a joined column > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
