How to get ORDER BY / LIMIT to stick to the fast-path?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

How to get ORDER BY / LIMIT to stick to the fast-path?

Jonathan Moules-3
Hi List,

The below query takes just 0.002 seconds to run (sqlite 3.15.0 and
3.23.0 (preview)) despite looking through hundreds of thousands of
records in each table, and it returns 86 records in all. This is great!

But when I stick an "ORDER BY" on the end (either ASC or DESC), the
processing time shoots up to 0.15s. The EXPLAIN between the two is
considerably different so it seems the ORDER BY is getting it to use a
sub-optimal query plan.
If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
are also here (they start changing at item 36).

Any suggestions for what's going on here and how to coerce the planner
to stick to the fast-path and then do a simple order by on those 86 (or
however many - it'll always be a low number) results?
(ANALYZE has been run)

Thanks,
Jonathan

     SELECT
         u.url_id, u.url, l.error_code
     FROM
         urls u
     JOIN
         lookups l
         USING(url_id)
     JOIN (
         SELECT
             url_id,
             MAX(retrieval_datetime) AS retrieval_datetime
         FROM lookups
         WHERE
             url_id IN (
                 SELECT url_id FROM urls WHERE url = 'example.com'
             )
         ) recent

         ON u.source_seed_id = recent.url_id
             OR u.url_id = recent.url_id
     WHERE
         l.is_generic_flag = 1
         AND
         l.retrieval_datetime >= recent.retrieval_datetime
         AND
         DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14
days', 'start of day')


--------------------
Fast EXPLAIN:

0    Init    0    63    0        00
1    Integer    35    1    0        00
2    Once    0    35    0        00
3    OpenEphemeral    2    2    0        00
4    Null    0    2    4        00
5    OpenRead    5    50315    0    k(4,-,,,)    02
6    Once    0    16    0        00
7    OpenEphemeral    7    1    0    k(1,B)    00
8    OpenRead    8    8    0    k(2,,)    02
9    String8    0    6    0    example.com    00
10    SeekGE    8    15    6    1    00
11    IdxGT    8    15    6    1    00
12    IdxRowid    8    7    0        00
13    MakeRecord    7    1    8    C    00
14    IdxInsert    7    8    0        00
15    Close    8    0    0        00
16    Rewind    7    28    0        00
17    Column    7    0    5        00
18    IsNull    5    27    0        00
19    SeekLE    5    27    5    1    00
20    IdxLT    5    27    5    1    00
21    Column    5    1    8        00
22    CollSeq    9    0    0    (BINARY)    00
23    AggStep0    0    8    3    max(1)    01
24    If    9    26    0        00
25    Column    5    0    2        00
26    Prev    5    20    0        00
27    NextIfOpen    7    17    0        00
28    Close    5    0    0        00
29    AggFinal    3    1    0    max(1)    00
30    SCopy    2    10    0        00
31    SCopy    3    11    0        00
32    MakeRecord    10    2    8        00
33    NewRowid    2    12    0        00
34    Insert    2    8    12        08
35    Return    1    0    0        00
36    OpenRead    1    9    0    8    00
37    OpenRead    0    7    0    5    00
38    Rewind    2    60    0        00
39    Column    2    1    14        00
40    Function0    0    14    13    datetime(-1)    01
41    Le    15    59    13        51
42    Rewind    1    59    0        00
43    Column    1    7    13        00
44    Ne    16    58    13    (BINARY)    53
45    Column    1    3    14        00
46    Column    2    1    17        00
47    Lt    17    58    14    (BINARY)    53
48    Column    1    1    18        00
49    SeekRowid    0    58    18        00
50    Column    0    4    19        00
51    Column    2    0    20        00
52    Eq    20    54    19    (BINARY)    43
53    Ne    20    58    18    (BINARY)    53
54    Copy    18    22    0        00
55    Column    0    1    23        00
56    Column    1    6    24        00
57    ResultRow    22    3    0        00
58    Next    1    43    0        01
59    Next    2    39    0        01
60    Close    1    0    0        00
61    Close    0    0    0        00
62    Halt    0    0    0        00
63    Transaction    0    0    496    0    01
64    TableLock    0    9    0    lookups    00
65    TableLock    0    7    0    urls    00
66    String8    0    25    0    now    00
67    String8    0    26    0    -14 days    00
68    String8    0    27    0    start of day    00
69    Function0    7    25    15    datetime(-1)    03
70    Integer    1    16    0        00
71    Goto    0    1    0        00


-------
ORDER BY and LIMIT Explain:
0    Init    0    77    0        00
1    Integer    35    1    0        00
2    Once    0    35    0        00
3    OpenEphemeral    2    2    0        00
4    Null    0    2    4        00
5    OpenRead    5    50315    0    k(4,-,,,)    02
6    Once    0    16    0        00
7    OpenEphemeral    7    1    0    k(1,B)    00
8    OpenRead    8    8    0    k(2,,)    02
9    String8    0    6    0    example.com    00
10    SeekGE    8    15    6    1    00
11    IdxGT    8    15    6    1    00
12    IdxRowid    8    7    0        00
13    MakeRecord    7    1    8    C    00
14    IdxInsert    7    8    0        00
15    Close    8    0    0        00
16    Rewind    7    28    0        00
17    Column    7    0    5        00
18    IsNull    5    27    0        00
19    SeekLE    5    27    5    1    00
20    IdxLT    5    27    5    1    00
21    Column    5    1    8        00
22    CollSeq    9    0    0    (BINARY)    00
23    AggStep0    0    8    3    max(1)    01
24    If    9    26    0        00
25    Column    5    0    2        00
26    Prev    5    20    0        00
27    NextIfOpen    7    17    0        00
28    Close    5    0    0        00
29    AggFinal    3    1    0    max(1)    00
30    SCopy    2    10    0        00
31    SCopy    3    11    0        00
32    MakeRecord    10    2    8        00
33    NewRowid    2    12    0        00
34    Insert    2    8    12        08
35    Return    1    0    0        00
36    OpenEphemeral    9    5    0    k(1,B)    00
37    Integer    1    13    0        00
38    OpenRead    1    9    0    8    00
39    OpenRead    0    7    0    5    00
40    Rewind    2    68    0        00
41    Column    2    1    15        00
42    Function0    0    15    14    datetime(-1)    01
43    Le    16    67    14        51
44    Rewind    1    67    0        00
45    Column    1    7    14        00
46    Ne    17    66    14    (BINARY)    53
47    Column    1    3    15        00
48    Column    2    1    18        00
49    Lt    18    66    15    (BINARY)    53
50    Column    1    1    19        00
51    SeekRowid    0    66    19        00
52    Column    0    4    20        00
53    Column    2    0    21        00
54    Eq    21    56    20    (BINARY)    43
55    Ne    21    66    19    (BINARY)    53
56    Copy    19    25    0        00
57    Column    0    1    26        00
58    Column    1    6    27        00
59    Copy    25    23    0        00
60    Sequence    9    24    0        00
61    MakeRecord    23    5    28        00
62    IdxInsert    9    28    0        00
63    IfNotZero    13    66    1        00
64    Last    9    0    0        00
65    Delete    9    0    0        00
66    Next    1    45    0        01
67    Next    2    41    0        01
68    Close    1    0    0        00
69    Close    0    0    0        00
70    Sort    9    76    0        00
71    Column    9    2    25        00
72    Column    9    3    26        00
73    Column    9    4    27        00
74    ResultRow    25    3    0        00
75    Next    9    71    0        00
76    Halt    0    0    0        00
77    Transaction    0    0    496    0    01
78    TableLock    0    9    0    lookups    00
79    TableLock    0    7    0    urls    00
80    String8    0    29    0    now    00
81    String8    0    30    0    -14 days    00
82    String8    0    31    0    start of day    00
83    Function0    7    29    16    datetime(-1)    03
84    Integer    1    17    0        00
85    Goto    0    1    0        00

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

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Simon Slavin-3
On 22 Mar 2018, at 9:24pm, Jonathan Moules <[hidden email]> wrote:

> But when I stick an "ORDER BY" on the end (either ASC or DESC), the processing time shoots up to 0.15s. The EXPLAIN between the two is considerably different so it seems the ORDER BY is getting it to use a sub-optimal query plan.

Hi, Jonathan.

Could you post details similar to the ones in that post, but instead of using EXPLAIN use EXPLAIN QUERY PLAN ?  This will give results on a scale which is easier to understand in the context of your queries and indexes.

<https://www.sqlite.org/eqp.html>

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

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Richard Hipp-3
In reply to this post by Jonathan Moules-3
Quick workaround: put a "+" on front of the first term of your ORDER BY
clause.

On Thursday, March 22, 2018, Jonathan Moules <[hidden email]>
wrote:
> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
(preview)) despite looking through hundreds of thousands of records in each
table, and it returns 86 records in all. This is great!
>
> But when I stick an "ORDER BY" on the end (either ASC or DESC), the
processing time shoots up to 0.15s. The EXPLAIN between the two is
considerably different so it seems the ORDER BY is getting it to use a
sub-optimal query plan.
> If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
are also here (they start changing at item 36).
>
> Any suggestions for what's going on here and how to coerce the planner to
stick to the fast-path and then do a simple order by on those 86 (or
however many - it'll always be a low number) results?

> (ANALYZE has been run)
>
> Thanks,
> Jonathan
>
>     SELECT
>         u.url_id, u.url, l.error_code
>     FROM
>         urls u
>     JOIN
>         lookups l
>         USING(url_id)
>     JOIN (
>         SELECT
>             url_id,
>             MAX(retrieval_datetime) AS retrieval_datetime
>         FROM lookups
>         WHERE
>             url_id IN (
>                 SELECT url_id FROM urls WHERE url = 'example.com'
>             )
>         ) recent
>
>         ON u.source_seed_id = recent.url_id
>             OR u.url_id = recent.url_id
>     WHERE
>         l.is_generic_flag = 1
>         AND
>         l.retrieval_datetime >= recent.retrieval_datetime
>         AND
>         DATETIME(recent.retrieval_datetime) > DATETIME('now', '-14 days',
'start of day')

>
>
> --------------------
> Fast EXPLAIN:
>
> 0    Init    0    63    0        00
> 1    Integer    35    1    0        00
> 2    Once    0    35    0        00
> 3    OpenEphemeral    2    2    0        00
> 4    Null    0    2    4        00
> 5    OpenRead    5    50315    0    k(4,-,,,)    02
> 6    Once    0    16    0        00
> 7    OpenEphemeral    7    1    0    k(1,B)    00
> 8    OpenRead    8    8    0    k(2,,)    02
> 9    String8    0    6    0    example.com    00
> 10    SeekGE    8    15    6    1    00
> 11    IdxGT    8    15    6    1    00
> 12    IdxRowid    8    7    0        00
> 13    MakeRecord    7    1    8    C    00
> 14    IdxInsert    7    8    0        00
> 15    Close    8    0    0        00
> 16    Rewind    7    28    0        00
> 17    Column    7    0    5        00
> 18    IsNull    5    27    0        00
> 19    SeekLE    5    27    5    1    00
> 20    IdxLT    5    27    5    1    00
> 21    Column    5    1    8        00
> 22    CollSeq    9    0    0    (BINARY)    00
> 23    AggStep0    0    8    3    max(1)    01
> 24    If    9    26    0        00
> 25    Column    5    0    2        00
> 26    Prev    5    20    0        00
> 27    NextIfOpen    7    17    0        00
> 28    Close    5    0    0        00
> 29    AggFinal    3    1    0    max(1)    00
> 30    SCopy    2    10    0        00
> 31    SCopy    3    11    0        00
> 32    MakeRecord    10    2    8        00
> 33    NewRowid    2    12    0        00
> 34    Insert    2    8    12        08
> 35    Return    1    0    0        00
> 36    OpenRead    1    9    0    8    00
> 37    OpenRead    0    7    0    5    00
> 38    Rewind    2    60    0        00
> 39    Column    2    1    14        00
> 40    Function0    0    14    13    datetime(-1)    01
> 41    Le    15    59    13        51
> 42    Rewind    1    59    0        00
> 43    Column    1    7    13        00
> 44    Ne    16    58    13    (BINARY)    53
> 45    Column    1    3    14        00
> 46    Column    2    1    17        00
> 47    Lt    17    58    14    (BINARY)    53
> 48    Column    1    1    18        00
> 49    SeekRowid    0    58    18        00
> 50    Column    0    4    19        00
> 51    Column    2    0    20        00
> 52    Eq    20    54    19    (BINARY)    43
> 53    Ne    20    58    18    (BINARY)    53
> 54    Copy    18    22    0        00
> 55    Column    0    1    23        00
> 56    Column    1    6    24        00
> 57    ResultRow    22    3    0        00
> 58    Next    1    43    0        01
> 59    Next    2    39    0        01
> 60    Close    1    0    0        00
> 61    Close    0    0    0        00
> 62    Halt    0    0    0        00
> 63    Transaction    0    0    496    0    01
> 64    TableLock    0    9    0    lookups    00
> 65    TableLock    0    7    0    urls    00
> 66    String8    0    25    0    now    00
> 67    String8    0    26    0    -14 days    00
> 68    String8    0    27    0    start of day    00
> 69    Function0    7    25    15    datetime(-1)    03
> 70    Integer    1    16    0        00
> 71    Goto    0    1    0        00
>
>
> -------
> ORDER BY and LIMIT Explain:
> 0    Init    0    77    0        00
> 1    Integer    35    1    0        00
> 2    Once    0    35    0        00
> 3    OpenEphemeral    2    2    0        00
> 4    Null    0    2    4        00
> 5    OpenRead    5    50315    0    k(4,-,,,)    02
> 6    Once    0    16    0        00
> 7    OpenEphemeral    7    1    0    k(1,B)    00
> 8    OpenRead    8    8    0    k(2,,)    02
> 9    String8    0    6    0    example.com    00
> 10    SeekGE    8    15    6    1    00
> 11    IdxGT    8    15    6    1    00
> 12    IdxRowid    8    7    0        00
> 13    MakeRecord    7    1    8    C    00
> 14    IdxInsert    7    8    0        00
> 15    Close    8    0    0        00
> 16    Rewind    7    28    0        00
> 17    Column    7    0    5        00
> 18    IsNull    5    27    0        00
> 19    SeekLE    5    27    5    1    00
> 20    IdxLT    5    27    5    1    00
> 21    Column    5    1    8        00
> 22    CollSeq    9    0    0    (BINARY)    00
> 23    AggStep0    0    8    3    max(1)    01
> 24    If    9    26    0        00
> 25    Column    5    0    2        00
> 26    Prev    5    20    0        00
> 27    NextIfOpen    7    17    0        00
> 28    Close    5    0    0        00
> 29    AggFinal    3    1    0    max(1)    00
> 30    SCopy    2    10    0        00
> 31    SCopy    3    11    0        00
> 32    MakeRecord    10    2    8        00
> 33    NewRowid    2    12    0        00
> 34    Insert    2    8    12        08
> 35    Return    1    0    0        00
> 36    OpenEphemeral    9    5    0    k(1,B)    00
> 37    Integer    1    13    0        00
> 38    OpenRead    1    9    0    8    00
> 39    OpenRead    0    7    0    5    00
> 40    Rewind    2    68    0        00
> 41    Column    2    1    15        00
> 42    Function0    0    15    14    datetime(-1)    01
> 43    Le    16    67    14        51
> 44    Rewind    1    67    0        00
> 45    Column    1    7    14        00
> 46    Ne    17    66    14    (BINARY)    53
> 47    Column    1    3    15        00
> 48    Column    2    1    18        00
> 49    Lt    18    66    15    (BINARY)    53
> 50    Column    1    1    19        00
> 51    SeekRowid    0    66    19        00
> 52    Column    0    4    20        00
> 53    Column    2    0    21        00
> 54    Eq    21    56    20    (BINARY)    43
> 55    Ne    21    66    19    (BINARY)    53
> 56    Copy    19    25    0        00
> 57    Column    0    1    26        00
> 58    Column    1    6    27        00
> 59    Copy    25    23    0        00
> 60    Sequence    9    24    0        00
> 61    MakeRecord    23    5    28        00
> 62    IdxInsert    9    28    0        00
> 63    IfNotZero    13    66    1        00
> 64    Last    9    0    0        00
> 65    Delete    9    0    0        00
> 66    Next    1    45    0        01
> 67    Next    2    41    0        01
> 68    Close    1    0    0        00
> 69    Close    0    0    0        00
> 70    Sort    9    76    0        00
> 71    Column    9    2    25        00
> 72    Column    9    3    26        00
> 73    Column    9    4    27        00
> 74    ResultRow    25    3    0        00
> 75    Next    9    71    0        00
> 76    Halt    0    0    0        00
> 77    Transaction    0    0    496    0    01
> 78    TableLock    0    9    0    lookups    00
> 79    TableLock    0    7    0    urls    00
> 80    String8    0    29    0    now    00
> 81    String8    0    30    0    -14 days    00
> 82    String8    0    31    0    start of day    00
> 83    Function0    7    29    16    datetime(-1)    03
> 84    Integer    1    17    0        00
> 85    Goto    0    1    0        00
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Jonathan Moules-3
In reply to this post by Simon Slavin-3
Hi Simon,
Sure; I didn't include them because the only difference is the last
line, and that just seems to be the standard "ordering" line. I figured
the explain was more useful as a lot has changed in that.

Cheers,
Jonathan

Fast version:
1    0    0    SEARCH TABLE lookups USING COVERING INDEX sgdsfweer
(url_id=?)
1    0    0    EXECUTE LIST SUBQUERY 2
2    0    0    SEARCH TABLE urls USING COVERING INDEX
sqlite_autoindex_urls_1 (url=?)
0    0    2    SCAN SUBQUERY 1 AS recent
0    1    1    SCAN TABLE lookups AS l
0    2    0    SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)


