Обсуждение: pgsql: Explore alternative orderings of group-by pathkeys during optimi
Explore alternative orderings of group-by pathkeys during optimization. When evaluating a query with a multi-column GROUP BY clause, we can minimize sort operations or avoid them if we synchronize the order of GROUP BY clauses with the ORDER BY sort clause or sort order, which comes from the underlying query tree. Grouping does not imply any ordering, so we can compare the keys in arbitrary order, and a Hash Agg leverages this. But for Group Agg, we simply compared keys in the order specified in the query. This commit explores alternative ordering of the keys, trying to find a cheaper one. The ordering of group keys may interact with other parts of the query, some of which may not be known while planning the grouping. For example, 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 eliminating the sort entirely. The patch always keeps the ordering specified in the query, assuming the user might have additional insights. This introduces a new GUC enable_group_by_reordering so that the optimization may be disabled if needed. Discussion: https://postgr.es/m/7c79e6a5-8597-74e8-0671-1c39d124c9d6%40sigaev.ru Author: Andrei Lepikhov, Teodor Sigaev Reviewed-by: Tomas Vondra, Claudio Freire, Gavin Flower, Dmitry Dolgov Reviewed-by: Robert Haas, Pavel Borisov, David Rowley, Zhihong Yu Reviewed-by: Tom Lane, Alexander Korotkov, Richard Guo, Alena Rybakina Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0452b461bc405e6d35d8a14c02813c15e28ae516 Modified Files -------------- src/backend/optimizer/path/equivclass.c | 13 +- src/backend/optimizer/path/pathkeys.c | 252 +++++++++++++++ src/backend/optimizer/plan/planner.c | 424 ++++++++++++-------------- src/backend/utils/misc/guc_tables.c | 10 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/nodes/pathnodes.h | 10 + src/include/optimizer/paths.h | 2 + src/test/regress/expected/aggregates.out | 202 ++++++++++++ src/test/regress/expected/sysviews.out | 3 +- src/test/regress/sql/aggregates.sql | 75 +++++ src/tools/pgindent/typedefs.list | 1 + 11 files changed, 770 insertions(+), 223 deletions(-)
Alexander Korotkov <akorotkov@postgresql.org> writes:
> Explore alternative orderings of group-by pathkeys during optimization.
Buildfarm's not terribly happy. Looks like you incautiously used
a table name "t1" that's already in use in a concurrent script.
regards, tom lane
Re: pgsql: Explore alternative orderings of group-by pathkeys during optimi
От
Alexander Korotkov
Дата:
On Sun, Jan 21, 2024 at 11:09 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alexander Korotkov <akorotkov@postgresql.org> writes: > > Explore alternative orderings of group-by pathkeys during optimization. > > Buildfarm's not terribly happy. Looks like you incautiously used > a table name "t1" that's already in use in a concurrent script. Thank you, I just saw it myself. Fix is pushed. ------ Regards, Alexander Korotkov