Horton

Re: POC: GROUP BY optimization

Teodor Sigaev <teodor@sigaev.ru>

View thread
From: Teodor Sigaev <teodor@sigaev.ru>
To: Claudio Freire <klaussfreire@gmail.com>, Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: PostgreSQL-Dev <pgsql-hackers@postgresql.org>
Date: 2018-06-07T13:48:28Z
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 →

  1. Restore preprocess_groupclause()

  2. Rename PathKeyInfo to GroupByOrdering

  3. Add invariants check to get_useful_group_keys_orderings()

  4. Fix asymmetry in setting EquivalenceClass.ec_sortref

  5. Multiple revisions to the GROUP BY reordering tests

  6. Get rid of pg_class usage in SJE regression tests

  7. Rename index "abc" in aggregates.sql

  8. Explore alternative orderings of group-by pathkeys during optimization.

  9. Generalize the common code of adding sort before processing of grouping

  10. Fix out-dated comment in preprocess_groupclause()

  11. Force parallelism in partition_aggregate

  12. Optimize order of GROUP BY keys

>> I don't see why not to generate all possible orderings (keeping only the
>> cheapest path for each pathkey variant) and let the optimizer to sort it
>> out.
> 
> I'm assuming costing the full N! possible orderings would be
> prohibitively expensive.

That's true, but for the first step we need to improve cost_sort and only then 
try to find the best pathkeys order by optimal way.

> - If the user requested that order, we assume it "somewhat
> subjectively better" (by giving it a slightly reduced cost).
I don't think so. It's not a SQL way - DB should define the optimal way to 
execute query.


-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/