Reduce palloc's in numeric operations. - Mailing list pgsql-hackers

Hello, I will propose reduce palloc's in numeric operations.

The numeric operations are slow by nature, but usually it is not
a problem for on-disk operations. Altough the slowdown is
enhanced on on-memory operations.

I inspcted them and found some very short term pallocs. These
palloc's are used for temporary storage for digits of unpaked
numerics.

The formats of numeric digits in packed and unpaked forms are
same. So we can kicked out a part of palloc's using digits in
packed numeric in-place to make unpakced one.

In this patch, I added new function set_var_from_num_nocopy() to
do this. And make use of it for operands which won't modified.

The performance gain seems quite moderate....

'SELECT SUM(numeric_column) FROM on_memory_table' for ten million
rows and about 8 digits numeric runs for 3480 ms aganst original
3930 ms. It's 11% gain.  'SELECT SUM(int_column) FROM
on_memory_table' needed 1570 ms.

Similary 8% gain for about 30 - 50 digits numeric. Performance of
avg(numeric) made no gain in contrast.

Do you think this worth doing?

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 68c1f1d..8e88bd5 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -367,6 +367,7 @@ static void zero_var(NumericVar *var);static const char *set_var_from_str(const char *str, const
char*cp,                 NumericVar *dest);static void set_var_from_num(Numeric value, NumericVar *dest);
 
+static void set_var_from_num_nocopy(Numeric num, NumericVar *dest);static void set_var_from_var(NumericVar *value,
NumericVar*dest);static char *get_str_from_var(NumericVar *var, int dscale);static char
*get_str_from_var_sci(NumericVar*var, int rscale);
 
@@ -540,12 +541,10 @@ numeric_out(PG_FUNCTION_ARGS)     * from rounding.     */    init_var(&x);
-    set_var_from_num(num, &x);
+    set_var_from_num_nocopy(num, &x);    str = get_str_from_var(&x, x.dscale);
-    free_var(&x);
-    PG_RETURN_CSTRING(str);}
@@ -617,11 +616,10 @@ numeric_out_sci(Numeric num, int scale)        return pstrdup("NaN");    init_var(&x);
-    set_var_from_num(num, &x);
+    set_var_from_num_nocopy(num, &x);    str = get_str_from_var_sci(&x, scale);
-    free_var(&x);    return str;}
@@ -696,7 +694,7 @@ numeric_send(PG_FUNCTION_ARGS)    int            i;    init_var(&x);
-    set_var_from_num(num, &x);
+    set_var_from_num_nocopy(num, &x);    pq_begintypsend(&buf);
@@ -707,8 +705,6 @@ numeric_send(PG_FUNCTION_ARGS)    for (i = 0; i < x.ndigits; i++)        pq_sendint(&buf,
x.digits[i],sizeof(NumericDigit));
 
