Re: slow delete due to reference

Rikard Pavelic <rikard(at)ngs(dot)hr> writes:
> Is it possible to speed up deletes which have null references so they don't check if a reference is valid?

You're thinking about the problem backwards. Since the table is
self-referential, each row is both a PK (referenced) row and an FK
(referencing) row. In its role as an FK row, a delete requires no work,
null referencing column or otherwise --- but in its role as a PK row, a
delete does require work. The PK column here is "id" which is not null in
any row, so for every row, the FK trigger must check to see whether that
id is referenced by any FK row. With no index on the FK column (undo_id)
that requires an expensive seqscan.

There are optimizations to skip the check when deleting a null PK value,
but that case never occurs in your example.

> --create unique index ix_undo on large_table(undo_id) where undo_id is not null;
> (I was actually expecting that commented out index to exists, but for some reason it didn't)

It would've done the job if you'd had it, I believe.

regards, tom lane