Re: Question about query optimization

On 11/15/06, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
>
> On 11/15/06, Matthias(dot)Pitzl(at)izb(dot)de <Matthias(dot)Pitzl(at)izb(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com