2009/8/29 Brett Henderson
<brett@bretth.com>
This is the query:
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 (
SELECT id, version FROM
nodes WHERE timestamp > ? AND timestamp <= ?
) 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 node table contains approx 500 million rows, the user table approx 100 thousand rows, and the changeset table somewhere in between but closer to the lower end. The server has 32 GB of RAM so can fit smaller tables in RAM, but not the node table.
The query retrieves all rows within a timestamp range. I realise the query could be re-written without the sub-select, but it is implemented in this way as a result of the way the query is dynamically constructed and allows the sub-select portion can be switched out for other row selection criteria.
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.idStill 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.idBest regards, Vitalii Tymchyshyn
