On Thu, 27 Jul 2023 at 14:51, David Rowley <dgrowle...@gmail.com> wrote: > Just to keep this moving and to make it easier for people to test the > pg_strtoint patches, I've pushed the fix_COPY_DEFAULT.patch patch. > The only thing I changed was to move the line that was allocating the > array to a location more aligned with the order that the fields are > defined in the struct.
I just did another round of benchmarking to see where we're at after fox_COPY_DEFAULT.patch has been committed. Below I've benchmarked REL_15_STABLE up to REL_16_STABLE with some hand-selected key commits, many of which have been mentioned on this thread already because they seem to affect the performance of COPY. To summarise, REL_15_STABLE can run this benchmark in 526.014 ms on my AMD 3990x machine. Today's REL_16_STABLE takes 530.344 ms. We're talking about another patch to speed up the pg_strtoint functions which gets this down to 483.790 ms. Do we need to do this for v16 or can we just leave this as it is already? The slowdown does not seem to be much above what we'd ordinarily classify as noise using this test on my machine. Benchmark setup: COPY (SELECT generate_series(1, 2000000) a, (random() * 100000 - 50000)::int b, 3243423 c) TO '/tmp/lotsaints.copy'; DROP TABLE lotsaints; CREATE UNLOGGED TABLE lotsaints(a int, b int, c int); Benchmark: psql -qX -c 'truncate lotsaints' && pgbench -n -P1 -f <( echo "COPY lotsaints FROM '/tmp/lotsaints.copy';") -t 15 2864eb977 REL_15_STABLE latency average = 526.014 ms 8.84% postgres [.] pg_strtoint32 29452de73 Sat Dec 3 10:50:39 2022 -0500 The commit before "Improve performance of pg_strtointNN functions" latency average = 508.453 ms 10.21% postgres [.] pg_strtoint32 6b423ec67 Sun Dec 4 16:18:18 2022 +1300 Improve performance of pg_strtointNN functions latency average = 492.943 ms 7.73% postgres [.] pg_strtoint32 1939d2628 Fri Dec 9 10:08:44 2022 -0500 The commit before "Convert a few datatype input functions to use "soft" error reporting." latency average = 485.016 ms 8.43% postgres [.] pg_strtoint32 ccff2d20e Fri Dec 9 10:14:53 2022 -0500 Convert a few datatype input functions to use "soft" error reporting. latency average = 501.325 ms 6.90% postgres [.] pg_strtoint32_safe 60684dd83 Tue Dec 13 17:33:28 2022 -0800 The commit before "Non-decimal integer literals" latency average = 500.889 ms 8.27% postgres [.] pg_strtoint32_safe 6fcda9aba Wed Dec 14 05:40:38 2022 +0100 Non-decimal integer literals latency average = 521.904 ms 9.02% postgres [.] pg_strtoint32_safe 1b6f632a3 Sat Feb 4 07:56:09 2023 +0100 The commit before "Allow underscores in integer and numeric constants." latency average = 523.195 ms 9.21% postgres [.] pg_strtoint32_safe faff8f8e4 Sat Feb 4 09:48:51 2023 +0000 Allow underscores in integer and numeric constants. latency average = 493.064 ms 10.25% postgres [.] pg_strtoint32_safe 9f8377f7a Mon Mar 13 10:01:56 2023 -0400 Add a DEFAULT option to COPY FROM latency average = 597.617 ms 12.91% postgres [.] CopyReadLine 10.62% postgres [.] CopyReadAttributesText 10.51% postgres [.] pg_strtoint32_safe 7.97% postgres [.] NextCopyFrom REL_16_STABLE @ c1308ce2d Thu Jul 27 14:48:44 2023 +1200 Fix performance problem with new COPY DEFAULT code latency average = 530.344 ms 13.51% postgres [.] CopyReadLine 9.62% postgres [.] pg_strtoint32_safe 8.97% postgres [.] CopyReadAttributesText 8.43% postgres [.] NextCopyFrom REL_16_STABLE + pg_strtoint_fastpath1.patch latency average = 493.136 ms 13.79% postgres [.] CopyReadLine 11.82% postgres [.] CopyReadAttributesText 7.07% postgres [.] NextCopyFrom 6.81% postgres [.] pg_strtoint32_safe REL_16_STABLE + pg_strtoint_fastpath2.patch latency average = 483.790 ms 13.87% postgres [.] CopyReadLine 10.40% postgres [.] CopyReadAttributesText 8.22% postgres [.] NextCopyFrom 5.52% postgres [.] pg_strtoint32_safe David
