From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sebastien Lemieux <slemieux@elitra.com>
Cc: Postgresql-performance <pgsql-performance@postgresql.org>
Subject: Re: How to efficiently duplicate a whole schema?
Date: Wed, 06 Aug 2003 15:13:49 -0400
Message-ID: <7235.1060197229@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.33.0308061437550.10321-100000@moebius.elitra.com>
References: <Pine.LNX.4.33.0308061437550.10321-100000@moebius.elitra.com>
Sebastien Lemieux <slemieux@elitra.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, slemieux@elitra.com
Subject: Re: How to efficiently duplicate a whole schema?
In-Reply-To: <7235.1060197229@sss.pgh.pa.us>
References: <Pine.LNX.4.33.0308061437550.10321-100000@moebius.elitra.com> <7235.1060197229@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox