"Pavel Stehule" <pavel.stehule@xxxxxxxxx> writes: >> >> 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. As I mentioned in the other post that's not true for this query. > without any limits, seq scan is optimal. That's not necessarily true either. You could have ten distinct ids and millions of dates for them. In that case a scan of the index which jumped around to scan from the beginning and end of each distinct id value would be faster. There's just no such plan type in Postgres currently. You can simulate such a plan with the subqueries I described but there's a bit more overhead than necessary and you need a reasonably efficient source of the distinct ids. Also it may or may not be faster than simply scanning the whole table like above and simulating it with subqueries makes it impossible to choose the best plan. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
