Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 21:39:

My fault...I thought I had extracted it under another name...turns out I was 
using a different db...duh...



Looks like it uses the indexes just fine.  I'm using the Window's EXE from the 
website.  Also got the same result on Unix.

Did you compile your own?  Or did you check to see that the indexes still 
weren't being used on your subset?

I compiled the amalgamation:

cc -o sqlite3 -O2 shell.c sqlite3.c -llpthread -ldl



Sorry, forgot to answer that.

The Arch build source is http://www.sqlite.org/sqlite-autoconf-3071100.tar.gz

The build runs ./configure --prefix=/usr --disable-static

Then make with CFLAGS='CFLAGS -DSQLITE_ENABLE_FTS3=1 
-DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_UNLOCK_NOTIFY 
-DSQLITE_SECURE_DELETE'


Pete


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 21:39:

My fault...I thought I had extracted it under another name...turns out I was 
using a different db...duh...




Well we're none of us perfect. Only the female of the species can do more than 
two things at once...



Yes, that query is fine.

But add an 'order by transfer_date' clause and the planner no longer uses 
indexes but uses scans instead.


Then if you do a select * ... it goes back to using indexes.

With 100K+ records in the full database the difference in execution times is not 
insignificant.


Pete
--

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

[sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

My fault...I thought I had extracted it under another name...turns out I was 
using a different db...duh...



Looks like it uses the indexes just fine.  I'm using the Window's EXE from the 
website.  Also got the same result on Unix.

Did you compile your own?  Or did you check to see that the indexes still 
weren't being used on your subset?

I compiled the amalgamation:

cc -o sqlite3 -O2 shell.c sqlite3.c -llpthread -ldl

Running on Redhat 5.



sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and 
transfer_date<= '2012-05-01' order by transfer_date asc;
1995-04-04 00:00:00
1999-04-01 12:00:00
2002-03-31 12:00:00

sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE 
regn_no = '023674' and transfer_date<= '2012-05-01' order by transfer_date asc;
3|0|1|SCAN TABLE flock AS f (~161 rows)
3|1|0|SEARCH TABLE transfer AS tr USING INDEX tr_flock_no_index (flock_no=?) 
(~5 rows)
4|0|1|SCAN TABLE flock AS f (~161 rows)
4|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_org_flock_index 
(originating_flock=?) (~5 rows)
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|1|SCAN TABLE flock AS f (~161 rows)
5|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_reg_flock_index 
(registering_flock=?) (~2 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN SUBQUERY 1 (~67 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 3:14 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 19:00:
> Sqliteman must be pointing to the wrong database.
>
>
>
> sqlite>  SELECT transfer_date FROM transfer_history WHERE regn_no =
> '039540'. Error: no such table: transfer_history
>

Hmm. I've just done the following - cut & paste from my terminal:

[home@system06 test]$ mkdir sqlite
[home@system06 test]$ cd sqlite
[home@system06 sqlite]$ wget
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip
--2012-04-26 21:09:04--
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip
Resolving www.somborneshetlands.co.uk<http://www.somborneshetlands.co.uk/>... 
91.197.33.236
Connecting to 
www.somborneshetlands.co.uk|91.197.33.236|:80<http://www.somborneshetlands.co.uk%7c91.197.33.236%7c/>...
 connected.
HTTP request sent, awaiting response... 200 OK
Length: 184279 (180K) [application/zip]
Saving to: `sss-test-nomem.zip'

100%[==>]
184,279  792K/s   in 0.2s

2012-04-26 21:09:04 (792 KB/s) - `sss-test-nomem.zip' saved [184279/184279]

[home@system06 sqlite]$ unzip sss-test-nomem.zip
Archive:  sss-test-nomem.zip
   inflating: sss-test-nomem.sqlite
[home@system06 sqlite]$ sqlite3 sss-test-nomem.sqlite
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from transfer_history where regn_no = '023674'
...> ;
023674|1610|Wycoller|1999-04-01 12:00:00|April 99|Presumed
023674|SSB900|(Dead)|2002-03-31 12:00:00|31/03/2002|Presumed
023674|1004|Glynwood|1995-04-04 00:00:00|4/4/95|Birth
sqlite>


Seems to work for me. You'll have to use 023674 instead of 039540 as the
latter doesn't exist in this test database.

Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 19:00:

Sqliteman must be pointing to the wrong database.



sqlite>  SELECT transfer_date FROM transfer_history WHERE regn_no =
'039540'. Error: no such table: transfer_history



Hmm. I've just done the following - cut & paste from my terminal:

[home@system06 test]$ mkdir sqlite
[home@system06 test]$ cd sqlite
[home@system06 sqlite]$ wget 
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip
--2012-04-26 21:09:04-- 
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip

Resolving www.somborneshetlands.co.uk... 91.197.33.236
Connecting to www.somborneshetlands.co.uk|91.197.33.236|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 184279 (180K) [application/zip]
Saving to: `sss-test-nomem.zip'

100%[==>] 
184,279  792K/s   in 0.2s


2012-04-26 21:09:04 (792 KB/s) - `sss-test-nomem.zip' saved [184279/184279]

[home@system06 sqlite]$ unzip sss-test-nomem.zip
Archive:  sss-test-nomem.zip
  inflating: sss-test-nomem.sqlite
[home@system06 sqlite]$ sqlite3 sss-test-nomem.sqlite
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from transfer_history where regn_no = '023674'
   ...> ;
023674|1610|Wycoller|1999-04-01 12:00:00|April 99|Presumed
023674|SSB900|(Dead)|2002-03-31 12:00:00|31/03/2002|Presumed
023674|1004|Glynwood|1995-04-04 00:00:00|4/4/95|Birth
sqlite>


Seems to work for me. You'll have to use 023674 instead of 039540 as the 
latter doesn't exist in this test database.


Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

Sqliteman must be pointing to the wrong database.



sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '039540'.
Error: no such table: transfer_history





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 12:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 18:21:
> There is no transfer_table_new in that database.  Or any view named 
> "transfer" anything.
>
> So what query are you running on this one?
>
>
Sorry, that should be transfer_history. The name seems to have got
corrupted during our email exchanges.

I've just downloaded and extracted the database and it has all 44 tables
and 18 views - well Sqliteman thinks it has anyway..

Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 18:21:

There is no transfer_table_new in that database.  Or any view named "transfer" 
anything.

So what query are you running on this one?


Sorry, that should be transfer_history. The name seems to have got 
corrupted during our email exchanges.


I've just downloaded and extracted the database and it has all 44 tables 
and 18 views - well Sqliteman thinks it has anyway..


Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

There is no transfer_table_new in that database.  Or any view named "transfer" 
anything.

So what query are you running on this one?



Here's all the VIEWs.



CREATE VIEW current_flock_owner AS SELECT latest.flock_no, 
latest.owner_person_id, latest.ow
 FROM flock_owner even_later WHERE  latest.flock_no = even_later.flock_no AND 
latest.owner_c
CREATE VIEW original_flock_owner AS SELECT first.flock_no, 
first.owner_person_id, first.owne
OM flock_owner even_earlier WHERE  first.flock_no = even_earlier.flock_no AND 
first.owner_ch
CREATE VIEW three_gen_ped AS SELECT s.regn_no, s.sire_no, s.dam_no, g1.sire_no 
AS gs1, g1.da
1, gg1.dam_no AS ggd1, gg2.sire_no AS ggs2, gg2.dam_no AS ggd2, gg3.sire_no AS 
ggs3, gg3.dam
IN sheep g1 ON s.sire_no = g1.regn_no JOIN sheep g2 ON s.dam_no = g2.regn_no 
JOIN sheep gg1
o JOIN sheep gg3 ON g2.sire_no = gg3.regn_no JOIN sheep gg4 ON g2.dam_no = 
gg4.regn_no;
CREATE VIEW two_gen_ped AS SELECT s.regn_no, s.sire_no, s.dam_no, g1.sire_no AS 
gs1, g1.dam_
ep g1 ON s.sire_no = g1.regn_no JOIN sheep g2 ON s.dam_no = g2.regn_no;
CREATE VIEW current_ear_tag as
CREATE VIEW original_ear_tag as
CREATE VIEW current_eid as
CREATE VIEW current_inspection as
CREATE VIEW current_prp as
CREATE VIEW sheep_data as
CREATE VIEW sheep_basic_data as
CREATE VIEW sheep_progeny_data as





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 11:54 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 17:05:
> What version are you using?  Can you extract an example of all your
> tables/indexes/data to demonstrate?

I'm on 3.7.11 (Arch-Linux).

You can grab a database at
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip

You will need to use a regn_no of 023674 with that as its a cut down
data set for testing (the live one is 35MB). The date can remain the same.


>
> One more idea.  Explicitly ask for the indexes...and a compound index
> might be nice to have around.
>
> I dont' think you can ask for more than one index without doing
> subselects, can you?

AFAIK you can't create indexes on a view, or can you?


Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 17:05:

What version are you using?  Can you extract an example of all your
tables/indexes/data to demonstrate?


I'm on 3.7.11 (Arch-Linux).

You can grab a database at 
http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip


You will need to use a regn_no of 023674 with that as its a cut down 
data set for testing (the live one is 35MB). The date can remain the same.





One more idea.  Explicitly ask for the indexes...and a compound index
might be nice to have around.

I dont' think you can ask for more than one index without doing
subselects, can you?


AFAIK you can't create indexes on a view, or can you?


Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

What version are you using?  Can you extract an example of all your 
tables/indexes/data to demonstrate?



One more idea.  Explicitly ask for the indexes...and a compound index might be 
nice to have around.

I dont' think you can ask for more than one index without doing subselects, can 
you?



SELECT transfer_date FROM transfer_history_new INDEXED BY myindex
WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
order by transfer_date asc





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 16:38:
> And does this also work?  Sounds like the planner isn't seeing all the 
> columns in the view unless in the select.
>
>
>
> SELECT transfer_date,regn_no FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
> order by transfer_date asc
>

No, that uses scans as well.

I'm tempted to suggest this might be a bug since the pattern seems
illogical.

Pete


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Simon Slavin wrote, On 26/04/12 16:39:

On 26 Apr 2012, at 4:24pm, Peter  wrote:


>  There are indexes on all the fields used in the tables of the 
transfer_history view.
>
>  While tinkering I have discovered something:
>
>  If instead of
>  SELECT transfer_date FROM transfer_history_new
>  WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
>  order by transfer_date asc
>
>  I write
>
>  SELECT * FROM transfer_history_new
>  WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
>  order by transfer_date asc
>
>  then I get an execution time of a couple of milliseconds instead of 300ms or 
so (times from Sqliteman this time). The planner has reverted to using indexes 
instead of scans...

Comparing the output of EXPLAIN QUERY PLAN for those two, can you see output 
that supports that ?  If so, it does seem to be a bug of some kind.

Simon.


See my original post. Explain does indeed show that the difference is 
the use of indexes vs scans.


Pete
--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 16:38:

And does this also work?  Sounds like the planner isn't seeing all the columns 
in the view unless in the select.



SELECT transfer_date,regn_no FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date<= '2012-05-01'
order by transfer_date asc



No, that uses scans as well.

I'm tempted to suggest this might be a bug since the pattern seems 
illogical.


Pete


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Marc L. Allen wrote, On 26/04/12 16:38:

Out of curiosity, try...

SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no
= '039540' and transfer_date<= '2012-05-01' order by transfer_date
asc

Is the problem that combining the order by with having transfer_date
as the only returned item make it use the transfer_date index instead
of the preferable regn_no index?


No, that makes no difference either.

Pete

--

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

Re: [sqlite] Re Query planner creating a slow plan

[Simon Slavin]

On 26 Apr 2012, at 4:24pm, Peter  wrote:

> There are indexes on all the fields used in the tables of the 
> transfer_history view.
> 
> While tinkering I have discovered something:
> 
> If instead of
> SELECT transfer_date FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> I write
> 
> SELECT * FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> then I get an execution time of a couple of milliseconds instead of 300ms or 
> so (times from Sqliteman this time). The planner has reverted to using 
> indexes instead of scans...

Comparing the output of EXPLAIN QUERY PLAN for those two, can you see output 
that supports that ?  If so, it does seem to be a bug of some kind.

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

Re: [sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

And does this also work?  Sounds like the planner isn't seeing all the columns 
in the view unless in the select.



SELECT transfer_date,regn_no FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
order by transfer_date asc



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re Query planner creating a slow plan

[Marc L. Allen]

Out of curiosity, try...

SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no = 
'039540' and transfer_date <= '2012-05-01'
order by transfer_date asc

Is the problem that combining the order by with having transfer_date as the 
only returned item make it use the transfer_date index instead of the 
preferable regn_no index?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Peter
> Sent: Thursday, April 26, 2012 11:24 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Re Query planner creating a slow plan
> 
> Marc L. Allen wrote, On 26/04/12 15:57:
> > What indexes are on the underlying tables?
> >
> 
> There are indexes on all the fields used in the tables of the
> transfer_history view.
> 
> While tinkering I have discovered something:
> 
> If instead of
> SELECT transfer_date FROM transfer_history_new WHERE regn_no = '039540'
> and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> I write
> 
> SELECT * FROM transfer_history_new
> WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> order by transfer_date asc
> 
> then I get an execution time of a couple of milliseconds instead of
> 300ms or so (times from Sqliteman this time). The planner has reverted
> to using indexes instead of scans...
> 
> --
> 
> Peter Hardman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Marc L. Allen wrote, On 26/04/12 15:57:

What indexes are on the underlying tables?



There are indexes on all the fields used in the tables of the 
transfer_history view.


While tinkering I have discovered something:

If instead of
SELECT transfer_date FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
order by transfer_date asc

I write

SELECT * FROM transfer_history_new
WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
order by transfer_date asc

then I get an execution time of a couple of milliseconds instead of 
300ms or so (times from Sqliteman this time). The planner has reverted 
to using indexes instead of scans...


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Marc L. Allen]

What indexes are on the underlying tables?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Peter
> Sent: Thursday, April 26, 2012 10:55 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Re Query planner creating a slow plan
> 
> Jim Morris wrote, On 26/04/12 15:36:
> > It is possible using an alias would force better behavior:
> >
> > selsect theDate
> > from (select transfer_date as theDate from transfer_history where
> > regn_no='039540' and transfer_date<= '2012-05-01') order by theDate
> >
> 
> Once again, it makes no difference - the planner still picks the same
> plan using scans.
> 
> 
> --
> 
> Peter Hardman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Jim Morris wrote, On 26/04/12 15:36:

It is possible using an alias would force better behavior:

selsect theDate
from (select transfer_date as theDate from transfer_history
where regn_no='039540' and transfer_date<= '2012-05-01') order by
theDate



Once again, it makes no difference - the planner still picks the same 
plan using scans.



--

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

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Marc L. Allen wrote, On 26/04/12 15:30:

I suppose using a temporary table is out of the question?


It's much simpler (and probably quicker though I didn't test it) to read 
the three rows, sort them by date, pick the first row and use that. No 
need to generate random table names, create and drop the table.


Python time.time() gives 0.001sec for all that (to 3 dec places) which 
is fast enough for me ;) (and faster than PostgreSQL at 0.023 using the 
plain query).


 But, then

again, that only solves the specific issue.  I guess the more general
question is how views with unions interact with aggregates and order
by.

What happens if you don't use the view, but perform the query using
the actual tables?


Makes no difference - the planner is still using the scans instead of 
indexes.


--

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

Re: [sqlite] Re Query planner creating a slow plan

[Jim Morris]

It is possible using an alias would force better behavior:

selsect theDate
 from (select transfer_date as theDate from transfer_history
where regn_no='039540' and transfer_date<= '2012-05-01') order by
theDate

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

Re: [sqlite] Re Query planner creating a slow plan

[Marc L. Allen]

I suppose using a temporary table is out of the question?  But, then again, 
that only solves the specific issue.  I guess the more general question is how 
views with unions interact with aggregates and order by.

What happens if you don't use the view, but perform the query using the actual 
tables?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Thursday, April 26, 2012 10:11 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Re Query planner creating a slow plan
> 
> What happens if you use a subselect?
> 
> 
> 
> selsect transfer_date from (select transfer_date from transfer_history
> where regn_no='039540' and transfer_date <= '2012-05-01') order by
> transfer_date;
> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> Advanced Analytics Directorate
> 
> Advanced GEOINT Solutions Operating Unit
> 
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Peter [pe...@somborneshetlands.co.uk]
> Sent: Thursday, April 26, 2012 7:00 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Query planner creating a slow plan
> 
> Hi,
> 
> I have a view 'transfer_history' which aggregates records from 3 tables
> using UNION ALL. the aggregate is about 102k records.
> 
> I have a query:
> 
> SELECT transfer_date from transfer_history
>   WHERE regn_no = '039540' and transfer_date <= '2012-05-01'
> 
> This returns three records and takes a couple of milliseconds - good.
> 
> But if I add an 'order by' clause, or an aggregate (max) on
> transfer_date, the time goes up to > 300ms. The reason seems to be that
> the query planner uses scans for all three sub-queries instead of using
> indexes on the underlying tables.
> 
> With the basic query yhe QP says;
> 
> SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
> (regn_no=? AND transfer_date INDEX sqlite_autoindex_sheep_1 (regn_no=?)
> (~1 rows)
> COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
> SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1
> (regn_no=?)
> (~1 rows)
> COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
> 
> With the 'order by' clause the QP says:
> 
> SCAN TABLE transfer AS tr (~49043 rows)
> SCAN TABLE sheep AS s (~51858 rows)
> COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
> SCAN TABLE sheep AS s (~25929 rows)
> COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
> SCAN SUBQUERY 1 AS t2 (~4227 rows)
> USE TEMP B-TREE FOR ORDER BY
> 
> Seems to me it ought to be able to just sort the result of the first
> plan. ATM it's an order of magnitude quicker at least to do the sort in
> Python in the application.
> 
> Pete
> 
> --
> 
> Peter Hardman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re Query planner creating a slow plan

[Peter]

Black, Michael (IS) wrote, On 26/04/12 15:11:

select transfer_date from (select transfer_date from
transfer_history where regn_no='039540' and transfer_date<=
'2012-05-01') order by transfer_date;



Makes no difference.

Pete

--

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

[sqlite] Re Query planner creating a slow plan

[Black, Michael (IS)]

What happens if you use a subselect?



selsect transfer_date from (select transfer_date from transfer_history where 
regn_no='039540' and transfer_date <= '2012-05-01') order by transfer_date;





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter [pe...@somborneshetlands.co.uk]
Sent: Thursday, April 26, 2012 7:00 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Query planner creating a slow plan

Hi,

I have a view 'transfer_history' which aggregates records from 3 tables
using UNION ALL. the aggregate is about 102k records.

I have a query:

SELECT transfer_date from transfer_history
  WHERE regn_no = '039540' and transfer_date <= '2012-05-01'

This returns three records and takes a couple of milliseconds - good.

But if I add an 'order by' clause, or an aggregate (max) on
transfer_date, the time goes up to > 300ms. The reason seems to be that
the query planner uses scans for all three sub-queries instead of using
indexes on the underlying tables.

With the basic query yhe QP says;

SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1
(regn_no=? AND transfer_datehttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users