Обсуждение: EXPLAIN doesn't show the actual function expression for FunctionScan
So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
report. Seems like this would be a good thing to print. Should
it appear always, or just with VERBOSE, or ???
regards, tom lane
On Tue, Aug 24, 2010 at 10:40:30AM -0400, Tom Lane wrote: > So I got annoyed by $SUBJECT just now while chasing Wheeler's bug > report. Seems like this would be a good thing to print. Should > it appear always, or just with VERBOSE, or ??? +1 for always. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Aug 24, 2010 at 10:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > So I got annoyed by $SUBJECT just now while chasing Wheeler's bug > report. Seems like this would be a good thing to print. Should > it appear always, or just with VERBOSE, or ??? I think showing it always is reasonable. I'd like to see it printed in a form such that casting to regproc will succeed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Aug 24, 2010 at 10:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug >> report. Seems like this would be a good thing to print. Should >> it appear always, or just with VERBOSE, or ??? > > I think showing it always is reasonable. I'd like to see it printed > in a form such that casting to regproc will succeed. On second thought, that second sentence may not make sense. What exactly did you have in mind for this to look like? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> I think showing it always is reasonable. �I'd like to see it printed
>> in a form such that casting to regproc will succeed.
> On second thought, that second sentence may not make sense.
It does not, because it's not the *name* of the function that I care
about --- it's the actual executable expression including arguments.
> What exactly did you have in mind for this to look like?
Wheeler's example involves
select ... from unnest(array[blah blah blah])
and I'd like it to regurgitate the whole unnest(array[blah blah blah])
expression. Not sure how to label it exactly. Right now you only see
Function Scan on unnest f (cost=0.00..1.50 rows=100 width=96)
or with VERBOSE, it'll give you some info about the targetlist (the ...
above), but still nothing about the FROM expression.
regards, tom lane
On Tue, Aug 24, 2010 at 11:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas <robertmhaas@gmail.com> wrote: >>> I think showing it always is reasonable. I'd like to see it printed >>> in a form such that casting to regproc will succeed. > >> On second thought, that second sentence may not make sense. > > It does not, because it's not the *name* of the function that I care > about --- it's the actual executable expression including arguments. > >> What exactly did you have in mind for this to look like? > > Wheeler's example involves > > select ... from unnest(array[blah blah blah]) > > and I'd like it to regurgitate the whole unnest(array[blah blah blah]) > expression. Not sure how to label it exactly. Right now you only see > > Function Scan on unnest f (cost=0.00..1.50 rows=100 width=96) > > or with VERBOSE, it'll give you some info about the targetlist (the ... > above), but still nothing about the FROM expression. If you try to put all that on the same line, I think it might get awkwardly long. Perhaps something like: Function Scan on function_name Expression: function_name(function_arg1, function_arg2, ...) ? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Hi, On Tuesday 24 August 2010 16:40:30 Tom Lane wrote: > So I got annoyed by $SUBJECT just now while chasing Wheeler's bug > report. Seems like this would be a good thing to print. Should > it appear always, or just with VERBOSE, or ??? I vote for only showing it with verbose - not that its a new problem, but too long argument lists (arrays) you could make otherwise readable plans unreadable. Andres
Robert Haas <robertmhaas@gmail.com> writes:
> If you try to put all that on the same line, I think it might get
> awkwardly long. Perhaps something like:
> Function Scan on function_name
> Expression: function_name(function_arg1, function_arg2, ...)
Yeah, that's what I had in mind, but I'm still fumbling for the right
label. "Expression" seems a bit too generic.
regards, tom lane
Andres Freund <andres@anarazel.de> writes:
> On Tuesday 24 August 2010 16:40:30 Tom Lane wrote:
>> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
>> report. Seems like this would be a good thing to print. Should
>> it appear always, or just with VERBOSE, or ???
> I vote for only showing it with verbose
That'd be fine with me. The lack of prior complaints suggests that
most of the time people don't need it.
regards, tom lane
On Tuesday 24 August 2010 17:36:50 Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > > On Tuesday 24 August 2010 16:40:30 Tom Lane wrote: > >> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug > >> report. Seems like this would be a good thing to print. Should > >> it appear always, or just with VERBOSE, or ??? > > > > I vote for only showing it with verbose > > That'd be fine with me. The lack of prior complaints suggests that > most of the time people don't need it. I personally missed it but never got around to complain/do something. Andres
Excerpts from Tom Lane's message of mar ago 24 11:36:50 -0400 2010: > Andres Freund <andres@anarazel.de> writes: > > On Tuesday 24 August 2010 16:40:30 Tom Lane wrote: > >> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug > >> report. Seems like this would be a good thing to print. Should > >> it appear always, or just with VERBOSE, or ??? > > > I vote for only showing it with verbose > > That'd be fine with me. The lack of prior complaints suggests that > most of the time people don't need it. Yeah, and if the expression is large, it could get cumbersome (consider crosstab functions which are often called with large gobs of text). On the other hand, the same case makes it very useful to have the text for further research on query behavior. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> If you try to put all that on the same line, I think it might get
>> awkwardly long. Perhaps something like:
>> Function Scan on function_name
>> Expression: function_name(function_arg1, function_arg2, ...)
> Yeah, that's what I had in mind, but I'm still fumbling for the right
> label. "Expression" seems a bit too generic.
How about "Function Call"?
A moment's hacking later:
regression=# explain verbose SELECT lv|| op|| rv FROM unnest(ARRAY[ ROW('1.2.2'::semver, '='::text,
'1.2.2'::semver), ROW('1.2.23', '=', '1.2.23') ]) AS f(lv semver, op text, rv semver);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------Function
Scanon pg_catalog.unnest f (cost=0.00..1.50 rows=100 width=96) Output: (((lv)::text || op) || (rv)::text) Function
Call:unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=', '1.2.23')])
(3 rows)
Look reasonable?
regards, tom lane
On Tue, Aug 24, 2010 at 12:06:34PM -0400, Tom Lane wrote:
> I wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:
> >> If you try to put all that on the same line, I think it might get
> >> awkwardly long. Perhaps something like:
>
> >> Function Scan on function_name
> >> Expression: function_name(function_arg1, function_arg2, ...)
>
> > Yeah, that's what I had in mind, but I'm still fumbling for the right
> > label. "Expression" seems a bit too generic.
>
> How about "Function Call"?
>
> A moment's hacking later:
>
> regression=# explain verbose SELECT lv|| op|| rv FROM unnest(ARRAY[
> ROW('1.2.2'::semver, '='::text, '1.2.2'::semver),
> ROW('1.2.23', '=', '1.2.23')
> ]) AS f(lv semver, op text, rv semver);
> QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------
> Function Scan on pg_catalog.unnest f (cost=0.00..1.50 rows=100 width=96)
> Output: (((lv)::text || op) || (rv)::text)
> Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=',
'1.2.23')])
> (3 rows)
>
> Look reasonable?
This would have helped me a good deal a couple of times this week :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Function Scan on pg_catalog.unnest f (cost=0.00..1.50 rows=100 width=96)
> Output: (((lv)::text || op) || (rv)::text)
> Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=',
'1.2.23')])
This may be the ultimate bike-shed but Wouldn't this be clearer the
other way around? I generally think input comes first and then output.
On the other hand the plan tree does read from bottom up with the
outputs going up to the next level up.
--
greg
Greg Stark <gsstark@mit.edu> writes:
> On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> �Function Scan on pg_catalog.unnest f �(cost=0.00..1.50 rows=100 width=96)
>> � Output: (((lv)::text || op) || (rv)::text)
>> � Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=',
'1.2.23')])
> This may be the ultimate bike-shed but Wouldn't this be clearer the
> other way around? I generally think input comes first and then output.
The order was bothering me a bit too, but there's a generic decision
in there that the tlist is shown before any node-type-specific items.
Not sure that we want to move it to the bottom for all of them.
regards, tom lane
On Tue, Aug 24, 2010 at 1:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Stark <gsstark@mit.edu> writes:
>> On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Function Scan on pg_catalog.unnest f (cost=0.00..1.50 rows=100 width=96)
>>> Output: (((lv)::text || op) || (rv)::text)
>>> Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=',
'1.2.23')])
>
>> This may be the ultimate bike-shed but Wouldn't this be clearer the
>> other way around? I generally think input comes first and then output.
>
> The order was bothering me a bit too, but there's a generic decision
> in there that the tlist is shown before any node-type-specific items.
> Not sure that we want to move it to the bottom for all of them.
I don't think we do. Although, it has seemed to me at times that we
might want to have "output" be controlled by its own EXPLAIN option.
EXPLAIN (VERBOSE on, OUTPUT off) ...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
Re: EXPLAIN doesn't show the actual function expression for FunctionScan
От
"David E. Wheeler"
Дата:
On Aug 24, 2010, at 10:21 AM, Tom Lane wrote: >> This may be the ultimate bike-shed but Wouldn't this be clearer the >> other way around? I generally think input comes first and then output. > > The order was bothering me a bit too, but there's a generic decision > in there that the tlist is shown before any node-type-specific items. > Not sure that we want to move it to the bottom for all of them. Does it make a difference for the other ouputs (JSON, YAML, XML)? Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Aug 24, 2010, at 10:21 AM, Tom Lane wrote:
>> The order was bothering me a bit too, but there's a generic decision
>> in there that the tlist is shown before any node-type-specific items.
>> Not sure that we want to move it to the bottom for all of them.
> Does it make a difference for the other ouputs (JSON, YAML, XML)?
It shouldn't should it? The order of fields in a node should not
matter too much for any readers of those formats.
regards, tom lane
Argument List?
--
dim
Le 24 août 2010 à 18:06, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
> I wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> If you try to put all that on the same line, I think it might get
>>> awkwardly long. Perhaps something like:
>
>>> Function Scan on function_name
>>> Expression: function_name(function_arg1, function_arg2, ...)
>
>> Yeah, that's what I had in mind, but I'm still fumbling for the right
>> label. "Expression" seems a bit too generic.
>
> How about "Function Call"?
>
> A moment's hacking later:
>
> regression=# explain verbose SELECT lv|| op|| rv FROM unnest(ARRAY[
> ROW('1.2.2'::semver, '='::text, '1.2.2'::semver),
> ROW('1.2.23', '=', '1.2.23')
> ]) AS f(lv semver, op text, rv semver);
> QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------
> Function Scan on pg_catalog.unnest f (cost=0.00..1.50 rows=100 width=96)
> Output: (((lv)::text || op) || (rv)::text)
> Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=',
'1.2.23')])
> (3 rows)
>
> Look reasonable?
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
Dimitri Fontaine <dfontaine@hi-media.com> writes:
> Argument List?
Well, as shown in the example I posted, it's not just the argument list
but the whole call:
>> Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, ('1.2.2'::text)::semver), ROW('1.2.23', '=',
'1.2.23')])
Now you might suggest that the function itself is redundant with the
information given in the FunctionScan node line and so we need only
show the argument list. Unfortunately there are cases where this fails;
in particular, the named function could have been "inlined" by the
planner, meaning that the actual expression could be just about anything
at all. So I think that trying to be cute is a bad idea and we should
just print the nodetree as-is.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Now you might suggest that the function itself is redundant with the > information given in the FunctionScan node line and so we need only > show the argument list. Unfortunately there are cases where this fails; > in particular, the named function could have been "inlined" by the > planner, meaning that the actual expression could be just about anything > at all. So I think that trying to be cute is a bad idea and we should > just print the nodetree as-is. Oh. +1 then. Regards, -- dim