Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Keith Medcalf
On Friday, 28 April, 2017 15:55, Simon Slavin wrote: > > The only difference is the explicit JOIN statement. I was under the > > impression that using this, vs. the way I wrote it, is a matter of taste > > that doesn’t affect the execution of the query. > SQLite computes

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Keith Medcalf
> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Friday, 28 April, 2017 15:00 > To: SQLite mailing list > Subject: Re: [sqlite] Query plan gone haywire lays waste to my library's > performance >

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Igor Tandetnik
On 4/28/2017 4:37 PM, Jens Alfke wrote: CREATE TABLE docs (doc_id INTEGER PRIMARY KEY, docid TEXT UNIQUE NOT NULL, expiry_timestamp INTEGER); CREATE INDEX docs_docid ON docs(docid); For the record, this index is redundant. There's already an automatically created index on docs(docid), thanks

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Simon Slavin
On 28 Apr 2017, at 10:23pm, Jens Alfke wrote: > On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > >> What indexes do you have on these two tables ? I can’t recommend one >> without knowing which columns belong to which tables. > > I showed them at

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Jens Alfke
> On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > > What indexes do you have on these two tables ? I can’t recommend one without > knowing which columns belong to which tables. I showed them at the end of my first message. > First query rewritten for clarity: > […]

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Simon Slavin
On 28 Apr 2017, at 9:49pm, Jens Alfke wrote: > SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs, docs > WHERE sequence>? AND current!=0 AND deleted=0 AND revs.doc_id = docs.doc_id > ORDER BY revs.doc_id, deleted, revid DESC > 0 0 0 SCAN TABLE revs USING

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Jens Alfke
Sorry for the quick follow-up, but I’ve just run a quick test that does an EXPLAIN QUERY PLAN for every statement that gets compiled, in an empty database, and confirmed that the query plan comes out wrong right from the start: SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM

[sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Jens Alfke
I’ve got an urgent-to-me issue wherein a customer says that our library has slowed down by several orders of magnitude in iOS 10.3 compared to iOS 9. I ran a test case they provided and found that some queries that should be fast are taking a very long time (~500ms) and generating huge numbers