On 05/10/2021 20:24, John Naylor wrote:
I've had a chance to review and test out the v5 patches.
Thanks! I fixed the stray reference to PostgreSQL 14 that Zhihong
mentioned, and pushed.
I've done some performance testing of master versus both patches
applied. The full results and test script are attached, but I'll give a
summary here. A variety of value distributions were tested, with
work_mem from 1MB to 16MB, plus 2GB which will not use external sort at
all. I settled on 2 million records for the sort, to have something
large enough to work with but also keep the test time reasonable. That
works out to about 130MB on disk. We have recent improvements to datum
sort, so I used both single values and all values in the SELECT list.
The system was on a Westmere-era Xeon with gcc 4.8. pg_prewarm was run
on the input tables. The raw measurements were reduced to the minimum of
five runs.
I can confirm that sort performance is improved with small values of
work_mem. That was not the motivating reason for the patch, but it's a
nice bonus. Even as high as 16MB work_mem, it's possible some of the
4-6% differences represent real improvement and not just noise or binary
effects, but it's much more convincing at 4MB and below, with 25-30%
faster with non-datum integer sorts at 1MB work_mem. The nominal
regressions seem within the noise level, with one exception that only
showed up in one set of measurements (-10.89% in the spreadsheet). I'm
not sure what to make of that since it only happens in one combination
of factors and nowhere else.
That's a bit odd, but given how many data points there are, I think we
can write it off as random noise.
- Heikki
