pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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=# 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




view thread (2+ messages)  latest in thread

Message-ID: <20041008121029.EEB485486C@bsd.mvh>
Permalink:  ../20041008121029.EEB485486C@bsd.mvh/
Also on:    postgresql.org/message-id/20041008121029.EEB485486C@bsd.mvh

 · 

reply

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