Re: Reusing abbreviated keys during second pass of ordered [set] aggregates

On Sat, Jul 11, 2015 at 10:05 AM, Peter Geoghegan <pg(at)heroku(dot)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.

--
Thomas Munro
http://www.enterprisedb.com