Re: EXPLAIN VERBOSE with parallel Aggregate

On 21 April 2016 at 17:14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
>> I'd like to admit that I'm a bit confused as to why
>> generate_function_name(), when it already knows the funcid, bothers to
>> call func_get_detail(), which performs a search for the function based
>> on the name and argument types, to find the function, most likely with
>> the same funcid as the one which it already knew.
>
>> Could you explain why this has to happen?
>
> The point is exactly to find out whether a search for the function
> given only the name and argument types would find the same function,
> or a similar function in a different schema --- which would happen
> if that other schema is earlier in the search path than the desired
> one, or maybe the desired one isn't in search_path at all. In such
> a case we must schema-qualify the function name in the printed
> expression.

Thanks. That makes more sense now.

> To some extent this is because ruleutils serves two masters. We would
> probably not care that much about schema exactness for EXPLAIN's purposes,
> but we do care for dumping views and rules.

OK, so here's my thoughts. Currently, as mentioned above, I've
included a PARTIAL prefix for partial aggregates. This is
syntactically incorrect for the dumping of views etc, but that should
not matter as partial Aggrefs never come from the parser, they're only
perhaps generated later in the planner. Same goes for combine
aggregates too.

In the attached I'm proposing that we simply just use the
pg_proc.proname which belongs to the aggref->aggfnoid for combine
aggregates. This gets around the function not being found by
generate_function_name() and the namespace problem, that code should
never be executed when getting a view def, since we can't have combine
aggs there.

The attached still does not get the output into the way Robert would
have liked, but I still stand by my dislike to pretending the combine
aggregate is a normal aggregate. It's not all that clear if FILTER
should be displayed in the combine agg. Combine Aggs don't do FILTER.

This makes the output:

postgres=# explain verbose select avg(num) FILTER (WHERE num >
0),sum(num),count(*) from i;
QUERY PLAN
-------------------------------------------------------------------------------------------
Finalize Aggregate (cost=13758.56..13758.57 rows=1 width=48)
Output: avg((PARTIAL avg(num) FILTER (WHERE (num > 0)))),
sum((sum(num))), count(*)
-> Gather (cost=13758.33..13758.54 rows=2 width=48)
Output: (PARTIAL avg(num) FILTER (WHERE (num > 0))),
(sum(num)), (count(*))
Workers Planned: 2
-> Partial Aggregate (cost=12758.33..12758.34 rows=1 width=48)
Output: PARTIAL avg(num) FILTER (WHERE (num > 0)),
sum(num), count(*)
-> Parallel Seq Scan on public.i (cost=0.00..8591.67
rows=416667 width=4)
Output: num

Comments welcome.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services