Yura Sokolov писал 2021-07-29 18:29:
I've attached IntegerSet2 patch for pgtools repo and benchmark results. Branch https://github.com/funny-falcon/pgtools/tree/integerset2
Strange web-mail client... I never can be sure what it will attach... Reattach benchmark results
regards, Yura Sokolov y.soko...@postgrespro.ru funny.fal...@gmail.com
* Test 1 select prepare( 1000000, -- max block 10, -- # of dead tuples per page 20, -- dead tuples interval within a page 1, -- # of consecutive pages having dead tuples 1 -- page interval ); name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 57.23 MB | 0.029 | 96.650 | 572.21 MB | _____ | ___ intset | 46.88 MB | 0.095 | 82.797 | 468.67 MB | _____ | ___ radix | 40.26 MB | 0.089 | 12.082 | 401.27 MB | 0.999 | 196.490 rtbm | 64.02 MB | 0.164 | 14.300 | 512.02 MB | 1.991 | 189.343 svtm | 17.78 MB | 0.098 | 12.411 | 178.59 MB | 0.973 | 210.901 tbm | 96.01 MB | 0.239 | 8.486 | 768.01 MB | 2.607 | 119.593 intset2| 17.78 MB | 0.220 | 19.203 | 175.84 MB | 2.030 | 320.488 * Test 2 select prepare( 1000000, -- max block 10, -- # of dead tuples per page 1, -- dead tuples interval within a page 1, -- # of consecutive pages having dead tuples 1 -- page interval ); name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 57.23 MB | 0.030 | 4.737 | 572.21 MB | _____ | ___ intset | 46.88 MB | 0.096 | 4.077 | 468.67 MB | _____ | ___ radix | 9.95 MB | 0.051 | 0.752 | 98.38 MB | 0.531 | 18.642 rtbm | 34.02 MB | 0.166 | 0.424 | 288.02 MB | 2.036 | 6.294 svtm | 5.78 MB | 0.037 | 0.203 | 54.60 MB | 0.400 | 5.682 tbm | 96.01 MB | 0.245 | 0.423 | 768.01 MB | 2.624 | 5.848 intset2| 6.27 MB | 0.097 | 0.414 | 61.29 MB | 1.002 | 12.823 * Test 3 select prepare( 1000000, -- max block 2, -- # of dead tuples per page 100, -- dead tuples interval within a page 1, -- # of consecutive pages having dead tuples 1 -- page interval ); name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 11.45 MB | 0.006 | 53.623 | 114.45 MB | _____ | ___ intset | 15.63 MB | 0.024 | 56.291 | 156.23 MB | _____ | ___ radix | 40.26 MB | 0.055 | 10.928 | 401.27 MB | 0.622 | 173.953 rtbm | 36.02 MB | 0.116 | 8.452 | 320.02 MB | 1.370 | 125.438 svtm | 9.28 MB | 0.069 | 6.780 | 102.10 MB | 0.690 | 167.220 tbm | 96.01 MB | 0.188 | 8.569 | 768.01 MB | 1.935 | 118.841 intset2| 4.28 MB | 0.022 | 5.624 | 42.04 MB | 0.229 | 146.096 * Test 4 select prepare( 1000000, -- max block 100, -- # of dead tuples per page 1, -- dead tuples interval within a page 1, -- # of consecutive pages having dead tuples 1 -- page interval ); name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 572.21 MB | 0.304 | 76.625 | 5722.05 MB | _____ | ___ intset | 93.74 MB | 0.795 | 47.977 | 937.34 MB | _____ | ___ radix | 40.26 MB | 0.264 | 6.322 | 401.27 MB | 2.674 | 101.887 rtbm | 36.02 MB | 0.360 | 4.195 | 320.02 MB | 4.009 | 62.441 svtm | 7.28 MB | 0.329 | 2.729 | 73.60 MB | 3.271 | 74.725 tbm | 96.01 MB | 0.564 | 4.220 | 768.01 MB | 5.711 | 59.047 svtm | 7.28 MB | 0.908 | 5.826 | 70.55 MB | 9.089 | 137.902 * Test 5 select prepare( 1000000, -- max block 150, -- # of dead tuples per page 1, -- dead tuples interval within a page 10000, -- # of consecutive pages having dead tuples 20000 -- page interval ); There are 10000 consecutive pages that have 150 dead tuples at every 20000 pages. name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 429.16 MB | 0.225 | 65.404 | 4291.54 MB | _____ | ___ intset | 46.88 MB | 0.510 | 39.460 | 468.67 MB | _____ | ___ radix | 20.26 MB | 0.192 | 6.440 | 201.48 MB | 1.954 | 111.539 rtbm | 18.02 MB | 0.225 | 7.077 | 160.02 MB | 2.515 | 120.816 svtm | 3.66 MB | 0.233 | 3.493 | 37.10 MB | 2.348 | 73.092 tbm | 48.01 MB | 0.341 | 9.146 | 384.01 MB | 3.544 | 161.435 intset2| 3.78 MB | 0.671 | 5.081 | 35.29 MB | 6.791 | 117.617 * Test 6 select prepare( 1000000, -- max block 10, -- # of dead tuples per page 1, -- dead tuples interval within a page 10000, -- # of consecutive pages having dead tuples 20000 -- page interval ); There are 10000 consecutive pages that have 10 dead tuples at every 20000 pages. name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 28.62 MB | 0.015 | 2.480 | 286.11 MB | _____ | ___ intset | 23.45 MB | 0.050 | 2.254 | 234.34 MB | _____ | ___ radix | 5.04 MB | 0.027 | 0.503 | 49.71 MB | 0.283 | 10.667 rtbm | 17.02 MB | 0.077 | 0.464 | 144.02 MB | 0.971 | 7.813 svtm | 3.16 MB | 0.020 | 0.181 | 27.60 MB | 0.201 | 3.823 tbm | 48.01 MB | 0.116 | 0.614 | 384.01 MB | 1.265 | 10.550 intset2| 3.28 MB | 0.049 | 0.307 | 30.54 MB | 0.490 | 7.153 * Test 7 select prepare( 1000000, -- max block 150, -- # of dead tuples per page 1, -- dead tuples interval within a page 1000, -- # of consecutive pages having dead tuples 999000 -- page interval ); There are pages that have 150 dead tuples at first 1000 blocks and last 1000 blocks. name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 1.72 MB | 0.002 | 5.610 | 9.45 MB | 0.005 | 87.133 intset | 0.20 MB | 0.003 | 8.488 | 1.05 MB | 0.011 | 110.711 radix | 0.20 MB | 0.001 | 1.585 | 0.58 MB | 0.005 | 24.742 rtbm | 0.15 MB | 0.001 | 3.348 | 0.34 MB | 0.005 | 46.014 svtm | 0.52 MB | 0.002 | 0.738 | 0.59 MB | 0.007 | 7.458 tbm | 0.20 MB | 0.002 | 4.007 | 0.76 MB | 0.007 | 55.573 intset2| 0.28 MB | 0.003 | 2.278 | 0.28 MB | 0.015 | 17.478 I don't know why, but this 10x test produces 10x index tuples, but just 5.5x dead tuples, therefore my results are fare from yours. Suprisingly, performance is worse for all implementations. * Test 8 select prepare( 1000000, -- max block 100, -- # of dead tuples per page 1, -- dead tuples interval within a page 50, -- # of consecutive pages having dead tuples 100 -- page interval ); There are 50 consecutive pages that have 100 dead tuples at every 100 pages. name | attach | attach | shuffled | size_x10 | attach_x10| shuffled_x10 --------+-----------+--------+----------+------------+-----------+------------- array | 286.11 MB | 0.150 | 65.133 | 2861.03 MB | _____ | ___ intset | 46.88 MB | 0.399 | 39.727 | 468.67 MB | _____ | ___ radix | 21.82 MB | 0.136 | 5.695 | 216.57 MB | 1.388 | 96.904 rtbm | 18.02 MB | 0.171 | 4.755 | 160.02 MB | 1.961 | 79.830 svtm | 4.28 MB | 0.164 | 2.735 | 37.60 MB | 1.640 | 57.850 tbm | 48.01 MB | 0.265 | 6.252 | 384.01 MB | 2.757 | 108.111 intset2| 4.03 MB | 0.453 | 4.434 | 37.79 MB | 4.549 | 92.312
