Re: Seeing execution plan of foreign key constraint check?

Jim Nasby <Jim.Nasby@bluetreble.com> writes:
> On 7/19/16 3:10 PM, Tom Lane wrote:
>> It's not so much that people don't care, as that it's not apparent how to
>> improve this without breaking desirable system properties --- in this
>> case, that functions are black boxes so far as callers are concerned.

> I thought we already broke out time spent in triggers as part of
> EXPLAIN,

... yes ...

> and that the FK "triggers" were specifically ignored?

No.  You get something like

# explain analyze insert into cc values(1);
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Insert on cc  (cost=0.00..0.01 rows=1 width=4) (actual time=0.192..0.192 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.035 ms
 Trigger for constraint cc_f1_fkey: time=1.246 calls=1
 Execution time: 1.473 ms
(5 rows)


EXPLAIN does know enough about FK triggers to label them with the
associated constraint name rather than calling them something like
"RI_ConstraintTrigger_c_81956"; but it does not have any ability
to reach inside them.

> As for function plans, ISTM that could be added to the PL handlers if we
> wanted to (allow a function invocation to return an array of explain
> outputs).

Where would you put those, particularly for functions executed many
times in the query?  Would it include sub-functions recursively?
I mean, yeah, in principle we could do something roughly like that,
but it's not easy and presenting the results intelligibly seems
almost impossible.

            regards, tom lane