Search Postgresql Archives

Re: DONT_CARE Aggregate

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Marti Raudsepp <marti@xxxxxxxxx> writes:
> On Thu, Dec 20, 2012 at 3:28 AM, Robert James <srobertjames@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux