pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
COPY slows down?
2+ messages / 2 participants
[nested] [flat]

* COPY slows down?
@ 2004-10-08 12:10 Mike Harding <mvh@ix.netcom.com>
  2004-10-11 14:22 ` Re: COPY slows down? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Mike Harding @ 2004-10-08 12:10 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: COPY slows down?
  2004-10-08 12:10 COPY slows down? Mike Harding <mvh@ix.netcom.com>
@ 2004-10-11 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-10-11 14:22 UTC (permalink / raw)
  To: Mike Harding <mvh@ix.netcom.com>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-10-11 14:22 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-08 12:10 COPY slows down? Mike Harding <mvh@ix.netcom.com>
2004-10-11 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>

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