Postgres Pro
Downloads
Home   >   mailing lists

Thread: COPY slows down?

COPY slows down?

From
Mike Harding
Date:
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

Re: COPY slows down?

From
Tom Lane
Date:
Mike Harding <mvh@ix.netcom.com> writes:
> I just ran a COPY of a million records several times, and each time I
> ran it it ran apparently exponentially slower.

Tell us about indexes, foreign keys involving this table, triggers, rules?

Some mention of your PG version would be appropriate, too.

            regards, tom lane