speedup COPY TO for partitioned table.
jian he <jian.universality@gmail.com>
From: jian he <jian.universality@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-12-19T12:02:20Z
Lists: pgsql-hackers
Attachments
- v1-0001-speedup-COPY-TO-for-partitioned-table.patch (text/x-patch) patch v1-0001
- copy_par_regress_test.sql (application/sql)
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
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Support COPY TO for partitioned tables.
- 4bea91f21f61 19 (unreleased) landed
-
Re-implement the ereport() macro using __VA_ARGS__.
- e3a87b4991cc 13.0 cited