Обсуждение: 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