Initial Unsigned Integer data type performance test results.

Hello All,

I wanted to pass some performance data on to the group regarding the
unsigned integer
data types I am working on. I tested on two systems running Ubuntu
Hardy. The first system
is an 8 x 2.66GHz x86-64 processor system. The second system is a 2 x
533 celeron i386
system. For this test I disabled autovaccuum and manually ran the
vacuum during times that
were not timed. I also added some sleep and sync commands to try and
stabalize the test
results. The integer and unsigned integer test results were also
interleaved to compensate for
other system activities. Finally, I have not done a lot of bench
marking, so feedback and
improvement ideas are welcome!

I benchmarked four tests:
32-bit int2 vs uint2 -- My expectation is these results
would be approximately
-- equal. The uint2 casts
up to an int4 which is still 32-bits.

32-bit int4 vs uint4 -- My expectation is <> operator
should be approximately
-- equal since I explicitly
added int4 <> uint4 operators.

64-bit int2 vs uint2 -- My expectation is these results
would be approximately
-- equal. The uint2 casts
up to an int4 which is less than
-- the 64-bit processor bus width.

64-bit int4 vs uint4 -- My expectation is these results
would be approximately
-- equal. The uint4 casts
up to an int8 which is the processor
-- bus width.

Here is the definition of each column (times are displayed in seconds):

Rows -- The number of rows added to the table.
Test -- The test number (I ran each benchmark 3 times).
S Load -- Time to load the table with signed integers.
U Load -- Time to load the table with unsigned integers.
S <> OP -- Time to perform an <> operator on each row in the
table (signed integers)
U <> OP -- Time to perform an <> operator on each row in the
table (unsigned integers)
S & OP -- Time to perform an AND operator on each row in
the table (signed integers)
U & OP -- Time to perform an AND operator on each row in
the table (unsigned integers)

I still have some more investigating to do into the results of this
data (i.e. why is the signed <> OP cheaper for int2 than uint2 types,
etc),
but I wanted to share this performance data with the community. Rough
calculations show about a 4% performance hit for 32-bit processors
to use the int8 type and a 6% performance hit for 64-bit processors.
This seems like a reasonable trade-off for the reduced storage space
I am after, and the reduction in operators Greg and Tom were concerned
about. If there are no objections from the community, I will plan to
complete my implementation of Tom's proposal at:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg01204.php.

Thanks!

- Ryan

P.S. The code I have so far is not ready for review. It was
implemented quickly to get performance numbers to determine if Tom's
proposal
would be acceptable by the community. I can send it out if people are
interested, but I am planning on submitting it to a future commit fest
once I have it cleaned up and better tested.

Benchmark Data:
==================================

32-bit int2 vs. uint2

Rows Test S Load U Load S <> OP U <> OP S
& OP U & OP
--------------------------------------------------------------------------------------
100 1 000.002888 000.002151 000.010881 000.014691
000.011124 000.011000
100 2 000.002780 000.002127 000.011729 000.011611
000.012014 000.011925
100 3 000.002747 000.002085 000.010193 000.010318
000.010588 000.010576

1000 1 000.003201 000.003870 000.037837 000.037360
000.032064 000.032478
1000 2 000.003259 000.003912 000.033495 000.036281
000.032502 000.035195
1000 3 000.003201 000.003913 000.039156 000.035592
000.032405 000.040543

10000 1 000.024683 000.021306 000.255958 000.329045
000.255887 000.283782
10000 2 000.020214 000.021224 000.260252 000.290933
000.281468 000.255171
10000 3 000.020371 000.020940 000.276401 000.264791
000.257598 000.257258

100000 1 001.669571 001.687523 002.591442 002.682428
003.410724 003.490362
100000 2 001.682251 001.702598 003.379377 002.855622
002.549476 002.583431
100000 3 001.693429 001.684732 002.546024 002.641240
002.540556 003.366534

