On Sat, Jul 11, 2015 at 10:05 AM, Peter Geoghegan <pg@heroku.com> wrote:
Currently, there are certain aggregates that sort some tuples before making a second pass over their memtuples array (through the tuplesort "gettuple" interface), calling one or more attribute equality operator functions as they go. For example, this occurs during the execution of the following queries:
-- Salary is a numeric column: SELECT count(DISTINCT salary) AS distinct_compensation_levels FROM employees; -- Get most common distinct salary in organization: SELECT mode() WITHIN GROUP (ORDER BY salary) AS most_common_compensation FROM employees;
Since these examples involve numeric comparisons, the equality operator calls involved in that second pass are expensive. There is no equality fast-path for numeric equality as there is for text; I imagine that in practice most calls to texteq() are resolved based on raw datum size mismatch, which is dirt cheap (while the alternative, a memcmp(), is still very cheap).
Forgive me for going off on a tangent here: I understand that the point of your patch is to avoid calls to numeric_eq altogether in these scenarios when cardinality is high, but the above sentence made me wonder how likely it is that numeric values in typical workloads have the same size, weight and sign and could therefore be handled in numeric_eq with memcmp() == 0 rather than numeric_cmp() == 0, and whether there would be any benefit.
Running various contrived aggregate queries on a large low cardinality dataset in a small range (therefore frequently the same weight & size), I managed to measure a small improvement of up to a few percent with the attached patch. I also wonder whether numeric_cmp could be profitably implemented with memcmp on big endian systems and some unrolled loops on little endian systems when size & weight match.
Of course there are a ton of other overheads involved with numeric. I wonder how crazy or difficult it would be to make it so that we could optionally put a pass-by-value NUMERIC in a Datum, setting a low order tag bit to say 'I'm an immediate value, not a pointer', and then packing 3 digits (= 12 significant figures) + sign + weight into the other 63 bits.
