From: | Brett Henderson <brett(at)bretth(dot)com> |
---|---|
To: | Віталій Тимчишин <tivv00(at)gmail(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Prepared Statement Query Planning |
Date: | 2009-08-29 07:51:00 |
Message-ID: | 4A98DDE4.4060509@bretth.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Віталій Тимчишин 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Jowett | 2009-08-29 08:55:33 | Re: Prepared Statement Query Planning |
Previous Message | Віталій Тимчишин | 2009-08-29 06:27:56 | Re: Prepared Statement Query Planning |