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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers