On Sat, Jul 6, 2024, at 17:36, Dean Rasheed wrote: > In the numeric width_bucket() code, we currently do the following: .. > Instead, this can be done more simply and efficiently, using division > with truncation as follows: .. > > Patch attached. I didn't bother with any new test cases, since there > appears to be sufficient coverage already. > > As a quick performance/correctness test, I ran the following: > > SELECT setseed(0); > CREATE TEMP TABLE t AS > SELECT random(-4.000000, 8.000000) op, > random(-4.100000, -2.000000) b1, > random(6.000000, 8.100000) b2, > random(1, 15) c > FROM generate_series(1, 10000000); > > SELECT hash_array(array_agg(width_bucket(op, b1, b2, c))) FROM t; > -- Result not changed by patch
Same hash_array on all my three machines: hash_array ------------- -1179801276 (1 row) > SELECT sum(width_bucket(op, b1, b2, c)) FROM t; > Time: 3658.962 ms (00:03.659) -- HEAD > Time: 3089.946 ms (00:03.090) -- with patch Significant improvement on all my three machines: /* * Apple M3 Max */ Time: 2255.154 ms (00:02.255) -- HEAD Time: 1830.985 ms (00:01.831) Time: 1826.190 ms (00:01.826) Time: 1831.020 ms (00:01.831) Time: 1832.934 ms (00:01.833) Time: 1843.061 ms (00:01.843) Time: 1957.062 ms (00:01.957) -- simplify-width_bucket_numeric.patch Time: 1545.121 ms (00:01.545) Time: 1541.621 ms (00:01.542) Time: 1536.388 ms (00:01.536) Time: 1538.721 ms (00:01.539) Time: 1592.384 ms (00:01.592) /* * Intel Core i9-14900K */ Time: 2541.959 ms (00:02.542) -- HEAD Time: 2534.803 ms (00:02.535) Time: 2532.343 ms (00:02.532) Time: 2529.408 ms (00:02.529) Time: 2528.600 ms (00:02.529) Time: 2107.901 ms (00:02.108) -- simplify-width_bucket_numeric.patch Time: 2095.413 ms (00:02.095) Time: 2093.985 ms (00:02.094) Time: 2093.910 ms (00:02.094) Time: 2094.935 ms (00:02.095) /* * AMD Ryzen 9 7950X3D */ Time: 2226.498 ms (00:02.226) -- HEAD Time: 2238.083 ms (00:02.238) Time: 2239.075 ms (00:02.239) Time: 2238.488 ms (00:02.238) Time: 2238.166 ms (00:02.238) Time: 1853.382 ms (00:01.853) -- simplify-width_bucket_numeric.patch Time: 1842.630 ms (00:01.843) Time: 1828.309 ms (00:01.828) Time: 1844.654 ms (00:01.845) Time: 1828.520 ms (00:01.829) Regards, Joel
