Re: [HACKERS] Foreign key performance

On Fri, 18 Apr 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > 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.
>
> At one time the deferred-trigger queue had an O(N^2) behavioral problem
> for large N = number of pending trigger events.  But I thought we'd
> fixed that.  What's the test case exactly?  Can you get a profile with
> gprof?

I'm going to tomorrow hopefully - but it looks to me that we fixed one, but
possibly not another place where we read through the list unnecessarily
AFAICS. I think deferredTriggerInvokeEvents (when called with
immediate_only = true) is going to go through the entire list looking for
immediate triggers to fire after each statement.  However, excepting set
constraints, any immediate triggers for any event added prior to this
statement will by necessity have already been run unless I'm missing
something, which means that we're often looking through entries that
aren't going to have any triggers to run now in any case.

Keeping a pointer to the end of the list as of last statement and going
through the list from there cut the time for the deferred case in half in
my simple test (about 3.3x the no fk and just under 2x the immediate).