On 05/09/07, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote:
"Gregory Stark" <stark@xxxxxxxxxxxxxxxx> writes:
> "JS Ubei" <jsubei@xxxxxxxx> 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.
