Execution time of triggers fired when a procedure is executed in postgresql

Posted on

Question :

I have a stored procedure which may delete and/or insert tuples into a database table. This table has an AFTER INSERT OR DELETE OR UPDATE trigger. Basically I want to retrieve how much time was spent in this trigger as a result of changes that were caused by the stored procedure.

I have tried to use EXPLAIN ANALYZE for this:


However the result that I get contains no information about triggers:

         'Node Type':'Result',
         'Parallel Aware':False,
         'Startup Cost':0.0,
         'Total Cost':0.26,
         'Plan Rows':1,
         'Plan Width':4,
         'Actual Startup Time':37.086,
         'Actual Total Time':37.087,
         'Actual Rows':1,
         'Actual Loops':1
      'Planning Time':0.041,

      'Execution Time':37.092

The database system is posgresql, accessed in python via psycopg2. Is there any way to get the timings from the database system directly?

Answer :

You can use pg_stat_statements extension to collect stats over all queries in your db. You can indirectly see to total runtime of INSERT/UPDATE OR DELETE for needed table.

Leave a Reply

Your email address will not be published. Required fields are marked *