Re: Use local buffer for packed numerics

From: David Geier <geidav(dot)pg(at)gmail(dot)com>
To: Andrei Lepikhov <lepihov(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Andreas Karlsson <andreas(at)proxel(dot)se>
Subject: Re: Use local buffer for packed numerics
Date: 2026-11-19 17:33:48
Message-ID: a1e37648-8aae-4e7c-b8e6-b013742c6c78@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>> 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.

Could you run your changes against my benchmark script? Once only with
the scratch buffer change and then combined with also avoiding the
unpacking. You could do that exemplary just for a few operations. It
would be great to talk about concrete, reproducible numbers. On that
note: what architecture are you testing on?

(2) and (3) of your patches are only for a narrow subset of NUMERIC
functionality, are they? I would love to apply the "not unpacking"
change to all of numeric.c.

> 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.

The "not unpacking" change is not complex and pretty repetitive but for
sure a lot of code churn.

>> I can send you my patch if you like.
>
> Yes, please. I’d like to compare the two.

It's attached. I saw really good results on x86 but some regressions on
M5. That's why I started working on centralizing the case distinction
work scattered throughout all the macros in set_var_from_num() and have
all functions use that. This also makes the code a lot nicer. This
change isn't complete yet, so I cannot send it to you.

I'll share the results on my box with you tomorrow. Right now, I don't
have access to my benchmark machine.

--
David Geier

Attachment Content-Type Size
v1-0001-Optimize-NUMERIC-by-not-unpacking-1-byte-varlenas.patch text/plain 79.0 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2026-11-19 17:43:58 Re: Hash aggregate collisions cause excessive spilling
Previous Message Nikhil Sontakke 2026-11-19 17:00:11 Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object