Re: Deleting, indexes and transactions

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> On 05/28/2012 08:46 AM, Stevo Slavi wrote:
>> I guess in this case, referential action, from your quote, on deleting
>> As is check that there are no Bs referencing to-be-deleted A row. But
>> since all Bs are deleted (not committed yet though) prior to deleting
>> As, I don't understand why is this check taking that long time.

> FKs, as I understand it, are basically system triggers. The exact method
> by which they work and the effect of indexes on that are beyond me at
> this point. Others may have more insight.

Right. The ON DELETE trigger for table A is fired for each row, and it
has to search table B to verify that there are no rows referencing the
to-be-deleted one. If there's an index on the referencing column, this
is a reasonably cheap operation; without an index, not so much. There
is no way for that trigger to know that the current transaction already
deleted all the referencing rows; and even if it did know that, it would
have to search table B anyway, because some other transaction could have
inserted a referencing row in between the DELETE on B and the DELETE on A.

> Well the issue seems to be with what you do to A not B. The FK is on B
> but the reference is to A and when you do an action on A in it needs to
> verify the state of the referring rows in B. By explicitly specifying a
> course of action (ON DELETE CASCADE) you streamline the process in the
> first case. In the second case it not as big an issue because you are
> only changing a small subset of A.

ON DELETE CASCADE isn't really going to help here, because that's just a
different trigger that has still got to search table B. You still need
the index if you want its performance to not suck.

We have had several discussions over the years about whether a foreign
key constraint should require indexes on both sides of the constraint,
not just the PK side. The answer has remained "no", on the grounds that
(a) this would be contrary to SQL standard, which does not require any
such thing, and (b) if you never delete keys from the PK table then you
don't really need to pay the cost of keeping an index on the FK column.
But the rule of thumb for most cases is that you want to have that
index.

regards, tom lane