Quantcast

Unary + isn't disabling use of index

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Unary + isn't disabling use of index

Jens Alfke-2
Another query using the wrong plan, even though I’ve added a unary “+” to prevent use of indexes on ‘current’:

        SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs
        JOIN docs ON docs.doc_id = revs.doc_id
        WHERE sequence > ? AND +current=1
        ORDER BY revs.doc_id, deleted, revid DESC

        0 0 0 SCAN TABLE revs USING COVERING INDEX revs_current
        0 1 1 SEARCH TABLE docs USING INTEGER PRIMARY KEY (rowid=?)
        0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY

Where the revs_current index is
        CREATE INDEX revs_current ON revs(doc_id, current desc, deleted, revid desc);

I’ve tried a number of things and I can’t get the optimizer to stop brute-force scanning “revs” instead of using the primary key “sequence”.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Unary + isn't disabling use of index

Igor Tandetnik-2
On 4/28/2017 5:30 PM, Jens Alfke wrote:

> Another query using the wrong plan, even though I’ve added a unary “+” to prevent use of indexes on ‘current’:
>
> SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs
> JOIN docs ON docs.doc_id = revs.doc_id
> WHERE sequence > ? AND +current=1
> ORDER BY revs.doc_id, deleted, revid DESC
>
> 0 0 0 SCAN TABLE revs USING COVERING INDEX revs_current
> 0 1 1 SEARCH TABLE docs USING INTEGER PRIMARY KEY (rowid=?)
> 0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
>
> Where the revs_current index is
> CREATE INDEX revs_current ON revs(doc_id, current desc, deleted, revid desc);

It seems the index is used to implement "ORDER BY revs.doc_id" part. Try
"ORDER BY +revs.doc_id"
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Unary + isn't disabling use of index

Richard Hipp-3
On 4/28/17, Igor Tandetnik <[hidden email]> wrote:
> On 4/28/2017 5:30 PM, Jens Alfke wrote:
>> Another query using the wrong plan, even though I’ve added a unary “+” to
>> prevent use of indexes on ‘current’:
>
> It seems the index is used to implement "ORDER BY revs.doc_id" part. Try
> "ORDER BY +revs.doc_id"

In an ideal world, none of this kind of query hinting would be
required.  Have you tried running "PRAGMA optimize" once after running
the query above, then rerunning the query to see if the query plan
changes?

Can you run ANALYZE and send us a dump of the sqlite_stat1 table?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Unary + isn't disabling use of index

Jens Alfke-2
In reply to this post by Igor Tandetnik-2

> On Apr 28, 2017, at 2:43 PM, Igor Tandetnik <[hidden email]> wrote:
>
> It seems the index is used to implement "ORDER BY revs.doc_id" part. Try "ORDER BY +revs.doc_id"

Yes! That fixed it, and the other query I was posting about too. Thanks!

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Unary + isn't disabling use of index

Keith Medcalf
In reply to this post by Jens Alfke-2

You can always list the tables in the order you want them visited replacing the "," operator with CROSS JOIN to force the optimizer to piss off.


  SELECT sequence, revs.doc_id, docid, revid, deleted  
    FROM docs CROSS JOIN revs
   WHERE sequence > ?
     AND current=1
     AND revs.doc_id = docs.doc_id
ORDER BY revs.doc_id, deleted, revid DESC

which will FORCE the docs table to be used in the outer loop and revs in the inner loop.

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jens Alfke
> Sent: Friday, 28 April, 2017 15:31
> To: SQLite mailing list
> Subject: [sqlite] Unary + isn't disabling use of index
>
> Another query using the wrong plan, even though I’ve added a unary “+” to
> prevent use of indexes on ‘current’:
>
> SELECT sequence, revs.doc_id, docid, revid, deleted FROM revs
> JOIN docs ON docs.doc_id = revs.doc_id
> WHERE sequence > ? AND +current=1
> ORDER BY revs.doc_id, deleted, revid DESC
>
> 0 0 0 SCAN TABLE revs USING COVERING INDEX revs_current
> 0 1 1 SEARCH TABLE docs USING INTEGER PRIMARY KEY (rowid=?)
> 0 0 0 USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
>
> Where the revs_current index is
> CREATE INDEX revs_current ON revs(doc_id, current desc, deleted,
> revid desc);
>
> I’ve tried a number of things and I can’t get the optimizer to stop brute-
> force scanning “revs” instead of using the primary key “sequence”.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...