Slow version (Order by and LIMIT):
1    0    0    SEARCH TABLE lookups USING COVERING INDEX sgdsfweer
(url_id=?)
1    0    0    EXECUTE LIST SUBQUERY 2
2    0    0    SEARCH TABLE urls USING COVERING INDEX
sqlite_autoindex_urls_1 (url=?)
0    0    2    SCAN SUBQUERY 1 AS recent
0    1    1    SCAN TABLE lookups AS l
0    2    0    SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)
0    0    0    USE TEMP B-TREE FOR ORDER BY

On 2018-03-22 22:01, Simon Slavin wrote:

> On 22 Mar 2018, at 9:24pm, Jonathan Moules <[hidden email]> wrote:
>
>> But when I stick an "ORDER BY" on the end (either ASC or DESC), the processing time shoots up to 0.15s. The EXPLAIN between the two is considerably different so it seems the ORDER BY is getting it to use a sub-optimal query plan.
> Hi, Jonathan.
>
> Could you post details similar to the ones in that post, but instead of using EXPLAIN use EXPLAIN QUERY PLAN ?  This will give results on a scale which is easier to understand in the context of your queries and indexes.
>
> <https://www.sqlite.org/eqp.html>
>
> Simon.
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Simon Slavin-3
On 22 Mar 2018, at 10:09pm, Jonathan Moules <[hidden email]> wrote:

