> It's attached. I saw really good results on x86 but some regressions on > M5. That's why I started working on centralizing the case distinction > work scattered throughout all the macros in set_var_from_num() and have > all functions use that. This also makes the code a lot nicer. This > change isn't complete yet, so I cannot send it to you. > > I'll share the results on my box with you tomorrow. Right now, I don't > have access to my benchmark machine.
Attached are the results from my AMD Ryzen 7 9700X. You can see that there are lots of speedups but also lots of regressions. The regressions stem from the additional case distinction for if it's a packed / unpacked varlena that are repeatedly scattered throughout the code via the NUMERIC_ macros. I also attached the benchmark script again. The version in my other mail had a few small issues. I also attached the patch that shows the idea of having all functions go via init_var_from_num() and having all "parsing" code happen there in a single place, only called once per function. -- David Geier
From 8fc0815c042b8a70855a5c023cb2c0e52c0969ac Mon Sep 17 00:00:00 2001 From: David Geier <[email protected]> Date: Mon, 5 Oct 2026 08:29:17 +0200 Subject: [PATCH v1] Optimize NUMERIC by having all code go through init_var_from_num() --- src/backend/utils/adt/numeric.c | 136 +++++++++++++++++--------------- src/include/utils/numeric.h | 7 ++ 2 files changed, 80 insertions(+), 63 deletions(-) diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c index 7deea2046f1..575de10430b 100644 --- a/src/backend/utils/adt/numeric.c +++ b/src/backend/utils/adt/numeric.c @@ -507,7 +507,7 @@ static bool set_var_from_non_decimal_integer_str(const char *str, const char **endptr, Node *escontext); static void set_var_from_num(Numeric num, NumericVar *dest); -static void init_var_from_num(Numeric num, NumericVar *dest); +static void init_var_from_num(const Numeric num, NumericVar *dest); static void set_var_from_var(const NumericVar *value, NumericVar *dest); static char *get_str_from_var(const NumericVar *var); static char *get_str_from_var_sci(const NumericVar *var, int rscale); @@ -2285,8 +2285,8 @@ numeric_abbrev_abort(int memtupcount, SortSupport ssup) static int numeric_fast_cmp(Datum x, Datum y, SortSupport ssup) { - Numeric nx = DatumGetNumeric(x); - Numeric ny = DatumGetNumeric(y); + Numeric nx = DatumGetNumericPacked(x); + Numeric ny = DatumGetNumericPacked(y); int result; result = cmp_numerics(nx, ny); @@ -2418,15 +2418,12 @@ numeric_abbrev_convert_var(const NumericVar *var, NumericSortSupport *nss) Datum numeric_cmp(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); int result; result = cmp_numerics(num1, num2); - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_INT32(result); } @@ -2434,90 +2431,72 @@ numeric_cmp(PG_FUNCTION_ARGS) Datum numeric_eq(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); bool result; result = cmp_numerics(num1, num2) == 0; - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_BOOL(result); } Datum numeric_ne(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); bool result; result = cmp_numerics(num1, num2) != 0; - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_BOOL(result); } Datum numeric_gt(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); bool result; result = cmp_numerics(num1, num2) > 0; - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_BOOL(result); } Datum numeric_ge(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); bool result; result = cmp_numerics(num1, num2) >= 0; - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_BOOL(result); } Datum numeric_lt(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); bool result; result = cmp_numerics(num1, num2) < 0; - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_BOOL(result); } Datum numeric_le(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); bool result; result = cmp_numerics(num1, num2) <= 0; - PG_FREE_IF_COPY(num1, 0); - PG_FREE_IF_COPY(num2, 1); - PG_RETURN_BOOL(result); } @@ -2525,51 +2504,55 @@ static int cmp_numerics(Numeric num1, Numeric num2) { int result; + NumericVar var1, var2; + + init_var_from_num(num1, &var1); + init_var_from_num(num2, &var2); /* * We consider all NANs to be equal and larger than any non-NAN (including * Infinity). This is somewhat arbitrary; the important thing is to have * a consistent sort order. */ - if (NUMERIC_IS_SPECIAL(num1)) + if (var1.sign == NUMERIC_PINF || var1.sign == NUMERIC_NINF || var1.sign == NUMERIC_NAN) { - if (NUMERIC_IS_NAN(num1)) + if (var1.sign == NUMERIC_NAN) { - if (NUMERIC_IS_NAN(num2)) + if (var2.sign == NUMERIC_NAN) result = 0; /* NAN = NAN */ else result = 1; /* NAN > non-NAN */ } - else if (NUMERIC_IS_PINF(num1)) + else if (var1.sign == NUMERIC_PINF) { - if (NUMERIC_IS_NAN(num2)) + if (var2.sign == NUMERIC_NAN) result = -1; /* PINF < NAN */ - else if (NUMERIC_IS_PINF(num2)) + else if (var2.sign == NUMERIC_POS) result = 0; /* PINF = PINF */ else result = 1; /* PINF > anything else */ } else /* num1 must be NINF */ { - if (NUMERIC_IS_NINF(num2)) + if (var2.sign == NUMERIC_NINF) result = 0; /* NINF = NINF */ else result = -1; /* NINF < anything else */ } } - else if (NUMERIC_IS_SPECIAL(num2)) + else if (var2.sign == NUMERIC_PINF || var2.sign == NUMERIC_NINF || var2.sign == NUMERIC_NAN) { - if (NUMERIC_IS_NINF(num2)) + if (var2.sign == NUMERIC_NINF) result = 1; /* normal > NINF */ else result = -1; /* normal < NAN or PINF */ } else { - result = cmp_var_common(NUMERIC_DIGITS(num1), NUMERIC_NDIGITS(num1), - NUMERIC_WEIGHT(num1), NUMERIC_SIGN(num1), - NUMERIC_DIGITS(num2), NUMERIC_NDIGITS(num2), - NUMERIC_WEIGHT(num2), NUMERIC_SIGN(num2)); + result = cmp_var_common(var1.digits, var1.ndigits, + var1.weight, var1.sign, + var2.digits, var2.ndigits, + var2.weight, var2.sign); } return result; @@ -3453,8 +3436,8 @@ numeric_inc(PG_FUNCTION_ARGS) Datum numeric_smaller(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); /* * Use cmp_numerics so that this will agree with the comparison operators, @@ -3475,8 +3458,8 @@ numeric_smaller(PG_FUNCTION_ARGS) Datum numeric_larger(PG_FUNCTION_ARGS) { - Numeric num1 = PG_GETARG_NUMERIC(0); - Numeric num2 = PG_GETARG_NUMERIC(1); + Numeric num1 = PG_GETARG_NUMERIC_PACKED(0); + Numeric num2 = PG_GETARG_NUMERIC_PACKED(1); /* * Use cmp_numerics so that this will agree with the comparison operators, @@ -7270,15 +7253,42 @@ set_var_from_num(Numeric num, NumericVar *dest) * propagate to the original Numeric! It's OK to use it as the destination * argument of one of the calculational functions, though. */ -static void -init_var_from_num(Numeric num, NumericVar *dest) +inline static void +init_var_from_num(const Numeric num, NumericVar *dest) { - dest->ndigits = NUMERIC_NDIGITS(num); - dest->weight = NUMERIC_WEIGHT(num); - dest->sign = NUMERIC_SIGN(num); - dest->dscale = NUMERIC_DSCALE(num); - dest->digits = NUMERIC_DIGITS(num); - dest->buf = NULL; /* digits array is not palloc'd */ + uint16 header; + bool isHeaderShort; + const char *varData = VARDATA_ANY(num); + const int varSize = VARSIZE_ANY_EXHDR(num); + + memcpy(&header, varData, sizeof(header)); + isHeaderShort = ((header & 0x8000) != 0); + + dest->buf = NULL; /* digits array is not palloc'd */ + + if (isHeaderShort) + { + dest->ndigits = ((varSize - sizeof(uint16)) / sizeof(NumericDigit)); + dest->weight = (header & NUMERIC_SHORT_WEIGHT_SIGN_MASK ? ~NUMERIC_SHORT_WEIGHT_MASK : 0) | (header & NUMERIC_SHORT_WEIGHT_MASK); + + dest->sign = (header & NUMERIC_SIGN_MASK) == NUMERIC_SHORT ? + ((header & NUMERIC_SHORT_SIGN_MASK) ? + NUMERIC_NEG : NUMERIC_POS) : (header & NUMERIC_EXT_SIGN_MASK); + + dest->dscale = (header & NUMERIC_SHORT_DSCALE_MASK) >> NUMERIC_SHORT_DSCALE_SHIFT; + dest->digits = (NumericDigit *)(varData + sizeof(header)); + } + else + { + int16 weight; + memcpy(&weight, varData+sizeof(header), sizeof(weight)); + + dest->ndigits = ((varSize - sizeof(uint16) - sizeof(int16)) / sizeof(NumericDigit)); + dest->weight = weight; + dest->sign = ((header & NUMERIC_SIGN_MASK) == NUMERIC_SPECIAL ? (header & NUMERIC_EXT_SIGN_MASK) : (header & NUMERIC_SIGN_MASK)); + dest->dscale = header & NUMERIC_DSCALE_MASK; + dest->digits = (NumericDigit *)(varData + sizeof(header) + sizeof(weight)); + } } diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h index ea289dabfeb..f513d108eee 100644 --- a/src/include/utils/numeric.h +++ b/src/include/utils/numeric.h @@ -66,6 +66,12 @@ DatumGetNumeric(Datum X) return (Numeric) PG_DETOAST_DATUM(X); } +static inline Numeric +DatumGetNumericPacked(Datum X) +{ + return (Numeric) PG_DETOAST_DATUM_PACKED(X); +} + static inline Numeric DatumGetNumericCopy(Datum X) { @@ -79,6 +85,7 @@ NumericGetDatum(Numeric X) } #define PG_GETARG_NUMERIC(n) DatumGetNumeric(PG_GETARG_DATUM(n)) +#define PG_GETARG_NUMERIC_PACKED(n) DatumGetNumericPacked(PG_GETARG_DATUM(n)) #define PG_GETARG_NUMERIC_COPY(n) DatumGetNumericCopy(PG_GETARG_DATUM(n)) #define PG_RETURN_NUMERIC(x) return NumericGetDatum(x) -- 2.53.0
postgres=# select a.query_number, a.query_text, a.min_runtime_ms,
b.min_runtime_ms, trunc(a.min_runtime_ms / b.min_runtime_ms,2) as speedup from
results_master a, results_opt b where a.query_number = b.query_number;
query_number |
query_text
| min_runtime_ms | min_runtime_ms | speedup
--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+---------
1 | select max(amount) from t
| 37.207000 | 35.135000 | 1.05
2 | select max(amount) from t where order_date > '2025-01-01'
| 34.862000 | 33.014000 | 1.05
3 | select order_date, max(amount) from t group by order_date
| 93.536000 | 90.528000 | 1.03
4 | select order_date, max(amount) from t where order_date >
'2025-01-01' group by order_date
| 54.587000 | 53.723000 | 1.01
5 | select min(amount) from t
| 36.595000 | 33.967000 | 1.07
6 | select min(amount) from t where order_date > '2025-01-01'
| 33.924000 | 33.063000 | 1.02
7 | select order_date, min(amount) from t group by order_date
| 97.666000 | 94.392000 | 1.03
8 | select order_date, min(amount) from t where order_date >
'2025-01-01' group by order_date
| 57.328000 | 55.677000 | 1.02
9 | select sum(amount) from t
| 44.599000 | 48.683000 | 0.91
10 | select sum(amount) from t where order_date > '2025-01-01'
| 38.894000 | 32.462000 | 1.19
11 | select order_date, sum(amount) from t group by order_date
| 103.263000 | 105.811000 | 0.97
12 | select order_date, sum(amount) from t where order_date >
'2025-01-01' group by order_date
| 60.231000 | 53.393000 | 1.12
13 | select avg(amount) from t
| 44.865000 | 49.007000 | 0.91
14 | select avg(amount) from t where order_date > '2025-01-01'
| 38.906000 | 32.363000 | 1.20
15 | select order_date, avg(amount) from t group by order_date
| 104.275000 | 105.785000 | 0.98
16 | select order_date, avg(amount) from t where order_date >
'2025-01-01' group by order_date
| 60.493000 | 54.393000 | 1.11
17 | select variance(amount) from t
| 67.175000 | 58.197000 | 1.15
18 | select variance(amount) from t where order_date > '2025-01-01'
| 49.954000 | 44.792000 | 1.11
19 | select order_date, variance(amount) from t group by order_date
| 128.421000 | 114.343000 | 1.12
20 | select order_date, variance(amount) from t where order_date >
'2025-01-01' group by order_date
| 71.869000 | 65.615000 | 1.09
21 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t
| 117.653000 | 111.448000 | 1.05
22 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t where order_date > '2025-01-01'
| 74.422000 | 65.187000 | 1.14
23 | select order_date, max(amount), min(amount), sum(amount),
avg(amount), variance(amount) from t group by order_date
| 187.896000 | 179.281000 | 1.04
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 | 99.881000 | 89.572000 | 1.11
25 | select count(*) from t where amount = 12345.67
| 35.842000 | 30.482000 | 1.17
26 | select count(*) from t where amount <> 0
| 42.990000 | 38.330000 | 1.12
27 | select count(*) from t where amount > 10000
| 44.079000 | 39.403000 | 1.11
28 | select count(*) from t where amount >= 10000
| 43.917000 | 39.488000 | 1.11
29 | select count(*) from t where amount < 50000
| 35.175000 | 31.346000 | 1.12
30 | select count(*) from t where amount <= 50000
| 34.843000 | 31.025000 | 1.12
31 | select count(*) from t where amount > amount * 0.5
| 79.274000 | 74.537000 | 1.06
32 | select count(*) from t where amount + 100 > amount * 1.1
| 124.406000 | 100.841000 | 1.23
33 | select sum(amount + 100) from t
| 93.809000 | 82.846000 | 1.13
34 | select sum(amount - 50) from t
| 76.000000 | 82.179000 | 0.92
35 | select sum(amount * 2) from t
| 73.238000 | 82.713000 | 0.88
36 | select sum(amount / 3) from t
| 97.425000 | 126.179000 | 0.77
37 | select sum(amount % 10) from t
| 104.109000 | 113.726000 | 0.91
38 | select count(*) from t where amount * 2 > 100000
| 72.689000 | 73.716000 | 0.98
39 | select count(*) from t where amount / 10 > 5000
| 84.816000 | 87.135000 | 0.97
40 | select count(*) from t where amount + amount > 200000
| 119.240000 | 90.903000 | 1.31
41 | select sum((amount + 100) * 0.95) from t
| 121.331000 | 113.015000 | 1.07
42 | select sum(amount * 1.08 - amount * 0.03) from t
| 155.628000 | 171.502000 | 0.90
43 | select sum(abs(amount)) from t
| 48.679000 | 58.156000 | 0.83
44 | select sum(abs(amount - 50000)) from t
| 80.633000 | 90.019000 | 0.89
45 | select count(*) from t where abs(amount - 50000) < 10000
| 70.389000 | 73.136000 | 0.96
46 | select sum(round(amount, 0)) from t
| 70.401000 | 79.536000 | 0.88
47 | select sum(round(amount, 1)) from t
| 73.561000 | 82.481000 | 0.89
48 | select sum(trunc(amount, 0)) from t
| 64.868000 | 73.435000 | 0.88
49 | select sum(trunc(amount, 1)) from t
| 67.459000 | 75.519000 | 0.89
50 | select sum(floor(amount)) from t
| 74.354000 | 81.228000 | 0.91
51 | select sum(ceil(amount)) from t
| 108.134000 | 100.624000 | 1.07
52 | select sum(ceiling(amount)) from t
| 108.207000 | 101.320000 | 1.06
53 | select sum(sign(amount)) from t
| 48.434000 | 56.830000 | 0.85
54 | select count(*) from t where sign(amount) > 0
| 46.291000 | 47.195000 | 0.98
55 | select sum(power(amount, 2)) from t
| 148.185000 | 155.305000 | 0.95
56 | select sum(sqrt(abs(amount))) from t
| 128.248000 | 134.172000 | 0.95
57 | select sum(mod(amount, 100)) from t
| 104.587000 | 114.414000 | 0.91
58 | select count(*) from t where mod(amount, 10) = 0
| 92.812000 | 94.375000 | 0.98
59 | select count(*) from t where amount % 1000 < 100
| 98.406000 | 99.133000 | 0.99
60 | select count(*), width_bucket(amount, 0, 100000, 10) from t
group by width_bucket(amount, 0, 100000, 10)
| 142.559000 | 126.292000 | 1.12
61 | select sum(round(abs(amount - 50000), 2)) from t
| 103.929000 | 113.465000 | 0.91
62 | select sum(floor(amount / 100) * 100) from t
| 148.440000 | 157.637000 | 0.94
63 | select sum(ceil(amount / 100) * 100) from t
| 180.442000 | 196.152000 | 0.91
64 | select sum(case when amount > 50000 then amount * 0.9 else
amount * 1.1 end) from t
| 88.848000 | 96.303000 | 0.92
65 | select amount from t order by amount limit 1000
| 53.853000 | 46.797000 | 1.15
66 | select amount from t order by amount desc limit 1000
| 55.463000 | 47.042000 | 1.17
67 | select amount from t order by abs(amount - 50000) limit 1000
| 122.718000 | 110.552000 | 1.11
68 | select max(amount) from t_big
| 45.437000 | 43.015000 | 1.05
69 | select max(amount) from t_big where order_date > '2025-01-01'
| 39.094000 | 38.203000 | 1.02
70 | select order_date, max(amount) from t_big group by order_date
| 101.424000 | 98.289000 | 1.03
71 | select order_date, max(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 58.907000 | 58.383000 | 1.00
72 | select min(amount) from t_big
| 36.546000 | 34.168000 | 1.06
73 | select min(amount) from t_big where order_date > '2025-01-01'
| 34.865000 | 33.758000 | 1.03
74 | select order_date, min(amount) from t_big group by order_date
| 92.644000 | 90.148000 | 1.02
75 | select order_date, min(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 54.795000 | 53.855000 | 1.01
76 | select sum(amount) from t_big
| 45.947000 | 51.015000 | 0.90
77 | select sum(amount) from t_big where order_date > '2025-01-01'
| 40.091000 | 34.442000 | 1.16
78 | select order_date, sum(amount) from t_big group by order_date
| 105.520000 | 108.826000 | 0.96
79 | select order_date, sum(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 62.128000 | 55.687000 | 1.11
80 | select avg(amount) from t_big
| 46.385000 | 50.987000 | 0.90
81 | select avg(amount) from t_big where order_date > '2025-01-01'
| 40.579000 | 33.781000 | 1.20
82 | select order_date, avg(amount) from t_big group by order_date
| 106.123000 | 108.882000 | 0.97
83 | select order_date, avg(amount) from t_big where order_date >
'2025-01-01' group by order_date
| 61.623000 | 55.116000 | 1.11
84 | select variance(amount) from t_big
| 78.844000 | 69.145000 | 1.14
85 | select variance(amount) from t_big where order_date >
'2025-01-01'
| 56.902000 | 51.521000 | 1.10
86 | select order_date, variance(amount) from t_big group by
order_date
| 140.798000 | 128.268000 | 1.09
87 | select order_date, variance(amount) from t_big where order_date
> '2025-01-01' group by order_date
| 77.794000 | 72.607000 | 1.07
88 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big
| 140.722000 | 134.958000 | 1.04
89 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_big where order_date > '2025-01-01'
| 87.243000 | 76.104000 | 1.14
90 | select order_date, max(amount), min(amount), sum(amount),
avg(amount), variance(amount) from t_big group by order_date
| 214.249000 | 195.731000 | 1.09
91 | 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 | 112.783000 | 99.273000 | 1.13
92 | select count(*) from t_big where amount = 12345.67
| 36.033000 | 31.150000 | 1.15
93 | select count(*) from t_big where amount <> 0
| 44.137000 | 38.387000 | 1.14
94 | select count(*) from t_big where amount > 10000
| 45.445000 | 39.988000 | 1.13
95 | select count(*) from t_big where amount >= 10000
| 45.269000 | 39.685000 | 1.14
96 | select count(*) from t_big where amount < 50000
| 35.672000 | 30.700000 | 1.16
97 | select count(*) from t_big where amount <= 50000
| 34.706000 | 30.666000 | 1.13
98 | select count(*) from t_big where amount > amount * 0.5
| 82.801000 | 77.671000 | 1.06
99 | select count(*) from t_big where amount + 100 > amount * 1.1
| 132.052000 | 105.272000 | 1.25
100 | select sum(amount + 100) from t_big
| 99.529000 | 86.006000 | 1.15
101 | select sum(amount - 50) from t_big
| 78.175000 | 85.976000 | 0.90
102 | select sum(amount * 2) from t_big
| 75.896000 | 86.348000 | 0.87
103 | select sum(amount / 3) from t_big
| 101.480000 | 130.254000 | 0.77
104 | select sum(amount % 10) from t_big
| 113.176000 | 124.968000 | 0.90
105 | select count(*) from t_big where amount * 2 > 100000
| 75.987000 | 77.380000 | 0.98
106 | select count(*) from t_big where amount / 10 > 5000
| 99.534000 | 102.052000 | 0.97
107 | select count(*) from t_big where amount + amount > 200000
| 130.615000 | 102.151000 | 1.27
108 | select sum((amount + 100) * 0.95) from t_big
| 128.681000 | 119.391000 | 1.07
109 | select sum(amount * 1.08 - amount * 0.03) from t_big
| 170.630000 | 173.068000 | 0.98
110 | select sum(abs(amount)) from t_big
| 50.924000 | 60.757000 | 0.83
111 | select sum(abs(amount - 50000)) from t_big
| 85.357000 | 94.166000 | 0.90
112 | select count(*) from t_big where abs(amount - 50000) < 10000
| 74.255000 | 76.247000 | 0.97
113 | select sum(round(amount, 0)) from t_big
| 71.560000 | 82.079000 | 0.87
114 | select sum(round(amount, 1)) from t_big
| 73.359000 | 81.594000 | 0.89
115 | select sum(trunc(amount, 0)) from t_big
| 65.517000 | 74.512000 | 0.87
116 | select sum(trunc(amount, 1)) from t_big
| 69.489000 | 77.103000 | 0.90
117 | select sum(floor(amount)) from t_big
| 75.628000 | 81.547000 | 0.92
118 | select sum(ceil(amount)) from t_big
| 113.676000 | 104.625000 | 1.08
119 | select sum(ceiling(amount)) from t_big
| 113.067000 | 104.251000 | 1.08
120 | select sum(sign(amount)) from t_big
| 48.976000 | 57.359000 | 0.85
121 | select count(*) from t_big where sign(amount) > 0
| 47.723000 | 47.576000 | 1.00
122 | select sum(power(amount, 2)) from t_big
| 152.940000 | 161.049000 | 0.94
123 | select sum(sqrt(abs(amount))) from t_big
| 128.596000 | 135.806000 | 0.94
124 | select sum(mod(amount, 100)) from t_big
| 112.668000 | 124.433000 | 0.90
125 | select count(*) from t_big where mod(amount, 10) = 0
| 104.290000 | 107.259000 | 0.97
126 | select count(*) from t_big where amount % 1000 < 100
| 107.427000 | 110.313000 | 0.97
127 | select count(*), width_bucket(amount, 0, 100000, 10) from t_big
group by width_bucket(amount, 0, 100000, 10)
| 142.988000 | 126.517000 | 1.13
128 | select sum(round(abs(amount - 50000), 2)) from t_big
| 107.746000 | 123.372000 | 0.87
129 | select sum(floor(amount / 100) * 100) from t_big
| 159.023000 | 192.001000 | 0.82
130 | select sum(ceil(amount / 100) * 100) from t_big
| 200.910000 | 214.051000 | 0.93
131 | select sum(case when amount > 50000 then amount * 0.9 else
amount * 1.1 end) from t_big
| 92.476000 | 98.485000 | 0.93
132 | select amount from t_big order by amount limit 1000
| 51.809000 | 45.621000 | 1.13
133 | select amount from t_big order by amount desc limit 1000
| 483.211000 | 327.030000 | 1.47
134 | select amount from t_big order by abs(amount - 50000) limit
1000
| 125.126000 | 112.314000 | 1.11
135 | select max(amount) from t_small
| 37.752000 | 34.751000 | 1.08
136 | select max(amount) from t_small where order_date > '2025-01-01'
| 35.189000 | 33.090000 | 1.06
137 | select order_date, max(amount) from t_small group by order_date
| 95.469000 | 90.887000 | 1.05
138 | select order_date, max(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 55.330000 | 53.988000 | 1.02
139 | select min(amount) from t_small
| 36.392000 | 32.258000 | 1.12
140 | select min(amount) from t_small where order_date > '2025-01-01'
| 34.382000 | 32.269000 | 1.06
141 | select order_date, min(amount) from t_small group by order_date
| 96.130000 | 92.650000 | 1.03
142 | select order_date, min(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 56.539000 | 55.730000 | 1.01
143 | select sum(amount) from t_small
| 45.603000 | 32.635000 | 1.39
144 | select sum(amount) from t_small where order_date > '2025-01-01'
| 39.797000 | 32.422000 | 1.22
145 | select order_date, sum(amount) from t_small group by order_date
| 104.553000 | 92.739000 | 1.12
146 | select order_date, sum(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 60.814000 | 53.763000 | 1.13
147 | select avg(amount) from t_small
| 45.614000 | 48.274000 | 0.94
148 | select avg(amount) from t_small where order_date > '2025-01-01'
| 39.551000 | 32.963000 | 1.19
149 | select order_date, avg(amount) from t_small group by order_date
| 106.068000 | 96.617000 | 1.09
150 | select order_date, avg(amount) from t_small where order_date >
'2025-01-01' group by order_date
| 60.794000 | 53.987000 | 1.12
151 | select variance(amount) from t_small
| 63.783000 | 52.839000 | 1.20
152 | select variance(amount) from t_small where order_date >
'2025-01-01'
| 48.649000 | 42.711000 | 1.13
153 | select order_date, variance(amount) from t_small group by
order_date
| 125.276000 | 113.031000 | 1.10
154 | select order_date, variance(amount) from t_small where
order_date > '2025-01-01' group by order_date
| 70.543000 | 63.924000 | 1.10
155 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small
| 113.254000 | 105.688000 | 1.07
156 | select max(amount), min(amount), sum(amount), avg(amount),
variance(amount) from t_small where order_date > '2025-01-01'
| 74.560000 | 61.704000 | 1.20
157 | select order_date, max(amount), min(amount), sum(amount),
avg(amount), variance(amount) from t_small group by order_date
| 180.042000 | 170.740000 | 1.05
158 | 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 | 95.715000 | 86.882000 | 1.10
159 | select count(*) from t_small where amount = 123.45
| 36.035000 | 30.686000 | 1.17
160 | select count(*) from t_small where amount <> 0
| 44.581000 | 38.283000 | 1.16
161 | select count(*) from t_small where amount > 100
| 45.893000 | 39.671000 | 1.15
162 | select count(*) from t_small where amount >= 100
| 45.983000 | 39.567000 | 1.16
163 | select count(*) from t_small where amount < 500
| 36.919000 | 30.786000 | 1.19
164 | select count(*) from t_small where amount <= 500
| 35.827000 | 30.604000 | 1.17
165 | select count(*) from t_small where amount > amount * 0.5
| 80.670000 | 74.864000 | 1.07
166 | select count(*) from t_small where amount + 10 > amount * 1.1
| 126.515000 | 99.698000 | 1.26
167 | select sum(amount + 10) from t_small
| 93.520000 | 82.844000 | 1.12
168 | select sum(amount - 5) from t_small
| 76.561000 | 82.693000 | 0.92
169 | select sum(amount * 2) from t_small
| 73.469000 | 82.568000 | 0.88
170 | select sum(amount / 3) from t_small
| 96.687000 | 103.316000 | 0.93
171 | select sum(amount % 10) from t_small
| 100.616000 | 107.888000 | 0.93
172 | select count(*) from t_small where amount * 2 > 1000
| 74.392000 | 74.391000 | 1.00
173 | select count(*) from t_small where amount / 10 > 50
| 88.807000 | 87.588000 | 1.01
174 | select count(*) from t_small where amount + amount > 2000
| 113.316000 | 85.034000 | 1.33
175 | select sum((amount + 10) * 0.95) from t_small
| 120.130000 | 112.682000 | 1.06
176 | select sum(amount * 1.08 - amount * 0.03) from t_small
| 147.971000 | 154.378000 | 0.95
177 | select sum(abs(amount)) from t_small
| 51.734000 | 59.992000 | 0.86
178 | select sum(abs(amount - 500)) from t_small
| 82.462000 | 91.348000 | 0.90
179 | select count(*) from t_small where abs(amount - 500) < 100
| 73.058000 | 74.333000 | 0.98
180 | select sum(round(amount, 0)) from t_small
| 71.027000 | 79.738000 | 0.89
181 | select sum(round(amount, 1)) from t_small
| 74.158000 | 80.298000 | 0.92
182 | select sum(trunc(amount, 0)) from t_small
| 67.506000 | 74.940000 | 0.90
183 | select sum(trunc(amount, 1)) from t_small
| 68.810000 | 75.383000 | 0.91
184 | select sum(floor(amount)) from t_small
| 76.531000 | 82.561000 | 0.92
185 | select sum(ceil(amount)) from t_small
| 109.591000 | 99.996000 | 1.09
186 | select sum(ceiling(amount)) from t_small
| 109.364000 | 100.901000 | 1.08
187 | select sum(sign(amount)) from t_small
| 50.602000 | 56.549000 | 0.89
188 | select count(*) from t_small where sign(amount) > 0
| 47.192000 | 46.904000 | 1.00
189 | select sum(power(amount, 2)) from t_small
| 132.877000 | 139.021000 | 0.95
190 | select sum(sqrt(abs(amount))) from t_small
| 130.652000 | 137.724000 | 0.94
191 | select sum(mod(amount, 100)) from t_small
| 99.057000 | 107.770000 | 0.91
192 | select count(*) from t_small where mod(amount, 10) = 0
| 90.182000 | 88.471000 | 1.01
193 | select count(*) from t_small where amount % 1000 < 100
| 92.247000 | 92.246000 | 1.00
194 | select count(*), width_bucket(amount, 0, 1000, 10) from t_small
group by width_bucket(amount, 0, 1000, 10)
| 139.562000 | 119.337000 | 1.16
195 | select sum(round(abs(amount - 500), 2)) from t_small
| 106.698000 | 113.952000 | 0.93
196 | select sum(floor(amount / 100) * 100) from t_small
| 146.958000 | 158.293000 | 0.92
197 | select sum(ceil(amount / 100) * 100) from t_small
| 182.747000 | 167.847000 | 1.08
198 | select sum(case when amount > 500 then amount * 0.9 else amount
* 1.1 end) from t_small
| 89.692000 | 78.958000 | 1.13
199 | select amount from t_small order by amount limit 1000
| 55.155000 | 47.433000 | 1.16
200 | select amount from t_small order by amount desc limit 1000
| 57.127000 | 47.400000 | 1.20
201 | select amount from t_small order by abs(amount - 500) limit
1000
| 123.531000 | 111.545000 | 1.10
202 | TOTAL
| 17265.426000 | 16794.680000 | 1.02
numeric_benchmark.sql
Description: application/sql