500000 1 010.138317 011.014532 015.707597 015.699991
015.394598 015.502639
500000 2 010.042176 010.179163 015.290994 015.407479
015.332925 016.321578
500000 3 010.047930 010.206489 015.016276 015.430527
015.201759 015.411601

1000000 1 020.762680 022.145950 030.338606 030.997060
030.971243 031.191622
1000000 2 020.645634 021.480215 031.222100 031.044017
031.489913 031.145342
1000000 3 020.908854 021.612863 031.735141 031.378030
033.323409 031.141103

5000000 1 108.538334 109.016628 151.732077 174.010438
183.003246 191.651814
5000000 2 109.040097 108.891102 152.354661 167.138746
183.423955 193.882820
5000000 3 108.302860 109.358920 151.745248 165.654813
182.462140 178.345020

10000000 1 217.707537 220.407657 313.047263 360.890670
415.392358 413.828828
10000000 2 217.200579 219.571731 307.769243 362.820028
404.587188 412.644460
10000000 3 218.502164 225.425290 308.132310 351.845443
401.132601 411.143661

32-bit int4 vs. uint4

Rows Test S Load U Load S <> OP U <> OP S
& OP U & OP
--------------------------------------------------------------------------------------
100 1 000.001379 000.004707 000.017991 000.004928
000.005532 000.023357
100 2 000.001192 000.008546 000.007557 000.004901
000.020157 000.008509
100 3 000.001211 000.009639 000.007729 000.004634
000.020458 000.008506

1000 1 000.003032 000.024415 000.049310 000.038847
000.028273 000.033758
1000 2 000.003094 000.023647 000.053854 000.029543
000.027895 000.030389
1000 3 000.003089 000.023558 000.049282 000.031464
000.028082 000.030534

10000 1 000.022861 000.206507 000.450483 000.294142
000.270429 000.285299
10000 2 000.020130 000.218892 000.452674 000.265420
000.294738 000.280119
10000 3 000.020216 000.207972 000.498028 000.264987
000.269354 000.307225

100000 1 001.654366 002.222839 003.158899 002.711246
002.797589 003.223520
100000 2 001.635678 002.117398 003.184728 002.687387
002.732426 002.908151
100000 3 001.641013 002.228674 003.122391 002.737217
002.726568 002.854913

500000 1 010.517882 011.035098 015.867863 015.819520
016.651296 017.366898
500000 2 010.301478 010.890427 015.898800 015.694182
016.374195 017.139048
500000 3 010.308812 011.336613 016.010874 015.694566
016.172569 017.050617

1000000 1 021.748016 022.032479 031.718041 031.377681
032.836890 034.495359
1000000 2 021.668741 022.764860 031.585905 031.485409
032.597464 034.408479
1000000 3 021.797422 022.287219 031.501989 031.310628
032.891124 034.352729

5000000 1 112.363704 113.290859 156.780144 157.629888
169.741430 178.738164
5000000 2 111.912800 113.728177 155.814622 157.592556
169.063996 177.893192
5000000 3 113.204484 113.338839 155.942430 157.708378
168.438944 177.544739

10000000 1 225.834990 227.816448 314.965601 318.707127
343.067000 355.863962
10000000 2 227.519247 227.054232 315.291132 318.536013
340.437748 356.954030
10000000 3 226.436991 227.728902 316.953600 319.432702
341.731871 356.278195

64-bit int2 vs. uint2

Rows Test S Load U Load S <> OP U <> OP S
& OP U & OP
--------------------------------------------------------------------------------------
100 1 000.000264 000.000378 000.005095 000.004019
000.004019 000.003967
100 2 000.000234 000.000149 000.005168 000.007987
000.008077 000.008046
100 3 000.000283 000.000422 000.000898 000.001001
000.003046 000.003973

1000 1 000.000472 000.000406 000.005944 000.004000
000.008402 000.006523
1000 2 000.000472 000.000614 000.003220 000.007445
000.008007 000.007899
1000 3 000.000434 000.000522 000.004744 000.003945
000.003904 000.003978

