Hi Chenhui Mo,It's been a while since you posted the patch but it still applies cleanly on latest master.
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.Yes, the code is badly optimized and there's lots of potential to make it faster.
But it's not only the functions you mentioned but pretty much all code.While I think it's a good idea to improve NUMERIC performance, I don't think it's a good idea to only do it for a narrow subset of the functionality. Especially, as you're introducing new custom code that is only used by these functions and makes
them work differently than the rest of numeric.c.cmp_numerics_packed() now duplicates cmp_numerics() and on top can no longer use the existing helper functions / macros such as NUMERIC_IS_NAN(), etc. I think we should instead tackle the problem in a way that all the other code can leverage the
optimizations as well - with minor but similar/identical changes.I don't know if that can truly work for all code. Probably there's some custom code in certain places needed but a big portion should be able to profit from a more
generalized abstraction of "being able to work with packed values".Changing all code at once is likely too big of a patch. What we can do instead is introduce new helpers that eventually can be used by all code. And then migrate the functionality step by step to the new helpers and eventually remove the old ones.
Here are the benchmark results executing on 20M rows using different precisions (NUMERIC(18,2), NUMERIC(38,2), NUMERIC(9,2)):I took your queries and put some scripting around them to more easily run and compare optimizations. We can also extend the query basket as we go to cover more and more functionality that we optimize. The script does a "best of three runs" for each query.
Use the scripts as: SET max_parallel_workers_per_gather = 0 \i benchmark_queries.sql -- run once to load the benchmark function \i benchmark_queries_run.sql My results with a 9700X CPU are attached. The total runtime dropped from 87,271 ms to 74,480 ms. Nice! -- David Geier
query_number |
query_text
| master | patched
------------- |
--------------------------------------------------------------------------------------------------------------------------------------------------------
+ --------------- | ---------------
1 | select max(amount) from t
| 755.095000 | 530.363000
2 | select max(amount) from t where order_date > '2025-01-01'
| 616.705000 | 510.843000
3 | select order_date, max(amount) from t group by order_date
| 1566.814000 | 1391.987000
4 | select order_date, max(amount) from t where order_date >
'2025-01-01' group by order_date
| 931.194000 | 822.004000
5 | select min(amount) from t
| 758.350000 | 523.290000
6 | select min(amount) from t where order_date > '2025-01-01'
| 614.658000 | 502.705000
7 | select order_date, min(amount) from t group by order_date
| 1573.367000 | 1430.723000
8 | select order_date, min(amount) from t where order_date >
'2025-01-01' group by order_date
| 935.755000 | 817.350000
9 | select sum(amount) from t
| 796.459000 | 498.837000
10 | select sum(amount) from t where order_date > '2025-01-01'
| 636.913000 | 491.944000
11 | select order_date, sum(amount) from t group by order_date
| 1639.108000 | 1381.257000
12 | select order_date, sum(amount) from t where order_date >
'2025-01-01' group by order_date
| 950.681000 | 816.838000
13 | select avg(amount) from t
| 787.415000 | 512.653000
14 | select avg(amount) from t where order_date > '2025-01-01'
| 635.941000 | 493.951000
15 | select order_date, avg(amount) from t group by order_date
| 1619.582000 | 1381.696000
16 | select order_date, avg(amount) from t where order_date >
'2025-01-01' group by order_date
| 950.122000 | 818.519000
17 | select variance(amount) from t
| 1091.719000 | 907.796000
18 | select variance(amount) from t where order_date > '2025-01-01'
| 780.570000 | 697.514000
19 | select order_date, variance(amount) from t group by order_date
| 1937.132000 | 1758.731000
20 | select order_date, variance(amount) from t where order_date >
'2025-01-01' group by order_date
| 1106.058000 | 1005.355000
21 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t
| 2194.749000 | 1626.647000
22 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t where order_date > '2025-01-01'
| 1333.256000 | 1055.262000
23 | select order_date, max(amount), min(amount), sum(amount),
avg(amount), variance(amount) from t group by order_date
| 3073.376000 | 2576.629000
24 | 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 | 1657.325000 | 1399.508000
25 | select max(amount) from t_big
| 768.123000 | 667.837000
26 | select max(amount) from t_big where order_date > '2025-01-01'
| 615.969000 | 575.293000
27 | select order_date, max(amount) from t_big group by order_date
| 1607.008000 | 1509.677000
28 | select order_date, max(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 935.139000 | 869.321000
29 | select min(amount) from t_big
| 643.622000 | 524.047000
30 | select min(amount) from t_big where order_date > '2025-01-01'
| 558.872000 | 499.286000
31 | select order_date, min(amount) from t_big group by order_date
| 1462.319000 | 1433.587000
32 | select order_date, min(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 886.861000 | 822.549000
33 | select sum(amount) from t_big
| 726.139000 | 542.414000
34 | select sum(amount) from t_big where order_date > '2025-01-01'
| 595.964000 | 514.314000
35 | select order_date, sum(amount) from t_big group by order_date
| 1583.034000 | 1393.443000
36 | select order_date, sum(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 914.099000 | 822.920000
37 | select avg(amount) from t_big
| 715.332000 | 542.270000
38 | select avg(amount) from t_big where order_date > '2025-01-01'
| 598.624000 | 512.919000
39 | select order_date, avg(amount) from t_big group by order_date
| 1572.520000 | 1411.037000
40 | select order_date, avg(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 920.763000 | 833.181000
41 | select variance(amount) from t_big
| 1405.280000 | 1289.609000
42 | select variance(amount) from t_big where order_date >
'2025-01-01'
| 944.228000 | 899.942000
43 | select order_date, variance(amount) from t_big group by
order_date
| 2298.828000 | 2165.781000
44 | select order_date, variance(amount) from t_big where order_date
> '2025-01-01' group by order_date
| 1309.516000 | 1212.564000
45 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big
| 2373.852000 | 2109.934000
46 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big where order_date > '2025-01-01'
| 1427.308000 | 1287.208000
47 | select order_date, max(amount), min(amount), sum(amount),
avg(amount), variance(amount) from t_big group by order_date
| 3312.573000 | 3093.991000
48 | 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 | 1773.448000 | 1635.395000
49 | select max(amount) from t_small
| 760.999000 | 556.076000
50 | select max(amount) from t_small where order_date > '2025-01-01'
| 620.693000 | 513.795000
51 | select order_date, max(amount) from t_small group by order_date
| 1590.856000 | 1377.047000
52 | select order_date, max(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 935.515000 | 822.892000
53 | select min(amount) from t_small
| 734.822000 | 499.962000
54 | select min(amount) from t_small where order_date > '2025-01-01'
| 602.871000 | 481.942000
55 | select order_date, min(amount) from t_small group by order_date
| 1548.656000 | 1376.220000
56 | select order_date, min(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 933.155000 | 808.288000
57 | select sum(amount) from t_small
| 792.655000 | 522.847000
58 | select sum(amount) from t_small where order_date > '2025-01-01'
| 637.174000 | 502.814000
59 | select order_date, sum(amount) from t_small group by order_date
| 1644.730000 | 1390.817000
60 | select order_date, sum(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 957.482000 | 815.457000
61 | select avg(amount) from t_small
| 789.809000 | 522.819000
62 | select avg(amount) from t_small where order_date > '2025-01-01'
| 634.651000 | 498.279000
63 | select order_date, avg(amount) from t_small group by order_date
| 1630.759000 | 1390.293000
64 | select order_date, avg(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 944.089000 | 817.813000
65 | select variance(amount) from t_small
| 1022.059000 | 846.510000
66 | select variance(amount) from t_small where order_date >
'2025-01-01'
| 753.993000 | 672.540000
67 | select order_date, variance(amount) from t_small group by
order_date
| 1885.687000 | 1708.292000
68 | select order_date, variance(amount) from t_small where
order_date > '2025-01-01' group by order_date
| 1081.340000 | 980.163000
69 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small
| 2058.858000 | 1508.958000
70 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small where order_date > '2025-01-01'
| 1273.651000 | 988.353000
71 | select order_date, max(amount), min(amount), sum(amount),
avg(amount), variance(amount) from t_small group by order_date
| 2942.705000 | 2426.668000
72 | 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 | 1602.466000 | 1330.515000
| TOTAL
| 87271.440000 | 74480.281000
(73 rows)
benchmark_queries_run.sql
Description: application/sql
benchmark_queries.sql
Description: application/sql