> Sure; I didn't include them because the only difference is the last line, and that just seems to be the standard "ordering" line. I figured the explain was more useful as a lot has changed in that.

I find EXPLAIN QUERY PLANs easier to read (probably unlike the development team who understand things at the level of EXPLAIN).

The situation as you describe it in the above post is that your query gets /slower/ when you add an ORDER BY clause.  This is not expected, and does not suggest anything wrong with SQLite.

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

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Mark Wagner
When I saw this post I just assumed there wasn't a sufficient index to
handle the select and the order by.

Curious about the suggestion of adding + to the order by first term.


On Thu, Mar 22, 2018 at 3:14 PM Simon Slavin <[hidden email]> wrote:

> On 22 Mar 2018, at 10:09pm, Jonathan Moules <[hidden email]>
> wrote:
>
> > Sure; I didn't include them because the only difference is the last
> line, and that just seems to be the standard "ordering" line. I figured the
> explain was more useful as a lot has changed in that.
>
> I find EXPLAIN QUERY PLANs easier to read (probably unlike the development
> team who understand things at the level of EXPLAIN).
>
> The situation as you describe it in the above post is that your query gets
> /slower/ when you add an ORDER BY clause.  This is not expected, and does
> not suggest anything wrong with SQLite.
>
> Simon.
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Simon Slavin-3
On 22 Mar 2018, at 10:16pm, Mark Wagner <[hidden email]> wrote:

