[pgpool-hackers: 3199] COPY performance

Tatsuo Ishii ishii at sraoss.co.jp
Tue Dec 25 17:57:50 JST 2018

I found "COPY xxx TO STDOUT"'s performance is pretty bad if Pgpool-II
is involved. You can check this by using pg_dump.
(I tested it on my loptop)

Direct PostgreSQL: 4.7sec
Via Pgpool-II 4.0: 19.8sec

Almost Pgpool-II is x5 slower than PostgreSQL direct.

Digging into this a little bit, I found followings in

	 * Optimization for "Data Row" message.  Since it is too often to receive
	 * and forward "Data Row" message, we do not flush the message to frontend
	 * now. We expect that "Command Complete" message (or "Error response" or
	 * "Notice response" message) follows the stream of data row message
	 * anyway, so flushing will be done at that time.
	if (kind == 'D')
		pool_write(frontend, p1, len1);
		pool_write_and_flush(frontend, p1, len1);

To optimize DataRow case, for each DataRow message
SimpleForwardToFrontend() does not flush message. But this
optimization is not done for CopyData. I think because a lot of
CopyData messages generated like DataDow, this optimization should be
applied to CopyData as well. Actually if I just change following:

if (kind == 'D')


if (kind == 'D' || kind == 'd')

19.8sec reduces to 7.8sec (x2.5 improvement).

Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php

More information about the pgpool-hackers mailing list