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