Hi,
I have a problem with one of my queries which take 2 orders of magnitude
more on Sqlite3 (3.7.9) compared with the identical query on PostgreSQL
(8.4). Times are 2270 ms on Sqlite3 and around 17ms on PG.
The difference seems to that the Sqlite is not optimising a subquery by
using an index in the way that PG does.
The query:
SELECT * FROM new_current_flock_2 WHERE regn_no = '030235'
(any regn no in the sheep table can be used)
new_current_flock_2 is a view:
CREATE VIEW new_current_flock_2 AS
SELECT latest.regn_no, latest.flock_no, latest.transfer_date,
latest.text_xfer_date, latest.transfer_reason
FROM new_sheep_xfer_history_2 latest WHERE NOT EXISTS
( SELECT 'x' FROM new_sheep_xfer_history_2 even_later
WHERE latest.regn_no = even_later.regn_no AND
latest.transfer_date < even_later.transfer_date)
new_sheep_xfer_history_2 is another view:
CREATE VIEW new_sheep_xfer_history_2 AS
SELECT sf.regn_no, sf.flock_no, f.flock_name, sf.transfer_date,
sf.text_xfer_date, sf.transfer_reason
FROM sheep_flock sf JOIN flock f ON sf.flock_no = f.flock_no
UNION ALL
SELECT s.regn_no, s.registering_flock, f.flock_name,
s.registration_date || '02:00:00',
s.registration_date, CAST('Registration' AS VARCHAR(16)) as transfer_reason
FROM sheep s JOIN flock f ON s.registering_flock = f.flock_no
UNION ALL
SELECT s.regn_no, s.originating_flock, f.flock_name,
s.sort_dob || '00:00:00',
s.text_dob, CAST('Birth' AS VARCHAR(16)) as transfer_reason
FROM sheep s JOIN flock f ON s.originating_flock = f.flock_no
As far as I can see Sqlite3 is only applying the where clause after
computing the whole of new_current_flock_2, including
new_sheep_xfer_history_2 (twice) whereas PG is applying the 'where'
clause to each of the three subqueries in new_sheep_xfer_history_2 as it
computes that table. But I might be wrong ;)
The sqlite database is at
http://www.somborneshetlands.co.uk/things/main.tar.gz
I _could_ restructure my database to avoid the second view, but I'd much
rather not as it's a pretty major change.
Does anyone have any ideas for optimisations on the current schema?
TIA
--
Peter Hardman
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
