pgx: CopyFrom is ~80x slower in v5.2.0 versus v4.17.2

Describe the bug CopyFrom in v5.2.0 is ~80x slower than CopyFrom in v4.17.2 Likely because of the new logic implemented in: func (pgConn *PgConn) CopyFrom(ctx context.Context, r io.Reader, sql string) (CommandTag, error)

Here is the bench result:

goos: windows goarch: amd64 pkg: nph/bench cpu: Intel® Core™ i5-8400 CPU @ 2.80GHz BenchmarkPgx4-6 10 106087930 ns/op 363762 B/op 66 allocs/op BenchmarkPgx5-6 1 8003570000 ns/op 2422688 B/op 100559 allocs/op PASS ok nph/bench 11.587s

To Reproduce Run the bench provided here below

package bench

import (
	"context"
	"testing"

	pgx4 "github.com/jackc/pgx/v4"
	pgx5 "github.com/jackc/pgx/v5"
)

var connString = "postgres://xxx"

func BenchmarkPgx4(b *testing.B) {

	ctx := context.Background()
	conn, err := pgx4.Connect(ctx, connString)
	if err != nil {
		panic(err)
	}
	_, err = conn.Exec(ctx, "CREATE TABLE IF NOT EXISTS test_bench(int_col BIGINT, text_col TEXT)")
	if err != nil {
		panic(err)
	}
	_, err = conn.Exec(ctx, "TRUNCATE TABLE test_bench")
	if err != nil {
		panic(err)
	}
	rows := make([][]any, 100000)
	for i := range rows {
		rows[i] = []any{123, "456789"}
	}
	b.ResetTimer()
	var count int64
	for n := 0; n < b.N; n++ {
		count, err = conn.CopyFrom(ctx, pgx4.Identifier{"test_bench"}, []string{"int_col", "text_col"}, pgx4.CopyFromRows(rows))
		if err != nil {
			panic(err)
		}
	}
	if int(count) != len(rows) {
		b.Fatal("bad result")
	}
}

func BenchmarkPgx5(b *testing.B) {

	ctx := context.Background()
	conn, err := pgx5.Connect(ctx, connString)
	if err != nil {
		panic(err)
	}
	_, err = conn.Exec(ctx, "CREATE TABLE IF NOT EXISTS test_bench(int_col BIGINT, text_col TEXT)")
	if err != nil {
		panic(err)
	}
	_, err = conn.Exec(ctx, "TRUNCATE TABLE test_bench")
	if err != nil {
		panic(err)
	}
	rows := make([][]any, 100000)
	for i := range rows {
		rows[i] = []any{123, "456789"}
	}
	b.ResetTimer()
	var count int64
	for n := 0; n < b.N; n++ {
		count, err = conn.CopyFrom(ctx, pgx5.Identifier{"test_bench"}, []string{"int_col", "text_col"}, pgx5.CopyFromRows(rows))
		if err != nil {
			panic(err)
		}
	}
	if int(count) != len(rows) {
		b.Fatal("bad result")
	}
}

Expected behavior I would expect CopyFrom v5.x to be roughly at par with CopyFrom v4.x performance-wise

Actual behavior CopyFrom v5.2.0 is 80x slower than v4.17.2

Version

  • Go: v1.19.5
  • PostgreSQL: v12.0 x64
  • pgx: v4.17.2 vs v5.2.0

Additional context

About this issue

  • Original URL
  • State: closed
  • Created a year ago
  • Comments: 19 (13 by maintainers)

Commits related to this issue

Most upvoted comments

v5.3.0 was released today.