Re: DONT_CARE Aggregate

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> On Thu, Dec 20, 2012 at 3:28 AM, Robert James <srobertjames(at)gmail(dot)com> wrote:
>> Is there an aggregate that will return an arbitrary instance? That is,
>> not necessarily the max or min, just any one? (Which might perform
>> better than max or min)
>>
>> More importantly:
>> Is there one which will return an arbitrary instance as long as it's not NULL

> There's an extension on PGXN which implements first()/last()
> aggregates in C: http://pgxn.org/dist/first_last_agg/

> It should be slightly faster than min()/max(), but the difference is
> probably not significant in more complex queries.

Another thing to consider is that the presence of any "generic"
aggregate forces a full-table scan, since the system doesn't know that
the aggregate has any particular behavior. MIN/MAX on the other hand
can be optimized into index probes, if they are on indexed columns.
If the query otherwise uses only MIN/MAX aggregates, it's not hard
to believe that adding a FIRST() or LAST() instead of a MIN/MAX
aggregate could make the query significantly slower, not faster.

However, if you're targeting queries containing a variety of aggregates,
or if any of them are on unindexed columns, then this special case may
not be of much interest.

regards, tom lane