Thread: Seeing execution plan of foreign key constraint check?
Hi, I was wondering whether there are any plans to include the plan of the FK check in EXPLAIN output. Or is there a different way to get to see all the plans of triggers as well as of the main SQL? When researching I found this thread from 2011 and the output format does not seem to have changed since then: https://www.postgresql.org/message-id/flat/3798971.mRNc5JcYXj%40moltowork#3798971.mRNc5JcYXj@moltowork Kind regards robert -- [guy, jim, charlie].each {|him| remember.him do |as, often| as.you_can - without end} http://blog.rubybestpractices.com/
On 7/5/16 7:14 AM, Robert Klemme wrote: > I was wondering whether there are any plans to include the plan of the > FK check in EXPLAIN output. Or is there a different way to get to see > all the plans of triggers as well as of the main SQL? > > When researching I found this thread from 2011 and the output format > does not seem to have changed since then: > > https://www.postgresql.org/message-id/flat/3798971.mRNc5JcYXj%40moltowork#3798971.mRNc5JcYXj@moltowork No one has discussed it recently. Unfortunately, this isn't the type of thing that would excite most of the core hackers, so it's unlikely any of them will pick this up. The best bet for getting this done is to decide you want to work on it yourself and email -hackers (please email before creating the patch!), pay one of the consulting companies to do it, or create a bounty and see if others will chip in to pay someone to do it. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 7/5/16 7:14 AM, Robert Klemme wrote:
>> I was wondering whether there are any plans to include the plan of the
>> FK check in EXPLAIN output. Or is there a different way to get to see
>> all the plans of triggers as well as of the main SQL?
> Unfortunately, this isn't the type of thing that would excite most of
> the core hackers, so it's unlikely any of them will pick this up.
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.
regards, tom lane
On 7/19/16 3:10 PM, Tom Lane wrote: > Jim Nasby <Jim.Nasby@BlueTreble.com> writes: >> On 7/5/16 7:14 AM, Robert Klemme wrote: >>> I was wondering whether there are any plans to include the plan of the >>> FK check in EXPLAIN output. Or is there a different way to get to see >>> all the plans of triggers as well as of the main SQL? > >> Unfortunately, this isn't the type of thing that would excite most of >> the core hackers, so it's unlikely any of them will pick this up. > > 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, and that the FK "triggers" were specifically ignored? (Granted, that doesn't give you plan info, just timing...) 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). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
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
On 7/21/16 4:59 PM, Tom Lane wrote: >> > 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. Yeah, it'd certainly need to be handled internally in a machine-understandable form that got aggregated before presentation (or with non-text output formats we could provide the raw data). Or just punt and don't capture the data unless you're using an alternative output format. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
On Fri, Jul 22, 2016 at 12:14 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 7/21/16 4:59 PM, Tom Lane wrote:
>>>
>>> > 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.
>
>
> Yeah, it'd certainly need to be handled internally in a
> machine-understandable form that got aggregated before presentation (or with
> non-text output formats we could provide the raw data). Or just punt and
> don't capture the data unless you're using an alternative output format.
I'd imagine the output to just list all "recursive" execution plans
executed probably along with indicators for how much IO and / or CPU
they were responsible for. The "recursive" plans could also be sorted
in decreasing order of total (i.e. across all individual invocations)
time spent so you see the most impacting plan first. All of that would
loose displaying calling relationships at the advantage of a simpler
presentation. I think, the relationship which statement / function
invoked with other could be determined by looking at statements /
functions. And I guess often it will be apparent from names already.
I am wondering what to do if the same statement has multiple execution
plans if that is possible in such a scenario. Present all the plans or
just the one with the highest impact? Show them next to each other so
the user is immediately aware that all these plans originated from the
same piece of SQL?
Kind regards
robert
--
[guy, jim, charlie].each {|him| remember.him do |as, often| as.you_can
- without end}
http://blog.rubybestpractices.com/
On 7/22/16 3:37 AM, Robert Klemme wrote: > I am wondering what to do if the same statement has multiple execution > plans if that is possible in such a scenario. Present all the plans or > just the one with the highest impact? Show them next to each other so > the user is immediately aware that all these plans originated from the > same piece of SQL? plpgsql runs all it's stuff via SPI, which can replan queries. So yes, I think it's necessary to deal with that. That said, if we only kept the most expensive X plans from a given function, that could handle both cases. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461