Subject: [PATCH] Optimize numeric comparisons and aggregations via packed-datum extraction

From: "ChenhuiMo" <[email-masked]>
Date: Sat, 4 Apr 2026 23:29:06 +0800

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.&nbsp; 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.&nbsp; 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.&nbsp; 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
&nbsp; &nbsp; DATE '2024-01-01' + (gs % 730),
&nbsp; &nbsp; gs::numeric(18, 2) * gs % 998244353 * gs % 1000000007 * 133.31
from
&nbsp; &nbsp; 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
&nbsp; &nbsp; DATE '2024-01-01' + (gs % 730),
&nbsp; &nbsp; gs::numeric(38, 2) * gs * gs * 133.31
from
&nbsp; &nbsp; 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
&nbsp; &nbsp; DATE '2024-01-01' + (gs % 730),
&nbsp; &nbsp; (gs % 1331) * (gs % 1331) * 1.31
from
&nbsp; &nbsp; 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 &gt; '2025-01-01';

select order_date, max(amount) from t group by order_date;
select order_date, max(amount) from t where order_date &gt; '2025-01-01' group by order_date;

select min(amount) from t;
select min(amount) from t where order_date &gt; '2025-01-01';

select order_date, min(amount) from t group by order_date;
select order_date, min(amount) from t where order_date &gt; '2025-01-01' group by order_date;

select sum(amount) from t;
select sum(amount) from t where order_date &gt; '2025-01-01';

select order_date, sum(amount) from t group by order_date;
select order_date, sum(amount) from t where order_date &gt; '2025-01-01' group by order_date;

select avg(amount) from t;
select avg(amount) from t where order_date &gt; '2025-01-01';

select order_date, avg(amount) from t group by order_date;
select order_date, avg(amount) from t where order_date &gt; '2025-01-01' group by order_date;

select variance(amount) from t;
select variance(amount) from t where order_date &gt; '2025-01-01';

select order_date, variance(amount) from t group by order_date;
select order_date, variance(amount) from t where order_date &gt; '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 &gt; '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 &gt; '2025-01-01' group by order_date;

select max(amount) from t_big;
select max(amount) from t_big where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select min(amount) from t_big;
select min(amount) from t_big where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select sum(amount) from t_big;
select sum(amount) from t_big where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select avg(amount) from t_big;
select avg(amount) from t_big where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select variance(amount) from t_big;
select variance(amount) from t_big where order_date &gt; '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 &gt; '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 &gt; '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 &gt; '2025-01-01' group by order_date;

select max(amount) from t_small;
select max(amount) from t_small where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select min(amount) from t_small;
select min(amount) from t_small where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select sum(amount) from t_small;
select sum(amount) from t_small where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select avg(amount) from t_small;
select avg(amount) from t_small where order_date &gt; '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 &gt; '2025-01-01' group by order_date;

