Re: Prepared Statement Query Planning

Віталій Тимчишин wrote:
> It the subselect is only for switching out, it can be rewritten to:
>
> SELECT e.id <http://e.id/>, e.version, e.timestamp, e.visible,
> u.data_public,
> u.id <http://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 <http://c.id/>
> INNER JOIN users u ON c.user_id = u.id <http://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 <http://e.id/>, e.version, e.timestamp, e.visible,
> u.data_public,
> u.id <http://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 <http://c.id/>
> INNER JOIN users u ON c.user_id = u.id <http://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.