dalibo/pev2

Huge constraint delays are not shown (due to missing index)

Opened this issue · 5 comments

andremussche commented

we had a huge delay when executing a delete / cleanup query.
The query seemed fast enough, however at the bottom of the plan these lines explained the bottleneck:

"Trigger RI_ConstraintTrigger_a_17371 for constraint log_notify_specify_audit_specify_id_fkey: time=11.395 calls=10"
"Trigger RI_ConstraintTrigger_a_17471 for constraint queue_notify_specify_audit_specify_id_fkey: time=0.756 calls=10"
"Trigger RI_ConstraintTrigger_a_77488 for constraint queue_notify_fulfilment_audit_specify_id_fkey: time=2588.670 calls=10" <<<<
"Trigger RI_ConstraintTrigger_a_77517 for constraint log_notify_fulfilment_audit_specify_id_fkey: time=1333.017 calls=10" <<<<

after adding 2 indexes on the foreign keys, the delete was fast again

pgiraud commented

Thanks for the report @andremussche
Would it possible to get a complete example plan?

pgiraud commented

Nice! Thanks.
Just in case, is it possible for you to get the same plan in JSON format?

pgiraud commented

For an unknown reason, the Triggers don't get are not displayed in the UI.