Re: Poor performance for delete query

[Jonatan Evald Buus <jonatan.buus@xxxxxxxxxxxxxxxxxxx>]

On 24 April 2014 23:25, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Jonatan Evald Buus <jonatan.buus@xxxxxxxxxxxxxxxxxxx> writes:
> On 24 April 2014 22:29, Tom Lane <tgl@xxxxxxxxxxxxx> 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.buus@xxxxxxxxxxxxxxxxxxx
www.cellpointmobile.com
 
CellPoint Mobile Inc.
4000 Ponce de Leon Boulevard
Suite 470
Coral Gables, FL 33146
USA

'Mobilizing the Enterprise'