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 |
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 |