Re: POC: GROUP BY optimization

On 4/12/24 06:44, Tom Lane wrote:
> If this patch were producing better results I'd be more excited
> about putting more work into it. But on the basis of what I'm
> seeing right now, I think maybe we ought to give up on it.
Let me show current cases where users have a profit with this tiny
improvement (see queries and execution results in query.sql):
1. 'Not optimised query text' — when we didn't consider group-by
ordering during database development.
2. 'Accidental pathkeys' - we didn't see any explicit orderings, but
accidentally, the planner used merge join that caused some orderings and
we can utilise it.
3. 'Uncertain scan path' — We have options regarding which index to use,
and because of that, we can't predict the optimal group-by ordering
before the start of query planning.
4. 'HashAgg V/S GroupAgg' — sometimes, the GroupAgg strategy outperforms
HashAgg just because we don't need any ordering procedure at all.

And the last thing here — this code introduces the basics needed to add
more sophisticated strategies, like ordering according to uniqueness or
preferring to set constant-width columns first in grouping.

--
regards,
Andrei Lepikhov
Postgres Professional