- argm 1.0.2
This PostgreSQL extension proivide several aggregate functions that could be used for SQL queries simplification and speedup.
Argmax and argmin
argmax(value, key_1, key_2, ...)
argmin(value, key_1, key_2, ...)
These functions pick the row with the highest/lowest keys combination within each group and return the corresponding values. Keys tuples are compared lexicographically, just like rows. Nulls are handled as unknown values. See PostgreSQL docs for more info on row-wise comparison. If there are several lines with the same keys the result line and value will be chosen arbitrarily.
Values could be of any PostgreSQL data type, while keys must be sortable. Return type is the same as value parameter type.
Logically using these functions with GROUP BY clause
SELECT argmax(value, key_1, key_2)
is equivalent to DISTINCT ON clause
GROUP BY gr
SELECT DISTINCT ON (gr) value
but there are the following pros and cons:
ORDER BY gr, key_1 DESC, key_2 DESC
GROUP BYcan use any grouping algorithm, including HashAgg. While
DISTINCT ONneeds the input to be sorted. This means in general
argmincan be used along with other aggregate functions using the same grouping clause. With
DISTINCT ONone can select only values calculated on the row chosen.
argmincan only order ascending or descending by all columns, use only one collation, nulls are always chosen last.
DISTINCT ONcan order using different directions, collations and nulls policies for different keys.
This function simply returns the first non-null value within the group.
Any PostgreSQL types are supported.
The function can be useful to write a value in a select list without grouping by
it and without calling any essential aggregate function. That is, the following
queries are equivalent if
foo_details is determined by
Grouping by both columns
GROUP BY foo,
This approach often leads to cardinality misestimations resulting in suboptimal
execution plans. Additionally there is an overhead in hashing/sorting
foo_details values while grouping by them.
GROUP BY foo
This requires a proper
min function for the data type of foo_details. Also
calculating a minimum of the values is still an overhead despite all the values
GROUP BY foo
anyold function is faster than
The extension is compatible with PostgreSQL 9.4 and higher.
To install the extension for your database cluster run the following command:
make && sudo make install && make installcheck
pg_config from your PostgreSQL installation to be available
To use the extension on particular database run the following SQL:
CREATE EXTENSION argm;
To compare the performance of the approaches listed above of one could execute