> 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

Attachment: numeric_benchmark.sql
Description: application/sql

Reply via email to