Re: Simplifying width_bucket_numeric()

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