X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 58C889DC9D1 for ; Tue, 21 Feb 2006 08:52:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64476-07 for ; Tue, 21 Feb 2006 08:52:13 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 2DB329DC9CC for ; Tue, 21 Feb 2006 08:52:08 -0400 (AST) Received: from jigsaw-sbs02.jigsawhq.com (hostA.jigsawfinance.com [213.106.224.113]) by svr4.postgresql.org (Postfix) with ESMTP id 983B85AF090 for ; Tue, 21 Feb 2006 12:52:11 +0000 (GMT) Received: from alfie.jigsawhq.com ([192.168.0.181]) by jigsaw-sbs02.jigsawhq.com with Microsoft SMTPSVC(5.0.2195.6713); Tue, 21 Feb 2006 12:50:50 +0000 From: Ashley Moran Organization: Codeweavers Ltd To: pgsql-admin@postgresql.org Subject: REINDEX during a transaction Date: Tue, 21 Feb 2006 12:50:44 +0000 User-Agent: KMail/1.9.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200602211250.44749.ashley.moran@codeweavers.net> X-OriginalArrivalTime: 21 Feb 2006 12:50:50.0515 (UTC) FILETIME=[716F0630:01C636E5] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[UPPERCASE_25_50=0] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/330 X-Sequence-Number: 20751 I'm sure this is a simple question but the docs only go as far as "For all indexes except the shared system catalogs, REINDEX is crash-safe and transaction-safe." I have a transaction that imports about 2.3 GB of data as pairs of DELETE FROM / COPY. What is the difference between reindexing tables during this transaction vs after? ie BEGIN WORK; DELETE FROM X; COPY X ...; REINDEX TABLE X; DELETE FROM Y; COPY Y ...; REINDEX TABLE Y; DELETE FROM Z; COPY Z ...; REINDEX TABLE Z; COMMIT; vs BEGIN WORK; DELETE FROM X; COPY X ...; DELETE FROM Y; COPY Y ...; DELETE FROM Z; COPY Z ...; COMMIT; REINDEX TABLE X; REINDEX TABLE Y; REINDEX TABLE Z; Thanks Ashley