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

[andremussche]

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]

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

[andremussche]

yes @pgiraud, here it is: https://explain.dalibo.com/plan/hb9979c3b424e5gg#raw

[pgiraud]

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

[pgiraud]

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

[andremussche]

@pgiraud here's json too: https://explain.dalibo.com/plan/22ab2c34f0ed5e68#raw

[pgiraud]

It seems like the issue faced here is the same as in #573.