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