-    free_var(&x);
-    PG_RETURN_BYTEA_P(pq_endtypsend(&buf));}
@@ -1577,15 +1573,13 @@ numeric_add(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    add_var(&arg1, &arg2, &result);    res = make_result(&result);
-    free_var(&arg1);
-    free_var(&arg2);    free_var(&result);    PG_RETURN_NUMERIC(res);
@@ -1620,15 +1614,13 @@ numeric_sub(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    sub_var(&arg1, &arg2, &result);    res = make_result(&result);
-    free_var(&arg1);
-    free_var(&arg2);    free_var(&result);    PG_RETURN_NUMERIC(res);
@@ -1667,15 +1659,13 @@ numeric_mul(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    mul_var(&arg1, &arg2, &result, arg1.dscale + arg2.dscale);    res =
make_result(&result);
-    free_var(&arg1);
-    free_var(&arg2);    free_var(&result);    PG_RETURN_NUMERIC(res);
@@ -1711,8 +1701,8 @@ numeric_div(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    /*     * Select scale for division result
@@ -1726,8 +1716,6 @@ numeric_div(PG_FUNCTION_ARGS)    res = make_result(&result);
-    free_var(&arg1);
-    free_var(&arg2);    free_var(&result);    PG_RETURN_NUMERIC(res);
@@ -1762,8 +1750,8 @@ numeric_div_trunc(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    /*     * Do the divide and return the result
@@ -1772,8 +1760,6 @@ numeric_div_trunc(PG_FUNCTION_ARGS)    res = make_result(&result);
-    free_var(&arg1);
-    free_var(&arg2);    free_var(&result);    PG_RETURN_NUMERIC(res);
@@ -1802,15 +1788,13 @@ numeric_mod(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    mod_var(&arg1, &arg2, &result);    res = make_result(&result);
-    free_var(&result);
-    free_var(&arg2);    free_var(&arg1);    PG_RETURN_NUMERIC(res);
@@ -1980,7 +1964,7 @@ numeric_sqrt(PG_FUNCTION_ARGS)    init_var(&arg);    init_var(&result);
-    set_var_from_num(num, &arg);
+    set_var_from_num_nocopy(num, &arg);    /* Assume the input was normalized, so arg.weight is accurate */    sweight
=(arg.weight + 1) * DEC_DIGITS / 2 - 1;
 
@@ -1998,7 +1982,6 @@ numeric_sqrt(PG_FUNCTION_ARGS)    res = make_result(&result);    free_var(&result);
-    free_var(&arg);    PG_RETURN_NUMERIC(res);}
@@ -2033,7 +2016,7 @@ numeric_exp(PG_FUNCTION_ARGS)    init_var(&arg);    init_var(&result);
-    set_var_from_num(num, &arg);
+    set_var_from_num_nocopy(num, &arg);    /* convert input to float8, ignoring overflow */    val =
numericvar_to_double_no_overflow(&arg);
@@ -2061,7 +2044,6 @@ numeric_exp(PG_FUNCTION_ARGS)    res = make_result(&result);    free_var(&result);
-    free_var(&arg);    PG_RETURN_NUMERIC(res);}
@@ -2091,7 +2073,7 @@ numeric_ln(PG_FUNCTION_ARGS)    init_var(&arg);    init_var(&result);
-    set_var_from_num(num, &arg);
+    set_var_from_num_nocopy(num, &arg);    /* Approx decimal digits before decimal point */    dec_digits =
(arg.weight+ 1) * DEC_DIGITS;
 
@@ -2112,7 +2094,6 @@ numeric_ln(PG_FUNCTION_ARGS)    res = make_result(&result);    free_var(&result);
-    free_var(&arg);    PG_RETURN_NUMERIC(res);}
@@ -2146,8 +2127,8 @@ numeric_log(PG_FUNCTION_ARGS)    init_var(&arg2);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    /*     * Call log_var() to compute and return the result; note it handles
scale
@@ -2158,8 +2139,6 @@ numeric_log(PG_FUNCTION_ARGS)    res = make_result(&result);    free_var(&result);
-    free_var(&arg2);
-    free_var(&arg1);    PG_RETURN_NUMERIC(res);}
@@ -2195,8 +2174,8 @@ numeric_power(PG_FUNCTION_ARGS)    init_var(&arg2_trunc);    init_var(&result);
-    set_var_from_num(num1, &arg1);
-    set_var_from_num(num2, &arg2);
+    set_var_from_num_nocopy(num1, &arg1);
+    set_var_from_num_nocopy(num2, &arg2);    set_var_from_var(&arg2, &arg2_trunc);    trunc_var(&arg2_trunc, 0);
@@ -2227,9 +2206,7 @@ numeric_power(PG_FUNCTION_ARGS)    res = make_result(&result);    free_var(&result);
-    free_var(&arg2);    free_var(&arg2_trunc);
-    free_var(&arg1);    PG_RETURN_NUMERIC(res);}
@@ -2277,9 +2254,8 @@ numeric_int4(PG_FUNCTION_ARGS)    /* Convert to variable format, then convert to int4 */
init_var(&x);
-    set_var_from_num(num, &x);
+    set_var_from_num_nocopy(num, &x);    result = numericvar_to_int4(&x);
-    free_var(&x);    PG_RETURN_INT32(result);}
@@ -2345,15 +2321,13 @@ numeric_int8(PG_FUNCTION_ARGS)    /* Convert to variable format and thence to int8 */
init_var(&x);
-    set_var_from_num(num, &x);
+    set_var_from_num_nocopy(num, &x);    if (!numericvar_to_int8(&x, &result))        ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),                errmsg("bigint out of range")));
 
-    free_var(&x);
-    PG_RETURN_INT64(result);}
@@ -2393,15 +2367,13 @@ numeric_int2(PG_FUNCTION_ARGS)    /* Convert to variable format and thence to int8 */
init_var(&x);
-    set_var_from_num(num, &x);
+    set_var_from_num_nocopy(num, &x);    if (!numericvar_to_int8(&x, &val))        ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),                errmsg("smallint out of range")));
 
-    free_var(&x);
-    /* Down-convert to int2 */    result = (int16) val;
@@ -2764,7 +2736,7 @@ numeric_stddev_internal(ArrayType *transarray,        return make_result(&const_nan);
init_var(&vN);
-    set_var_from_num(N, &vN);
+    set_var_from_num_nocopy(N, &vN);    /*     * Sample stddev and variance are undefined when N <= 1; population
stddev
@@ -2786,9 +2758,9 @@ numeric_stddev_internal(ArrayType *transarray,    sub_var(&vN, &const_one, &vNminus1);
init_var(&vsumX);
-    set_var_from_num(sumX, &vsumX);
+    set_var_from_num_nocopy(sumX, &vsumX);    init_var(&vsumX2);
-    set_var_from_num(sumX2, &vsumX2);
+    set_var_from_num_nocopy(sumX2, &vsumX2);    /* compute rscale for mul_var calls */    rscale = vsumX.dscale * 2;
@@ -2816,10 +2788,7 @@ numeric_stddev_internal(ArrayType *transarray,        res = make_result(&vsumX);    }
-    free_var(&vN);    free_var(&vNminus1);
-    free_var(&vsumX);
-    free_var(&vsumX2);    return res;}
@@ -3448,6 +3417,21 @@ set_var_from_num(Numeric num, NumericVar *dest)    memcpy(dest->digits, NUMERIC_DIGITS(num),
ndigits* sizeof(NumericDigit));}
 
+/*
+ * set_var_from_num_nocopy() -
+ *
+ *    Convert the packed db format into a variable - without copying digits
+ */
+static void
+set_var_from_num_nocopy(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);
+}
+/* * set_var_from_var() -