On 11/15/06,
Gurjeet Singh <
singh.gurjeet@gmail.com> wrote:
On 11/15/06, Matthias.Pitzl@izb.de < Matthias.Pitzl@izb.de> wrote:Is there any other, and more performat way, to get the last history entry
for a given date than this query?
Create an (independent) index on history_timestamp column and use a min/max in the subquery.
More specifically, your query should look like this:
SELECT *
FROM component
JOIN component_history AS c_h
USING(component_id)
WHERE history_timestamp = (SELECT max(history_timestamp)
FROM component_history
WHERE c_h.component_id =
component_history.component_id
)
Here's a session snippet for an example of how drastically that can reduce the cost and the run-time:
Sorry for such a bad example... In case you haven't noticed, ind_t_a was not used anywhere in those plans. My mistake... I was trying some other non-correlated subqueries, and ind_t_a got picked up for those; so I assumed that it'd get picked up for correlated subqueries too! But it didn't.
BTW, here's a query that would use ind_t_a:
explain select * from t where a = (select max(a) from t);
I'll try for a better examples for correlated subqueries.
--
gurjeet[.singh]@ EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com