Re: Prepared Statement Query Planning

2009/8/29 Brett Henderson <brett(at)bretth(dot)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 <http://e.id/>
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

Best regards, Vitalii Tymchyshyn