X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 836C9D1D26A for ; Fri, 6 Feb 2004 06:23:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53685-07 for ; Fri, 6 Feb 2004 02:23:03 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 5F6D2D1D1CD for ; Fri, 6 Feb 2004 02:23:02 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i166N1o3024578; Fri, 6 Feb 2004 01:23:01 -0500 (EST) To: Steve Lane Cc: pgsql-admin@postgresql.org Subject: Re: Drop indexes inside transaction? In-reply-to: References: Comments: In-reply-to Steve Lane message dated "Thu, 05 Feb 2004 21:48:35 -0600" Date: Fri, 06 Feb 2004 01:23:01 -0500 Message-ID: <24577.1076048581@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/69 X-Sequence-Number: 12243 Steve Lane writes: > Now we want to use COPY to bring the data in. The target table has 6 > indexes. Without indexes, naturally, we can load 80K rows in 2 seconds. With > indexes, 46 seconds. (oddly, ONE index contributes 40+ seconds of that, yet > they're all similar, single-column indexes. Anyway, that's another story). No, I'd say that's the key part of the story. Details? > So I hit on the idea of doing the same thing, but inside a transaction. In > theory that should affect no one else. ... other than locking them out of the table while the transaction runs. That doesn't sound like what you want to do. In any case, reindexing the table will get slower and slower as the pre-existing data in the table expands. regards, tom lane