Hi,
I noticed that the COPY performance on PG 17.0 Windows is worse than PG 16.4.
* Environment
OS: Windows Server 2022
CPU: 22 core * 2CPU
Memory: 512GB
Storage: 700GB HDD
* Input data
10GB csv file
* Executed command
psql -c 'copy table from '\''C:\data.csv'\'' WITH csv'
(Only one psql command)
* Performance
PG 16.4: 405.2s
PG 17.0: 417.4s
* Analysis
I noticed that the commit 82a4edabd2 affects the performance.
The logic of mdzeroextend() is following.
if (numblocks > 8)
{
...
ret = FileFallocate(); // if HAVE_POSIX_FALLOCATE, call flloacate(), else
pwrite()
...
}
else
{
...
ret = FileZero(); // call pwrite()
...
}
In XFS filesystem, switching fallocate() and pwritev() reduce performance.
So, 82a4edabd2 increased numblocks to call fallocate() if fallocate() is once
called.
On the other hand, Windows does not have fallocate().
So, pwrite() is always called regardless of numblocks.
As a result, 82a4edabd2 just increased the numblocks to be written on Windows.
* Improvement
I think 82a4edabd2 is only effective for the HAVE_POSIX_FALLOCATE system.
So, I made the attached patch.
By applying the attached patch to PG 17.0, the copy result is 401.5s.
How do you think about this?
Regards,
Ryohei Takahashi
001-skip-increasing-numblocks-without-fallocate-system.patch
Description: 001-skip-increasing-numblocks-without-fallocate-system.patch
