Re: column order in GROUP BY

Lukas Smith <smith(at)pooteeweet(dot)org> writes:
> Tom Lane wrote:
>> I think that's the wrong place. transformGroupClause is the right
>> place. It already does some hacking to try to make the GROUP BY
>> semantics match ORDER BY, but it doesn't think to try reordering
>> the GROUP BY items.

> Does it also throw out unnecessary columns in the GROUP BY? Like when
> the GROUP BY contains multiple columns of which one (or a set) already
> uniquely identifies every row.

No, and it would be quite inappropriate to do that in the parser, since
the constraints making it a valid transformation might get dropped
before the query is planned/used. It'd be OK to throw out trivial
duplicates ("GROUP BY x,x") but I doubt that it's worth the cycles even
to try --- if you write a query that stupid you shouldn't complain that
it doesn't run efficiently.

There's a fairly fundamental point here, which is that the parser is
responsible for determining semantics --- in this case, what is the
semantic meaning of GROUP BY, in particular which operators should
implement it --- and then the planner is responsible for optimization
without changing those semantics. Given the system design assumption
that GROUP BY is associated with a specific sort ordering, changing the
column order is a semantic change and so it's reasonable for the parser
to do it. If we got rid of that design assumption then it'd become
planner territory, but as Neil observes that's not exactly low-hanging
fruit.

regards, tom lane