Re: optimize query with a maximum(date) extraction

On 05/09/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>
> "Gregory Stark" <stark(at)enterprisedb(dot)com> writes:
>
> > "JS Ubei" <jsubei(at)yahoo(dot)fr> writes:
> >
> >> I need to improve a query like :
> >>
> >> SELECT id, min(the_date), max(the_date) FROM my_table GROUP BY id;
> >...
> > I don't think you'll find anything much faster for this particular
> query. You
> > could profile running these two (non-standard) queries:
> >
> > SELECT DISTINCT ON (id) id, the_date AS min_date FROM my_table ORDER BY
> id, the_date ASC
> > SELECT DISTINCT ON (id) id, the_date AS max_date FROM my_table ORDER BY
> id, the_date DESC
>
> Something else you might try:
>
> select id,
> (select min(the_date) from my_table where id=x.id) as min_date,
> (select max(the_date) from my_table where id=x.id) as max_date
> from (select distinct id from my_table)
>
> Recent versions of Postgres do know how to use the index for a simple
> ungrouped min() or max() like these subqueries.
>
> This would be even better if you have a better source for the list of
> distinct
> ids you're interested in than my_table. If you have a source that just has
> one
> record for each id then you won't need an extra step to eliminate
> duplicates.
>
>
My personal reaction is why are you using distinct at all?

why not

select id,
min(the_date) as min_date,
max(the_date) as max_date
from my_table group by id;

Since 8.0 or was it earlier this will use an index should a reasonable one
exist.

Peter.