From: Mike Harding <mvh@ix.netcom.com>
To: pgsql-performance@postgresql.org
Subject: COPY slows down?
Date: Fri, 8 Oct 2004 05:10:29 -0700 (PDT)
Message-ID: <20041008121029.EEB485486C@bsd.mvh> (raw)
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=# [2Pdelete from bgtest;mvh=# [4hcopy bgtest from '/home/mvh/database[4lstuff/dbdmp/bgdump';
COPY
Time: 69796.130 ms
mvh=# vacuum analyze;
VACUUM
Time: 19148.621 ms
mvh=# vacuum analyze;mvh=# [4hcopy bgtest from '/home/mvh/databasestuff[4l/dbdmp/bgdump';
COPY
Time: 89189.939 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;[K
VACUUM
Time: 26814.670 ms
mvh=# vacuum analyze;mvh=# [4hcopy bgtest from '/home/mvh/databasestuff[4l/dbdmp/bgdump';
COPY
Time: 131131.982 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;[K
VACUUM
Time: 64997.264 ms
mvh=# vacuum analyze;mvh=# [4hcopy bgtest from '/home/mvh/databasestuff[4l/dbdmp/bgdump';
COPY
Time: 299977.697 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;[K
VACUUM
Time: 103541.716 ms
mvh=# vacuum analyze;mvh=# [4hcopy bgtest from '/home/mvh/databasestuff[4l/dbdmp/bgdump';
COPY
Time: 455292.600 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;[K
VACUUM
Time: 138910.015 ms
mvh=# vacuum analyze;mvh=# [4hcopy bgtest from '/home/mvh/databasestuff[4l/dbdmp/bgdump';
COPY
Time: 612119.661 ms
mvh=# copy bgtest from '/home/mvh/databasestuff/dbdmp/bgdump';mvh=# vacuum analyze;[K
VACUUM
Time: 151331.243 ms
mvh=# \q
bash-3.00$ exit
Script done on Wed Oct 6 10:43:04 2004
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mvh@ix.netcom.com
Subject: Re: COPY slows down?
In-Reply-To: <20041008121029.EEB485486C@bsd.mvh>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox