From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim Nasby <Jim.Nasby@bluetreble.com>
cc: Robert Klemme <shortcutter@googlemail.com>,
        pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Seeing execution plan of foreign key constraint check?
In-reply-to: <c693bae7-1779-d7f7-5485-2578974b02b9@BlueTreble.com>
References: 
 <CAM9pMnPs3Jtw9nT7m9E7eH2E3xiExSR6mjmFQrZzoL__hwWxQQ@mail.gmail.com>
 <4b2f737a-ea37-35cd-012b-307ee1c9a584@BlueTreble.com>
 <11766.1468959038@sss.pgh.pa.us>
 <c693bae7-1779-d7f7-5485-2578974b02b9@BlueTreble.com>
Comments: In-reply-to Jim Nasby <Jim.Nasby@bluetreble.com>
	message dated "Thu, 21 Jul 2016 16:42:04 -0500"
Date: Thu, 21 Jul 2016 17:59:44 -0400
Message-ID: <15306.1469138384@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance