"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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster