Re: How to EXPLAIN a trigger function

Dne Po 17. srpna 2015 13:25:25, Albe Laurenz napsal(a):
> Zdenek Belehrádek wrote:
> 
> > We
> > would like to know if there is simple way to EXPLAIN the trigger function,
> > so we could optimize it.
> 
> 
> I believe that the auto_explain module could help you:
> http://www.postgresql.org/docs/current/static/auto-explain.html
> 
> If you turn on "auto_explain.log_nested_statements" and set
> "auto_explain.log_min_duration" low enough, the execution plans of
> all SQL statements calle from a function will be logged.
> 
> Yours,
> Laurenz Albe

I did not know about this, it could be useful in future. Thanks.

Sadly, it does not solve our problem. When used on the INSERT INTO dummytable, 
it produces:

2015-08-17 14:08:14 GMT LOG:  duration: 1.274 ms  plan:
        Query Text: INSERT INTO dummytable [...]
        Insert on dummytable  (cost=44.44..44.45 rows=1 width=0) (actual 
time=2.956..2.956 rows=0 loops=1)
          InitPlan 1 (returns $0)
[...]
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual 
time=0.013..0.013 rows=1 loops=1)

There is nothing in the log about calling trigger. I edited the trigger 
function so it would use the slow select, and the output in the log was 
exactly  the same (not counting different - longer - durations).

When I do EXPLAIN ANALYZE, at least I get some info about the trigger:

Insert on dummytable  (cost=44.44..44.45 rows=1 width=0) (actual 
time=1.135..1.135 rows=0 loops=1)
   InitPlan 1 (returns $0)
[...]
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.018..0.018 
rows=1 loops=1)
 Planning time: 0.111 ms
 Trigger trigger: time=1.113 calls=1
 Execution time: 1.244 ms


-- Zdeněk Bělehrádek