X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4A3BFD1C50B for ; Wed, 6 Aug 2003 19:14:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 65925-06 for ; Wed, 6 Aug 2003 16:12:43 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 21641D1C92B for ; Wed, 6 Aug 2003 16:13:54 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h76JDnSa007236; Wed, 6 Aug 2003 15:13:49 -0400 (EDT) To: Sebastien Lemieux Cc: Postgresql-performance Subject: Re: How to efficiently duplicate a whole schema? In-reply-to: References: Comments: In-reply-to Sebastien Lemieux message dated "Wed, 06 Aug 2003 14:56:14 -0400" Date: Wed, 06 Aug 2003 15:13:49 -0400 Message-ID: <7235.1060197229@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/108 X-Sequence-Number: 2925 Sebastien Lemieux writes: > All the time is taken at the commit of both transaction. Sounds like the culprit is foreign-key checks. One obvious question is whether you have your foreign keys set up efficiently in the first place. As a rule, the referenced and referencing columns should have identical datatypes and both should be indexed. (PG will often let you create foreign key constraints that don't meet these rules ... but performance will suffer.) Also, what procedure are you using to delete all the old data? What I'd recommend is ANALYZE table; TRUNCATE table; INSERT new data; The idea here is to make sure that the planner's statistics reflect the "full" state of the table, not the "empty" state. Otherwise it may pick plans for the foreign key checks that are optimized for small tables. regards, tom lane