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

postgresqlstored-procedurestrigger

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:

EXPLAIN (FORMAT JSON, ANALYZE, TIMING) SELECT f(x);

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

[
   {
      'Plan':{
         '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,
      'Triggers':[

      ],
      '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?

Best 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.

Related Question