On Wed, 6 Aug 2003, Tom Lane wrote:

> Sebastien Lemieux <[EMAIL PROTECTED]> 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






---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to