Use local buffer for packed numerics

From: Andrei Lepikhov <lepihov(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Andreas Karlsson <andreas(at)proxel(dot)se>
Subject: Use local buffer for packed numerics
Date: 2026-11-17 09:58:50
Message-ID: 5f99450c-4b45-4b82-8498-15461411037c@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

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.

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.

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:

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;

On my laptop, I see stable 6400ms for the current master branch and 4900ms with
this patchset applied.

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.

[1] https://www.pgedge.com/blog/why-is-postgresql-s-numeric-type-so-slow

--
regards, Andrei Lepikhov,
pgEdge

Attachment Content-Type Size
v0-0001-Unpack-short-numerics-on-the-local-buffer.patch text/plain 8.8 KB
v0-0002-Unpack-short-numerics-on-the-stack-in-the-remaini.patch text/plain 9.4 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message Daniel Gustafsson 2026-11-17 09:51:56 Re: libpq: SCRAM client proofs serialize across threads on OpenSSL 3 (re-init of the HMAC per PBKDF2 iteration)