2

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?

  • 1
    If there is no time reported I'd say your triggers didn't fire. You could also add verbose to the explain options – a_horse_with_no_name May 3 '17 at 20:33
  • 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. – J. Gambolputty May 3 '17 at 22:00
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.