bm-results.md

Tests repeated 10 times on roads_rdr, 3 times on OSM Belarus, "self-join" test omitted for OSM Belarus due to long execution time.

Intel Core i7-11370H @ 3.30GHz × 8, 16 Gb RAM.

Tiling/kNN (tile centroids):

• roads_rdr: zoom level 14, # of tiles: 2296
• Belarus: zoom level 11, # of tiles: 4032

Table columns:

• original -- GitHub postgres repo master (https://github.com/postgres/postgres/commit/ece8c76192fee0b78509688325631ceabca44ff5), with geometry_gist_sortsupport_2d sortsupport function;
• nosortsupport -- same, without sortsupport function;
• patched -- using sortsupport function, with suggested patch applied.

roads_rdr

gist_stat

	original	patched	nosortsupport	original, fillfactor=50	patched, fillfactor=50,	nosortsupport, fillfactor=50
Number of levels	3	3	3	3	3	3
Number of pages	359	605	671	650	615	1208
Number of leaf pages	356	600	666	644	609	1194
Number of tuples	93034	93280	93346	93325	93290	93883
Number of invalid tuples	0	0	0	0	0	0
Number of leaf tuples	92676	92676	92676	92676	92676	92676
Total size of tuples	2609260 bytes	2619100 bytes	2621740 bytes	2620900 bytes	2619500 bytes	2643220 bytes
Total size of leaf tuples	2599200 bytes	2602128 bytes	2602920 bytes	2602656 bytes	2602236 bytes	2609256 bytes
Total size of index	2940928 bytes	4956160 bytes	5496832 bytes	5324800 bytes	5038080 bytes	9895936 bytes
Effective fill	0.8872	0.5285	0.477	0.4922	0.5199	0.2671

Benchmarks (ms, mean/median of 10)

	original	patched	nosortsupport	original, fillfactor=50	patched, fillfactor=50,	nosortsupport, fillfactor=50
Create index	51.2429 / 49.085	165.6421 / 164.4735	273.2494 / 270.9475	55.5905 / 54.0845	136.4715 / 135.3335	233.9812 / 232.115
Self-join	4100.5645 / 4093.227	1752.5885 / 1752.0995	1709.3702 / 1708.841	2951.8764 / 2943.608	1805.9543 / 1799.056	1183.0967 / 1181.63
Tiling	458.5242 / 455.017	418.7391 / 416.4425	424.466 / 424.548	441.8033 / 436.993	426.9725 / 422.82	417.6006 / 416.346
kNN k=1	401.8068 / 401.1595	242.7706 / 241.64	248.6685 / 247.928	306.6242 / 304.366	240.7218 / 239.898	210.1498 / 209.614
kNN k=100	2300.7032 / 2277.1295	2035.3501 / 2035.1155	2072.0323 / 2070.766	2144.8499 / 2143.695	2053.8628 / 2052.981	2033.3401 / 2030.6085

OSM Belarus

gist_stat

	original	patched	nosortsupport	original, fillfactor=50	patched, fillfactor=50,	nosortsupport, fillfactor=50
Number of levels	3	4	4	4	4	4
Number of pages	30978	82201	75225	56323	88435	136474
Number of leaf pages	30858	81330	74436	55930	87437	133766
Number of tuples	8084789	8136012	8129036	8110134	8142246	8190285
Number of invalid tuples	0	0	0	0	0	0
Number of leaf tuples	8053812	8053812	8053812	8053812	8053812	8053812
Total size of tuples	226745828 bytes	228794748 bytes	228515708 bytes	227759628 bytes	229044108 bytes	230965668 bytes
Total size of leaf tuples	225877032 bytes	226482696 bytes	226399968 bytes	226177896 bytes	226555980 bytes	227111928 bytes
Total size of index	253771776 bytes	673390592 bytes	616243200 bytes	461398016 bytes	724459520 bytes	1117995008 bytes
Effective fill	0.8935	0.3398	0.3708	0.4936	0.3162	0.2066

Benchmarks (ms, mean/median of 3)

	original	patched	nosortsupport	original, fillfactor=50	patched, fillfactor=50,	nosortsupport, fillfactor=50
Create index	4844.912 / 4862.456	20253.2657 / 20243.563	37201.5303 / 36912.927	5096.8537 / 5076.912	16561.6093 / 16545.133	32065.531 / 31629.298
Tiling	33299.7407 / 33193.819	33186.009 / 33139.855	33574.6183 / 33568.739	33273.3527 / 33175.289	33071.5833 / 33092.694	33748.344 / 33719.998
kNN k=1	18321.334 / 18306.257	17073.0877 / 17078.36	15653.4417 / 15676.135	17942.7383 / 17935.191	17122.2993 / 17119.56	15003.3757 / 15019.28
kNN k=100	40903.604 / 40843.21	40395.8873 / 40399.456	40849.377 / 40832.727	39927.2597 / 39965.97	40720.9393 / 40692.708	39922.7693 / 39932.796