pgsql: Optimize order of GROUP BY keys - Mailing list pgsql-committers

Optimize order of GROUP BY keys

When evaluating a query with a multi-column GROUP BY clause using sort,
the cost may be heavily dependent on the order in which the keys are
compared when building the groups. Grouping does not imply any ordering,
so we're allowed to compare the keys in arbitrary order, and a Hash Agg
leverages this. But for Group Agg, we simply compared keys in the order
as specified in the query. This commit explores alternative ordering of
the keys, trying to find a cheaper one.

In principle, we might generate grouping paths for all permutations of
the keys, and leave the rest to the optimizer. But that might get very
expensive, so we try to pick only a couple interesting orderings based
on both local and global information.

When planning the grouping path, we explore statistics (number of
distinct values, cost of the comparison function) for the keys and
reorder them to minimize comparison costs. Intuitively, it may be better
to perform more expensive comparisons (for complex data types etc.)
last, because maybe the cheaper comparisons will be enough. Similarly,
the higher the cardinality of a key, the lower the probability we’ll
need to compare more keys. The patch generates and costs various
orderings, picking the cheapest ones.

The ordering of group keys may interact with other parts of the query,
some of which may not be known while planning the grouping. E.g. there
may be an explicit ORDER BY clause, or some other ordering-dependent
operation, higher up in the query, and using the same ordering may allow
using either incremental sort or even eliminate the sort entirely.

The patch generates orderings and picks those minimizing the comparison
cost (for various pathkeys), and then adds orderings that might be
useful for operations higher up in the plan (ORDER BY, etc.). Finally,
it always keeps the ordering specified in the query, on the assumption
the user might have additional insights.

This introduces a new GUC enable_group_by_reordering, so that the
optimization may be disabled if needed.

The original patch was proposed by Teodor Sigaev, and later improved and
reworked by Dmitry Dolgov. Reviews by a number of people, including me,
Andrey Lepikhov, Claudio Freire, Ibrar Ahmed and Zhihong Yu.

Author: Dmitry Dolgov, Teodor Sigaev, Tomas Vondra
Reviewed-by: Tomas Vondra, Andrey Lepikhov, Claudio Freire, Ibrar Ahmed, Zhihong Yu
Discussion: https://postgr.es/m/7c79e6a5-8597-74e8-0671-1c39d124c9d6%40sigaev.ru
Discussion: https://postgr.es/m/CA%2Bq6zcW_4o2NC0zutLkOJPsFt80megSpX_dVRo6GK9PC-Jx_Ag%40mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/db0d67db2401eb6238ccc04c6407a4fd4f985832

Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out    |  15 +-
doc/src/sgml/config.sgml                          |  14 +
src/backend/optimizer/path/costsize.c             | 366 +++++++++++-
src/backend/optimizer/path/equivclass.c           |  13 +-
src/backend/optimizer/path/pathkeys.c             | 569 +++++++++++++++++++
src/backend/optimizer/plan/planner.c              | 649 +++++++++++++---------
src/backend/optimizer/util/pathnode.c             |   2 +-
src/backend/utils/adt/selfuncs.c                  |  38 +-
src/backend/utils/misc/guc.c                      |  10 +
src/backend/utils/misc/postgresql.conf.sample     |   1 +
src/include/nodes/nodes.h                         |   1 +
src/include/nodes/pathnodes.h                     |  10 +
src/include/optimizer/cost.h                      |   4 +-
src/include/optimizer/paths.h                     |   7 +
src/include/utils/selfuncs.h                      |   5 +
src/test/regress/expected/aggregates.out          | 244 +++++++-
src/test/regress/expected/incremental_sort.out    |   2 +-
src/test/regress/expected/join.out                |  51 +-
src/test/regress/expected/partition_aggregate.out | 136 +++--
src/test/regress/expected/partition_join.out      |  75 ++-
src/test/regress/expected/sysviews.out            |   3 +-
src/test/regress/expected/union.out               |  60 +-
src/test/regress/sql/aggregates.sql               |  99 ++++
src/test/regress/sql/incremental_sort.sql         |   2 +-
24 files changed, 1882 insertions(+), 494 deletions(-)