X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1C83432A31D for ; Fri, 8 Oct 2004 13:10:37 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91914-08 for ; Fri, 8 Oct 2004 12:10:29 +0000 (GMT) Received: from scaup.mail.pas.earthlink.net (scaup.mail.pas.earthlink.net [207.217.120.49]) by svr1.postgresql.org (Postfix) with ESMTP id B3AE032A311 for ; Fri, 8 Oct 2004 13:10:32 +0100 (BST) Received: from lsanca1-ar6-4-62-201-153.lsanca1.elnk.dsl.genuity.net ([4.62.201.153] helo=bsd.mvh) by scaup.mail.pas.earthlink.net with esmtp (Exim 3.33 #1) id 1CFtZv-0001FB-00 for pgsql-performance@postgresql.org; Fri, 08 Oct 2004 05:10:31 -0700 Received: from localhost (localhost [127.0.0.1]) by bsd.mvh (Postfix) with ESMTP id 6489B5485C for ; Fri, 8 Oct 2004 05:10:31 -0700 (PDT) Received: from bsd.mvh ([127.0.0.1]) by localhost (bsd.mvh [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 73471-07 for ; Fri, 8 Oct 2004 05:10:30 -0700 (PDT) Received: by bsd.mvh (Postfix, from userid 1000) id EEB485486C; Fri, 8 Oct 2004 05:10:29 -0700 (PDT) From: Mike Harding To: pgsql-performance@postgresql.org Subject: COPY slows down? Message-Id: <20041008121029.EEB485486C@bsd.mvh> Date: Fri, 8 Oct 2004 05:10:29 -0700 (PDT) X-Virus-Scanned: by amavisd-new at bsd.mvh X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/129 X-Sequence-Number: 8605 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