| 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-18 08:06:55 |
| Message-ID: | a4c808af-5977-4727-a5cc-c790747bcebf@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
No longer unpacking results in unaligned memory accesses. To avoid them
I had to change all memory reads to use a function that internally does
a memcpy(). On architectures that allow unaligned memory accesses this
gets compiled down to the original 16-bit memory read.
> After reviewing the current state of the numeric type [1], I noticed there is
> still room to improve CPU and memory usage. Here's the first piece of
> optimisation ideas.
Agreed. It can be a lot faster in almost all areas.
> The support function that sorts abbreviated numerics already uses a local buffer
> to avoid calling palloc and pfree for each input value. I believe this method
> could work for other numeric operations too.
>
> This makes sense because almost every numeric that comes out of a heap tuple has
> a one-byte varlena header and needs to be unpacked into a standard format before
> use. Right now, each operator calls palloc() for every numeric value. Using a
> preallocated local buffer could save some processing time.
>
> This simple patchset shows how this could work. The first patch, 0001, adds a
> structure called NumericLocalBuf, which should be a local variable in the main
> operator. It also adds a function, numeric_unpack_local, that unpacks a numeric
> into this static buffer or calls palloc if the value is too large.
Being able to provide the target buffer would allow for similar
optimizations in other code that uses PG_DETOAST_DATUM(). If this is the
route forward, we can also consider changing / extending the API.
> 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 send you my patch if you like.
Code that supports both, packed and unpacked varlenas, needs to be
crafted carefully though. Just changing the macros such as
NUMERIC_DIGITS(), NUMERIC_NDIGITS(), NUMERIC_IS_SPECIAL() to support
causes regressions because the compiler isn't always able to hoist out
common code but instead repeats all the bit fiddling and case
distinctions each time.
What I ended up doing in my hack branch is having all code always go
through set_var_from_num(). This allowed me to get rid of all the macros
and instead have all varlena packed/unpacked and NUMERIC short/long case
distinctions in a single function that I optimized. This worked nicely
but the diff was pretty big though very repetitive.
> CREATE TABLE t_num AS
> SELECT i AS id,
> round((random() * 1e8)::numeric, 2)::numeric(12,2) AS a,
> ((i % 1000) * 1.37)::numeric(12,2) AS k1k,
> random()::numeric AS payload
> FROM generate_series(1, 5000000) i;
>
> SELECT k1k, a FROM t_num ORDER BY k1k, a OFFSET 4999999;
Sorting benefits the most because the comparator is called more than
once per element.
> On my laptop, I see stable 6400ms for the current master branch and 4900ms with
> this patchset applied.
Cool! I attached you my benchmark script which exercises a wide range of
NUMERIC functionality. Maybe that's useful. It's by no means complete
but a starting point. Let's improve it together to have something that
covers as much functionality as possible. It's missing e.g. special
NUMERICs like NaN, +Inf, -Inf. You can use the script like that:
\i numeric_benchmark.sql
SELECT * FROM numeric_benchmark_results;
> What's interesting: it might provide a nice 10% speedup or more for sum(numeric)
> because unpack is the only allocation that is needed by the aggregate. So, here
> we save not only the palloc() call, but the context reset effort that should be
> made on each value.
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.
--
David Geier
[1]
https://www.postgresql.org/message-id/flat/tencent_D999DB30A62C6B4D956CA9BADF35CFE91408%40qq.com
| Attachment | Content-Type | Size |
|---|---|---|
| numeric_benchmark.sql | text/plain | 15.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Amit Kapila | 2026-11-18 08:22:03 | Re: Document prepared transaction lock waits with two_phase subscriptions |
| Previous Message | Bertrand Drouvot | 2026-11-18 07:47:15 | Re: Fix ReadStream handling when heap rescan strategy changes |