Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite7a9ccd

Browse files
committed
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 manycomparisons or calculations of NUMERIC are executed, the conversionof 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_varThe create index command executes many comparisons of Numeric values.Functions other than comparetup_index spent a lot of cycles forconversion from Numeric to NumericVar.An attached patch enables the comparison of Numeric values withoutexecuting conversion to NumericVar. The execution time of that SQLbecomes 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 4.07 36.30 2.38 71101189 0.00 0.00 AllocSetFree 3.83 38.53 2.23 69084012 0.00 0.00 free_varThe create index command executes many comparisons of Numeric values.Functions other than comparetup_index spent a lot of cycles forconversion from Numeric to NumericVar.An attached patch enables the comparison of Numeric values withoutexecuting conversion to NumericVar. The execution time of that SQLbecomes 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.595sAtsushi Ogawa
1 parent15be0b8 commite7a9ccd

File tree

1 file changed

+73
-39
lines changed

1 file changed

+73
-39
lines changed

‎src/backend/utils/adt/numeric.c

Lines changed: 73 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Copyright (c) 1998-2005, PostgreSQL Global Development Group
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.90 2006/01/25 18:15:03 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.91 2006/02/07 16:03:50 momjian Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -229,6 +229,10 @@ static void dump_var(const char *str, NumericVar *var);
229229

230230
#defineinit_var(v)MemSetAligned(v, 0, sizeof(NumericVar))
231231

232+
#defineNUMERIC_DIGITS(num) ((NumericDigit *)(num)->n_data)
233+
#defineNUMERIC_NDIGITS(num) \
234+
(((num)->varlen - NUMERIC_HDRSZ) / sizeof(NumericDigit))
235+
232236
staticvoidalloc_var(NumericVar*var,intndigits);
233237
staticvoidfree_var(NumericVar*var);
234238
staticvoidzero_var(NumericVar*var);
@@ -250,6 +254,10 @@ static double numericvar_to_double_no_overflow(NumericVar *var);
250254

251255
staticintcmp_numerics(Numericnum1,Numericnum2);
252256
staticintcmp_var(NumericVar*var1,NumericVar*var2);
257+
staticintcmp_var_common(constNumericDigit*var1digits,intvar1ndigits,
258+
intvar1weight,intvar1sign,
259+
constNumericDigit*var2digits,intvar2ndigits,
260+
intvar2weight,intvar2sign);
253261
staticvoidadd_var(NumericVar*var1,NumericVar*var2,NumericVar*result);
254262
staticvoidsub_var(NumericVar*var1,NumericVar*var2,NumericVar*result);
255263
staticvoidmul_var(NumericVar*var1,NumericVar*var2,NumericVar*result,
@@ -271,6 +279,10 @@ static void power_var_int(NumericVar *base, int exp, NumericVar *result,
271279
intrscale);
272280

273281
staticintcmp_abs(NumericVar*var1,NumericVar*var2);
282+
staticintcmp_abs_common(constNumericDigit*var1digits,intvar1ndigits,
283+
intvar1weight,
284+
constNumericDigit*var2digits,intvar2ndigits,
285+
intvar2weight);
274286
staticvoidadd_abs(NumericVar*var1,NumericVar*var2,NumericVar*result);
275287
staticvoidsub_abs(NumericVar*var1,NumericVar*var2,NumericVar*result);
276288
staticvoidround_var(NumericVar*var,intrscale);
@@ -1061,19 +1073,10 @@ cmp_numerics(Numeric num1, Numeric num2)
10611073
}
10621074
else
10631075
{
1064-
NumericVararg1;
1065-
NumericVararg2;
1066-
1067-
init_var(&arg1);
1068-
init_var(&arg2);
1069-
1070-
set_var_from_num(num1,&arg1);
1071-
set_var_from_num(num2,&arg2);
1072-
1073-
result=cmp_var(&arg1,&arg2);
1074-
1075-
free_var(&arg1);
1076-
free_var(&arg2);
1076+
result=cmp_var_common(NUMERIC_DIGITS(num1),NUMERIC_NDIGITS(num1),
1077+
num1->n_weight,NUMERIC_SIGN(num1),
1078+
NUMERIC_DIGITS(num2),NUMERIC_NDIGITS(num2),
1079+
num2->n_weight,NUMERIC_SIGN(num2));
10771080
}
10781081

