1 package pgx_test
2
3 import (
4 "bytes"
5 "context"
6 "fmt"
7 "io"
8 "net"
9 "os"
10 "strconv"
11 "strings"
12 "testing"
13 "time"
14
15 "github.com/jackc/pgx/v5"
16 "github.com/jackc/pgx/v5/pgconn"
17 "github.com/jackc/pgx/v5/pgtype"
18 "github.com/stretchr/testify/require"
19 )
20
21 func BenchmarkConnectClose(b *testing.B) {
22 for i := 0; i < b.N; i++ {
23 conn, err := pgx.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
24 if err != nil {
25 b.Fatal(err)
26 }
27
28 err = conn.Close(context.Background())
29 if err != nil {
30 b.Fatal(err)
31 }
32 }
33 }
34
35 func BenchmarkMinimalUnpreparedSelectWithoutStatementCache(b *testing.B) {
36 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
37 config.DefaultQueryExecMode = pgx.QueryExecModeDescribeExec
38 config.StatementCacheCapacity = 0
39 config.DescriptionCacheCapacity = 0
40
41 conn := mustConnect(b, config)
42 defer closeConn(b, conn)
43
44 var n int64
45
46 b.ResetTimer()
47 for i := 0; i < b.N; i++ {
48 err := conn.QueryRow(context.Background(), "select $1::int8", i).Scan(&n)
49 if err != nil {
50 b.Fatal(err)
51 }
52
53 if n != int64(i) {
54 b.Fatalf("expected %d, got %d", i, n)
55 }
56 }
57 }
58
59 func BenchmarkMinimalUnpreparedSelectWithStatementCacheModeDescribe(b *testing.B) {
60 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
61 config.DefaultQueryExecMode = pgx.QueryExecModeCacheDescribe
62 config.StatementCacheCapacity = 0
63 config.DescriptionCacheCapacity = 32
64
65 conn := mustConnect(b, config)
66 defer closeConn(b, conn)
67
68 var n int64
69
70 b.ResetTimer()
71 for i := 0; i < b.N; i++ {
72 err := conn.QueryRow(context.Background(), "select $1::int8", i).Scan(&n)
73 if err != nil {
74 b.Fatal(err)
75 }
76
77 if n != int64(i) {
78 b.Fatalf("expected %d, got %d", i, n)
79 }
80 }
81 }
82
83 func BenchmarkMinimalUnpreparedSelectWithStatementCacheModePrepare(b *testing.B) {
84 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
85 config.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement
86 config.StatementCacheCapacity = 32
87 config.DescriptionCacheCapacity = 0
88
89 conn := mustConnect(b, config)
90 defer closeConn(b, conn)
91
92 var n int64
93
94 b.ResetTimer()
95 for i := 0; i < b.N; i++ {
96 err := conn.QueryRow(context.Background(), "select $1::int8", i).Scan(&n)
97 if err != nil {
98 b.Fatal(err)
99 }
100
101 if n != int64(i) {
102 b.Fatalf("expected %d, got %d", i, n)
103 }
104 }
105 }
106
107 func BenchmarkMinimalPreparedSelect(b *testing.B) {
108 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
109 defer closeConn(b, conn)
110
111 _, err := conn.Prepare(context.Background(), "ps1", "select $1::int8")
112 if err != nil {
113 b.Fatal(err)
114 }
115
116 var n int64
117
118 b.ResetTimer()
119 for i := 0; i < b.N; i++ {
120 err = conn.QueryRow(context.Background(), "ps1", i).Scan(&n)
121 if err != nil {
122 b.Fatal(err)
123 }
124
125 if n != int64(i) {
126 b.Fatalf("expected %d, got %d", i, n)
127 }
128 }
129 }
130
131 func BenchmarkMinimalPgConnPreparedSelect(b *testing.B) {
132 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
133 defer closeConn(b, conn)
134
135 pgConn := conn.PgConn()
136
137 _, err := pgConn.Prepare(context.Background(), "ps1", "select $1::int8", nil)
138 if err != nil {
139 b.Fatal(err)
140 }
141
142 encodedBytes := make([]byte, 8)
143
144 b.ResetTimer()
145 for i := 0; i < b.N; i++ {
146
147 rr := pgConn.ExecPrepared(context.Background(), "ps1", [][]byte{encodedBytes}, []int16{1}, []int16{1})
148 if err != nil {
149 b.Fatal(err)
150 }
151
152 for rr.NextRow() {
153 for i := range rr.Values() {
154 if !bytes.Equal(rr.Values()[0], encodedBytes) {
155 b.Fatalf("unexpected values: %s %s", rr.Values()[i], encodedBytes)
156 }
157 }
158 }
159 _, err = rr.Close()
160 if err != nil {
161 b.Fatal(err)
162 }
163 }
164 }
165
166 func BenchmarkPointerPointerWithNullValues(b *testing.B) {
167 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
168 defer closeConn(b, conn)
169
170 _, err := conn.Prepare(context.Background(), "selectNulls", "select 1::int4, 'johnsmith', null::text, null::text, null::text, null::date, null::timestamptz")
171 if err != nil {
172 b.Fatal(err)
173 }
174
175 b.ResetTimer()
176 for i := 0; i < b.N; i++ {
177 var record struct {
178 id int32
179 userName string
180 email *string
181 name *string
182 sex *string
183 birthDate *time.Time
184 lastLoginTime *time.Time
185 }
186
187 err = conn.QueryRow(context.Background(), "selectNulls").Scan(
188 &record.id,
189 &record.userName,
190 &record.email,
191 &record.name,
192 &record.sex,
193 &record.birthDate,
194 &record.lastLoginTime,
195 )
196 if err != nil {
197 b.Fatal(err)
198 }
199
200
201
202 if record.id != 1 {
203 b.Fatalf("bad value for id: %v", record.id)
204 }
205 if record.userName != "johnsmith" {
206 b.Fatalf("bad value for userName: %v", record.userName)
207 }
208 if record.email != nil {
209 b.Fatalf("bad value for email: %v", record.email)
210 }
211 if record.name != nil {
212 b.Fatalf("bad value for name: %v", record.name)
213 }
214 if record.sex != nil {
215 b.Fatalf("bad value for sex: %v", record.sex)
216 }
217 if record.birthDate != nil {
218 b.Fatalf("bad value for birthDate: %v", record.birthDate)
219 }
220 if record.lastLoginTime != nil {
221 b.Fatalf("bad value for lastLoginTime: %v", record.lastLoginTime)
222 }
223 }
224 }
225
226 func BenchmarkPointerPointerWithPresentValues(b *testing.B) {
227 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
228 defer closeConn(b, conn)
229
230 _, err := conn.Prepare(context.Background(), "selectNulls", "select 1::int4, 'johnsmith', 'johnsmith@example.com', 'John Smith', 'male', '1970-01-01'::date, '2015-01-01 00:00:00'::timestamptz")
231 if err != nil {
232 b.Fatal(err)
233 }
234
235 b.ResetTimer()
236 for i := 0; i < b.N; i++ {
237 var record struct {
238 id int32
239 userName string
240 email *string
241 name *string
242 sex *string
243 birthDate *time.Time
244 lastLoginTime *time.Time
245 }
246
247 err = conn.QueryRow(context.Background(), "selectNulls").Scan(
248 &record.id,
249 &record.userName,
250 &record.email,
251 &record.name,
252 &record.sex,
253 &record.birthDate,
254 &record.lastLoginTime,
255 )
256 if err != nil {
257 b.Fatal(err)
258 }
259
260
261
262 if record.id != 1 {
263 b.Fatalf("bad value for id: %v", record.id)
264 }
265 if record.userName != "johnsmith" {
266 b.Fatalf("bad value for userName: %v", record.userName)
267 }
268 if record.email == nil || *record.email != "johnsmith@example.com" {
269 b.Fatalf("bad value for email: %v", record.email)
270 }
271 if record.name == nil || *record.name != "John Smith" {
272 b.Fatalf("bad value for name: %v", record.name)
273 }
274 if record.sex == nil || *record.sex != "male" {
275 b.Fatalf("bad value for sex: %v", record.sex)
276 }
277 if record.birthDate == nil || *record.birthDate != time.Date(1970, 1, 1, 0, 0, 0, 0, time.UTC) {
278 b.Fatalf("bad value for birthDate: %v", record.birthDate)
279 }
280 if record.lastLoginTime == nil || *record.lastLoginTime != time.Date(2015, 1, 1, 0, 0, 0, 0, time.Local) {
281 b.Fatalf("bad value for lastLoginTime: %v", record.lastLoginTime)
282 }
283 }
284 }
285
286 const benchmarkWriteTableCreateSQL = `drop table if exists t;
287
288 create table t(
289 varchar_1 varchar not null,
290 varchar_2 varchar not null,
291 varchar_null_1 varchar,
292 date_1 date not null,
293 date_null_1 date,
294 int4_1 int4 not null,
295 int4_2 int4 not null,
296 int4_null_1 int4,
297 tstz_1 timestamptz not null,
298 tstz_2 timestamptz,
299 bool_1 bool not null,
300 bool_2 bool not null,
301 bool_3 bool not null
302 );
303 `
304
305 const benchmarkWriteTableInsertSQL = `insert into t(
306 varchar_1,
307 varchar_2,
308 varchar_null_1,
309 date_1,
310 date_null_1,
311 int4_1,
312 int4_2,
313 int4_null_1,
314 tstz_1,
315 tstz_2,
316 bool_1,
317 bool_2,
318 bool_3
319 ) values (
320 $1::varchar,
321 $2::varchar,
322 $3::varchar,
323 $4::date,
324 $5::date,
325 $6::int4,
326 $7::int4,
327 $8::int4,
328 $9::timestamptz,
329 $10::timestamptz,
330 $11::bool,
331 $12::bool,
332 $13::bool
333 )`
334
335 type benchmarkWriteTableCopyFromSrc struct {
336 count int
337 idx int
338 row []any
339 }
340
341 func (s *benchmarkWriteTableCopyFromSrc) Next() bool {
342 next := s.idx < s.count
343 s.idx++
344 return next
345 }
346
347 func (s *benchmarkWriteTableCopyFromSrc) Values() ([]any, error) {
348 return s.row, nil
349 }
350
351 func (s *benchmarkWriteTableCopyFromSrc) Err() error {
352 return nil
353 }
354
355 func newBenchmarkWriteTableCopyFromSrc(count int) pgx.CopyFromSource {
356 return &benchmarkWriteTableCopyFromSrc{
357 count: count,
358 row: []any{
359 "varchar_1",
360 "varchar_2",
361 &pgtype.Text{},
362 time.Date(2000, 1, 1, 0, 0, 0, 0, time.Local),
363 &pgtype.Date{},
364 1,
365 2,
366 &pgtype.Int4{},
367 time.Date(2001, 1, 1, 0, 0, 0, 0, time.Local),
368 time.Date(2002, 1, 1, 0, 0, 0, 0, time.Local),
369 true,
370 false,
371 true,
372 },
373 }
374 }
375
376 func benchmarkWriteNRowsViaInsert(b *testing.B, n int) {
377 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
378 defer closeConn(b, conn)
379
380 mustExec(b, conn, benchmarkWriteTableCreateSQL)
381 _, err := conn.Prepare(context.Background(), "insert_t", benchmarkWriteTableInsertSQL)
382 if err != nil {
383 b.Fatal(err)
384 }
385
386 b.ResetTimer()
387
388 for i := 0; i < b.N; i++ {
389 src := newBenchmarkWriteTableCopyFromSrc(n)
390
391 tx, err := conn.Begin(context.Background())
392 if err != nil {
393 b.Fatal(err)
394 }
395
396 for src.Next() {
397 values, _ := src.Values()
398 if _, err = tx.Exec(context.Background(), "insert_t", values...); err != nil {
399 b.Fatalf("Exec unexpectedly failed with: %v", err)
400 }
401 }
402
403 err = tx.Commit(context.Background())
404 if err != nil {
405 b.Fatal(err)
406 }
407 }
408 }
409
410 func benchmarkWriteNRowsViaBatchInsert(b *testing.B, n int) {
411 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
412 defer closeConn(b, conn)
413
414 mustExec(b, conn, benchmarkWriteTableCreateSQL)
415 _, err := conn.Prepare(context.Background(), "insert_t", benchmarkWriteTableInsertSQL)
416 if err != nil {
417 b.Fatal(err)
418 }
419
420 b.ResetTimer()
421
422 for i := 0; i < b.N; i++ {
423 src := newBenchmarkWriteTableCopyFromSrc(n)
424
425 batch := &pgx.Batch{}
426 for src.Next() {
427 values, _ := src.Values()
428 batch.Queue("insert_t", values...)
429 }
430
431 err = conn.SendBatch(context.Background(), batch).Close()
432 if err != nil {
433 b.Fatal(err)
434 }
435 }
436 }
437
438 type queryArgs []any
439
440 func (qa *queryArgs) Append(v any) string {
441 *qa = append(*qa, v)
442 return "$" + strconv.Itoa(len(*qa))
443 }
444
445
446
447 func multiInsert(conn *pgx.Conn, tableName string, columnNames []string, rowSrc pgx.CopyFromSource) (int, error) {
448 maxRowsPerInsert := 65535 / len(columnNames)
449 rowsThisInsert := 0
450 rowCount := 0
451
452 sqlBuf := &bytes.Buffer{}
453 args := make(queryArgs, 0)
454
455 resetQuery := func() {
456 sqlBuf.Reset()
457 fmt.Fprintf(sqlBuf, "insert into %s(%s) values", tableName, strings.Join(columnNames, ", "))
458
459 args = args[0:0]
460
461 rowsThisInsert = 0
462 }
463 resetQuery()
464
465 tx, err := conn.Begin(context.Background())
466 if err != nil {
467 return 0, err
468 }
469 defer tx.Rollback(context.Background())
470
471 for rowSrc.Next() {
472 if rowsThisInsert > 0 {
473 sqlBuf.WriteByte(',')
474 }
475
476 sqlBuf.WriteByte('(')
477
478 values, err := rowSrc.Values()
479 if err != nil {
480 return 0, err
481 }
482
483 for i, val := range values {
484 if i > 0 {
485 sqlBuf.WriteByte(',')
486 }
487 sqlBuf.WriteString(args.Append(val))
488 }
489
490 sqlBuf.WriteByte(')')
491
492 rowsThisInsert++
493
494 if rowsThisInsert == maxRowsPerInsert {
495 _, err := tx.Exec(context.Background(), sqlBuf.String(), args...)
496 if err != nil {
497 return 0, err
498 }
499
500 rowCount += rowsThisInsert
501 resetQuery()
502 }
503 }
504
505 if rowsThisInsert > 0 {
506 _, err := tx.Exec(context.Background(), sqlBuf.String(), args...)
507 if err != nil {
508 return 0, err
509 }
510
511 rowCount += rowsThisInsert
512 }
513
514 if err := tx.Commit(context.Background()); err != nil {
515 return 0, err
516 }
517
518 return rowCount, nil
519
520 }
521
522 func benchmarkWriteNRowsViaMultiInsert(b *testing.B, n int) {
523 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
524 defer closeConn(b, conn)
525
526 mustExec(b, conn, benchmarkWriteTableCreateSQL)
527 _, err := conn.Prepare(context.Background(), "insert_t", benchmarkWriteTableInsertSQL)
528 if err != nil {
529 b.Fatal(err)
530 }
531
532 b.ResetTimer()
533
534 for i := 0; i < b.N; i++ {
535 src := newBenchmarkWriteTableCopyFromSrc(n)
536
537 _, err := multiInsert(conn, "t",
538 []string{"varchar_1",
539 "varchar_2",
540 "varchar_null_1",
541 "date_1",
542 "date_null_1",
543 "int4_1",
544 "int4_2",
545 "int4_null_1",
546 "tstz_1",
547 "tstz_2",
548 "bool_1",
549 "bool_2",
550 "bool_3"},
551 src)
552 if err != nil {
553 b.Fatal(err)
554 }
555 }
556 }
557
558 func benchmarkWriteNRowsViaCopy(b *testing.B, n int) {
559 conn := mustConnect(b, mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE")))
560 defer closeConn(b, conn)
561
562 mustExec(b, conn, benchmarkWriteTableCreateSQL)
563
564 b.ResetTimer()
565
566 for i := 0; i < b.N; i++ {
567 src := newBenchmarkWriteTableCopyFromSrc(n)
568
569 _, err := conn.CopyFrom(context.Background(),
570 pgx.Identifier{"t"},
571 []string{"varchar_1",
572 "varchar_2",
573 "varchar_null_1",
574 "date_1",
575 "date_null_1",
576 "int4_1",
577 "int4_2",
578 "int4_null_1",
579 "tstz_1",
580 "tstz_2",
581 "bool_1",
582 "bool_2",
583 "bool_3"},
584 src)
585 if err != nil {
586 b.Fatal(err)
587 }
588 }
589 }
590
591 func BenchmarkWrite2RowsViaInsert(b *testing.B) {
592 benchmarkWriteNRowsViaInsert(b, 2)
593 }
594
595 func BenchmarkWrite2RowsViaMultiInsert(b *testing.B) {
596 benchmarkWriteNRowsViaMultiInsert(b, 2)
597 }
598
599 func BenchmarkWrite2RowsViaBatchInsert(b *testing.B) {
600 benchmarkWriteNRowsViaBatchInsert(b, 2)
601 }
602
603 func BenchmarkWrite2RowsViaCopy(b *testing.B) {
604 benchmarkWriteNRowsViaCopy(b, 2)
605 }
606
607 func BenchmarkWrite5RowsViaInsert(b *testing.B) {
608 benchmarkWriteNRowsViaInsert(b, 5)
609 }
610
611 func BenchmarkWrite5RowsViaMultiInsert(b *testing.B) {
612 benchmarkWriteNRowsViaMultiInsert(b, 5)
613 }
614 func BenchmarkWrite5RowsViaBatchInsert(b *testing.B) {
615 benchmarkWriteNRowsViaBatchInsert(b, 5)
616 }
617
618 func BenchmarkWrite5RowsViaCopy(b *testing.B) {
619 benchmarkWriteNRowsViaCopy(b, 5)
620 }
621
622 func BenchmarkWrite10RowsViaInsert(b *testing.B) {
623 benchmarkWriteNRowsViaInsert(b, 10)
624 }
625
626 func BenchmarkWrite10RowsViaMultiInsert(b *testing.B) {
627 benchmarkWriteNRowsViaMultiInsert(b, 10)
628 }
629 func BenchmarkWrite10RowsViaBatchInsert(b *testing.B) {
630 benchmarkWriteNRowsViaBatchInsert(b, 10)
631 }
632
633 func BenchmarkWrite10RowsViaCopy(b *testing.B) {
634 benchmarkWriteNRowsViaCopy(b, 10)
635 }
636
637 func BenchmarkWrite100RowsViaInsert(b *testing.B) {
638 benchmarkWriteNRowsViaInsert(b, 100)
639 }
640
641 func BenchmarkWrite100RowsViaMultiInsert(b *testing.B) {
642 benchmarkWriteNRowsViaMultiInsert(b, 100)
643 }
644 func BenchmarkWrite100RowsViaBatchInsert(b *testing.B) {
645 benchmarkWriteNRowsViaBatchInsert(b, 100)
646 }
647
648 func BenchmarkWrite100RowsViaCopy(b *testing.B) {
649 benchmarkWriteNRowsViaCopy(b, 100)
650 }
651
652 func BenchmarkWrite1000RowsViaInsert(b *testing.B) {
653 benchmarkWriteNRowsViaInsert(b, 1000)
654 }
655
656 func BenchmarkWrite1000RowsViaMultiInsert(b *testing.B) {
657 benchmarkWriteNRowsViaMultiInsert(b, 1000)
658 }
659
660 func BenchmarkWrite1000RowsViaBatchInsert(b *testing.B) {
661 benchmarkWriteNRowsViaBatchInsert(b, 1000)
662 }
663
664 func BenchmarkWrite1000RowsViaCopy(b *testing.B) {
665 benchmarkWriteNRowsViaCopy(b, 1000)
666 }
667
668 func BenchmarkWrite10000RowsViaInsert(b *testing.B) {
669 benchmarkWriteNRowsViaInsert(b, 10000)
670 }
671
672 func BenchmarkWrite10000RowsViaMultiInsert(b *testing.B) {
673 benchmarkWriteNRowsViaMultiInsert(b, 10000)
674 }
675 func BenchmarkWrite10000RowsViaBatchInsert(b *testing.B) {
676 benchmarkWriteNRowsViaBatchInsert(b, 10000)
677 }
678
679 func BenchmarkWrite10000RowsViaCopy(b *testing.B) {
680 benchmarkWriteNRowsViaCopy(b, 10000)
681 }
682
683 func BenchmarkMultipleQueriesNonBatchNoStatementCache(b *testing.B) {
684 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
685 config.DefaultQueryExecMode = pgx.QueryExecModeDescribeExec
686 config.StatementCacheCapacity = 0
687 config.DescriptionCacheCapacity = 0
688
689 conn := mustConnect(b, config)
690 defer closeConn(b, conn)
691
692 benchmarkMultipleQueriesNonBatch(b, conn, 3)
693 }
694
695 func BenchmarkMultipleQueriesNonBatchPrepareStatementCache(b *testing.B) {
696 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
697 config.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement
698 config.StatementCacheCapacity = 32
699 config.DescriptionCacheCapacity = 0
700
701 conn := mustConnect(b, config)
702 defer closeConn(b, conn)
703
704 benchmarkMultipleQueriesNonBatch(b, conn, 3)
705 }
706
707 func BenchmarkMultipleQueriesNonBatchDescribeStatementCache(b *testing.B) {
708 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
709 config.DefaultQueryExecMode = pgx.QueryExecModeCacheDescribe
710 config.StatementCacheCapacity = 0
711 config.DescriptionCacheCapacity = 32
712
713 conn := mustConnect(b, config)
714 defer closeConn(b, conn)
715
716 benchmarkMultipleQueriesNonBatch(b, conn, 3)
717 }
718
719 func benchmarkMultipleQueriesNonBatch(b *testing.B, conn *pgx.Conn, queryCount int) {
720 b.ResetTimer()
721 for i := 0; i < b.N; i++ {
722 for j := 0; j < queryCount; j++ {
723 rows, err := conn.Query(context.Background(), "select n from generate_series(0, 5) n")
724 if err != nil {
725 b.Fatal(err)
726 }
727
728 for k := 0; rows.Next(); k++ {
729 var n int
730 if err := rows.Scan(&n); err != nil {
731 b.Fatal(err)
732 }
733 if n != k {
734 b.Fatalf("n => %v, want %v", n, k)
735 }
736 }
737
738 if rows.Err() != nil {
739 b.Fatal(rows.Err())
740 }
741 }
742 }
743 }
744
745 func BenchmarkMultipleQueriesBatchNoStatementCache(b *testing.B) {
746 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
747 config.DefaultQueryExecMode = pgx.QueryExecModeDescribeExec
748 config.StatementCacheCapacity = 0
749 config.DescriptionCacheCapacity = 0
750
751 conn := mustConnect(b, config)
752 defer closeConn(b, conn)
753
754 benchmarkMultipleQueriesBatch(b, conn, 3)
755 }
756
757 func BenchmarkMultipleQueriesBatchPrepareStatementCache(b *testing.B) {
758 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
759 config.DefaultQueryExecMode = pgx.QueryExecModeCacheStatement
760 config.StatementCacheCapacity = 32
761 config.DescriptionCacheCapacity = 0
762
763 conn := mustConnect(b, config)
764 defer closeConn(b, conn)
765
766 benchmarkMultipleQueriesBatch(b, conn, 3)
767 }
768
769 func BenchmarkMultipleQueriesBatchDescribeStatementCache(b *testing.B) {
770 config := mustParseConfig(b, os.Getenv("PGX_TEST_DATABASE"))
771 config.DefaultQueryExecMode = pgx.QueryExecModeCacheDescribe
772 config.StatementCacheCapacity = 0
773 config.DescriptionCacheCapacity = 32
774
775 conn := mustConnect(b, config)
776 defer closeConn(b, conn)
777
778 benchmarkMultipleQueriesBatch(b, conn, 3)
779 }
780
781 func benchmarkMultipleQueriesBatch(b *testing.B, conn *pgx.Conn, queryCount int) {
782 b.ResetTimer()
783 for i := 0; i < b.N; i++ {
784 batch := &pgx.Batch{}
785 for j := 0; j < queryCount; j++ {
786 batch.Queue("select n from generate_series(0,5) n")
787 }
788
789 br := conn.SendBatch(context.Background(), batch)
790
791 for j := 0; j < queryCount; j++ {
792 rows, err := br.Query()
793 if err != nil {
794 b.Fatal(err)
795 }
796
797 for k := 0; rows.Next(); k++ {
798 var n int
799 if err := rows.Scan(&n); err != nil {
800 b.Fatal(err)
801 }
802 if n != k {
803 b.Fatalf("n => %v, want %v", n, k)
804 }
805 }
806
807 if rows.Err() != nil {
808 b.Fatal(rows.Err())
809 }
810 }
811
812 err := br.Close()
813 if err != nil {
814 b.Fatal(err)
815 }
816 }
817 }
818
819 func BenchmarkSelectManyUnknownEnum(b *testing.B) {
820 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
821 defer closeConn(b, conn)
822
823 ctx := context.Background()
824 tx, err := conn.Begin(ctx)
825 require.NoError(b, err)
826 defer tx.Rollback(ctx)
827
828 _, err = tx.Exec(context.Background(), "drop type if exists color;")
829 require.NoError(b, err)
830
831 _, err = tx.Exec(ctx, `create type color as enum ('blue', 'green', 'orange')`)
832 require.NoError(b, err)
833
834 b.ResetTimer()
835 var x, y, z string
836 for i := 0; i < b.N; i++ {
837 rows, err := conn.Query(ctx, "select 'blue'::color, 'green'::color, 'orange'::color from generate_series(1,10)")
838 if err != nil {
839 b.Fatal(err)
840 }
841
842 for rows.Next() {
843 err = rows.Scan(&x, &y, &z)
844 if err != nil {
845 b.Fatal(err)
846 }
847
848 if x != "blue" {
849 b.Fatal("unexpected result")
850 }
851 if y != "green" {
852 b.Fatal("unexpected result")
853 }
854 if z != "orange" {
855 b.Fatal("unexpected result")
856 }
857 }
858
859 if rows.Err() != nil {
860 b.Fatal(rows.Err())
861 }
862 }
863 }
864
865 func BenchmarkSelectManyRegisteredEnum(b *testing.B) {
866 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
867 defer closeConn(b, conn)
868
869 ctx := context.Background()
870 tx, err := conn.Begin(ctx)
871 require.NoError(b, err)
872 defer tx.Rollback(ctx)
873
874 _, err = tx.Exec(context.Background(), "drop type if exists color;")
875 require.NoError(b, err)
876
877 _, err = tx.Exec(ctx, `create type color as enum ('blue', 'green', 'orange')`)
878 require.NoError(b, err)
879
880 var oid uint32
881 err = conn.QueryRow(context.Background(), "select oid from pg_type where typname=$1;", "color").Scan(&oid)
882 require.NoError(b, err)
883
884 conn.TypeMap().RegisterType(&pgtype.Type{Name: "color", OID: oid, Codec: &pgtype.EnumCodec{}})
885
886 b.ResetTimer()
887 var x, y, z string
888 for i := 0; i < b.N; i++ {
889 rows, err := conn.Query(ctx, "select 'blue'::color, 'green'::color, 'orange'::color from generate_series(1,10)")
890 if err != nil {
891 b.Fatal(err)
892 }
893
894 for rows.Next() {
895 err = rows.Scan(&x, &y, &z)
896 if err != nil {
897 b.Fatal(err)
898 }
899
900 if x != "blue" {
901 b.Fatal("unexpected result")
902 }
903 if y != "green" {
904 b.Fatal("unexpected result")
905 }
906 if z != "orange" {
907 b.Fatal("unexpected result")
908 }
909 }
910
911 if rows.Err() != nil {
912 b.Fatal(rows.Err())
913 }
914 }
915 }
916
917 func getSelectRowsCounts(b *testing.B) []int64 {
918 var rowCounts []int64
919 {
920 s := os.Getenv("PGX_BENCH_SELECT_ROWS_COUNTS")
921 if s != "" {
922 for _, p := range strings.Split(s, " ") {
923 n, err := strconv.ParseInt(p, 10, 64)
924 if err != nil {
925 b.Fatalf("Bad PGX_BENCH_SELECT_ROWS_COUNTS value: %v", err)
926 }
927 rowCounts = append(rowCounts, n)
928 }
929 }
930 }
931
932 if len(rowCounts) == 0 {
933 rowCounts = []int64{1, 10, 100, 1000}
934 }
935
936 return rowCounts
937 }
938
939 type BenchRowSimple struct {
940 ID int32
941 FirstName string
942 LastName string
943 Sex string
944 BirthDate time.Time
945 Weight int32
946 Height int32
947 UpdateTime time.Time
948 }
949
950 func BenchmarkSelectRowsScanSimple(b *testing.B) {
951 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
952 defer closeConn(b, conn)
953
954 rowCounts := getSelectRowsCounts(b)
955
956 for _, rowCount := range rowCounts {
957 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
958 br := &BenchRowSimple{}
959 for i := 0; i < b.N; i++ {
960 rows, err := conn.Query(context.Background(), "select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + $1) n", rowCount)
961 if err != nil {
962 b.Fatal(err)
963 }
964
965 for rows.Next() {
966 rows.Scan(&br.ID, &br.FirstName, &br.LastName, &br.Sex, &br.BirthDate, &br.Weight, &br.Height, &br.UpdateTime)
967 }
968
969 if rows.Err() != nil {
970 b.Fatal(rows.Err())
971 }
972 }
973 })
974 }
975 }
976
977 type BenchRowStringBytes struct {
978 ID int32
979 FirstName []byte
980 LastName []byte
981 Sex []byte
982 BirthDate time.Time
983 Weight int32
984 Height int32
985 UpdateTime time.Time
986 }
987
988 func BenchmarkSelectRowsScanStringBytes(b *testing.B) {
989 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
990 defer closeConn(b, conn)
991
992 rowCounts := getSelectRowsCounts(b)
993
994 for _, rowCount := range rowCounts {
995 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
996 br := &BenchRowStringBytes{}
997 for i := 0; i < b.N; i++ {
998 rows, err := conn.Query(context.Background(), "select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + $1) n", rowCount)
999 if err != nil {
1000 b.Fatal(err)
1001 }
1002
1003 for rows.Next() {
1004 rows.Scan(&br.ID, &br.FirstName, &br.LastName, &br.Sex, &br.BirthDate, &br.Weight, &br.Height, &br.UpdateTime)
1005 }
1006
1007 if rows.Err() != nil {
1008 b.Fatal(rows.Err())
1009 }
1010 }
1011 })
1012 }
1013 }
1014
1015 type BenchRowDecoder struct {
1016 ID pgtype.Int4
1017 FirstName pgtype.Text
1018 LastName pgtype.Text
1019 Sex pgtype.Text
1020 BirthDate pgtype.Date
1021 Weight pgtype.Int4
1022 Height pgtype.Int4
1023 UpdateTime pgtype.Timestamptz
1024 }
1025
1026 func BenchmarkSelectRowsScanDecoder(b *testing.B) {
1027 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
1028 defer closeConn(b, conn)
1029
1030 rowCounts := getSelectRowsCounts(b)
1031
1032 for _, rowCount := range rowCounts {
1033 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
1034 formats := []struct {
1035 name string
1036 code int16
1037 }{
1038 {"text", pgx.TextFormatCode},
1039 {"binary", pgx.BinaryFormatCode},
1040 }
1041 for _, format := range formats {
1042 b.Run(format.name, func(b *testing.B) {
1043
1044 br := &BenchRowDecoder{}
1045 for i := 0; i < b.N; i++ {
1046 rows, err := conn.Query(
1047 context.Background(),
1048 "select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + $1) n",
1049 pgx.QueryResultFormats{format.code},
1050 rowCount,
1051 )
1052 if err != nil {
1053 b.Fatal(err)
1054 }
1055
1056 for rows.Next() {
1057 rows.Scan(&br.ID, &br.FirstName, &br.LastName, &br.Sex, &br.BirthDate, &br.Weight, &br.Height, &br.UpdateTime)
1058 }
1059
1060 if rows.Err() != nil {
1061 b.Fatal(rows.Err())
1062 }
1063 }
1064 })
1065 }
1066 })
1067 }
1068 }
1069
1070 func BenchmarkSelectRowsPgConnExecText(b *testing.B) {
1071 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
1072 defer closeConn(b, conn)
1073
1074 rowCounts := getSelectRowsCounts(b)
1075
1076 for _, rowCount := range rowCounts {
1077 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
1078 for i := 0; i < b.N; i++ {
1079 mrr := conn.PgConn().Exec(context.Background(), fmt.Sprintf("select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + %d) n", rowCount))
1080 for mrr.NextResult() {
1081 rr := mrr.ResultReader()
1082 for rr.NextRow() {
1083 rr.Values()
1084 }
1085 }
1086
1087 err := mrr.Close()
1088 if err != nil {
1089 b.Fatal(err)
1090 }
1091 }
1092 })
1093 }
1094 }
1095
1096 func BenchmarkSelectRowsPgConnExecParams(b *testing.B) {
1097 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
1098 defer closeConn(b, conn)
1099
1100 rowCounts := getSelectRowsCounts(b)
1101
1102 for _, rowCount := range rowCounts {
1103 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
1104 formats := []struct {
1105 name string
1106 code int16
1107 }{
1108 {"text", pgx.TextFormatCode},
1109 {"binary - mostly", pgx.BinaryFormatCode},
1110 }
1111 for _, format := range formats {
1112 b.Run(format.name, func(b *testing.B) {
1113 for i := 0; i < b.N; i++ {
1114 rr := conn.PgConn().ExecParams(
1115 context.Background(),
1116 "select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + $1) n",
1117 [][]byte{[]byte(strconv.FormatInt(rowCount, 10))},
1118 nil,
1119 nil,
1120 []int16{format.code, pgx.TextFormatCode, pgx.TextFormatCode, pgx.TextFormatCode, format.code, format.code, format.code, format.code},
1121 )
1122 for rr.NextRow() {
1123 rr.Values()
1124 }
1125
1126 _, err := rr.Close()
1127 if err != nil {
1128 b.Fatal(err)
1129 }
1130 }
1131 })
1132 }
1133 })
1134 }
1135 }
1136
1137 func BenchmarkSelectRowsPgConnExecPrepared(b *testing.B) {
1138 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE"))
1139 defer closeConn(b, conn)
1140
1141 rowCounts := getSelectRowsCounts(b)
1142
1143 _, err := conn.PgConn().Prepare(context.Background(), "ps1", "select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + $1) n", nil)
1144 if err != nil {
1145 b.Fatal(err)
1146 }
1147
1148 for _, rowCount := range rowCounts {
1149 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
1150 formats := []struct {
1151 name string
1152 code int16
1153 }{
1154 {"text", pgx.TextFormatCode},
1155 {"binary - mostly", pgx.BinaryFormatCode},
1156 }
1157 for _, format := range formats {
1158 b.Run(format.name, func(b *testing.B) {
1159 for i := 0; i < b.N; i++ {
1160 rr := conn.PgConn().ExecPrepared(
1161 context.Background(),
1162 "ps1",
1163 [][]byte{[]byte(strconv.FormatInt(rowCount, 10))},
1164 nil,
1165 []int16{format.code, pgx.TextFormatCode, pgx.TextFormatCode, pgx.TextFormatCode, format.code, format.code, format.code, format.code},
1166 )
1167 for rr.NextRow() {
1168 rr.Values()
1169 }
1170
1171 _, err := rr.Close()
1172 if err != nil {
1173 b.Fatal(err)
1174 }
1175 }
1176 })
1177 }
1178 })
1179 }
1180 }
1181
1182 type queryRecorder struct {
1183 conn net.Conn
1184 writeBuf []byte
1185 readCount int
1186 }
1187
1188 func (qr *queryRecorder) Read(b []byte) (n int, err error) {
1189 n, err = qr.conn.Read(b)
1190 qr.readCount += n
1191 return n, err
1192 }
1193
1194 func (qr *queryRecorder) Write(b []byte) (n int, err error) {
1195 qr.writeBuf = append(qr.writeBuf, b...)
1196 return qr.conn.Write(b)
1197 }
1198
1199 func (qr *queryRecorder) Close() error {
1200 return qr.conn.Close()
1201 }
1202
1203 func (qr *queryRecorder) LocalAddr() net.Addr {
1204 return qr.conn.LocalAddr()
1205 }
1206
1207 func (qr *queryRecorder) RemoteAddr() net.Addr {
1208 return qr.conn.RemoteAddr()
1209 }
1210
1211 func (qr *queryRecorder) SetDeadline(t time.Time) error {
1212 return qr.conn.SetDeadline(t)
1213 }
1214
1215 func (qr *queryRecorder) SetReadDeadline(t time.Time) error {
1216 return qr.conn.SetReadDeadline(t)
1217 }
1218
1219 func (qr *queryRecorder) SetWriteDeadline(t time.Time) error {
1220 return qr.conn.SetWriteDeadline(t)
1221 }
1222
1223
1224
1225
1226
1227 func BenchmarkSelectRowsRawPrepared(b *testing.B) {
1228 rowCounts := getSelectRowsCounts(b)
1229
1230 for _, rowCount := range rowCounts {
1231 b.Run(fmt.Sprintf("%d rows", rowCount), func(b *testing.B) {
1232 formats := []struct {
1233 name string
1234 code int16
1235 }{
1236 {"text", pgx.TextFormatCode},
1237 {"binary - mostly", pgx.BinaryFormatCode},
1238 }
1239 for _, format := range formats {
1240 b.Run(format.name, func(b *testing.B) {
1241 conn := mustConnectString(b, os.Getenv("PGX_TEST_DATABASE")).PgConn()
1242 defer conn.Close(context.Background())
1243
1244 _, err := conn.Prepare(context.Background(), "ps1", "select n, 'Adam', 'Smith ' || n, 'male', '1952-06-16'::date, 258, 72, '2001-01-28 01:02:03-05'::timestamptz from generate_series(100001, 100000 + $1) n", nil)
1245 if err != nil {
1246 b.Fatal(err)
1247 }
1248
1249 hijackedConn, err := conn.Hijack()
1250 require.NoError(b, err)
1251
1252 qr := &queryRecorder{
1253 conn: hijackedConn.Conn,
1254 }
1255
1256 hijackedConn.Conn = qr
1257 hijackedConn.Frontend = hijackedConn.Config.BuildFrontend(qr, qr)
1258 conn, err = pgconn.Construct(hijackedConn)
1259 require.NoError(b, err)
1260
1261 {
1262 rr := conn.ExecPrepared(
1263 context.Background(),
1264 "ps1",
1265 [][]byte{[]byte(strconv.FormatInt(rowCount, 10))},
1266 nil,
1267 []int16{format.code, pgx.TextFormatCode, pgx.TextFormatCode, pgx.TextFormatCode, format.code, format.code, format.code, format.code},
1268 )
1269 _, err := rr.Close()
1270 require.NoError(b, err)
1271 }
1272
1273 buf := make([]byte, qr.readCount)
1274
1275 b.ResetTimer()
1276 for i := 0; i < b.N; i++ {
1277 _, err := qr.conn.Write(qr.writeBuf)
1278 if err != nil {
1279 b.Fatal(err)
1280 }
1281
1282 _, err = io.ReadFull(qr.conn, buf)
1283 if err != nil {
1284 b.Fatal(err)
1285 }
1286 }
1287 })
1288 }
1289 })
1290 }
1291 }
1292
View as plain text