Re: Poor performance with ON DELETE CASCADE

Conal <Conal(dot)Tuohy(at)gmail(dot)com> writes:
> I have a database schema which has a "central" table with several
> others depending on it. The dependent tables all have foreign key
> constraints with ON DELETE CASCADE so that I can remove tuples from
> the "central" table and have the dependent rows removed automatically.
> This all works, but it's very slow, and I can't see why. The dependent
> tables are all indexed by this foreign key, so the deletions should be
> very fast.

Did you recently add the required indexes? Existing releases of
Postgres cache query plans for FK queries for the life of a session,
so it seems barely possible that you are just working with a stale
plan. Another possibility is that you need to ANALYZE the tables
involved so that the planner knows what it's dealing with.

> Unfortunately EXPLAIN doesn't provide any information about the
> details of how it executes the cascading deletion; there's no query
> plan for this, so I can't see why it is taking so long. Is it possible
> to obtain a query plan for these "cascaded" delete queries?

If you have the log message level cranked up high enough when the FK
trigger is first fired during a session, it'll log the actual FK query,
and then you can use PREPARE and EXPLAIN EXECUTE to see how it gets
planned. (You need to take that route because it'll be a parameterized
query --- do NOT just plug in some constants and assume you'll get the
same plan.)

regards, tom lane