Re: [sqlite] Weird query plan when sorting by a joined column

Hi Richard, On 16.05.2018 12:32, Richard Hipp wrote: >> Actually it is: SQLite will generate the same query plan for both >> queries as long as no statistics data is available. Adding the >> statistics information will lead to the more complex and slow query plan >> which is why I kept the

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 12:32, Richard Hipp wrote: > On 5/16/18, Torsten Landschoff wrote: >> >> Actually it is: SQLite will generate the same query plan for both >> queries as long as no statistics data is available. Adding the >> statistics information will lead to the more

Re: [sqlite] Weird query plan when sorting by a joined column

On 5/16/18, Torsten Landschoff wrote: > > Actually it is: SQLite will generate the same query plan for both > queries as long as no statistics data is available. Adding the > statistics information will lead to the more complex and slow query plan > which is why I

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 01:48, Keith Medcalf wrote: > > There is no LEFT join visible to anyone except someone who is in love > with LEFT joins without knowing what one is. Sorry, I corrupted my text while trying to simplify it. I originally wrote "since this is not a left outer join", but tried to remove

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 01:24, Simon Slavin wrote: >> I would have expected that b.id is basically an alias for d.id as > > Obvious to a good programmer. Not obvious to SQLite. Your SELECT is > selecting from the "base" table, but trying to order by a table > JOINed to it. The fact that d.id is always

Re: [sqlite] Weird query plan when sorting by a joined column

6: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

Re: [sqlite] Weird query plan when sorting by a joined column

On 15 May 2018, at 11:12pm, Torsten Landschoff wrote: > 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 >

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 00:12, Torsten Landschoff wrote: > 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 >>

[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