On Wed, Jan 5, 2011 at 5:32 PM, Simon Slavin <[email protected]> wrote:
>
> On 5 Jan 2011, at 5:42pm, Peter wrote:
>
> > Both the last two 'tables' are views of the form I gave above. I'm using
> > the same indexes for both SQLite and PostgreSQL.
>
> PostgreSQL will make up its own indexes if it thinks they will speed up a
> search. Actually, so will SQLite. The problem is that SQLite throws its
> index away after the SELECT is finished, whereas PostgreSQL does not.
> Consequently if you don't have good indexes for a SELECT PostgreSQL will
> make up the index once and cache it for future SELECTs, whereas SQLite will
> make up the index again every time you execute another SELECT.
>
> So take a good look at both your SELECTS and figure out whether you have
> indexes ideal for every part of the SELECT: the main part and every JOIN and
> sub-SELECT.
>
In this particular case the query is of the form:
SELECT ... FROM table LEFT JOIN view LEFT JOIN view;
Each of the views is a self-join. What SQLite is doing is first
materializing both views, then constructing transient automatic indices for
each materialized view, then running the query, then discarding both
materialized views and their indices. It takes a little time to create the
indices. But, that is still much faster than trying to do the 3-way join
without indices, which is what SQLite did prior to version 3.7.0. So SQLite
3.7.4 might not be as fast as PostgreSQL on this query, but it is way faster
than SQLite 3.6.23.
I don't know what PostgreSQL is doing with this query. But I suspect that
it is caching the materialized views and their indices and reusing them.
Perhaps a reasonable workaround would be to add a boolean "is_latest" column
to each of the tables and keep that column up-to-date using triggers:
CREATE TRIGGER xyz_update AFTER UPDATE ON xyz
BEGIN
UPDATE xyz
SET is_latest = NOT EXISTS
(SELECT 1 FROM xyz AS later
WHERE later.id=id AND later.time>time)
WHERE xyz.id=NEW.id;
END;
CREATE TRIGGER xyz_insert AFTER INSERT ON xyz
BEGIN
UPDATE xyz
SET is_latest = NOT EXISTS
(SELECT 1 FROM xyz AS later
WHERE later.id=id AND later.time>time)
WHERE xyz.id=NEW.id;
END;
CREATE TRIGGER xyz_delete AFTER DELETE ON xyz
BEGIN
UPDATE xyz
SET is_latest = NOT EXISTS
(SELECT 1 FROM xyz AS later
WHERE later.id=id AND later.time>time)
WHERE xyz.id=OLD.id;
END;
Then instead of querying against the view, just query against the table but
add an "ON is_latest" restriction.
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
