Re: Foreign key performance

On Thu, 17 Apr 2003, Kevin Brown wrote:

> I have no problem with that under normal circumstances (i.e., the
> foreign key constraints are actively being enforced): it may well be
> the nature of foreign keys, but the problem is this: all the keys are
> DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program
> will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction.
>
> If I remove all the foreign key constraints, my performance goes up to
> 700 inserts per second!
>
> Why isn't the insert performance with all the constraints deferred
> approximating that of the performance I get without the foreign keys??

It appears (from some not terribly scientific experiments - see below)
that it's likely to be related to managing the deferred trigger queue
given that in my case at least running the constraints non-deferred was
negligible in comparison.

On batch inserts to three tables each with a foreign key to a table
containing one row (and inserts of lots of that value), I saw a ratio of
approximately 1:1.7:7 for normal inserts:non-deferred fk:deferred fk on my
7.4 dev server.