10000 1 000.003583 000.002897 000.027068 000.024923
000.026969 000.027948
10000 2 000.002681 000.002879 000.023959 000.028943
000.028847 000.024163
10000 3 000.002713 000.002826 000.027868 000.024297
000.027626 000.027936

100000 1 000.147438 000.158207 000.181544 000.281516
000.216321 000.256955
100000 2 000.122763 000.158806 000.262845 000.262513
000.206650 000.242393
100000 3 000.165053 000.137694 000.199290 000.200812
000.190772 000.231532

500000 1 000.861558 001.857679 001.201773 001.141055
001.146796 001.196645
500000 2 000.751839 001.769231 001.124357 001.096142
001.154715 001.156669
500000 3 000.757383 000.746437 001.046433 001.043038
001.092662 001.188915

1000000 1 001.624921 001.501079 002.219032 002.221471
002.351225 002.259619
1000000 2 001.635181 001.490060 002.132329 002.179557
002.230080 002.230306
1000000 3 001.642645 001.578584 002.204351 002.204351
002.271596 002.251025

5000000 1 008.175122 007.608278 012.993807 024.737981
032.636002 023.532173
5000000 2 008.402995 007.788409 022.455597 022.722125
023.375628 024.364674
5000000 3 008.146802 007.560563 011.000594 023.479316
023.127872 023.647729

10000000 1 027.417696 025.444743 035.633955 044.665248
056.828230 053.103862
10000000 2 026.629527 015.461252 033.425061 047.231670
058.463149 059.917299
10000000 3 026.714370 015.133970 033.187095 045.359560
059.858288 060.539286

64-bit int4 vs. uint4

Rows Test S Load U Load S <> OP U <> OP S
& OP U & OP
--------------------------------------------------------------------------------------
100 1 000.000283 000.000146 000.000887 000.000648
000.003931 000.000884
100 2 000.000285 000.000174 000.000884 000.000690
000.000652 000.000725
100 3 000.000225 000.000137 000.000657 000.003389
000.000738 000.000720

1000 1 000.000507 000.000409 000.002872 000.002924
000.003109 000.005706
1000 2 000.000458 000.000386 000.005279 000.003111
000.003007 000.002984
1000 3 000.000526 000.000458 000.003005 000.003102
000.007094 000.003343

10000 1 000.003591 000.002837 000.026670 000.025760
000.025237 000.026846
10000 2 000.002800 000.002690 000.023960 000.029733
000.031866 000.026354
10000 3 000.002718 000.002645 000.023977 000.024667
000.024566 000.034090

100000 1 000.149074 000.134729 000.212927 000.231054
000.236246 000.267968
100000 2 000.133630 000.116737 000.224492 000.218364
000.197834 000.213061
100000 3 000.142504 000.111011 000.239096 000.227658
000.213063 000.246546

500000 1 000.758492 000.716535 001.122045 001.088708
001.189956 001.211962
500000 2 000.771229 000.762520 001.084013 001.131540
001.171574 001.171354
500000 3 000.784780 000.694077 001.110563 001.207835
001.148557 001.239808

1000000 1 001.538802 001.519817 002.143364 002.182663
002.347049 002.496311
1000000 2 001.588864 001.438750 002.196512 002.236763
002.378072 002.443330
1000000 3 001.565230 001.535085 002.127375 002.468330
002.325026 002.470508

5000000 1 008.095642 007.514390 020.867962 011.222382
024.817883 025.539325
5000000 2 008.279959 007.822827 023.514454 024.288419
025.346532 025.263223
5000000 3 008.313125 007.794659 023.427362 023.892352
026.224463 024.725105

10000000 1 025.903194 027.249769 048.426016 045.101048
051.549006 052.169178
10000000 2 025.365287 026.901960 046.175783 045.509540
049.219715 052.168185
10000000 3 027.882902 028.379166 047.237950 048.097603
049.311239 051.762996