Thread: when is a DELETE FK trigger planned?

I have a cascading delete trigger that is obviously using a seqscan.
(Explain analyze shows that trigger as taking over 1000s while all
other triggers are <1s. The value in test delete didn't even appear in
this child table, so an index scan would have been almost instant.)

If I do
DELETE FROM child_table WHERE fkey = value;
I get an index scan. Why doesn't the trigger do that, and how can I
force it to re-plan?

Andrew Lazarus <andrew@pillette.com> writes:
> I have a cascading delete trigger that is obviously using a seqscan.
> (Explain analyze shows that trigger as taking over 1000s while all
> other triggers are <1s. The value in test delete didn't even appear in
> this child table, so an index scan would have been almost instant.)

> If I do
> DELETE FROM child_table WHERE fkey = value;
> I get an index scan. Why doesn't the trigger do that, and how can I
> force it to re-plan?

That would depend on what PG version you're using.

However, starting a fresh connection should get you a new trigger
function plan in any case.

            regards, tom lane

I figured out what appears to happen with cascading delete using a
seqscan. In this case, the foreign keys in the child table are not
equally distributed. A few parent values occur often. Most parent
values do not occur at all. So the planner, faced with an unknown
generic key, takes the safe route.

What I've done is remove the FK (maybe it would be better to leave it
albeit disabled for documentation) and written my own AFTER DELETE
trigger that uses EXECUTE to delay planning until the actual value is
known. This appears to work correctly.

--
Sincerely,
 Andrew Lazarus        mailto:andrew@pillette.com