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

You can use COPY over DBI.

https://metacpan.org/pod/DBD::Pg#COPY-support

On Fri, Jan 31, 2020 at 2:03 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> pá 31. 1. 2020 v 19:25 odesílatel Matthias Apitz <guru(at)unixarea(dot)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(at)unixarea(dot)de, http://www.unixarea.de/
>> +49-176-38902045
>> Public GnuPG key: http://www.unixarea.de/key.pub
>>
>>
>>