Issues with \copy from file

From: Sigurgeir Gunnarsson <sgunnars(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Issues with \copy from file
Date: 2009-10-12 22:05:36
Message-ID: ebd3ad520910121505m7e126f9akbaafdb20ea4d8755@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I'm doing \copy from file into table. There are two files one with 7 million
lines and the other with around 24 million and the data goes into separate
table. There are only three columns in each file and four in each table (the
primary key, id serial is the fourt). The data is about 150 MB and 450 MB
and takes from 5 to 20 minutes to load into the database.

What I'm wondering about is what parameters to tweak to improve the
operation and shorten the time of the \copy ? I think I have tweaked most of
the available in postgresql.conf, that is shared_buffer, temp_buffers,
work_mem, maintenance_work_mem, max_fsm_pages. Maybe someone could point out
the one really related to \copy ?

I would hope that there is some way for me to improve the operation. I used
to do the operation on a MySQL server and simple time measurements gives me
a difference of a multiple 3 to 4, where the MySQL is faster.

I would also be satisfied to know if this is an expected difference.

Regards, Sigurgeir

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2009-10-13 07:03:10 Re: Are folks running 8.4 in production environments? and 8.4 and slon 1.2?
Previous Message Tory M Blue 2009-10-12 19:06:37 Are folks running 8.4 in production environments? and 8.4 and slon 1.2?