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
DO $$
BEGIN
-- Table t
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
't') THEN
CREATE TABLE t(id bigserial primary key, order_date date, amount
numeric(18, 2));
INSERT INTO t(order_date, amount) SELECT DATE '2024-01-01' + (gs %
730), gs::numeric(18, 2) * gs % 998244353 * gs % 1000000007 * 133.31 FROM
generate_series(1, 1000000) AS gs;
END IF;
-- Table t_big
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
't_big') THEN
CREATE TABLE t_big(id bigserial primary key, order_date date, amount
numeric(38, 2));
INSERT INTO t_big(order_date, amount) SELECT DATE '2024-01-01' + (gs %
730), gs::numeric(38, 2) * gs * gs * 133.31 FROM generate_series(1, 1000000) AS
gs;
END IF;
-- Table t_small
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name =
't_small') THEN
CREATE TABLE t_small(id bigserial primary key, order_date date, amount
numeric(9, 2));
INSERT INTO t_small(order_date, amount) SELECT DATE '2024-01-01' + (gs
% 730), (gs % 1331) * (gs % 1331) * 1.31 FROM generate_series(1, 1000000) AS gs;
END IF;
END $$;
VACUUM ANALYZE t;
VACUUM ANALYZE t_big;
VACUUM ANALYZE t_small;
CREATE OR REPLACE FUNCTION benchmark_queries(queries text[])
RETURNS TABLE(query_number integer, query_text text, min_runtime_ms numeric) AS
$$
DECLARE
q text;
start_time timestamp;
end_time timestamp;
runtime_ms numeric;
i integer;
min_time numeric;
query_idx integer := 0;
total_runtime numeric := 0;
BEGIN
SET LOCAL max_parallel_workers_per_gather = 0;
FOREACH q IN ARRAY queries
LOOP
query_idx := query_idx + 1;
min_time := NULL;
FOR i IN 1..2 LOOP
start_time := clock_timestamp();
EXECUTE q;
end_time := clock_timestamp();
runtime_ms := EXTRACT(EPOCH FROM (end_time - start_time)) * 1000;
IF min_time IS NULL OR runtime_ms < min_time THEN
min_time := runtime_ms;
END IF;
END LOOP;
query_number := query_idx;
query_text := q;
min_runtime_ms := min_time;
RETURN NEXT;
total_runtime := total_runtime + min_time;
RAISE WARNING 'Query % completed: min runtime = % ms', query_idx,
min_time;
END LOOP;
query_number := NULL;
query_text := 'TOTAL';
min_runtime_ms := total_runtime;
RETURN NEXT;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE numeric_benchmark_results AS
SELECT * FROM benchmark_queries(ARRAY[
-- Table t
'select max(amount) from t',
'select max(amount) from t where order_date > ''2025-01-01''',
'select order_date, max(amount) from t group by order_date',
'select order_date, max(amount) from t where order_date > ''2025-01-01''
group by order_date',
'select min(amount) from t',
'select min(amount) from t where order_date > ''2025-01-01''',
'select order_date, min(amount) from t group by order_date',
'select order_date, min(amount) from t where order_date > ''2025-01-01''
group by order_date',
'select sum(amount) from t',
'select sum(amount) from t where order_date > ''2025-01-01''',
'select order_date, sum(amount) from t group by order_date',
'select order_date, sum(amount) from t where order_date > ''2025-01-01''
group by order_date',
'select avg(amount) from t',
'select avg(amount) from t where order_date > ''2025-01-01''',
'select order_date, avg(amount) from t group by order_date',
'select order_date, avg(amount) from t where order_date > ''2025-01-01''
group by order_date',
'select variance(amount) from t',
'select variance(amount) from t where order_date > ''2025-01-01''',
'select order_date, variance(amount) from t group by order_date',
'select order_date, variance(amount) from t where order_date >
''2025-01-01'' group by order_date',
'select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t',
'select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t where order_date > ''2025-01-01''',
'select order_date, max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t group by order_date',
'select order_date, max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t where order_date > ''2025-01-01'' group by order_date',
'select count(*) from t where amount = 12345.67',
'select count(*) from t where amount <> 0',
'select count(*) from t where amount > 10000',
'select count(*) from t where amount >= 10000',
'select count(*) from t where amount < 50000',
'select count(*) from t where amount <= 50000',
'select count(*) from t where amount > amount * 0.5',
'select count(*) from t where amount + 100 > amount * 1.1',
'select sum(amount + 100) from t',
'select sum(amount - 50) from t',
'select sum(amount * 2) from t',
'select sum(amount / 3) from t',
'select sum(amount % 10) from t',
'select count(*) from t where amount * 2 > 100000',
'select count(*) from t where amount / 10 > 5000',
'select count(*) from t where amount + amount > 200000',
'select sum((amount + 100) * 0.95) from t',
'select sum(amount * 1.08 - amount * 0.03) from t',
'select sum(abs(amount)) from t',
'select sum(abs(amount - 50000)) from t',
'select count(*) from t where abs(amount - 50000) < 10000',
'select sum(round(amount, 0)) from t',
'select sum(round(amount, 1)) from t',
'select sum(trunc(amount, 0)) from t',
'select sum(trunc(amount, 1)) from t',
'select sum(floor(amount)) from t',
'select sum(ceil(amount)) from t',
'select sum(ceiling(amount)) from t',
'select sum(sign(amount)) from t',
'select count(*) from t where sign(amount) > 0',
'select sum(power(amount, 2)) from t',
'select sum(sqrt(abs(amount))) from t',
-- 'select sum(power(amount, 0.5)) from t',
'select sum(mod(amount, 100)) from t',
'select count(*) from t where mod(amount, 10) = 0',
'select count(*) from t where amount % 1000 < 100',
'select count(*), width_bucket(amount, 0, 100000, 10) from t group by
width_bucket(amount, 0, 100000, 10)',
'select sum(round(abs(amount - 50000), 2)) from t',
'select sum(floor(amount / 100) * 100) from t',
'select sum(ceil(amount / 100) * 100) from t',
'select sum(case when amount > 50000 then amount * 0.9 else amount * 1.1
end) from t',
'select amount from t order by amount limit 1000',
'select amount from t order by amount desc limit 1000',
'select amount from t order by abs(amount - 50000) limit 1000',
-- Table t_big
'select max(amount) from t_big',
'select max(amount) from t_big where order_date > ''2025-01-01''',
'select order_date, max(amount) from t_big group by order_date',
'select order_date, max(amount) from t_big where order_date >
''2025-01-01'' group by order_date',
'select min(amount) from t_big',
'select min(amount) from t_big where order_date > ''2025-01-01''',
'select order_date, min(amount) from t_big group by order_date',
'select order_date, min(amount) from t_big where order_date >
''2025-01-01'' group by order_date',
'select sum(amount) from t_big',
'select sum(amount) from t_big where order_date > ''2025-01-01''',
'select order_date, sum(amount) from t_big group by order_date',
'select order_date, sum(amount) from t_big where order_date >
''2025-01-01'' group by order_date',
'select avg(amount) from t_big',
'select avg(amount) from t_big where order_date > ''2025-01-01''',
'select order_date, avg(amount) from t_big group by order_date',
'select order_date, avg(amount) from t_big where order_date >
''2025-01-01'' group by order_date',
'select variance(amount) from t_big',
'select variance(amount) from t_big where order_date > ''2025-01-01''',
'select order_date, variance(amount) from t_big group by order_date',
'select order_date, variance(amount) from t_big where order_date >
''2025-01-01'' group by order_date',
'select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big',
'select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big where order_date > ''2025-01-01''',
'select order_date, max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big group by order_date',
'select order_date, max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big where order_date > ''2025-01-01'' group by
order_date',
'select count(*) from t_big where amount = 12345.67',
'select count(*) from t_big where amount <> 0',
'select count(*) from t_big where amount > 10000',
'select count(*) from t_big where amount >= 10000',
'select count(*) from t_big where amount < 50000',
'select count(*) from t_big where amount <= 50000',
'select count(*) from t_big where amount > amount * 0.5',
'select count(*) from t_big where amount + 100 > amount * 1.1',
'select sum(amount + 100) from t_big',
'select sum(amount - 50) from t_big',
'select sum(amount * 2) from t_big',
'select sum(amount / 3) from t_big',
'select sum(amount % 10) from t_big',
'select count(*) from t_big where amount * 2 > 100000',
'select count(*) from t_big where amount / 10 > 5000',
'select count(*) from t_big where amount + amount > 200000',
'select sum((amount + 100) * 0.95) from t_big',
'select sum(amount * 1.08 - amount * 0.03) from t_big',
'select sum(abs(amount)) from t_big',
'select sum(abs(amount - 50000)) from t_big',
'select count(*) from t_big where abs(amount - 50000) < 10000',
'select sum(round(amount, 0)) from t_big',
'select sum(round(amount, 1)) from t_big',
'select sum(trunc(amount, 0)) from t_big',
'select sum(trunc(amount, 1)) from t_big',
'select sum(floor(amount)) from t_big',
'select sum(ceil(amount)) from t_big',
'select sum(ceiling(amount)) from t_big',
'select sum(sign(amount)) from t_big',
'select count(*) from t_big where sign(amount) > 0',
'select sum(power(amount, 2)) from t_big',
'select sum(sqrt(abs(amount))) from t_big',
--'select sum(power(amount, 0.5)) from t_big',
'select sum(mod(amount, 100)) from t_big',
'select count(*) from t_big where mod(amount, 10) = 0',
'select count(*) from t_big where amount % 1000 < 100',
'select count(*), width_bucket(amount, 0, 100000, 10) from t_big group by
width_bucket(amount, 0, 100000, 10)',
'select sum(round(abs(amount - 50000), 2)) from t_big',
'select sum(floor(amount / 100) * 100) from t_big',
'select sum(ceil(amount / 100) * 100) from t_big',
'select sum(case when amount > 50000 then amount * 0.9 else amount * 1.1
end) from t_big',
'select amount from t_big order by amount limit 1000',
'select amount from t_big order by amount desc limit 1000',
'select amount from t_big order by abs(amount - 50000) limit 1000',
-- Table t_small
'select max(amount) from t_small',
'select max(amount) from t_small where order_date > ''2025-01-01''',
'select order_date, max(amount) from t_small group by order_date',
'select order_date, max(amount) from t_small where order_date >
''2025-01-01'' group by order_date',
'select min(amount) from t_small',
'select min(amount) from t_small where order_date > ''2025-01-01''',
'select order_date, min(amount) from t_small group by order_date',
'select order_date, min(amount) from t_small where order_date >
''2025-01-01'' group by order_date',
'select sum(amount) from t_small',
'select sum(amount) from t_small where order_date > ''2025-01-01''',
'select order_date, sum(amount) from t_small group by order_date',
'select order_date, sum(amount) from t_small where order_date >
''2025-01-01'' group by order_date',
'select avg(amount) from t_small',
'select avg(amount) from t_small where order_date > ''2025-01-01''',
'select order_date, avg(amount) from t_small group by order_date',
'select order_date, avg(amount) from t_small where order_date >
''2025-01-01'' group by order_date',
'select variance(amount) from t_small',
'select variance(amount) from t_small where order_date > ''2025-01-01''',
'select order_date, variance(amount) from t_small group by order_date',
'select order_date, variance(amount) from t_small where order_date >
''2025-01-01'' group by order_date',
'select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small',
'select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small where order_date > ''2025-01-01''',
'select order_date, max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small group by order_date',
'select order_date, max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small where order_date > ''2025-01-01'' group by
order_date',
'select count(*) from t_small where amount = 123.45',
'select count(*) from t_small where amount <> 0',
'select count(*) from t_small where amount > 100',
'select count(*) from t_small where amount >= 100',
'select count(*) from t_small where amount < 500',
'select count(*) from t_small where amount <= 500',
'select count(*) from t_small where amount > amount * 0.5',
'select count(*) from t_small where amount + 10 > amount * 1.1',
'select sum(amount + 10) from t_small',
'select sum(amount - 5) from t_small',
'select sum(amount * 2) from t_small',
'select sum(amount / 3) from t_small',
'select sum(amount % 10) from t_small',
'select count(*) from t_small where amount * 2 > 1000',
'select count(*) from t_small where amount / 10 > 50',
'select count(*) from t_small where amount + amount > 2000',
'select sum((amount + 10) * 0.95) from t_small',
'select sum(amount * 1.08 - amount * 0.03) from t_small',
'select sum(abs(amount)) from t_small',
'select sum(abs(amount - 500)) from t_small',
'select count(*) from t_small where abs(amount - 500) < 100',
'select sum(round(amount, 0)) from t_small',
'select sum(round(amount, 1)) from t_small',
'select sum(trunc(amount, 0)) from t_small',
'select sum(trunc(amount, 1)) from t_small',
'select sum(floor(amount)) from t_small',
'select sum(ceil(amount)) from t_small',
'select sum(ceiling(amount)) from t_small',
'select sum(sign(amount)) from t_small',
'select count(*) from t_small where sign(amount) > 0',
'select sum(power(amount, 2)) from t_small',
'select sum(sqrt(abs(amount))) from t_small',
-- 'select sum(power(amount, 0.5)) from t_small',
'select sum(mod(amount, 100)) from t_small',
'select count(*) from t_small where mod(amount, 10) = 0',
'select count(*) from t_small where amount % 1000 < 100',
'select count(*), width_bucket(amount, 0, 1000, 10) from t_small group by
width_bucket(amount, 0, 1000, 10)',
'select sum(round(abs(amount - 500), 2)) from t_small',
'select sum(floor(amount / 100) * 100) from t_small',
'select sum(ceil(amount / 100) * 100) from t_small',
'select sum(case when amount > 500 then amount * 0.9 else amount * 1.1 end)
from t_small',
'select amount from t_small order by amount limit 1000',
'select amount from t_small order by amount desc limit 1000',
'select amount from t_small order by abs(amount - 500) limit 1000'
]);