Re: optimize query with a maximum(date) extraction

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> 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