10791082
returnresult;
@@ -2591,11 +2594,11 @@ int8_avg(PG_FUNCTION_ARGS)
25912594
staticvoid
25922595
dump_numeric(constchar*str,Numericnum)
25932596
{
2594-
NumericDigit*digits=(NumericDigit*)num->n_data;
2597+
NumericDigit*digits=NUMERIC_DIGITS(num);
25952598
intndigits;
25962599
inti;
25972600

2598-
ndigits= (num->varlen-NUMERIC_HDRSZ) /sizeof(NumericDigit);
2601+
ndigits=NUMERIC_NDIGITS(num);
25992602

26002603
printf("%s: NUMERIC w=%d d=%d ",str,num->n_weight,NUMERIC_DSCALE(num));
26012604
switch (NUMERIC_SIGN(num))
@@ -2895,7 +2898,7 @@ set_var_from_num(Numeric num, NumericVar *dest)
28952898
{
28962899
intndigits;
28972900

2898-
ndigits= (num->varlen-NUMERIC_HDRSZ) /sizeof(NumericDigit);
2901+
ndigits=NUMERIC_NDIGITS(num);
28992902

29002903
alloc_var(dest,ndigits);
29012904

@@ -3394,32 +3397,52 @@ numericvar_to_double_no_overflow(NumericVar *var)
33943397
staticint
33953398
cmp_var(NumericVar*var1,NumericVar*var2)
33963399
{
3397-
if (var1->ndigits==0)
3400+
returncmp_var_common(var1->digits,var1->ndigits,
3401+
var1->weight,var1->sign,
3402+
var2->digits,var2->ndigits,
3403+
var2->weight,var2->sign);
3404+
}
3405+
3406+
/*
3407+
* cmp_var_common() -
3408+
*
3409+
* Main routine of cmp_var(). This function can be used by both
3410+
* NumericVar and Numeric.
3411+
*/
3412+
staticint
3413+
cmp_var_common(constNumericDigit*var1digits,intvar1ndigits,
3414+
intvar1weight,intvar1sign,
3415+
constNumericDigit*var2digits,intvar2ndigits,
3416+
intvar2weight,intvar2sign)
3417+
{
3418+
if (var1ndigits==0)
33983419
{
3399-
if (var2->ndigits==0)
3420+
if (var2ndigits==0)
34003421
return0;
3401-
if (var2->sign==NUMERIC_NEG)
3422+
if (var2sign==NUMERIC_NEG)
34023423
return1;
34033424
return-1;
34043425
}
3405-
if (var2->ndigits==0)
3426+
if (var2ndigits==0)
34063427
{
3407-
if (var1->sign==NUMERIC_POS)
3428+
if (var1sign==NUMERIC_POS)
34083429
return1;
34093430
return-1;
34103431
}
34113432

3412-
if (var1->sign==NUMERIC_POS)
3433+
if (var1sign==NUMERIC_POS)
34133434
{
3414-
if (var2->sign==NUMERIC_NEG)
3435+
if (var2sign==NUMERIC_NEG)
34153436
return1;
3416-
returncmp_abs(var1,var2);
3437+
returncmp_abs_common(var1digits,var1ndigits,var1weight,
3438+
var2digits,var2ndigits,var2weight);
34173439
}
34183440

3419-
if (var2->sign==NUMERIC_POS)
3441+
if (var2sign==NUMERIC_POS)
34203442
return-1;
34213443

3422-
returncmp_abs(var2,var1);
3444+
returncmp_abs_common(var2digits,var2ndigits,var2weight,
3445+
var1digits,var1ndigits,var1weight);
34233446
}
34243447

34253448

@@ -4814,33 +4837,44 @@ power_var_int(NumericVar *base, int exp, NumericVar *result, int rscale)
48144837
staticint
48154838
cmp_abs(NumericVar*var1,NumericVar*var2)
48164839
{
4817-
NumericDigit*var1digits=var1->digits;
4818-
NumericDigit*var2digits=var2->digits;
4840+
returncmp_abs_common(var1->digits,var1->ndigits,var1->weight,
4841+
var2->digits,var2->ndigits,var2->weight);
4842+
}
4843+
4844+
/* ----------
4845+
* cmp_abs_common() -
4846+
*
4847+
* Main routine of cmp_abs(). This function can be used by both
4848+
* NumericVar and Numeric.
4849+
* ----------
4850+
*/
4851+
staticint
4852+
cmp_abs_common(constNumericDigit*var1digits,intvar1ndigits,intvar1weight,
4853+
constNumericDigit*var2digits,intvar2ndigits,intvar2weight)
4854+
{
48194855
inti1=0;
48204856
inti2=0;
4821-
intw1=var1->weight;
4822-
intw2=var2->weight;
48234857

48244858
/* Check any digits before the first common digit */
48254859

4826-
while (w1>w2&&i1<var1->ndigits)
4860+
while (var1weight>var2weight&&i1<var1ndigits)
48274861
{
48284862
if (var1digits[i1++]!=0)
48294863
return1;
4830-
w1--;
4864+
var1weight--;
48314865
}
4832-
while (w2>w1&&i2<var2->ndigits)
4866+
while (var2weight>var1weight&&i2<var2ndigits)
48334867
{
48344868
if (var2digits[i2++]!=0)
48354869
return-1;
4836-
w2--;
4870+
var2weight--;
48374871
}
48384872

48394873
/* At this point, either w1 == w2 or we've run out of digits */
48404874

4841-
if (w1==w2)
4875+
if (var1weight==var2weight)
48424876
{
4843-
while (i1<var1->ndigits&&i2<var2->ndigits)
4877+
while (i1<var1ndigits&&i2<var2ndigits)
48444878
{
48454879
intstat=var1digits[i1++]-var2digits[i2++];
48464880

@@ -4857,12 +4891,12 @@ cmp_abs(NumericVar *var1, NumericVar *var2)
48574891
* At this point, we've run out of digits on one side or the other; so any
48584892
* remaining nonzero digits imply that side is larger
48594893
*/
4860-
while (i1<var1->ndigits)
4894+
while (i1<var1ndigits)
48614895
{
48624896
if (var1digits[i1++]!=0)
48634897
return1;
48644898
}
4865-
while (i2<var2->ndigits)
4899+
while (i2<var2ndigits)
48664900
{
48674901
if (var2digits[i2++]!=0)
48684902
return-1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp