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
- BufferReadUntilBlock should release buf when no bytes read This was causing allocations every time there was a non-blocking read with nothing to read. https://github.com/jackc/pgx/issues/1481 — committed to jackc/pgx by jackc a year ago
- Memoize encode plans This significantly reduces memory allocations in paths that repeatedly encode the same type of values such as CopyFrom. https://github.com/jackc/pgx/issues/1481 — committed to jackc/pgx by jackc a year ago
- Only create RawConn.Write callback once This saves an allocation on every call. https://github.com/jackc/pgx/issues/1481 — committed to jackc/pgx by jackc a year ago
- Save memory on non blocking read path Only create RawConn.Read callback once and have it use NetConn fields. Avoids the closure and some allocations. https://github.com/jackc/pgx/issues/1481 — committed to jackc/pgx by jackc a year ago
- Fake non-blocking read adapts its max wait time The reason for a high max wait time was to ensure that reads aren't cancelled when there is data waiting for it in Go or the OS's receive buffer. Unfor... — committed to jackc/pgx by jackc a year ago
v5.3.0
was released today.