Re: How to efficiently duplicate a whole schema?

On Wed, 6 Aug 2003, Tom Lane wrote:

> Sebastien Lemieux <slemieux(at)elitra(dot)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.)

I've checked and all the foreign keys are setup between 'serial' (the
primary key of the referenced table) and 'integer not null' (the foreign
key field). Would that be same type? A couple of my foreign keys are not
indexed, I'll fix that. The latter seems to do the job, since I can now
synchronize in about 75 seconds (compared to 30 minutes), which seems good
enough.

> 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.

I added the 'analyze' but without any noticable gain in speed. I can't
use 'truncate' since I need to 'set constraints all deferred'. I guess
the bottom line is that I really need to first drop all constraints and
indexes, synchronize and then rebuild indexes and check constraints. But
for that I'll need to reorganize my code a little bit!

In the meantime, how bad a decision would it be to simply remove all
foreign key constraints? Because, currently I think they are causing more
problems than they are avoiding...

thanks,

--
Sebastien Lemieux
Bioinformatics, post-doc
Elitra-canada