Re: POC: GROUP BY optimization

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>

To: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>

Cc: Zhihong Yu <zyu@yugabyte.com>, Teodor Sigaev <teodor@sigaev.ru>, Tomas Vondra <tomas.vondra@2ndquadrant.com>, David Rowley <dgrowleyml@gmail.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, "a.rybakina" <a.rybakina@postgrespro.ru>, Белялов Дамир Наилевич <d.belyalov@postgrespro.ru>

Date: 2023-07-20T06:37:24Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Restore preprocess_groupclause()
- 505c008ca37c 17.0 landed
Rename PathKeyInfo to GroupByOrdering
- 0c1af2c35c7b 17.0 landed
Add invariants check to get_useful_group_keys_orderings()
- 91143c03d4ca 17.0 landed
Fix asymmetry in setting EquivalenceClass.ec_sortref
- 199012a3d844 17.0 landed
Multiple revisions to the GROUP BY reordering tests
- 874d817baa16 17.0 landed
Get rid of pg_class usage in SJE regression tests
- e1b7fde418f2 17.0 landed
Rename index "abc" in aggregates.sql
- b91f91870828 17.0 landed
Explore alternative orderings of group-by pathkeys during optimization.
- 0452b461bc40 17.0 landed
Generalize the common code of adding sort before processing of grouping
- 7ab80ac1caf9 17.0 landed
Fix out-dated comment in preprocess_groupclause()
- f6c70b81802a 15.0 landed
- 78a9af1a2764 16.0 landed
Force parallelism in partition_aggregate
- 2fe6b2a806f2 16.0 landed
- 01474f56981a 15.0 landed
Optimize order of GROUP BY keys
- db0d67db2401 15.0 landed

Attachments

0001-When-evaluating-a-query-with-a-multi-column-GROUP-BY-20230720.patch (text/plain) patch 0001

On 3/10/2022 21:56, Tom Lane wrote:
 > Revert "Optimize order of GROUP BY keys".
 >
 > This reverts commit db0d67db2401eb6238ccc04c6407a4fd4f985832 and
 > several follow-on fixes.
 > ...
 > Since we're hard up against the release deadline for v15, let's
 > revert these changes for now.  We can always try again later.

It may be time to restart the project. As a first step, I rebased the 
patch on the current master. It wasn't trivial because of some latest 
optimizations (a29eab, 1349d27 and 8d83a5d).
Now, Let's repeat the review and rewrite the current path according to 
the reasons uttered in the revert commit.

-- 
regards,
Andrey Lepikhov
Postgres Professional