pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Revert "Optimize order of GROUP BY keys". 2+ messages / 1 participants [nested] [flat]
* pgsql: Revert "Optimize order of GROUP BY keys". @ 2022-10-03 14:56 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2022-10-03 14:56 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Revert "Optimize order of GROUP BY keys". This reverts commit db0d67db2401eb6238ccc04c6407a4fd4f985832 and several follow-on fixes. The idea of making a cost-based choice of the order of the sorting columns is not fundamentally unsound, but it requires cost information and data statistics that we don't really have. For example, relying on procost to distinguish the relative costs of different sort comparators is pretty pointless so long as most such comparator functions are labeled with cost 1.0. Moreover, estimating the number of comparisons done by Quicksort requires more than just an estimate of the number of distinct values in the input: you also need some idea of the sizes of the larger groups, if you want an estimate that's good to better than a factor of three or so. That's data that's often unknown or not very reliable. Worse, to arrive at estimates of the number of calls made to the lower-order-column comparison functions, the code needs to make estimates of the numbers of distinct values of multiple columns, which are necessarily even less trustworthy than per-column stats. Even if all the inputs are perfectly reliable, the cost algorithm as-implemented cannot offer useful information about how to order sorting columns beyond the point at which the average group size is estimated to drop to 1. Close inspection of the code added by db0d67db2 shows that there are also multiple small bugs. These could have been fixed, but there's not much point if we don't trust the estimates to be accurate in-principle. Finally, the changes in cost_sort's behavior made for very large changes (often a factor of 2 or so) in the cost estimates for all sorting operations, not only those for multi-column GROUP BY. That naturally changes plan choices in many situations, and there's precious little evidence to show that the changes are for the better. Given the above doubts about whether the new estimates are really trustworthy, it's hard to summon much confidence that these changes are better on the average. Since we're hard up against the release deadline for v15, let's revert these changes for now. We can always try again later. Note: in v15, I left T_PathKeyInfo in place in nodes.h even though it's unreferenced. Removing it would be an ABI break, and it seems a bit late in the release cycle for that. Discussion: https://postgr.es/m/TYAPR01MB586665EB5FB2C3807E893941F5579@TYAPR01MB5866.jpnprd01.prod.outlook.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/f4c7c410ee4a7baa06f51ebb8d5333c169691dd3 Modified Files -------------- contrib/postgres_fdw/expected/postgres_fdw.out | 15 +- doc/src/sgml/config.sgml | 14 - src/backend/optimizer/path/costsize.c | 371 +---------- src/backend/optimizer/path/equivclass.c | 13 +- src/backend/optimizer/path/pathkeys.c | 594 ------------------ src/backend/optimizer/plan/planner.c | 710 ++++++++-------------- src/backend/optimizer/util/pathnode.c | 4 +- src/backend/utils/adt/selfuncs.c | 38 +- src/backend/utils/misc/guc_tables.c | 10 - src/backend/utils/misc/postgresql.conf.sample | 1 - src/include/nodes/pathnodes.h | 12 - src/include/optimizer/cost.h | 4 +- src/include/optimizer/paths.h | 8 - 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/merge.out | 15 +- src/test/regress/expected/partition_aggregate.out | 44 +- 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, 455 insertions(+), 1939 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Revert "Optimize order of GROUP BY keys". @ 2022-10-03 14:56 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2022-10-03 14:56 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Revert "Optimize order of GROUP BY keys". This reverts commit db0d67db2401eb6238ccc04c6407a4fd4f985832 and several follow-on fixes. The idea of making a cost-based choice of the order of the sorting columns is not fundamentally unsound, but it requires cost information and data statistics that we don't really have. For example, relying on procost to distinguish the relative costs of different sort comparators is pretty pointless so long as most such comparator functions are labeled with cost 1.0. Moreover, estimating the number of comparisons done by Quicksort requires more than just an estimate of the number of distinct values in the input: you also need some idea of the sizes of the larger groups, if you want an estimate that's good to better than a factor of three or so. That's data that's often unknown or not very reliable. Worse, to arrive at estimates of the number of calls made to the lower-order-column comparison functions, the code needs to make estimates of the numbers of distinct values of multiple columns, which are necessarily even less trustworthy than per-column stats. Even if all the inputs are perfectly reliable, the cost algorithm as-implemented cannot offer useful information about how to order sorting columns beyond the point at which the average group size is estimated to drop to 1. Close inspection of the code added by db0d67db2 shows that there are also multiple small bugs. These could have been fixed, but there's not much point if we don't trust the estimates to be accurate in-principle. Finally, the changes in cost_sort's behavior made for very large changes (often a factor of 2 or so) in the cost estimates for all sorting operations, not only those for multi-column GROUP BY. That naturally changes plan choices in many situations, and there's precious little evidence to show that the changes are for the better. Given the above doubts about whether the new estimates are really trustworthy, it's hard to summon much confidence that these changes are better on the average. Since we're hard up against the release deadline for v15, let's revert these changes for now. We can always try again later. Note: in v15, I left T_PathKeyInfo in place in nodes.h even though it's unreferenced. Removing it would be an ABI break, and it seems a bit late in the release cycle for that. Discussion: https://postgr.es/m/TYAPR01MB586665EB5FB2C3807E893941F5579@TYAPR01MB5866.jpnprd01.prod.outlook.com Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/443df6e2db932a7cd6d85ddfb67e11a43345130d Modified Files -------------- contrib/postgres_fdw/expected/postgres_fdw.out | 15 +- doc/src/sgml/config.sgml | 14 - src/backend/optimizer/path/costsize.c | 371 +----------- src/backend/optimizer/path/equivclass.c | 13 +- src/backend/optimizer/path/pathkeys.c | 581 ------------------- src/backend/optimizer/plan/planner.c | 654 +++++++++------------- 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/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/merge.out | 15 +- src/test/regress/expected/partition_aggregate.out | 44 +- 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, 453 insertions(+), 1867 deletions(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-10-03 14:56 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-10-03 14:56 pgsql: Revert "Optimize order of GROUP BY keys". Tom Lane <tgl@sss.pgh.pa.us> 2022-10-03 14:56 pgsql: Revert "Optimize order of GROUP BY keys". Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox