pg.ddx.io  psycopg/psycopg GitHub issues and pull requests  
help / color / mirror / Atom feed
From: DylanYoung (@DylanYoung) <noreply+DylanYoung@github.com>
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
Subject: [psycopg/psycopg] PR #1324: Faster C Load Sequence
Date: Sat, 13 Jun 2026 19:45:49 +0000
Message-ID: <gh-psycopg-psycopg-1324@github.com> (raw)

Fixes #1191. 

Also encapsulates the API for use in other work (e.g. replication and #1323 or perf follow-up). 

I haven't done a proper benchmark, but I updated the `tests/scripts/copytest.py` to check some basic numbers.  Looks like about a 20% to 35% speedup.   Interestingly, there seems to be an unrelated pessimization in the async code path that doesn't appear on the "COPY IN" side of things.... 

EDIT:  I think the async pessimization is entirely due to the `wait_async` method.  I suspect it could be written in Cython for a healthy speedup in all things async. 

Question:  Why does the `Copy` class take a binary argument? It doesn't work unless the query itself specifies format binary, in which case it can be inferred (as it is by default).  Is the hope that someday there will be support for setting it on the protocol level?   

## On Master --> This Branch (200,000 rows, width 11 (1 int4, 10 text))
#### SYNC
0.757028 sec --> 0.498472 sec
#### SYNC BINARY
0.710254 sec --> 0.444449 sec
#### ASYNC
1.110847 sec --> 0.887244 sec
#### ASYNC BINARY
1.069361 sec  --> 0.834796 sec

## with `set_types`:

#### SYNC
0.873483 sec --> 0.532475 sec
#### SYNC BINARY
0.767204 sec --> 0.477692 sec
#### ASYNC
1.135291 sec --> 0.854667 sec
#### ASYNC BINARY
1.165879 sec --> 0.853913 sec

inline patch 1: psycopg_c/psycopg_c/_psycopg/copy.pyx, psycopg_c/psycopg_c/_psycopg/transform.pyx, tests/scripts/copytest.py (+497/-45 lines) download inline-1.patchdiff --git a/psycopg_c/psycopg_c/_psycopg/copy.pyx b/psycopg_c/psycopg_c/_psycopg/copy.pyx index ff1d213ca..31aa11827 100644 --- a/psycopg_c/psycopg_c/_psycopg/copy.pyx +++ b/psycopg_c/psycopg_c/_psycopg/copy.pyx @@ -7,13 +7,12 @@ C optimised functions for the copy system. from libc.stdint cimport int32_t, uint16_t, uint32_t from libc.string cimport memcpy +from cpython.tuple cimport PyTuple_New, PyTuple_SET_ITEM from cpython.sequence cimport PySequence_Fast, PySequence_Fast_GET_ITEM from cpython.sequence cimport PySequence_Fast_GET_SIZE -from cpython.bytearray cimport PyByteArray_AS_STRING, PyByteArray_FromStringAndSize -from cpython.bytearray cimport PyByteArray_GET_SIZE, PyByteArray_Resize -from cpython.memoryview cimport PyMemoryView_FromObject +from cpython.bytearray cimport PyByteArray_AS_STRING, PyByteArray_GET_SIZE +from cpython.bytearray cimport PyByteArray_Resize -from psycopg_c.pq cimport ViewBuffer from psycopg_c._psycopg cimport endian from psycopg import errors as e @@ -244,8 +243,10 @@ def parse_row_binary(data, tx: Transformer) -> tuple[Any, ...]: memcpy(&benfields, ptr, sizeof(benfields)) cdef int nfields = endian.be16toh(benfields) ptr += sizeof(benfields) - cdef list row = PyList_New(nfields) + tx._c_check_num_loaders(nfields) + + cdef tuple row = PyTuple_New(nfields) cdef int col cdef int32_t belength cdef Py_ssize_t length @@ -259,14 +260,13 @@ def parse_row_binary(data, tx: Transformer) -> tuple[Any, ...]: length = endian.be32toh(belength) if ptr + length > bufend: raise e.DataError("bad copy data: length exceeding data") - field = PyMemoryView_FromObject( - ViewBuffer._from_buffer(data, ptr, length)) + field = tx._c_load_item(ptr, length, col) ptr += length Py_INCREF(field) - PyList_SET_ITEM(row, col, field) + PyTuple_SET_ITEM(row, col, field) - return tx.load_sequence(row) + return row def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: @@ -276,11 +276,15 @@ def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: # politely assume that the number of fields will be what in the result cdef int nfields = tx._nfields - cdef list row = PyList_New(nfields) + tx._c_check_num_loaders(nfields) + + cdef tuple row = PyTuple_New(nfields) cdef unsigned char *fend cdef unsigned char *rowend = fstart + size cdef unsigned char *src + cdef unsigned char *scratch = NULL + cdef size_t sclen = 0 cdef unsigned char *tgt cdef int col cdef int num_bs @@ -312,15 +316,18 @@ def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: # Is this a field with no backslash? elif num_bs == 0: # Nothing to unescape: we don't need a copy - field = PyMemoryView_FromObject( - ViewBuffer._from_buffer(data, fstart, fend - fstart)) + field = tx._c_load_item(fstart, fend - fstart, col) # This is a field containing backslashes else: # We need a copy of the buffer to unescape - field = PyByteArray_FromStringAndSize("", 0) - PyByteArray_Resize(field, fend - fstart - num_bs) - tgt = <unsigned char *>PyByteArray_AS_STRING(field) + if <unsigned int>(fend - fstart - num_bs) > sclen: + # Above condition will always be true on the first run + # since sclen is 0. After that, we only extend scratch + # if it's too small. + sclen = fend - fstart - num_bs + scratch = <unsigned char *>PyMem_Realloc(scratch, sclen) + tgt = scratch src = fstart while (src < fend): if src[0] != b'\\': @@ -330,15 +337,18 @@ def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: tgt[0] = copy_unescape_lut[src[0]] src += 1 tgt += 1 + field = tx._c_load_item(scratch, fend - fstart - num_bs, col) Py_INCREF(field) - PyList_SET_ITEM(row, col, field) + PyTuple_SET_ITEM(row, col, field) # Start of the field fstart = fend + 1 - # Convert the array of buffers into Python objects - return tx.load_sequence(row) + if sclen > 0: + PyMem_Free(scratch) + + return row cdef extern from *: diff --git a/psycopg_c/psycopg_c/_psycopg/transform.pyx b/psycopg_c/psycopg_c/_psycopg/transform.pyx index 2b61c6443..9880a712b 100644 --- a/psycopg_c/psycopg_c/_psycopg/transform.pyx +++ b/psycopg_c/psycopg_c/_psycopg/transform.pyx @@ -17,9 +17,11 @@ from cpython.tuple cimport PyTuple_New, PyTuple_SET_ITEM from cpython.object cimport PyObject, PyObject_CallFunctionObjArgs from cpython.sequence cimport PySequence_Fast, PySequence_Fast_GET_ITEM from cpython.sequence cimport PySequence_Fast_GET_SIZE +from cpython.memoryview cimport PyMemoryView_FromObject from typing import Sequence +from psycopg_c.pq cimport ViewBuffer from psycopg import errors as e from psycopg.pq import Format as PqFormat from psycopg.rows import Row @@ -541,10 +543,7 @@ cdef class Transformer: out = PyTuple_New(nfields) row_loaders = self._row_loaders # avoid an incref/decref per item - if PyList_GET_SIZE(row_loaders) != nfields: - raise e.ProgrammingError( - f"cannot load sequence of {nfields} items:" - f" {len(self._row_loaders)} loaders registered") + self._c_check_num_loaders(nfields) for col in range(nfields): item = PySequence_Fast_GET_ITEM(record_fast, col) @@ -564,6 +563,29 @@ cdef class Transformer: return out + cdef inline _c_check_num_loaders(self, nfields): + if PyList_GET_SIZE(self._row_loaders) != nfields: + raise e.ProgrammingError( + f"cannot load sequence of {nfields} items:" + f" {len(self._row_loaders)} loaders registered") + + cdef inline _c_load_item( + self, unsigned char *ptr, Py_ssize_t size, Py_ssize_t index + ): + cdef RowLoader loader + + row_loaders = self._row_loaders + loader = <RowLoader>PyList_GET_ITEM(row_loaders, index) + if (<RowLoader>loader).cloader is not None: + pyval = loader.cloader.cload(<char *>ptr, size) + else: + item = PyMemoryView_FromObject( + ViewBuffer._from_buffer(self, ptr, size) + ) + pyval = PyObject_CallFunctionObjArgs( + loader.loadfunc, <PyObject*>item, NULL) + return pyval + def get_loader(self, oid: int, format: PqFormat) -> "Loader": cdef PyObject *row_loader = self._c_get_loader( <PyObject *>oid, <PyObject *>format) diff --git a/tests/scripts/copytest.py b/tests/scripts/copytest.py index 4325d080f..590509807 100755 --- a/tests/scripts/copytest.py +++ b/tests/scripts/copytest.py @@ -4,11 +4,15 @@ from __future__ import annotations import sys +import pstats import asyncio import logging -from time import time +import cProfile +import statistics +from time import perf_counter from typing import Any -from argparse import ArgumentParser, Namespace +from argparse import ArgumentParser, BooleanOptionalAction, Namespace +from contextlib import nullcontext import psycopg from psycopg import sql @@ -20,45 +24,365 @@ format="%(asctime)s %(levelname)s %(message)s", ) +pr = cProfile.Profile() + def main(): args = parse_cmdline() logger.setLevel(args.loglevel) if getattr(args, "async"): - asyncio.run(main_async(args)) + t_ins, t_outs, t_inserts, t_selects = asyncio.run(main_async(args)) + else: + t_ins, t_outs, t_inserts, t_selects = main_sync(args) + + if args.executemany: + execute_descr = "executemany" else: - main_sync(args) + execute_descr = "execute" + if args.pipeline: + execute_descr = "pipelined execute" + else: + execute_descr = "execute" + + if t_ins: + output_timings(args, t_ins, "copy in") + if t_outs: + output_timings(args, t_outs, "copy out") + if t_inserts: + output_timings(args, t_inserts, f"{execute_descr} insert") + if t_selects: + output_timings(args, t_selects, f"{execute_descr} select") + + if args.cprofile: + pr.dump_stats("output.prof") + stats = pstats.Stats(pr) + stats.sort_stats("cumulative") + stats.print_stats(10) + + with psycopg.Connection.connect(args.dsn, autocommit=True) as conn: + conn.execute("DROP TABLE IF EXISTS testcopy") -def main_sync(args: Namespace) -> None: +def main_sync(args: Namespace) -> tuple[list[float], ...]: test = CopyPutTest(args) + t_ins, t_outs, t_inserts, t_selects = [], [], [], [] + if args.executemany: + insert = executemany_insert_sync + select = executemany_select_sync + else: + insert = execute_insert_sync + select = execute_select_sync with psycopg.Connection.connect(args.dsn) as conn: with conn.cursor() as cur: - writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None cur.execute(test.get_table_stmt()) - t0 = time() - with cur.copy(test.get_copy_stmt(), writer=writer) as copy: - for i in range(args.nrecs): - copy.write_row(test.get_record()) - tf = time() - - logger.info("time to copy: %.6f sec", tf - t0) + n_in, n_out, n_insert, n_select = get_effective_repeats(args) + for i in range(n_insert): + t_insert = insert(args, test, cur) + t_inserts.append(t_insert) + for i in range(n_in): + t_in = copy_in_sync(args, test, cur) + if args.copy_in: + t_ins.append(t_in) + if i != n_in - 1: + cur.execute(test.get_truncate_stmt()) + for i in range(n_select): + t_select = select(args, test, cur) + t_selects.append(t_select) + for i in range(n_out): + t_out = copy_out_sync(args, test, cur) + t_outs.append(t_out) + return t_ins, t_outs, t_inserts, t_selects -async def main_async(args: Namespace) -> None: +async def main_async(args: Namespace) -> tuple[list[float], ...]: test = CopyPutTest(args) + t_ins, t_outs, t_inserts, t_selects = [], [], [], [] + if args.executemany: + insert = executemany_insert_async + select = executemany_select_async + else: + insert = execute_insert_async + select = execute_select_async async with await psycopg.AsyncConnection.connect(args.dsn) as conn: async with conn.cursor() as cur: await cur.execute(test.get_table_stmt()) - writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None - t0 = time() - async with cur.copy(test.get_copy_stmt(), writer=writer) as copy: - for i in range(args.nrecs): - await copy.write_row(test.get_record()) - tf = time() + n_in, n_out, n_insert, n_select = get_effective_repeats(args) + for i in range(n_insert): + t_insert = await insert(args, test, cur) + t_inserts.append(t_insert) + await cur.execute(test.get_truncate_stmt()) + for i in range(n_in): + t_in = await copy_in_async(args, test, cur) + if args.copy_in: + t_ins.append(t_in) + if not i == n_in - 1: + await cur.execute(test.get_truncate_stmt()) + for i in range(n_select): + t_select = await select(args, test, cur) + t_selects.append(t_select) + for i in range(n_out): + t_out = await copy_out_async(args, test, cur) + t_outs.append(t_out) + return t_ins, t_outs, t_inserts, t_selects + + +def get_effective_repeats(args): + # copy in always runs once or we have nothing to copy out + n_in = args.repeat if args.copy_in else 1 if args.copy_out or args.select else 0 + n_out = args.repeat if args.copy_out else 0 + n_insert = args.repeat if args.insert else 0 + n_select = args.repeat if args.select else 0 + + if all(r == 0 for r in (n_in, n_out, n_insert, n_select)): + print("Nothing to do.") + return n_in, n_out, n_insert, n_select + + +def output_timings(args, timings, description): + if args.repeat > 1: + logger.info( + f"time to {description}: %.6f sec [%.6f +/- %.6f from %i results]", + *get_statistics(timings), + ) + else: + logger.info(f"time to {description}: %.6f sec", timings[0]) + + +def get_statistics(timings): + orig_timings = timings + min_val = min(timings) + mean_val = statistics.mean(timings) + stddev = statistics.stdev(timings) + + if len(timings) <= 4: + return (min_val, mean_val, stddev, len(timings)) + + # trim upper outliers + robust_timings = [t for t in timings if t <= statistics.quantiles(timings)[-1]] + threshold = statistics.mean(robust_timings) + 3 * statistics.stdev(robust_timings) + while max(timings) > threshold: + timings = [t for t in timings if t <= threshold] + if len(timings) < 4: + logger.warning( + "Not trimming outliers as it would result in less than 4 timings" + ) + timings = orig_timings + break + mean_val = statistics.mean(timings) + stddev = statistics.stdev(timings) + threshold = mean_val + 3 * stddev + + return (min_val, mean_val, stddev, len(timings)) + + +def copy_in_sync(args, test, cur): + writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None + with cur.copy(test.get_copy_stmt(), writer=writer) as copy: + if args.set_types: + copy.set_types(["text"] * args.nfields) + records = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.copy_in and args.cprofile: + pr.enable() + for record in records: + copy.write_row(record) + if args.copy_in and args.cprofile: + pr.disable() + tf = perf_counter() + + return tf - t0 - logger.info("time to copy: %.6f sec", tf - t0) + +def copy_out_sync(args, test, cur): + with cur.copy(test.get_copy_out_stmt()) as copy: + if args.set_types: + copy.set_types(["int4"] + ["text"] * args.nfields) + t0 = perf_counter() + if args.cprofile: + pr.enable() + while copy.read_row(): + pass + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def executemany_insert_sync(args, test, cur): + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.cprofile: + pr.enable() + cur.executemany(insert, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def executemany_select_sync(args, test, cur): + select = test.get_select_stmt() + params = test.get_record_ids(cur) + t0 = perf_counter() + if args.cprofile: + pr.enable() + cur.executemany(select, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def execute_insert_sync(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + cur.execute(insert, param) + if pipeline is not None: + pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def execute_select_sync(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + select = test.get_select_stmt() + params = test.get_record_ids(cur) + with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + cur.execute(select, param) + if pipeline is not None: + pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def copy_in_async(args, test, cur): + writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None + async with cur.copy(test.get_copy_stmt(), writer=writer) as copy: + if args.set_types: + copy.set_types(["text"] * args.nfields) + records = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.copy_in and args.cprofile: + pr.enable() + for record in records: + await copy.write_row(record) + if args.copy_in and args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def copy_out_async(args, test, cur): + async with cur.copy(test.get_copy_out_stmt()) as copy: + if args.set_types: + copy.set_types(["int4"] + ["text"] * args.nfields) + t0 = perf_counter() + if args.cprofile: + pr.enable() + while await copy.read_row(): + pass + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def executemany_insert_async(args, test, cur): + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.cprofile: + pr.enable() + await cur.executemany(insert, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def executemany_select_async(args, test, cur): + select = test.get_select_stmt() + params = await test.get_record_ids_async(cur) + t0 = perf_counter() + if args.cprofile: + pr.enable() + await cur.executemany(select, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def execute_insert_async(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + async with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + await cur.execute(insert, param) + if pipeline is not None: + await pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def execute_select_async(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + select = test.get_select_stmt() + params = await test.get_record_ids_async(cur) + async with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + await cur.execute(select, param) + if pipeline is not None: + await pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 class CopyPutTest: @@ -79,13 +403,49 @@ def get_copy_stmt(self) -> Query: [sql.Identifier(f"f{i}") for i in range(self.args.nfields)] ) stmt = sql.SQL("""\ -copy testcopy ({}) from stdin -""").format(fields) +copy testcopy ({}) from stdin{} +""").format(fields, sql.SQL(" WITH (FORMAT BINARY)" if self.args.binary else "")) + return stmt + + def get_select_stmt(self) -> Query: + stmt = sql.SQL("""\ +SELECT * FROM testcopy WHERE id = {} +""").format(sql.SQL("%b" if self.args.binary else "%t")) + return stmt + + def get_insert_stmt(self) -> Query: + fields = sql.SQL(", ").join( + [sql.Identifier(f"f{i}") for i in range(self.args.nfields)] + ) + formatter = sql.SQL("%b" if self.args.binary else "%t") + stmt = sql.SQL("""\ +INSERT INTO testcopy ({}) VALUES ({}) +""").format( + fields, + sql.SQL(", ").join(formatter for _ in range(self.args.nfields)), + ) + return stmt + + def get_truncate_stmt(self) -> Query: + return sql.SQL("TRUNCATE testcopy") + + def get_copy_out_stmt(self) -> Query: + stmt = sql.SQL("""\ +COPY testcopy TO STDOUT{} +""").format(sql.SQL(" WITH (FORMAT BINARY)" if self.args.binary else "")) return stmt def get_record(self) -> tuple[Any, ...]: return tuple("x" * self.args.colsize for _ in range(self.args.nfields)) + def get_record_ids(self, cur: psycopg.Cursor) -> list[tuple[int]]: + cur.execute("SELECT id FROM testcopy") + return cur.fetchall() + + async def get_record_ids_async(self, cur: psycopg.AsyncCursor) -> list[tuple[int]]: + await cur.execute("SELECT id FROM testcopy") + return await cur.fetchall() + def parse_cmdline() -> Namespace: parser = ArgumentParser(description=__doc__) @@ -93,6 +453,66 @@ def parse_cmdline() -> Namespace: parser.add_argument( "--async", action="store_true", default=False, help="test async objects" ) + parser.add_argument( + "--repeat", + type=int, + default=1, + help="number of repeats [default: %(default)s]", + ) + parser.add_argument( + "--cprofile", + action=BooleanOptionalAction, + default=False, + help="output cProfile information and save profile to output.prof", + ) + parser.add_argument( + "--copy-in", + action=BooleanOptionalAction, + default=True, + help="output copy in timings and profile", + ) + parser.add_argument( + "--copy-out", + action=BooleanOptionalAction, + default=False, + help="output copy out timings and profile", + ) + parser.add_argument( + "--insert", + action=BooleanOptionalAction, + default=False, + help="output insert timings and profile", + ) + parser.add_argument( + "--select", + action=BooleanOptionalAction, + default=False, + help="output select timings and profile", + ) + parser.add_argument( + "--pipeline", + action=BooleanOptionalAction, + default=False, + help="use pipeline mode for --select and --insert with --no-executemany", + ) + parser.add_argument( + "--executemany", + action=BooleanOptionalAction, + default=True, + help="use executemany instead of execute", + ) + parser.add_argument( + "--binary", + action=BooleanOptionalAction, + default=False, + help="binary or text output format", + ) + parser.add_argument( + "--set-types", + action=BooleanOptionalAction, + default=False, + help="binary or text output format", + ) parser.add_argument( "--nrecs", type=int,

view thread (23+ messages)  latest in thread

Message-ID: <gh-psycopg-psycopg-1324@github.com>
Permalink:  ../gh-psycopg-psycopg-1324@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox