COPY slows down?

From: Mike Harding <mvh(at)ix(dot)netcom(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: COPY slows down?
Date: 2004-10-08 12:10:29
Message-ID: 20041008121029.EEB485486C@bsd.mvh
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


I just ran a COPY of a million records several times, and each time I
ran it it ran apparently exponentially slower. If I do an insert of
10 million records, even with 2 indexes (same table) it doesn't appear
to slow down at all. Any ideas?

- Mike H.

(I apologize for the ^Hs)

Script started on Wed Oct 6 08:37:32 2004
bash-3.00$ psql
Welcome to psql 7.4.5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

mvh=# \timing
Timing is on.
mvh=# \timingreindex table bgtest;mvh=# delete from bgtest;mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';
COPY
Time: 69796.130 ms
mvh=# vacuum analyze;
VACUUM
Time: 19148.621 ms
mvh=# vacuum analyze;mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';
COPY
Time: 89189.939 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;
VACUUM
Time: 26814.670 ms
mvh=# vacuum analyze;mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';
COPY
Time: 131131.982 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;
VACUUM
Time: 64997.264 ms
mvh=# vacuum analyze;mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';
COPY
Time: 299977.697 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;
VACUUM
Time: 103541.716 ms
mvh=# vacuum analyze;mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';
COPY
Time: 455292.600 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;
VACUUM
Time: 138910.015 ms
mvh=# vacuum analyze;mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';
COPY
Time: 612119.661 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;
VACUUM
Time: 151331.243 ms
mvh=# \q
bash-3.00$ exit

Script done on Wed Oct 6 10:43:04 2004

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Harald Fuchs 2004-10-08 12:28:52 Re: integer[] indexing.
Previous Message Pierre-Frédéric Caillaud 2004-10-08 09:57:13 Re: sequential scan on select distinct