Re: How to efficiently duplicate a whole schema?

> >> 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'.
>
> What are you using, exactly?

What I want to do:

  let t be the list of tables

  for t in tables:
    delete from db_dev.t;

  for t in tables:
    insert into db_dev.t (...) select ... from db.t;

Some of my foreign keys are creating references loops in my schema, thus
there is no correct order to do the deletes and inserts so that the
constraints are satisfied at all time.  I have to enclose those two loops
in a 'set constraints all deferred' to avoid complaints from the
constraints.

I tried dropping the indexes first, doing the transfer and recreating the
indexes: no gain.  So computing the indexes doesn't take significant time.

I then tried removing all the foreign keys constraints, replacing delete
by truncate and it now runs in about 25 seconds.  Downside is that I lose
the foreign keys integrity verification, but because of this reference
loop in my schema it has caused me more problem than it has avoided until
now.  So I can live with that!

Thanks all!

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