On 2025-10-29 We 8:28 PM, Matheus Alcantara wrote:
On Wed Oct 29, 2025 at 12:10 AM -03, jian he wrote:
On Sat, Oct 25, 2025 at 2:27 AM Matheus Alcantara
<[email protected]> wrote:
On this new version I also added some regress tests on postgres_fdw.sql
In the CopyFrom function, we have the CopyInsertMethod, CIM_SINGLE is slower
than CIM_MULTI, I think.
We should do performance tests for the case where the COPY statement is limited
to use CIM_SINGLE.
You can use triggers to make COPY can only use the CIM_SINGLE copymethod.
for example:
create function dummy() returns trigger as $$ begin return new; end $$
language plpgsql;
create trigger dummy
before insert or update on batch_table_3
for each row execute procedure dummy();
My local tests show that when batch_size is greater than 2, COPY performs faster
than batch inserts into a foreign table, even though COPY can only use
CIM_SINGLE.
However, my tests were done with an enable-assert build, since I
encountered issues compiling the release build.
anyway, I am sharing my test script.
I've benchmarked using buildtype=release with Dcassert=false and
buildtype=debug with Dcassert=true and in both cases I've got a worst
performance when using the COPY for batching insert into a a foreign
table with a trigger. See the results (best of 4 runs).
Batch using INSERT
batch_size: 100
buildtype=debug
Dcassert=true
tps = 13.596754
Batch using COPY
batch_size: 100
buildtype=debug
Dcassert=true
tps = 11.650642
--------------------
Batch using INSERT
batch_size: 100
buildtype=release
Dcassert=false
tps = 28.333161
Batch using COPY
batch_size: 100
buildtype=release
Dcassert=false
tps = 18.499420
It seems to me that we need to disable the COPY usage when the foreign
table has triggers enabled.
I think it's probably worth finding out why COPY is so much worse in the
presence of triggers. Is there something we can do to improve that, at
least so it's no worse?
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