> Curious about the suggestion of adding + to the order by first term.

This stops SQLite from realising it can use an existing index.  If you do

    CREATE INDEX m_s ON members (score)
    SELECT * FROM members ORDER BY score DESC

SQLite cleverly spots that the index is useful.  However

    SELECT * FROM members ORDER BY +score DESC

has SQLite looking for "+score" but find "score", and they are clearly different, so SQLite will not use index m_s.

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

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Jonathan Moules-3
In reply to this post by Richard Hipp-3
On 2018-03-22 22:08, Richard Hipp wrote:
> Quick workaround: put a "+" on front of the first term of your ORDER BY
> clause.

This gives me an ending of:
ORDER BY

+u.url_id ASC

LIMIT 1;


Alas it makes no difference to the speed. The sole difference in the
EXPLAIN plan when that's added from the ORDER BY/LIMIT one posted in the
question is this line:

59    Copy    25    23    0        00

Where the number 25 becomes the number 19.
(Note: The EXPLAINs are from 3.15)

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

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Jonathan Moules-3
In reply to this post by Simon Slavin-3
Hi Simon,
Yep, I too find the Query Plan's easier to read (sometimes I even think
I understand bits of them!)

I do expect SQLite to get slower with an Order By - it has more work to
do after all, but I thought I'd ask for this one because it's slowing
down by almost two orders of magnitude (a bit less in 3.23.0) when I add
the ORDER which seems quite a lot for ordering 86 items. I have no idea
what the EXPLAIN /says/, but I can see it's very different between the
two of them so this suggested the QP might be going astray.

