speedup COPY TO for partitioned table.

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: speedup COPY TO for partitioned table.
Date: 2024-12-19 12:02:20
Message-ID: CACJufxEZt+G19Ors3bQUq-42-61__C=y5k2wk=sHEFRusu7=iQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.

COPY (select_query) generally slower than
table_beginscan.. table_scan_getnextslot ..table_endscan,
especially for partitioned tables.
so in the function DoCopyTo
trying to use table_beginscan.. table_scan_getnextslot ..table_endscan
for COPY TO when source table is a partitioned table.

----setup-----
CREATE TABLE t3 (a INT, b int ) PARTITION BY RANGE (a);
create table t3_1 partition of t3 for values from (1) to (11);
create table t3_2 partition of t3 for values from (11) to (15);
insert into t3 select g from generate_series(1, 3) g;
insert into t3 select g from generate_series(11, 11) g;

so now you can do:
copy t3 to stdout;

in the master, you will get:
ERROR: cannot copy from partitioned table "t3"
HINT: Try the COPY (SELECT ...) TO variant.

attached copy_par_regress_test.sql is a simple benchmark sql file,
a partitioned table with 10 partitions, 2 levels of indirection.
The simple benchmark shows around 7.7% improvement in my local environment.

local environment:
PostgreSQL 18devel_debug_build_382092a0cd on x86_64-linux, compiled by
gcc-14.1.0, 64-bit

Attachment Content-Type Size
v1-0001-speedup-COPY-TO-for-partitioned-table.patch text/x-patch 4.2 KB
copy_par_regress_test.sql application/sql 2.5 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Nishant Sharma 2024-12-19 12:07:51 Re: [PROPOSAL] : Disallow use of empty column name in (column_name '') in ALTER or CREATE of foreign table.
Previous Message Zhijie Hou (Fujitsu) 2024-12-19 11:04:46 RE: Conflict detection for update_deleted in logical replication