User-Agent: Microsoft-MacOutlook/14.6.7.160722
Date: Thu, 07 Dec 2017 20:21:45 -0800
Subject: Table with large number of int columns, very slow COPY FROM
From: Alex Tokarev <dwalin@dwalin.ru>
To: <pgsql-performance@postgresql.org>
Message-ID: <D64F5359.2993D%dwalin@dwalin.ru>
Thread-Topic: Table with large number of int columns, very slow COPY FROM
Mime-version: 1.0
Content-type: multipart/alternative;
	boundary="B_3595522911_17418745"
Precedence: bulk

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3595522911_17418745
Content-type: text/plain;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

Hi,

I have a set of tables with fairly large number of columns, mostly int with
a few bigints and short char/varchar columns. I=B9ve noticed that Postgres is
pretty slow at inserting data in such a table. I tried to tune every
possible setting: using unlogged tables, increased shared_buffers, etc; eve=
n
placed the db cluster on ramfs and turned fsync off. The results are pretty
much the same with the exception of using unlogged tables that improves
performance just a little bit.

I have made a minimally reproducible test case consisting of a table with
848 columns, inserting partial dataset of 100,000 rows with 240 columns. On
my dev VM the COPY FROM operation takes just shy of 3 seconds to complete,
which is entirely unexpected for such a small dataset.

Here=B9s a tarball with test schema and data:
http://nohuhu.org/copy_perf.tar.bz2; it=B9s 338k compressed but expands to
~50mb. Here=B9s the result of profiling session with perf:
https://pastebin.com/pjv7JqxD


--=20
Regards,
Alex.


--B_3595522911_17418745
Content-type: text/html;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

<html><head></head><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: s=
pace; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size:=
 14px; font-family: Calibri, sans-serif;"><div>Hi,</div><div><br></div><div>=
I have a set of tables with fairly large number of columns, mostly int with =
a few bigints and short char/varchar columns. I&#8217;ve noticed that Postgr=
es is pretty slow at inserting data in such a table. I tried to tune every p=
ossible setting: using unlogged tables, increased shared_buffers, etc; even =
placed the db cluster on ramfs and turned fsync off. The results are pretty =
much the same with the exception of using unlogged tables that improves perf=
ormance just a little bit.</div><div><br></div><div>I have made a minimally =
reproducible test case consisting of a table with 848 columns, inserting par=
tial dataset of 100,000 rows with 240 columns. On my dev VM the COPY FROM op=
eration takes just shy of 3 <i>seconds</i>&nbsp;to complete, which is entire=
ly unexpected for such a small dataset.&nbsp;</div><div><br></div><div>Here&=
#8217;s a tarball with test schema and data:&nbsp;<a href=3D"http://nohuhu.org=
/copy_perf.tar.bz2">http://nohuhu.org/copy_perf.tar.bz2</a>;&nbsp;it&#8217;s=
 338k compressed but expands to ~50mb. Here&#8217;s the result of profiling =
session with perf: <a href=3D"https://pastebin.com/pjv7JqxD">https://pastebin.=
com/pjv7JqxD</a></div><div><br></div><div><div><div><br></div><div>--&nbsp;<=
/div><div>Regards,</div></div><div>Alex.</div><div><br></div></div></body></=
html>

--B_3595522911_17418745--