Re: [PERFORM] Foreign key performance

On Fri, 18 Apr 2003, Stephan Szabo wrote:

> On Fri, 18 Apr 2003, Tom Lane wrote:
>
> > But the call counts do sound pretty damning.
>
> Yeah, but even with my hack last night it was still appreciably slower
> than immediate constraints. Comparing the call counts in that function
> for the immediate versus deferred(hacked) weren't giving me a good idea of
> where that time was going.

This last was due to assert checking I think.  AllocSetCheck was the big
time waster on the hacked deferred case. Turning off assert checking I
get:

Median over 3 100000 inserts in one transaction (excepting the original
code which took a really long time so I ran it once) from time psql ...

No Fk                   24.14s
Immediate FK            42.80s
Original Deferred FK  1862.06s
Hacked Deferred FK      35.30s

The hack was just the keeping around the list pointer from the last run
through (see attached - passed simple fk tests and regression, but there
might be problems I don't see).  Looking at the code, I also wonder if we
would get some gain by not allocating the per_tuple_context at the
beginning but only when a non-deferred constraint is found since otherwise
we're creating and destroying the context and possibly never using it.
The cost would presumably be some boolean tests inside the inner loop.