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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brett Henderson | 2009-08-29 07:51:00 | Re: Prepared Statement Query Planning |
Previous Message | Brett Henderson | 2009-08-29 02:31:01 | Prepared Statement Query Planning |