Re: Poor Delete performance

Bill Huff <bhuff(at)colltech(dot)com> writes:
> There is a foreign key constraint, but that is accomplished by an
> INSERT/UPDATE trigger. I don't see why that should cause any problems.

Ah, I believe I see the problem: it's the inefficient implementation of
AFTER EVENT triggers. The existence of either an AFTER UPDATE or AFTER
DELETE trigger causes trigger.c to add an event to its event queue for
every operation on the affected table. And every addition of an event
searches that queue --- with a linear scan. Thus operations on such a
table incur O(N^2) search cost if N tuples are affected in one
operation.

This needs to be improved (if we can't get rid of the lookup completely,
maybe use a hash table instead of sequential scan?) but it's much too
late to consider fixing it for 7.1 :-(.

Actually, though, it might be even stupider than that: it looks like
the queue should only be searched if the tuple being deleted was
inserted/modified earlier in the same transaction. Assuming that that
doesn't apply to Bill's case, the only thing I can see that could be
causing O(N^2) behavior is the lappend() in deferredTriggerAddEvent.
That's simple enough that we *could* fix it for 7.1 ...

regards, tom lane