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

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:

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?

• user1822: If there is no time reported I'd say your triggers didn't fire. You could also add verbose to the explain options
• J. Gambolputty: The triggers definitely are executed because if I add some RAISE NOTICE statements to them these notices are correctly printed. Adding verbose to the options also did not give me any additional information about the triggers.

Answer 1

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.