Horton

Re: POC: GROUP BY optimization

Teodor Sigaev <teodor@sigaev.ru>

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

> So the costing was fairly trivial, we simply do something like
> 
>      comparison_cost = 2.0 * cpu_operator_cost;
> 
>      sort_cost = comparison_cost * tuples * LOG2(tuples);
> 
> which essentially ignores that there might be multiple columns, or that
> the columns may have sort operator with different costs.
Agree. And distribution of keys.
> 
> The question is how reliable the heuristics can be. The current patch
> uses just plain ndistinct, but that seems rather unreliable but I don't
> have a clear idea how to improve that - we may have MCV for the columns
> and perhaps some extended statistics, but I'm not sure how far we can
> run with that.
v8 already uses another algorithm.

> 
> Essentially what we need to estimate the number of comparisons for each
> column, to compute better comparison_cost.
Exactly

>> Priorization of the user-provided order can be as simple as giving
>> that comparison_cost a small handicap.
> 
> I see no point in doing that, and I don't recall a single place in the
> planner where we do that. If the user specified ORDER BY, we'll slap an
> explicit Sort on top when needed (which acts as the handicap, but in a
> clear way). Otherwise we don't do such things - it'd be just plain
> confusing (consider "ORDER BY a,b" vs. "ORDER BY b,c" with same data
> types, ndistinct etc. but unexpectedly different costs). Also, what
> would be a good value for the handicap?

Again agree. If we have fixed order of columns (ORDER BY) then we should not try 
to reorder it. Current patch follows that if I didn't a mistake.

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