| From: | Tomás Senart <ts(at)perfloop(dot)ai> |
|---|---|
| To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | [PATCH v1] Avoid deforming unused trailing columns in COPY TO |
| Date: | 2026-11-16 13:00:33 |
| Message-ID: | CAEEBa3W+8WV8bXnZERCv2=1bcpB50DC99Z=Rx4K8kyg8YKq5vg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
COPY TO from a table deforms every attribute of each row, even when its
column list ends well before the last column. The attached patch deforms
only up to the highest attribute that is sent. Text, CSV and binary
output read only the listed attributes, so their output does not change.
JSON output still deforms the whole row, and so does the partition path
that converts tuples to the root table's row type.
slot_getallattrs() walks each attribute to find its offset and stores a
Datum and a null flag for it. With the patch, the walk stops at the
highest exported attribute.
This helps exports of leading columns from wide tables. One in-core
caller is the initial table synchronization of a publication with a
column list: the publisher runs COPY t (published columns) TO STDOUT.
pg_dump lists every column, so it gains only when a table's last
columns are generated or dropped.
Measurements on a 300,000-row table with 60 columns (234 MB) of int,
bigint, numeric, text, varchar, timestamptz and bool, some half NULL.
COPY ... TO '/dev/null', warm shared_buffers, -O2 build without
assertions, x86-64 VM with 8 vCPUs. Median ms per COPY over alternating
unpatched/patched pairs (3 pairs for narrow exports, 11 for the rest):
columns exported unpatched patched
first 5 of 60, CSV 290 191 -34%
first 5 of 60, text 252 174 -31%
column 30 of 60, CSV 162 127 -22%
id and column 60, CSV 170 167 no change
all 60, CSV 1257 1266 no change
pgbench_accounts, all 287 286 no change
Run-to-run spread was about 10%, so differences under 5% are noise. For
a full row the patched code does the same deforming as before. With the
patch, COPY t (first 5 columns) costs about the same as
COPY (SELECT first 5 columns FROM t), which took 189 and 195 ms.
There is no new regression test, because the output does not change.
With assertions enabled, the regress suite and the subscription TAP
tests (including 031_column_list) pass, and pgindent is clean. The
output of 15 COPY TO commands matched an unpatched build byte for byte:
column lists in and out of order, a dropped column, columns added with a
default, a zero-column table, a partitioned table with a reordered
partition, and the text, CSV, binary and JSON formats.
The patch is against master. It is a performance change, so it is not
meant for back-patching.
Tool-use disclosure: Perfloop found the issue and made the patch.
Human owner: me, Tomás Senart.
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Avoid-deforming-unused-trailing-columns-in-COPY-T.patch | text/x-patch | 2.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rajesh Kumar Thandapani | 2026-11-16 13:36:18 | Re: Re: Re: BUG #19776: pg_combinebackup ignores the truncation_block_length of an intermediate incremental backup |
| Previous Message | Joao Rito | 2026-11-16 12:46:02 | [PATCH] Fix IMPORT FOREIGN SCHEMA resolving lookup names in pg_temp |