postgresqlquery-optimizationcascade

Debugging PostgreSQL triggers performance

I have a slow delete query. I used EXPLAIN ANALYZE to understand the bottleneck and I saw two triggers that are slow:

Trigger for constraint other_table_1_fid_fkey: time=3.644 calls=1
Trigger for constraint other_table_2_fid_fkey: time=6.289 calls=1

Following this discussion I added indexes. The index on fid in other_table_1 indeed improved the performance. But adding an index on fid in other_table_2 didn't make any difference, and it is seems to be the bottleneck in the delete query.

My question is how can I debug (or EXPLAIN ANALYZE) the trigger itself.

Thanks,

P.S To solve my concrete problem I temporary remove the foreign key constraint and it improved the performance.

Solution

  • Set these parameters:

    shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_triggers = on
auto_explain.log_nested_statements = on

    Then restart PostgreSQL (so that shared_preload_libraries takes effect) and execute the statement again. You will find the execution plans of all SQL statements in the trigger functions in the PostgreSQL log. That will enable you to find and tune slow statements.