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

On 22 December 2017 at 03:37, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
>> 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.

Thinking a bit more about this, it would be pretty silly to go and try
random combinations of columns or all combinations up to a certain
level. It would be much smarter to look for a btree index that has all
of the GROUP BY columns as leading keys and use that column order
instead. Perhaps it could be just changed to that regardless unless
there's also an ORDER BY in the query. Nothing would need to be
touched if there was only 1 GROUP BY expr, and we probably couldn't do
anything if the GROUP BY contains Vars/Exprs from multiple relations.

Of course, it needs more thought than just the above, but it seems
like an idea that might be workable.

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