Skip site navigation (1) Skip section navigation (2)

Re: Prepared Statement Query Planning

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 (view raw or flat)
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.

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2009-08-29 08:55:33
Subject: Re: Prepared Statement Query Planning
Previous:From: Віталій ТимчишинDate: 2009-08-29 06:27:56
Subject: Re: Prepared Statement Query Planning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group