Re: Improve the comparison of NUMERIC data - Mailing list pgsql-patches

Patch applied.  Thanks.

---------------------------------------------------------------------------


pAtsushi Ogawa wrote:
> I think that NUMERIC datatype has a problem in the performance that
> the format on Tuple(Numeric) and the format to calculate(NumericVar)
> are different. I understood that to reduce I/O. However, when many
> comparisons or calculations of NUMERIC are executed, the conversion
> of Numeric and NumericVar becomes a bottleneck.
>
> It is profile result when "create index on NUMERIC column" is executed:
>
>   %   cumulative   self              self     total
>  time   seconds   seconds    calls   s/call   s/call  name
>  17.61     10.27    10.27 34542006     0.00     0.00  cmp_numerics
>  11.90     17.21     6.94 34542006     0.00     0.00  comparetup_index
>   7.42     21.54     4.33 71102587     0.00     0.00  AllocSetAlloc
>   7.02     25.64     4.09 69084012     0.00     0.00  set_var_from_num
>   4.87     28.48     2.84 69084012     0.00     0.00  alloc_var
>   4.79     31.27     2.79 142205745     0.00     0.00  AllocSetFreeIndex
>   4.55     33.92     2.65 34542004     0.00     0.00  cmp_abs
>   4.07     36.30     2.38 71101189     0.00     0.00  AllocSetFree
>   3.83     38.53     2.23 69084012     0.00     0.00  free_var
>
> The create index command executes many comparisons of Numeric values.
> Functions other than comparetup_index spent a lot of cycles for
> conversion from Numeric to NumericVar.
>
> An attached patch enables the comparison of Numeric values without
> executing conversion to NumericVar. The execution time of that SQL
> becomes half.
>
> o Test SQL (index_test table has 1,000,000 tuples)
>  create index index_test_idx on index_test(num_col);
>
> o Test results (executed the test five times)
> (1)PentiumIII
>  original: 39.789s  36.823s  36.737s  37.752s  37.019s
>  patched : 18.560s  19.103s  18.830s  18.408s  18.853s
>
> (2)Pentium4
>  original: 16.349s  14.997s  12.979s  13.169s  12.955s
>  patched :  7.005s   6.594s   6.770s   6.740s   6.828s
>
> (3)Itanium2
>  original: 15.392s  15.447s  15.350s  15.370s  15.417s
>  patched :  7.413s   7.330s   7.334s   7.339s   7.339s
>
> (4)Ultra Sparc
>  original: 64.435s  59.336s  59.332s  58.455s  59.781s
>  patched : 28.630s  28.666s  28.983s  28.744s  28.595s
>
> regards,
>
> --- Atsushi Ogawa

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073