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

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to