[PATCH] Optimize numeric comparisons and aggregations via packed-datum extraction
Hi,
While profiling the execution of MAX(), MIN(), SUM(), AVG(), and VARIANCE() on numeric columns, I noticed that a significant amount of CPU time is spent on heap allocations and varlena detoasting. Specifically, PG_GETARG_NUMERIC() unconditionally detoasts short-header datums, which incurs continuous palloc/memcpy overhead in tight aggregation loops.
This patch introduces a fast-path optimization by directly extracting numeric fields from packed datums.
The key modifications include:
1. cmp_numerics_packed(): A new comparison function that reads the `n_header` and payload directly from `VARDATA_ANY()` using pointer arithmetic. This avoids the standard `NUMERIC_*` macros which assume a 4-byte header structure, allowing us to safely compare 1-byte header numerics in-place.
2. Zero-copy MIN/MAX: In `numeric_smaller` and `numeric_larger`, instead of unpacking inputs to `Numeric` and repacking them for return, the patch uses `PG_DETOAST_DATUM_PACKED` and directly returns the original `Datum` using `PG_RETURN_DATUM(PG_GETARG_DATUM(X))`.
3. init_var_from_packed(): Extended the same direct-extraction logic to `do_numeric_accum` and `do_numeric_discard` for SUM, AVG, and VARIANCE.
I ran the built-in `numeric.sql` regression tests, and all passed without issues.
Here are the benchmark results executing on 20M rows using different precisions (NUMERIC(18,2), NUMERIC(38,2), NUMERIC(9,2)):
The DDLs and DMLs are:
drop table if exists t;
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, 20000000) as gs;
vacuum analyze t;
drop table if exists t_big;
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, 20000000) as gs;
vacuum analyze t_big;
drop table if exists t_small;
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, 20000000) as gs;
vacuum analyze t_small;
and the TEST CASEs are:
elect 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 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 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;
And I ran the test cases on my machine (12th Gen Intel(R) Core(TM) i9-12900H (2.50 GHz), 64GB RAM), better performance shows below:
# ====== DATASET: t (20M rows, NUMERIC(18,2)) ======
# ---- Baseline (All optimizations reverted) ----
# MAX queries
select max(amount) from t; Time: 980.844 ms
select max(amount) from t where order_date > '2025-01-01'; Time: 406.842 ms
select order_date, max(amount) from t group by order_date; Time: 952.209 ms
select order_date, max(amount) from t where order_date > '2025-01-01' group by ...; Time: 571.053 ms
# MIN queries
select min(amount) from t; Time: 567.840 ms
select min(amount) from t where order_date > '2025-01-01'; Time: 405.364 ms
select order_date, min(amount) from t group by order_date; Time: 935.274 ms
select order_date, min(amount) from t where order_date > '2025-01-01' group by ...; Time: 546.571 ms
# SUM queries
select sum(amount) from t; Time: 562.059 ms
select sum(amount) from t where order_date > '2025-01-01'; Time: 400.519 ms
select order_date, sum(amount) from t group by order_date; Time: 920.640 ms
select order_date, sum(amount) from t where order_date > '2025-01-01' group by ...; Time: 543.760 ms
# AVG queries
select avg(amount) from t; Time: 566.719 ms
select avg(amount) from t where order_date > '2025-01-01'; Time: 404.249 ms
select order_date, avg(amount) from t group by order_date; Time: 922.547 ms
select order_date, avg(amount) from t where order_date > '2025-01-01' group by ...; Time: 534.897 ms
# VARIANCE queries
select variance(amount) from t; Time: 699.547 ms
select variance(amount) from t where order_date > '2025-01-01'; Time: 479.679 ms
select order_date, variance(amount) from t group by order_date; Time: 1081.712 ms
select order_date, variance(amount) from t where order_date > '2025-01-01' group by Time: 620.472 ms
# COMBINED queries
select max, min, sum, avg, variance from t; Time: 1686.727 ms
select max, min, sum, avg, variance from t where order_date > '2025-01-01'; Time: 971.959 ms
select order_date, max, min, sum, avg, variance from t group by order_date; Time: 2206.506 ms
select order_date, max, min, sum... from t where order_date > '2025-01-01'... Time: 1155.119 ms
# ---- Optimized (packed-datum extraction for MAX, MIN, SUM, AVG, VARIANCE) ----
# MAX queries
select max(amount) from t; Time: 297.890 ms (-70%)
select max(amount) from t where order_date > '2025-01-01'; Time: 261.415 ms (-36%)
select order_date, max(amount) from t group by order_date; Time: 702.121 ms (-26%)
select order_date, max(amount) from t where order_date > '2025-01-01' group by ...; Time: 407.533 ms (-29%)
# MIN queries
select min(amount) from t; Time: 301.846 ms (-47%)
select min(amount) from t where order_date > '2025-01-01'; Time: 266.653 ms (-34%)
select order_date, min(amount) from t group by order_date; Time: 681.805 ms (-27%)
select order_date, min(amount) from t where order_date > '2025-01-01' group by ...; Time: 413.830 ms (-24%)
# SUM queries
select sum(amount) from t; Time: 300.850 ms (-46%)
select sum(amount) from t where order_date > '2025-01-01'; Time: 282.615 ms (-29%)
select order_date, sum(amount) from t group by order_date; Time: 765.818 ms (-17%)
select order_date, sum(amount) from t where order_date > '2025-01-01' group by ...; Time: 463.414 ms (-15%)
# AVG queries
select avg(amount) from t; Time: 327.844 ms (-42%)
select avg(amount) from t where order_date > '2025-01-01'; Time: 365.507 ms (-10%)
select order_date, avg(amount) from t group by order_date; Time: 650.977 ms (-29%)
select order_date, avg(amount) from t where order_date > '2025-01-01' group by ...; Time: 430.043 ms (-20%)
# VARIANCE queries
select variance(amount) from t; Time: 505.815 ms (-28%)
select variance(amount) from t where order_date > '2025-01-01'; Time: 403.832 ms (-16%)
select order_date, variance(amount) from t group by order_date; Time: 901.068 ms (-17%)
select order_date, variance(amount) from t where order_date > '2025-01-01' group by Time: 561.622 ms (-9%)
# COMBINED queries
select max, min, sum, avg, variance from t; Time: 804.479 ms (-52%)
select max, min, sum, avg, variance from t where order_date > '2025-01-01'; Time: 530.819 ms (-45%)
select order_date, max, min, sum, avg, variance from t group by order_date; Time: 1241.194 ms (-44%)
select order_date, max, min, sum... from t where order_date > '2025-01-01'... Time: 741.431 ms (-36%)
# ====== DATASET: t_big (20M rows, NUMERIC(38,2)) ======
# ---- Baseline (All optimizations reverted) ----
# MAX queries
select max(amount) from t_big; Time: 1143.060 ms
select max(amount) from t... where order_date > '2025-01-01'; Time: 462.994 ms
select order_date, max(amount) from t_big group by order_date; Time: 1032.899 ms
select order_date, max... from t... where order_date > '2025-01-01' group by ...; Time: 556.832 ms
# MIN queries
select min(amount) from t_big; Time: 534.970 ms
select min(amount) from t... where order_date > '2025-01-01'; Time: 412.798 ms
select order_date, min(amount) from t_big group by order_date; Time: 923.593 ms
select order_date, min... from t... where order_date > '2025-01-01' group by ...; Time: 565.523 ms
# SUM queries
select sum(amount) from t_big; Time: 611.950 ms
select sum(amount) from t... where order_date > '2025-01-01'; Time: 476.377 ms
select order_date, sum(amount) from t_big group by order_date; Time: 1009.434 ms
select order_date, sum... from t... where order_date > '2025-01-01' group by ...; Time: 589.432 ms
# AVG queries
select avg(amount) from t_big; Time: 601.082 ms
select avg(amount) from t... where order_date > '2025-01-01'; Time: 450.408 ms
select order_date, avg(amount) from t_big group by order_date; Time: 1038.965 ms
select order_date, avg... from t... where order_date > '2025-01-01' group by ...; Time: 670.555 ms
# VARIANCE queries
select variance(amount) from t_big; Time: 1019.580 ms
select variance(amount) from t... where order_date > '2025-01-01'; Time: 658.554 ms
select order_date, variance(amount) from t_big group by order_date; Time: 1366.489 ms
select order_date, variance... from t... where order_date > '2025-01-01' group by Time: 766.608 ms
# COMBINED queries
select max, min, sum, avg, variance from t_big; Time: 1797.785 ms
select max, min, sum, avg, variance from t... where order_date > '2025-01-01'; Time: 1064.796 ms
select order_date, max, min, sum, avg, variance from t_big group by order_date; Time: 2254.985 ms
select order_date, max, min, sum... from t... where order_date > '2025-01-01'... Time: 1189.765 ms
# ---- Optimized (packed-datum extraction for MAX, MIN, SUM, AVG, VARIANCE) ----
# MAX queries
select max(amount) from t_big; Time: 457.682 ms (-60%)
select max(amount) from t... where order_date > '2025-01-01'; Time: 342.199 ms (-26%)
select order_date, max(amount) from t_big group by order_date; Time: 777.064 ms (-25%)
select order_date, max... from t... where order_date > '2025-01-01' group by ...; Time: 463.163 ms (-17%)
# MIN queries
select min(amount) from t_big; Time: 328.795 ms (-39%)
select min(amount) from t... where order_date > '2025-01-01'; Time: 305.936 ms (-26%)
select order_date, min(amount) from t_big group by order_date; Time: 693.899 ms (-25%)
select order_date, min... from t... where order_date > '2025-01-01' group by ...; Time: 456.664 ms (-19%)
# SUM queries
select sum(amount) from t_big; Time: 329.647 ms (-46%)
select sum(amount) from t... where order_date > '2025-01-01'; Time: 311.748 ms (-35%)
select order_date, sum(amount) from t_big group by order_date; Time: 703.230 ms (-30%)
select order_date, sum... from t... where order_date > '2025-01-01' group by ...; Time: 473.354 ms (-20%)
# AVG queries
select avg(amount) from t_big; Time: 357.884 ms (-40%)
select avg(amount) from t... where order_date > '2025-01-01'; Time: 381.753 ms (-15%)
select order_date, avg(amount) from t_big group by order_date; Time: 707.939 ms (-32%)
select order_date, avg... from t... where order_date > '2025-01-01' group by ...; Time: 489.593 ms (-27%)
# VARIANCE queries
select variance(amount) from t_big; Time: 723.205 ms (-29%)
select variance(amount) from t... where order_date > '2025-01-01'; Time: 481.065 ms (-27%)
select order_date, variance(amount) from t_big group by order_date; Time: 1273.396 ms (-7%)
select order_date, variance... from t... where order_date > '2025-01-01' group by Time: 692.473 ms (-10%)
# COMBINED queries
select max, min, sum, avg, variance from t_big; Time: 1144.183 ms (-36%)
select max, min, sum, avg, variance from t... where order_date > '2025-01-01'; Time: 735.677 ms (-31%)
select order_date, max, min, sum, avg, variance from t_big group by order_date; Time: 1603.737 ms (-29%)
select order_date, max, min, sum... from t... where order_date > '2025-01-01'... Time: 869.972 ms (-27%)
# ====== DATASET: t_small (20M rows, NUMERIC(9,2)) ======
# ---- Baseline (All optimizations reverted) ----
# MAX queries
select max(amount) from t_small; Time: 919.394 ms
select max(amount) from t... where order_date > '2025-01-01'; Time: 414.951 ms
select order_date, max(amount) from t_small group by order_date; Time: 939.192 ms
select order_date, max... from t... where order_date > '2025-01-01' group by ...; Time: 548.936 ms
# MIN queries
select min(amount) from t_small; Time: 566.213 ms
select min(amount) from t... where order_date > '2025-01-01'; Time: 468.580 ms
select order_date, min(amount) from t_small group by order_date; Time: 973.943 ms
select order_date, min... from t... where order_date > '2025-01-01' group by ...; Time: 536.868 ms
# SUM queries
select sum(amount) from t_small; Time: 549.539 ms
select sum(amount) from t... where order_date > '2025-01-01'; Time: 405.480 ms
select order_date, sum(amount) from t_small group by order_date; Time: 970.394 ms
select order_date, sum... from t... where order_date > '2025-01-01' group by ...; Time: 521.120 ms
# AVG queries
select avg(amount) from t_small; Time: 557.897 ms
select avg(amount) from t... where order_date > '2025-01-01'; Time: 417.120 ms
select order_date, avg(amount) from t_small group by order_date; Time: 937.021 ms
select order_date, avg... from t... where order_date > '2025-01-01' group by ...; Time: 552.019 ms
# VARIANCE queries
select variance(amount) from t_small; Time: 655.459 ms
select variance(amount) from t... where order_date > '2025-01-01'; Time: 494.058 ms
select order_date, variance(amount) from t_small group by order_date; Time: 1051.335 ms
select order_date, variance... from t... where order_date > '2025-01-01' group by Time: 606.667 ms
# COMBINED queries
select max, min, sum, avg, variance from t_small; Time: 1489.296 ms
select max, min, sum, avg, variance from t... where order_date > '2025-01-01'; Time: 892.727 ms
select order_date, max, min, sum, avg, variance from t_small group by order_date; Time: 1940.004 ms
select order_date, max, min, sum... from t... where order_date > '2025-01-01'... Time: 1041.348 ms
# ---- Optimized (packed-datum extraction for MAX, MIN, SUM, AVG, VARIANCE) ----
# MAX queries
select max(amount) from t_small; Time: 332.218 ms (-64%)
select max(amount) from t... where order_date > '2025-01-01'; Time: 293.562 ms (-29%)
select order_date, max(amount) from t_small group by order_date; Time: 670.589 ms (-29%)
select order_date, max... from t... where order_date > '2025-01-01' group by ...; Time: 485.264 ms (-12%)
# MIN queries
select min(amount) from t_small; Time: 303.559 ms (-46%)
select min(amount) from t... where order_date > '2025-01-01'; Time: 330.970 ms (-29%)
select order_date, min(amount) from t_small group by order_date; Time: 726.441 ms (-25%)
select order_date, min... from t... where order_date > '2025-01-01' group by ...; Time: 455.427 ms (-15%)
# SUM queries
select sum(amount) from t_small; Time: 331.040 ms (-40%)
select sum(amount) from t... where order_date > '2025-01-01'; Time: 354.306 ms (-13%)
select order_date, sum(amount) from t_small group by order_date; Time: 731.451 ms (-25%)
select order_date, sum... from t... where order_date > '2025-01-01' group by ...; Time: 479.442 ms (-8%)
# AVG queries
select avg(amount) from t_small; Time: 397.363 ms (-29%)
select avg(amount) from t... where order_date > '2025-01-01'; Time: 325.047 ms (-22%)
select order_date, avg(amount) from t_small group by order_date; Time: 750.833 ms (-20%)
select order_date, avg... from t... where order_date > '2025-01-01' group by ...; Time: 547.354 ms (-1%)
# VARIANCE queries
select variance(amount) from t_small; Time: 504.897 ms (-23%)
select variance(amount) from t... where order_date > '2025-01-01'; Time: 405.917 ms (-18%)
select order_date, variance(amount) from t_small group by order_date; Time: 871.387 ms (-17%)
select order_date, variance... from t... where order_date > '2025-01-01' group by Time: 542.256 ms (-11%)
# COMBINED queries
select max, min, sum, avg, variance from t_small; Time: 753.569 ms (-49%)
select max, min, sum, avg, variance from t... where order_date > '2025-01-01'; Time: 524.862 ms (-41%)
select order_date, max, min, sum, avg, variance from t_small group by order_date; Time: 1228.248 ms (-37%)
select order_date, max, min, sum... from t... where order_date > '2025-01-01'... Time: 724.252 ms (-30%)
Feedback and review welcome.
--
Regards, Chenhui Mo,
pgEdge