>> I'd really have to bet that you forgot to index one of the referencing >> tables.
> *That was our first thought, so we went through the child tables to check
> but apparently we missed some. (please see below for the difference in the
> explain analyze output)*
I'm confused. Your second EXPLAIN ANALYZE looks like you fixed the problem. Are you still thinking there's an issue?
I believe we improved it at least, whether it's permanently fixed remaines to be seen once transaction volume increases again.
> Why is "order2transaction_fk" being triggered twice? Is that because > there're two affected rows?
No, I'd have expected a delete of multiple rows to show as calls=N, not N separate entries.
Maybe there are recursive queries buried under here somewhere? That is, are you expecting any of the cascaded deletes to cascade further? I don't recall exactly what EXPLAIN is likely to do with such cases.
Deleting from the TopTable (Transaction), I'd expect the following effects:
- 0 affected rows in Address using address2transaction_fk - 0 affected rows in Certificate using certificate2transaction_fk - 0 affected rows in Note using note2transaction_fk
- 1 - N affected rows in Order usingorder2transaction_fk
A deletion in "Order" would also trigger an ON DELETE CASCADE to Certificate using certificate2order_fk, which affects 0 rows.
This doesn't explain the extra trigger of "order2transaction_fk".
Any guidelines as to how we may investigate this further would be greatly appreciated.