RE: POC: postgres_fdw insert batching

tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com>

From: "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>

To: 'Tomas Vondra' <tomas.vondra@enterprisedb.com>

Cc: 'Tomas Vondra' <tomas.vondra@2ndquadrant.com>, "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>, "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>

Date: 2020-11-17T09:11:55Z

Lists: pgsql-hackers

Attachments

fdw.sql (application/octet-stream)
fdw_part.sql (application/octet-stream)
local.sql (application/octet-stream)
local_part.sql (application/octet-stream)
prepare.sql (application/octet-stream)
v2-0001-Add-bulk-insert-for-foreign-tables.patch (application/octet-stream) patch v2-0001

Hello,


Modified the patch as I talked with Tomas-san.  The performance results of loading one million records into a hash-partitioned table with 8 partitions are as follows:

    unpatched, local: 8.6 seconds
	    unpatched, fdw: 113.7 seconds
    patched, fdw: 12.5 seconds  (9x improvement)

The test scripts are also attached.  Run prepare.sql once to set up tables and source data.  Run local_part.sql and fdw_part.sql to load source data into a partitioned table with local partitions and a partitioned table with foreign tables respectively.


Regards
Takayuki Tsunakawa

Commits

Fix tuple routing to initialize batching only for inserts
- 927f453a9410 14.0 landed
Fix pointer type in ExecForeignBatchInsert SGML docs
- c15283ff429b 14.0 landed
Fix initialization of FDW batching in ExecInitModifyTable
- 920f853dc948 14.0 landed
Implement support for bulk inserts in postgres_fdw
- b663a4136331 14.0 landed