Віталій Тимчишин wrote:
It the subselect is only for switching out, it can be rewritten to:
SELECT e.id, e.version, e.timestamp, e.visible, u.data_public,
u.id AS user_id, u.display_name, e.changeset_id, e.latitude, e.longitude
FROM nodes e
INNER JOIN (
values (?,?)
) t(from, to) ON timestamp > from AND timestamp <= to
INNER JOIN changesets c ON e.changeset_id = c.id
INNER JOIN users u ON c.user_id = u.id
Still don't know if this will make things better or worse.
Another (better) option could be not to add a join, but replace original "from nodes e" in this case:
SELECT e.id, e.version, e.timestamp, e.visible, u.data_public,
u.id AS user_id, u.display_name, e.changeset_id, e.latitude, e.longitude
FROM (select * from nodes where timestamp > ? AND timestamp <= ?) e
INNER JOIN changesets c ON e.changeset_id = c.id
INNER JOIN users u ON c.user_id = u.id
Thanks for the tips. I haven't see the first style before, and hadn't considered the second.
However I don't think it will work in my case. I currently have three different ways of selecting records, 1. by timestamp range (as in my initial example), 2. by records in a temp table, and 3. unrestricted. The unrestricted example isn't an issue because a full table scan is appropriate in that case. However the temp table one is a different matter. In that case the query looks like this:
SELECT e.id, e.version, e.timestamp, e.visible, u.data_public,
u.id AS user_id, u.display_name, e.changeset_id, e.latitude, e.longitude
FROM nodes e
INNER JOIN
tmp_nodes
t ON e.id = t.id AND e.version = t.version
INNER JOIN changesets c ON e.changeset_id = c.id
INNER JOIN users u ON c.user_id = u.id
The tmp_nodes table contains only two columns id and version which have been built up by previous queries. Most of the query remains identical, but instead of doing a "SELECT id, version FROM nodes WHERE timestamp > ? AND timestamp <= ?", I join to tmp_nodes which contains only the records I'm interested in. Originally I was creating a temp table in the timestamp range case as well but moved away in an attempt to get better performance, I suspect I was encountering bad query plans in that case as well.