On 24 April 2014 23:25, Tom Lane <t...@sss.pgh.pa.us> wrote: > Jonatan Evald Buus <jonatan.b...@cellpointmobile.com> writes: > > On 24 April 2014 22:29, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> 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 using *order2transaction_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. > regards, tom lane > -- JONATAN EVALD BUUS CTO Mobile US +1 (305) 331-5242 Mobile DK +45 2888 2861 Telephone +1 (305) 777-0392 Fax. +1 (305) 777-0449 jonatan.b...@cellpointmobile.com www.cellpointmobile.com CellPoint Mobile Inc. 4000 Ponce de Leon Boulevard Suite 470 Coral Gables, FL 33146 USA 'Mobilizing the Enterprise'