select variance(amount) from t_small;
select variance(amount) from t_small where order_date &gt; '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 &gt; '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 &gt; '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 &gt; '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; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 980.844 ms
select max(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 406.842 ms
select order_date, max(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 952.209 ms
select order_date, max(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 571.053 ms

# MIN queries
select min(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 567.840 ms
select min(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 405.364 ms
select order_date, min(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 935.274 ms
select order_date, min(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 546.571 ms

# SUM queries
select sum(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 562.059 ms
select sum(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 400.519 ms
select order_date, sum(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 920.640 ms
select order_date, sum(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 543.760 ms

# AVG queries
select avg(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 566.719 ms
select avg(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 404.249 ms
select order_date, avg(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 922.547 ms
select order_date, avg(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 534.897 ms

# VARIANCE queries
select variance(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 699.547 ms
select variance(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 479.679 ms
select order_date, variance(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1081.712 ms
select order_date, variance(amount) from t where order_date &gt; '2025-01-01' group by &nbsp;Time: 620.472 ms

# COMBINED queries
select max, min, sum, avg, variance from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1686.727 ms
select max, min, sum, avg, variance from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 971.959 ms
select order_date, max, min, sum, avg, variance from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 2206.506 ms
select order_date, max, min, sum... from t where order_date &gt; '2025-01-01'... &nbsp; &nbsp; &nbsp; &nbsp;Time: 1155.119 ms

# ---- Optimized (packed-datum extraction for MAX, MIN, SUM, AVG, VARIANCE) ----
# MAX queries
select max(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 297.890 ms &nbsp;(-70%)
select max(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 261.415 ms &nbsp;(-36%)
select order_date, max(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 702.121 ms &nbsp;(-26%)
select order_date, max(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 407.533 ms &nbsp;(-29%)

# MIN queries
select min(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 301.846 ms &nbsp;(-47%)
select min(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 266.653 ms &nbsp;(-34%)
select order_date, min(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 681.805 ms &nbsp;(-27%)
select order_date, min(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 413.830 ms &nbsp;(-24%)

# SUM queries
select sum(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 300.850 ms &nbsp;(-46%)
select sum(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 282.615 ms &nbsp;(-29%)
select order_date, sum(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 765.818 ms &nbsp;(-17%)
select order_date, sum(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 463.414 ms &nbsp;(-15%)

# AVG queries
select avg(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 327.844 ms &nbsp;(-42%)
select avg(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 365.507 ms &nbsp;(-10%)
select order_date, avg(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 650.977 ms &nbsp;(-29%)
select order_date, avg(amount) from t where order_date &gt; '2025-01-01' group by ...; &nbsp;Time: 430.043 ms &nbsp;(-20%)

# VARIANCE queries
select variance(amount) from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 505.815 ms &nbsp;(-28%)
select variance(amount) from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 403.832 ms &nbsp;(-16%)
select order_date, variance(amount) from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 901.068 ms &nbsp;(-17%)
select order_date, variance(amount) from t where order_date &gt; '2025-01-01' group by &nbsp;Time: 561.622 ms &nbsp;(-9%)

# COMBINED queries
select max, min, sum, avg, variance from t; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 804.479 ms &nbsp;(-52%)
select max, min, sum, avg, variance from t where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 530.819 ms &nbsp;(-45%)
select order_date, max, min, sum, avg, variance from t group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1241.194 ms (-44%)
select order_date, max, min, sum... from t where order_date &gt; '2025-01-01'... &nbsp; &nbsp; &nbsp; &nbsp;Time: 741.431 ms &nbsp;(-36%)

# ====== DATASET: t_big (20M rows, NUMERIC(38,2)) ======
# ---- Baseline (All optimizations reverted) ----
# MAX queries
select max(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1143.060 ms
select max(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 462.994 ms
select order_date, max(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1032.899 ms
select order_date, max... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 556.832 ms

# MIN queries
select min(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 534.970 ms
select min(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 412.798 ms
select order_date, min(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 923.593 ms
select order_date, min... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 565.523 ms

# SUM queries
select sum(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 611.950 ms
select sum(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 476.377 ms
select order_date, sum(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1009.434 ms
select order_date, sum... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 589.432 ms

# AVG queries
select avg(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 601.082 ms
select avg(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 450.408 ms
select order_date, avg(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 1038.965 ms
select order_date, avg... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 670.555 ms

# VARIANCE queries
select variance(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1019.580 ms
select variance(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 658.554 ms
select order_date, variance(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1366.489 ms
select order_date, variance... from t... where order_date &gt; '2025-01-01' group by &nbsp; Time: 766.608 ms

# COMBINED queries
select max, min, sum, avg, variance from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1797.785 ms
select max, min, sum, avg, variance from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp;Time: 1064.796 ms
select order_date, max, min, sum, avg, variance from t_big group by order_date; &nbsp; &nbsp; Time: 2254.985 ms
select order_date, max, min, sum... from t... where order_date &gt; '2025-01-01'... &nbsp; &nbsp;Time: 1189.765 ms

# ---- Optimized (packed-datum extraction for MAX, MIN, SUM, AVG, VARIANCE) ----
# MAX queries
select max(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 457.682 ms &nbsp;(-60%)
select max(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 342.199 ms &nbsp;(-26%)
select order_date, max(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 777.064 ms &nbsp;(-25%)
select order_date, max... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 463.163 ms &nbsp;(-17%)

# MIN queries
select min(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 328.795 ms &nbsp;(-39%)
select min(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 305.936 ms &nbsp;(-26%)
select order_date, min(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 693.899 ms &nbsp;(-25%)
select order_date, min... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 456.664 ms &nbsp;(-19%)

# SUM queries
select sum(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 329.647 ms &nbsp;(-46%)
select sum(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 311.748 ms &nbsp;(-35%)
select order_date, sum(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 703.230 ms &nbsp;(-30%)
select order_date, sum... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 473.354 ms &nbsp;(-20%)

# AVG queries
select avg(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 357.884 ms &nbsp;(-40%)
select avg(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 381.753 ms &nbsp;(-15%)
select order_date, avg(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 707.939 ms &nbsp;(-32%)
select order_date, avg... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 489.593 ms &nbsp;(-27%)

# VARIANCE queries
select variance(amount) from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 723.205 ms &nbsp;(-29%)
select variance(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 481.065 ms &nbsp;(-27%)
select order_date, variance(amount) from t_big group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1273.396 ms (-7%)
select order_date, variance... from t... where order_date &gt; '2025-01-01' group by &nbsp; Time: 692.473 ms &nbsp;(-10%)

# COMBINED queries
select max, min, sum, avg, variance from t_big; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1144.183 ms (-36%)
select max, min, sum, avg, variance from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp;Time: 735.677 ms &nbsp;(-31%)
select order_date, max, min, sum, avg, variance from t_big group by order_date; &nbsp; &nbsp; Time: 1603.737 ms (-29%)
select order_date, max, min, sum... from t... where order_date &gt; '2025-01-01'... &nbsp; &nbsp;Time: 869.972 ms &nbsp;(-27%)

# ====== DATASET: t_small (20M rows, NUMERIC(9,2)) ======
# ---- Baseline (All optimizations reverted) ----
# MAX queries
select max(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 919.394 ms
select max(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 414.951 ms
select order_date, max(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 939.192 ms
select order_date, max... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 548.936 ms

# MIN queries
select min(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 566.213 ms
select min(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 468.580 ms
select order_date, min(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 973.943 ms
select order_date, min... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 536.868 ms

# SUM queries
select sum(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 549.539 ms
select sum(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 405.480 ms
select order_date, sum(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 970.394 ms
select order_date, sum... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 521.120 ms

# AVG queries
select avg(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 557.897 ms
select avg(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 417.120 ms
select order_date, avg(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 937.021 ms
select order_date, avg... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 552.019 ms

# VARIANCE queries
select variance(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 655.459 ms
select variance(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 494.058 ms
select order_date, variance(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1051.335 ms
select order_date, variance... from t... where order_date &gt; '2025-01-01' group by &nbsp; Time: 606.667 ms

# COMBINED queries
select max, min, sum, avg, variance from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 1489.296 ms
select max, min, sum, avg, variance from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp;Time: 892.727 ms
select order_date, max, min, sum, avg, variance from t_small group by order_date; &nbsp; Time: 1940.004 ms
select order_date, max, min, sum... from t... where order_date &gt; '2025-01-01'... &nbsp; &nbsp;Time: 1041.348 ms

# ---- Optimized (packed-datum extraction for MAX, MIN, SUM, AVG, VARIANCE) ----
# MAX queries
select max(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 332.218 ms &nbsp;(-64%)
select max(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 293.562 ms &nbsp;(-29%)
select order_date, max(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 670.589 ms &nbsp;(-29%)
select order_date, max... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 485.264 ms &nbsp;(-12%)

# MIN queries
select min(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 303.559 ms &nbsp;(-46%)
select min(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 330.970 ms &nbsp;(-29%)
select order_date, min(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 726.441 ms &nbsp;(-25%)
select order_date, min... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 455.427 ms &nbsp;(-15%)

# SUM queries
select sum(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 331.040 ms &nbsp;(-40%)
select sum(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 354.306 ms &nbsp;(-13%)
select order_date, sum(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 731.451 ms &nbsp;(-25%)
select order_date, sum... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 479.442 ms &nbsp;(-8%)

# AVG queries
select avg(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 397.363 ms &nbsp;(-29%)
select avg(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 325.047 ms &nbsp;(-22%)
select order_date, avg(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 750.833 ms &nbsp;(-20%)
select order_date, avg... from t... where order_date &gt; '2025-01-01' group by ...; &nbsp; Time: 547.354 ms &nbsp;(-1%)

# VARIANCE queries
select variance(amount) from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 504.897 ms &nbsp;(-23%)
select variance(amount) from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Time: 405.917 ms &nbsp;(-18%)
select order_date, variance(amount) from t_small group by order_date; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 871.387 ms &nbsp;(-17%)
select order_date, variance... from t... where order_date &gt; '2025-01-01' group by &nbsp; Time: 542.256 ms &nbsp;(-11%)

# COMBINED queries
select max, min, sum, avg, variance from t_small; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Time: 753.569 ms &nbsp;(-49%)
select max, min, sum, avg, variance from t... where order_date &gt; '2025-01-01'; &nbsp; &nbsp; &nbsp;Time: 524.862 ms &nbsp;(-41%)
select order_date, max, min, sum, avg, variance from t_small group by order_date; &nbsp; Time: 1228.248 ms (-37%)
select order_date, max, min, sum... from t... where order_date &gt; '2025-01-01'... &nbsp; &nbsp;Time: 724.252 ms &nbsp;(-30%)

Feedback&nbsp;and&nbsp;review&nbsp;welcome.

--
Regards, Chenhui Mo,
pgEdge

---

From: "ChenhuiMo" <[email-masked]>
Date: Sun, 5 Apr 2026 14:58:35 +0800

Hi all,

the CFBot caught a SIGBUS/Alignment issue on strict architectures because 1-byte varlena payloads start at an odd offset, leading to unaligned int16 reads. I've attached a v2 patch that safely copies short unaligned digits to a small stack buffer, preserving the zero-palloc performance benefits while ensuring strict architecture compatibility.

--
Regards, Chenhui Mo,
pgEdge

         原始邮件

发件人：ChenhuiMo <[email-masked]&gt;
发件时间：2026年4月4日 23:29
收件人：pgsql-hackers <[email-masked]&gt;
主题：[PATCH] Optimize numeric comparisons and aggregations via packed-datum extraction

Hi,

The DDLs and DMLs are:

drop table if exists t;

vacuum analyze t;

vacuum analyze t_big;

vacuum analyze t_small;

and the TEST CASEs are:

# MIN queries

# SUM queries

# AVG queries

# VARIANCE queries

# COMBINED queries

# MIN queries

# SUM queries

# AVG queries

# VARIANCE queries

# COMBINED queries

# MIN queries

# SUM queries

# AVG queries

# VARIANCE queries

# COMBINED queries

# MIN queries

# SUM queries

# AVG queries

# VARIANCE queries

# COMBINED queries

# MIN queries

# SUM queries

# AVG queries

# VARIANCE queries

# COMBINED queries

# MIN queries

# SUM queries

# AVG queries

# VARIANCE queries

# COMBINED queries

--
Regards, Chenhui Mo,
pgEdge