Skip site navigation (1) Skip section navigation (2)

Re: Improve the comparison of NUMERIC data

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Atsushi Ogawa <atsushi(dot)ogawa(at)gmail(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: Improve the comparison of NUMERIC data
Date: 2006-02-07 16:04:15
Message-ID: 200602071604.k17G4FU11193@candle.pha.pa.us (view raw or flat)
Thread:
Lists: 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(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-patches by date

Next:From: Andy KlostermanDate: 2006-02-07 20:15:45
Subject: BUG #2246: Bad malloc interactions: ecpg, openssl
Previous:From: Greg Sabino MullaneDate: 2006-02-07 15:01:09
Subject: Re: Patch to readme

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group