Received: from k3.aeccom.com (IDENT:root@gate1.aeccom.com [193.158.11.51]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA99949 for ; Tue, 11 Jan 2000 10:21:00 -0500 (EST) (envelope-from lutzeb@aeccom.com) Received: from ampato.aeccom.com (IDENT:root@ampato.aeccom.com [192.168.0.11]) by k3.aeccom.com (8.9.3/8.9.3) with ESMTP id QAA13689; Tue, 11 Jan 2000 16:21:52 +0100 Received: (from lutzeb@localhost) by ampato.aeccom.com (8.9.3/8.9.3) id QAA11522; Tue, 11 Jan 2000 16:21:52 +0100 From: Dirk Lutzebaeck MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <14459.19087.735901.684613@ampato.aeccom.com> Date: Tue, 11 Jan 2000 16:21:51 +0100 (CET) To: pgsql-sql@hub.org Subject: best strategy doing a large copy and using indexes X-Mailer: VM 6.71 under 21.1 (patch 4) "Arches" XEmacs Lucid I have a table with 7 individual indexes (it models an object store with different types) and need to copy large data lists into this table regularly. I'm using COPY for it but it is unacceptable slow (4min for 1000 rows on a Linux/PII-400). I'm anticipating more than 20000 rows to copy. What combination of the following options is the best strategy to make this more efficient? a) drop indexes before COPY and recreate them after COPY b) put COPY in a transaction c) do VACUUM before (or after?) d) do VACUUM ANALYZE before (or after?) e) COPY to temp table and then make an INSERT SELECT FROM temp table f) anything else I'm using -f for the backend already. Dirk