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

Re: Prepared Statement Query Planning

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: Brett Henderson <brett(at)bretth(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Prepared Statement Query Planning
Date: 2009-08-29 06:27:56
Message-ID: 331e40660908282327y22c4e6d7wc4e6f69337d08a9f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Brett HendersonDate: 2009-08-29 07:51:00
Subject: Re: Prepared Statement Query Planning
Previous:From: Brett HendersonDate: 2009-08-29 02:31:01
Subject: Prepared Statement Query Planning

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