On 18/11/2026 09:06, David Geier wrote:
> Hi Andrei,
>
> I had looked optimizing NUMERIC as well inspired by [1].
>
> The observation there is similar to your's but goes a step further:
>
> Instead of copying into a scratch buffer to get rid of repeated
> alloc/free operations, it completely avoids unpacking the varlena and
> instead works directly off of the packed representation.
Thanks, I missed that thread. After a quick read, I see it more as a complement
to this patch rather than an alternative. I also think local buffers could
become a general way to avoid allocations, not just for numeric cases.
>> The second patch, 0002, adds this setup to other numeric-related operations.
>> It
>> acts as a helper to test how well this optimisation works.
>>
>> From what I've seen, the usual improvement is between 15% and 30%, with up to
>> 35% speedup in multi-value corner cases. But of course, it depends on the
>> workload. An example:
>
> It would be good to know how much we can save on top by directly working
> off of the packed representation as outlined above. On x86 that test is
> simple to do because unaligned memory accesses are allowed. You would
> just replace PG_DETOAST_DATUM() by PG_DETOAST_DATUM_PACKED() and adapt
> the macros to work with a packed representation.
I can answer part of your question. This patch set is one of three parts of a
numeric speed-up project I implemented a year ago for an ERP workload that is
very heavy in numeric types, and I'm now trying to publish these ideas to the
community.
1. The first part is about unpacking the short numerics into a local buffer
(this thread).
2. The second part uses numeric_eq() and numeric_ne() to derive the result
directly from the stored bytes, with no unpacking at all.
3. Part 3 is a fast 128 bit accumulator for numeric aggregates (not ready for
publishing yet).
I've attached a draft of part 2. It just handles = and <> without any unpacking.
Compared to master, it's typically ~30% faster with clang 19 and ~60% faster
with gcc. So, I guess it is a practical limit for your feature too.
It doesn't overlap with the idea you're referring to, but it has a similar
issue: the code is quite specialized and complex. I'm not sure if an open-source
project would want to take on that challenge. Still, we can try to use common
helpers to make it simpler and more broadly useful.
>
> I can send you my patch if you like.
Yes, please. I’d like to compare the two.
>
> In my tests I saw up to 30% for aggregates. IIRC, you save more than
> just the unpacking in the comparator but also some complexity in the
> aggregation state handling.
Thanks for feedback. Let’s see how far we can take this together.
--
regards, Andrei Lepikhov,
pgEdge
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 7deea2046f1..748cb6d62f6 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -2431,13 +2431,267 @@ numeric_cmp(PG_FUNCTION_ARGS)
}
+/*
+ * numeric_stored_parts() -
+ *
+ * Locate the sign, weight and digits of a stored finite numeric, given its
+ * header word, a pointer to its data (just past the varlena header, so
+ * possibly unaligned) and the data length. Returns false for anything too
+ * short or of odd length to be well formed.
+ *
+ * The sign and weight come out as NUMERIC_SIGN() and NUMERIC_WEIGHT()
would
+ * give them, but those work on a Numeric with a 4-byte varlena header.
+ */
+static pg_always_inline bool
+numeric_stored_parts(uint16 header, const char *p, Size len,
+ int *sign, int *weight, const char
**digits, Size *nbytes)
+{
+ Size hdrsz;
+
+ Assert((header & NUMERIC_SIGN_MASK) != NUMERIC_SPECIAL);
+
+ if (header & NUMERIC_SHORT)
+ {
+ *sign = (header & NUMERIC_SHORT_SIGN_MASK) ? NUMERIC_NEG :
NUMERIC_POS;
+ *weight = ((header & NUMERIC_SHORT_WEIGHT_SIGN_MASK) ?
+ ~NUMERIC_SHORT_WEIGHT_MASK : 0) |
+ (header & NUMERIC_SHORT_WEIGHT_MASK);
+ hdrsz = sizeof(uint16);
+ }
+ else
+ {
+ int16 w;
+
+ if (len < sizeof(uint16) + sizeof(int16))
+ return false;
+ memcpy(&w, p + sizeof(uint16), sizeof(int16));
+ *sign = header & NUMERIC_SIGN_MASK;
+ *weight = w;
+ hdrsz = sizeof(uint16) + sizeof(int16);
+ }
+
+ *digits = p + hdrsz;
+ *nbytes = len - hdrsz;
+
+ return (*nbytes % sizeof(NumericDigit)) == 0;
+}
+
+/*
+ * numeric_stored_digit() -
+ *
+ * Fetch the digit at the given byte offset of a stored, possibly unaligned
+ * digit array.
+ */
+static pg_always_inline NumericDigit
+numeric_stored_digit(const char *digits, Size offset)
+{
+ NumericDigit digit;
+
+ memcpy(&digit, digits + offset, sizeof(NumericDigit));
+ return digit;
+}
+
+/*
+ * numeric_special_eq() -
+ *
+ * Equality of two stored numerics, given their header words, when at least
+ * one of them is NaN or an infinity. Such a value never equals a finite
+ * one, and two of them are compared just as cmp_numerics() does it.
+ */
+static pg_always_inline int
+numeric_special_eq(uint16 h1, uint16 h2)
+{
+ bool special1 = (h1 & NUMERIC_SIGN_MASK) == NUMERIC_SPECIAL;
+ bool special2 = (h2 & NUMERIC_SIGN_MASK) == NUMERIC_SPECIAL;
+
+ if (special1 != special2)
+ return 0;
+ if (h1 == NUMERIC_NAN)
+ return h2 == NUMERIC_NAN;
+ if (h1 == NUMERIC_PINF)
+ return h2 == NUMERIC_PINF;
+ return h2 == NUMERIC_NINF;
+}
+
+/*
+ * numeric_quick_eq() -
+ *
+ * Try to decide whether two numeric datums are equal from their stored
+ * form, without detoasting either one. Returns 1 if they are equal, 0 if
+ * they are not, and -1 if it cannot tell and the caller must fall back to
+ * cmp_numerics().
+ *
+ * A finite numeric is stored as a sign, a display scale, a weight and an
+ * array of base-NBASE digits. The display scale takes no part in
+ * comparison, and neither does the choice between the short and the long
+ * header format, so the stored bytes of equal values can differ: 1.5 and
+ * 1.50 are equal.
+ *
+ * What follows walks through cmp_numerics() -- that is, cmp_var_common()
+ * and cmp_abs_common() -- test by test, replacing each with a cheaper one
+ * that gives the same answer. It therefore does not depend on the inputs
+ * having the form make_result() produces, with leading and trailing zero
+ * digits stripped; where a zero digit in such a position would matter, we
+ * give up and let cmp_numerics() decide.
+ *
+ * - NaN and the infinities are settled by their header words.
+ * - cmp_var_common() takes a value without digits to be zero, whatever its
+ * stored sign and weight, and equal only to another value without
+ * digits.
+ * - Values of different sign differ.
+ * - If the weights differ, the values differ as soon as the first digit of
+ * the one with the larger weight is nonzero.
+ * - If the weights are equal, the values differ if the digits they both
+ * have do, which memcmp() can tell. If those match and so do the
+ * lengths, the values are equal. Otherwise the values differ as soon as
+ * the last digit of the longer one is nonzero.
+ *
+ * Compressed and out-of-line datums are left to the general path.
+ *
+ * This is the whole fast path of its two callers, hence pg_always_inline.
+ */
+static pg_always_inline int
+numeric_quick_eq(Datum d1, Datum d2)
+{
+ const varlena *v1 = (const varlena *) DatumGetPointer(d1);
+ const varlena *v2 = (const varlena *) DatumGetPointer(d2);
+ const char *p1;
+ const char *p2;
+ Size len1;
+ Size len2;
+ uint16 h1;
+ uint16 h2;
+ int sign1;
+ int sign2;
+ int weight1;
+ int weight2;
+ const char *digits1;
+ const char *digits2;
+ Size nbytes1;
+ Size nbytes2;
+ NumericDigit last;
+
+ if (unlikely(VARATT_IS_EXTERNAL(v1) || VARATT_IS_COMPRESSED(v1) ||
+ VARATT_IS_EXTERNAL(v2) ||
VARATT_IS_COMPRESSED(v2)))
+ return -1;
+
+ len1 = VARSIZE_ANY_EXHDR(v1);
+ len2 = VARSIZE_ANY_EXHDR(v2);
+ if (unlikely(len1 < sizeof(uint16) || len2 < sizeof(uint16)))
+ return -1;
+
+ p1 = VARDATA_ANY(v1);
+ p2 = VARDATA_ANY(v2);
+ memcpy(&h1, p1, sizeof(uint16));
+ memcpy(&h2, p2, sizeof(uint16));
+
+ if (unlikely((h1 & NUMERIC_SIGN_MASK) == NUMERIC_SPECIAL ||
+ (h2 & NUMERIC_SIGN_MASK) == NUMERIC_SPECIAL))
+ return numeric_special_eq(h1, h2);
+
+ /*
+ * Same header word and length. This is the usual case for values of
one
+ * column, or of columns declared with the same scale, both when they
are
+ * equal (hash aggregation, hash joins) and when they are not (WHERE a =
+ * b), so settle it by comparing the bytes.
+ *
+ * If the bytes differ, the signs still match, being part of the header
+ * word, and so does the number of digits. If the weights match as
well,
+ * some digit must differ, and equal-length digit strings of equal
weight
+ * denote different values. In the short format the weight is part of
+ * the header word too; in the long format it is the word that follows.
+ * A long-format pair with different weights gets the full treatment
+ * below.
+ */
+ if (h1 == h2 && len1 == len2)
+ {
+ if (memcmp(p1, p2, len1) == 0)
+ return 1;
+
+ if (likely(len1 % sizeof(NumericDigit) == 0))
+ {
+ if (likely((h1 & NUMERIC_SIGN_MASK) == NUMERIC_SHORT))
+ return 0;
+
+ /* Bytes differ after an equal header word, so there
are more */
+ Assert(len1 >= sizeof(uint16) + sizeof(int16));
+ if (memcmp(p1 + sizeof(uint16), p2 + sizeof(uint16),
+ sizeof(int16)) == 0)
+ return 0;
+ }
+ }
+
+ /*
+ * The values differ in display scale, header format, sign, weight or
+ * digits. Take both apart and compare what counts.
+ */
+ if (unlikely(!numeric_stored_parts(h1, p1, len1, &sign1, &weight1,
+
&digits1, &nbytes1) ||
+ !numeric_stored_parts(h2, p2, len2, &sign2,
&weight2,
+
&digits2, &nbytes2)))
+ return -1;
+
+ /* No digits on either side, or on one only; see above */
+ if (nbytes1 == 0 || nbytes2 == 0)
+ return nbytes1 == nbytes2;
+
+ if (sign1 != sign2)
+ return 0;
+
+ if (weight1 != weight2)
+ {
+ /*
+ * The one with the larger weight is the larger in absolute
value,
+ * unless it starts with a zero digit.
+ */
+ const char *larger = (weight1 > weight2) ? digits1 : digits2;
+
+ if (numeric_stored_digit(larger, 0) != 0)
+ return 0;
+ return -1;
+ }
+
+ /* Equal weights: compare the digits that both values have */
+ if (memcmp(digits1, digits2, Min(nbytes1, nbytes2)) != 0)
+ return 0;
+ if (nbytes1 == nbytes2)
+ return 1;
+
+ /*
+ * One value has more digits than the other, and they agree as far as
the
+ * shorter one goes. They differ unless those extra digits are all
zero,
+ * which the last one normally is not.
+ */
+ if (nbytes1 > nbytes2)
+ last = numeric_stored_digit(digits1, nbytes1 -
sizeof(NumericDigit));
+ else
+ last = numeric_stored_digit(digits2, nbytes2 -
sizeof(NumericDigit));
+ if (last != 0)
+ return 0;
+ return -1;
+}
+
+/*
+ * Equality and inequality are decided from the stored form when possible.
+ * That saves copying inputs that have a 1-byte varlena header into palloc'd
+ * storage, which PG_GETARG_NUMERIC() must do to align them, as well as the
+ * call to cmp_numerics().
+ */
Datum
numeric_eq(PG_FUNCTION_ARGS)
{
- Numeric num1 = PG_GETARG_NUMERIC(0);
- Numeric num2 = PG_GETARG_NUMERIC(1);
+ Numeric num1;
+ Numeric num2;
+ int quick;
bool result;
+ quick = numeric_quick_eq(PG_GETARG_DATUM(0), PG_GETARG_DATUM(1));
+ if (quick >= 0)
+ PG_RETURN_BOOL(quick == 1);
+
+ num1 = PG_GETARG_NUMERIC(0);
+ num2 = PG_GETARG_NUMERIC(1);
+
result = cmp_numerics(num1, num2) == 0;
PG_FREE_IF_COPY(num1, 0);
@@ -2449,10 +2703,18 @@ numeric_eq(PG_FUNCTION_ARGS)
Datum
numeric_ne(PG_FUNCTION_ARGS)
{
- Numeric num1 = PG_GETARG_NUMERIC(0);
- Numeric num2 = PG_GETARG_NUMERIC(1);
+ Numeric num1;
+ Numeric num2;
+ int quick;
bool result;
+ quick = numeric_quick_eq(PG_GETARG_DATUM(0), PG_GETARG_DATUM(1));
+ if (quick >= 0)
+ PG_RETURN_BOOL(quick == 0);
+
+ num1 = PG_GETARG_NUMERIC(0);
+ num2 = PG_GETARG_NUMERIC(1);
+
result = cmp_numerics(num1, num2) != 0;
PG_FREE_IF_COPY(num1, 0);