| From: | Andrei Lepikhov <lepihov(at)gmail(dot)com> |
|---|---|
| To: | David Geier <geidav(dot)pg(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-18 10:01:39 |
| Message-ID: | 3f8a1de9-74a0-4838-bfe8-cc00584970b6@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| eq-noneq.no-cfbot | text/plain | 9.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jakub Wartak | 2026-11-18 10:14:42 | Re: amcheck: add index-all-keys-match verification for B-Tree |
| Previous Message | Dewei Dai | 2026-11-18 09:50:54 | 回复: Re: [PATCH] Reject duplicate SQL/JSON PASSING argument names |