Thread: Slow delete with with cascading foreign keys

Hi,

I have a rather complex set of relations, connected with cascading
foreign keys on delete. I'm experiencing very slow performance when
deleting *the* lead node, which everything eventually depends on. The
number of records ultimately to be deleted aren't that many (perhaps
2000-3000) but there are maybe 30 relations involved. I understand
that this is most likely due to missing indices, but I have been
unable to figure out how to get PostgreSQL to tell me where the
performance bottleneck is.

Say my simple query looks like "delete from foo where id = 1". An
"explain" on this won't yield any information for example.

Is there any way to get PostgreSQL to do an analys of a delete
statement like the way I need?

// Matthias

"Matthias Karlsson" <matthias@yacc.se> writes:
> I have a rather complex set of relations, connected with cascading
> foreign keys on delete. I'm experiencing very slow performance when
> deleting *the* lead node, which everything eventually depends on. The
> number of records ultimately to be deleted aren't that many (perhaps
> 2000-3000) but there are maybe 30 relations involved. I understand
> that this is most likely due to missing indices, but I have been
> unable to figure out how to get PostgreSQL to tell me where the
> performance bottleneck is.

If it's a reasonably modern PG version, EXPLAIN ANALYZE will break out
the time spent in each on-delete trigger, which should be enough to
answer the question.

            regards, tom lane

Tom Lane skrev:
> "Matthias Karlsson" <matthias@yacc.se> writes:
>> I have a rather complex set of relations, connected with cascading
>> foreign keys on delete. I'm experiencing very slow performance when
>> deleting *the* lead node, which everything eventually depends on. The
>> number of records ultimately to be deleted aren't that many (perhaps
>> 2000-3000) but there are maybe 30 relations involved. I understand
>> that this is most likely due to missing indices, but I have been
>> unable to figure out how to get PostgreSQL to tell me where the
>> performance bottleneck is.
>
> If it's a reasonably modern PG version, EXPLAIN ANALYZE will break out
> the time spent in each on-delete trigger, which should be enough to
> answer the question.
>
>             regards, tom lane

Thanks, that gave me something to work with. I targeted the triggers that had the most "uses", but it did not seem to
help that much. I managed to reduce execution time with maybe 10-15%, but I'll try to apply indices more aggressively
to 
see if it helps.

// Matthias

Matthias Karlsson <matthias@yacc.se> writes:
> Tom Lane skrev:
>> If it's a reasonably modern PG version, EXPLAIN ANALYZE will break out
>> the time spent in each on-delete trigger, which should be enough to
>> answer the question.

> Thanks, that gave me something to work with. I targeted the triggers that had the most "uses", but it did not seem to

> help that much. I managed to reduce execution time with maybe 10-15%, but I'll try to apply indices more aggressively
to 
> see if it helps.

Hm, you still didn't mention which PG version you're using --- but if
it's pre-8.3, keep in mind that you need to start a fresh session to
get the plans for FK triggers to change.

            regards, tom lane