Re: Order of columns in GROUP BY is significant to the planner.

David Rowley <david.rowley@2ndquadrant.com> writes:
> On 21 December 2017 at 19:16, Jeff Janes <jeff.janes@gmail.com> wrote:
>> The first one inserts a sort node on col1,col2 before doing the Group
>> Aggregate.  The second one uses the ordering of the tuples derived from the
>> index scan to do the Group Aggregate directly.  Isn't it surprising that the
>> order of the columns in the GROUP BY has to be same as the order in the
>> index definition in order to make maximal use of the index?  Is that a bug?

> Not a bug,

No, although you could argue that it's a hangover from back before we
had hash-based grouping.  If you look into the parser's processing of
GROUP BY clauses you'll find out that the semantics of GROUP BY are
more tightly tied to ORDER BY than you might have expected --
specifically, the operator class involved, and therefore potentially
the definition of equality, can be absorbed from ORDER BY if the column
lists match.  So historically the planner treated GROUP BY + ORDER BY
as largely one thing, and there was no value in considering grouping
with a different column ordering.

> just the number of combinations to try could end up growing
> very large

Yeah, I'm pretty doubtful that the potential improvement would be
worth the extra planner cycles in most cases.  Maybe if there are
just two or three GROUP BY columns, it'd be OK to consider all the
combinations, but it could get out of hand very quickly.

            regards, tom lane