| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Tomás Senart <ts(at)perfloop(dot)ai> |
| Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: [PATCH v1] Avoid deforming unused trailing columns in COPY TO |
| Date: | 2026-11-18 07:06:57 |
| Message-ID: | 62A3AC4C-64AB-46C9-AC4F-6F2B7825B0D9@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
> On Nov 16, 2026, at 21:00, Tomás Senart <ts(at)perfloop(dot)ai> wrote:
>
> 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.
> <v1-0001-Avoid-deforming-unused-trailing-columns-in-COPY-T.patch>
Thanks for the patch. I haven't done a load test to measure the performance improvement. After reviewing the code changes, I have a few comments to raise first.
1
```
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -87,6 +87,7 @@ typedef struct CopyToStateData
Relation rel; /* relation to copy to */
QueryDesc *queryDesc; /* executable query to copy from */
List *attnumlist; /* integer list of attnums to copy */
+ int max_attnum; /* highest attribute to fetch from the slot */
char *filename; /* filename, or NULL for STDOUT */
bool is_program; /* is 'filename' a program to popen? */
bool json_row_delim_needed; /* need delimiter before next row */
```
If we define max_attnum as type AttrNumber, which is int16, and place it after the two bool fields, it will fit into the existing padding space, so we won't increase the size of this structure.
2
```
else
{
- /* Deconstruct the tuple */
- slot_getallattrs(slot);
+ /* Deconstruct the prefix needed for output. */
+ slot_getsomeattrs(slot, cstate->max_attnum);
copyslot = slot;
}
```
I think this slot_getsomeattrs() call is not needed, because after the else clause, the code immediately calls CopyOneRowTo(), which calls slot_getsomeattrs() again. So, I think we can just remove this slot_getsomeattrs() call and only keep copyslot = slot.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Amit Langote | 2026-11-18 07:12:57 | Re: Two more RI fast-path issues |
| Previous Message | Richard Guo | 2026-11-18 07:00:35 | Re: Tracking per-RelOptInfo uniqueness during planning |