Even if I delete all the indexes it's using, it still evidences, though
the timings are higher (0.1s without, 0.2s with ORDER BY/LIMIT).
Cheers,
Jonathan

On 2018-03-22 22:13, Simon Slavin wrote:

> On 22 Mar 2018, at 10:09pm, Jonathan Moules <[hidden email]> wrote:
>
>> Sure; I didn't include them because the only difference is the last line, and that just seems to be the standard "ordering" line. I figured the explain was more useful as a lot has changed in that.
> I find EXPLAIN QUERY PLANs easier to read (probably unlike the development team who understand things at the level of EXPLAIN).
>
> The situation as you describe it in the above post is that your query gets /slower/ when you add an ORDER BY clause.  This is not expected, and does not suggest anything wrong with SQLite.
>
> Simon.
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: How to get ORDER BY / LIMIT to stick to the fast-path?

Rowan Worth-2
In reply to this post by Jonathan Moules-3
On 23 March 2018 at 05:24, Jonathan Moules <[hidden email]>
wrote:

> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
> (preview)) despite looking through hundreds of thousands of records in each
> table, and it returns 86 records in all. This is great!
>
> But when I stick an "ORDER BY" on the end (either ASC or DESC), the
> processing time shoots up to 0.15s. The EXPLAIN between the two is
> considerably different so it seems the ORDER BY is getting it to use a
> sub-optimal query plan.
> If I put a LIMIT 1 on the end, the speed remains at 0.15s, but the query
> plan is different again. The fast EXPLAIN and the ORDER BY/LIMIT EXPLAIN
> are also here (they start changing at item 36).
>
> Any suggestions for what's going on here and how to coerce the planner to
> stick to the fast-path and then do a simple order by on those 86 (or
> however many - it'll always be a low number) results?
> (ANALYZE has been run)
>

Does it help if you move the ORDER BY to an outer select? ie:

SELECT id, u, err FROM (
    SELECT u.url_id, u.url, l.error_code ...
) ORDER BY id;

If the query planner flattens the subquery this probably won't make a
difference though...
-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users