Re: 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> wrote:
> On Tue, Aug 24, 2010 at 10:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> writes:
> On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas <robertmhaas(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)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(at)anarazel(dot)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(at)anarazel(dot)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(at)anarazel(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

I wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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

On Tue, Aug 24, 2010 at 12:06:34PM -0400, Tom Lane wrote:
> I wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)mit(dot)edu> writes:
> On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

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(at)kineticode(dot)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(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> I wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Dimitri Fontaine <dfontaine(at)hi-media(dot)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(at)sss(dot)pgh(dot)pa(dot)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