Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI
Дата
Msg-id CAFj8pRCGwod+pUczLa-0yuBnSyz0RVzXJ6Nk-PAL=_PL3pmBRw@mail.gmail.com
обсуждение исходный текст
Ответ на performance of loading CSV data with COPY is 50 times faster thanPerl::DBI  (Matthias Apitz <guru@unixarea.de>)
Ответы Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI  (Brian Dunavant <dunavant@gmail.com>)
Re: performance of loading CSV data with COPY is 50 times fasterthan Perl::DBI  (Steven Lembark <lembark@wrkhors.com>)
Список pgsql-general
Дерево обсуждения


pá 31. 1. 2020 v 19:25 odesílatel Matthias Apitz <guru@unixarea.de> napsal:

Hello,

Since ages, we transfer data between different DBS (Informix, Sybase,
Oracle, and now PostgreSQL) with our own written tool, based on
Perl::DBI which produces a CSV like export in a common way, i.e. an
export of Oracle can be loaded into Sybase and vice versa. Export and
Import is done row by row, for some tables millions of rows.

We produced a special version of the tool to export the rows into a
format which understands the PostgreSQL's COPY command and got to know
that the import into PostgreSQL of the same data with COPY is 50 times
faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6
million rows into an empty table without indexes.

How can COPY do this so fast?

Probably there are more reasons

1. probably DBI implementation is not too effective (for Postgres), maybe because COPY is not fault tolerant
2. postgres has not implicit plan cache, so every INSERT planned again and again
3. COPY bypass planner and executor and it has very effective network communication
4. with COPY you have a sure so autocommit is disabled.

Regards

Pavel

 

        matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: performance of loading CSV data with COPY is 50 times faster thanPerl::DBI
Следующее
От: Brian Dunavant
Дата:
Сообщение: Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI