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

2018-03-22 Thread Rowan Worth 
On 23 March 2018 at 05:24, Jonathan Moules 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-03-22 Thread Jonathan Moules 

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

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

2018-03-22 Thread Jonathan Moules 

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:


59Copy2523000

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-03-22 Thread Simon Slavin 
On 22 Mar 2018, at 10:16pm, Mark Wagner  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-03-22 Thread 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  wrote:

> On 22 Mar 2018, at 10:09pm, Jonathan Moules 
> 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
> 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

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

2018-03-22 Thread Simon Slavin 
On 22 Mar 2018, at 10:09pm, Jonathan Moules  
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-03-22 Thread Jonathan Moules 

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:
100SEARCH TABLE lookups USING COVERING INDEX sgdsfweer 
(url_id=?)

100EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)

002SCAN SUBQUERY 1 AS recent
011SCAN TABLE lookups AS l
020SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)


Slow version (Order by and LIMIT):
100SEARCH TABLE lookups USING COVERING INDEX sgdsfweer 
(url_id=?)

100EXECUTE LIST SUBQUERY 2
200SEARCH TABLE urls USING COVERING INDEX 
sqlite_autoindex_urls_1 (url=?)

002SCAN SUBQUERY 1 AS recent
011SCAN TABLE lookups AS l
020SEARCH TABLE urls AS u USING INTEGER PRIMARY KEY (rowid=?)
000USE TEMP B-TREE FOR ORDER BY

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

On 22 Mar 2018, at 9:24pm, Jonathan Moules  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.



Simon.
___
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

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

2018-03-22 Thread Richard Hipp 
Quick workaround: put a "+" on front of the first term of your ORDER BY
clause.

On Thursday, March 22, 2018, Jonathan Moules 
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:
>
> 0Init063000
> 1Integer351000
> 2Once035000
> 3OpenEphemeral22000
> 4Null02400
> 5OpenRead5503150k(4,-,,,)02
> 6Once016000
> 7OpenEphemeral710k(1,B)00
> 8OpenRead880k(2,,)02
> 9String8060example.com00
> 10SeekGE8156100
> 11IdxGT8156100
> 12IdxRowid87000
> 13MakeRecord718C00
> 14IdxInsert78000
> 15Close80000
> 16Rewind728000
> 17Column70500
> 18IsNull527000
> 19SeekLE5275100
> 20IdxLT5275100
> 21Column51800
> 22CollSeq900(BINARY)00
> 23AggStep0083max(1)01
> 24If926000
> 25Column50200
> 26Prev520000
> 27NextIfOpen717000
> 28Close50000
> 29AggFinal310max(1)00
> 30SCopy210000
> 31SCopy311000
> 32MakeRecord102800
> 33NewRowid212000
> 34Insert281208
> 35Return10000
> 36OpenRead190800
> 37OpenRead070500
> 38Rewind260000
> 39Column211400
> 40Function001413datetime(-1)01
> 41Le15591351
> 42Rewind159000
> 43Column171300
> 44Ne165813(BINARY)53
> 45Column131400
> 46Column211700
> 47Lt175814(BINARY)53
> 48Column111800
> 49SeekRowid0581800
> 50Column041900
> 51Column202000
> 52Eq205419(BINARY)43
> 53Ne205818(BINARY)53
> 54Copy1822000
> 55Column012300
> 56Column162400
> 57ResultRow223000
> 58Next143001
> 59Next239001
> 60Close10000
> 61Close00000
> 62Halt00000
> 63Transaction00496001
> 64TableLock090lookups00
> 65TableLock070urls00
> 66String80250now00
> 67String80260-14 days00
> 68String80270start of day00
> 69Function072515datetime(-1)03
> 70Integer116000
> 71Goto01000
>
>
> ---
> ORDER BY and LIMIT Explain:
> 0Init07700

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

2018-03-22 Thread Simon Slavin 
On 22 Mar 2018, at 9:24pm, Jonathan Moules  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.



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2018-03-22 Thread Jonathan Moules 

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:

0Init063000
1Integer351000
2Once035000
3OpenEphemeral22000
4Null02400
5OpenRead5503150k(4,-,,,)02
6Once016000
7OpenEphemeral710k(1,B)00
8OpenRead880k(2,,)02
9String8060example.com00
10SeekGE8156100
11IdxGT8156100
12IdxRowid87000
13MakeRecord718C00
14IdxInsert78000
15Close80000
16Rewind728000
17Column70500
18IsNull527000
19SeekLE5275100
20IdxLT5275100
21Column51800
22CollSeq900(BINARY)00
23AggStep0083max(1)01
24If926000
25Column50200
26Prev520000
27NextIfOpen717000
28Close50000
29AggFinal310max(1)00
30SCopy210000
31SCopy311000
32MakeRecord102800
33NewRowid212000
34Insert281208
35Return10000
36OpenRead190800
37OpenRead070500
38Rewind260000
39Column211400
40Function001413datetime(-1)01
41Le15591351
42Rewind159000
43Column171300
44Ne165813(BINARY)53
45Column131400
46Column211700
47Lt175814(BINARY)53
48Column111800
49SeekRowid0581800
50Column041900
51Column202000
52Eq205419(BINARY)43
53Ne205818(BINARY)53
54Copy1822000
55Column012300
56Column162400
57ResultRow223000
58Next143001
59Next239001
60Close10000
61Close00000
62Halt00000
63Transaction00496001
64TableLock090lookups00
65TableLock070urls00
66String80250now00
67String80260-14 days00
68String80270start of day00
69Function072515datetime(-1)03
70Integer116000
71Goto01000


---
ORDER BY and LIMIT Explain:
0Init077000
1Integer351000
2Once035000
3OpenEphemeral22000
4Null02400
5OpenRead5503150k(4,-,,,)02
6Once016000
7OpenEphemeral710k(1,B)00
8OpenRead880k(2,,)02
9String