From 4dd50166459d6fb1c131d215cf79237dfdfd4b05 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Mon, 28 Mar 2022 23:31:29 +0200 Subject: [PATCH 7/7] demonstrate disabled optimization tweak tests to show costing, disable optimization to show what changes --- .../postgres_fdw/expected/postgres_fdw.out | 4 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 2 +- src/backend/optimizer/path/pathkeys.c | 2 +- src/backend/utils/misc/guc.c | 2 +- src/test/regress/expected/aggregates.out | 168 +++++++++--------- src/test/regress/sql/aggregates.sql | 26 +-- 6 files changed, 102 insertions(+), 102 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 2ffc836824a..bd826eaedd5 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2739,11 +2739,11 @@ select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::i (9 rows) -- GROUP BY clause in various forms, cardinal, alias and constant expression -explain (verbose, costs off) +explain (verbose, costs on) select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------ - Foreign Scan + Foreign Scan (cost=110.50..132.85 rows=10 width=52) Output: (count(c2)), c2, 5, 7.0, 9 Relations: Aggregate on (public.ft1) Remote SQL: SELECT count(c2), c2, 5, 7.0, 9 FROM "S 1"."T 1" GROUP BY 2, 3, 5 ORDER BY c2 ASC NULLS LAST diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 95b6b7192e6..61b04d6f9a0 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -732,7 +732,7 @@ explain (verbose, costs off) select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::int order by 1; -- GROUP BY clause in various forms, cardinal, alias and constant expression -explain (verbose, costs off) +explain (verbose, costs on) select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2; diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c index 7913d62625d..9488798d445 100644 --- a/src/backend/optimizer/path/pathkeys.c +++ b/src/backend/optimizer/path/pathkeys.c @@ -32,7 +32,7 @@ #include "utils/selfuncs.h" /* Consider reordering of GROUP BY keys? */ -bool enable_group_by_reordering = true; +bool enable_group_by_reordering = false; static bool pathkey_is_redundant(PathKey *new_pathkey, List *pathkeys); static bool matches_boolean_partition_clause(RestrictInfo *rinfo, diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 9e8ab1420d9..ebc9904487f 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1189,7 +1189,7 @@ static struct config_bool ConfigureNamesBool[] = GUC_EXPLAIN }, &enable_group_by_reordering, - true, + false, NULL, NULL, NULL }, { diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 601047fa3dd..26ea59f015e 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1206,35 +1206,35 @@ select min(f1), max(f1) from minmaxtest; (1 row) -- DISTINCT doesn't do anything useful here, but it shouldn't fail -explain (costs off) +explain (costs on) select distinct min(f1), max(f1) from minmaxtest; - QUERY PLAN ---------------------------------------------------------------------------------------------- - HashAggregate + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------- + HashAggregate (cost=1.40..1.41 rows=1 width=8) Group Key: $0, $1 InitPlan 1 (returns $0) - -> Limit - -> Merge Append + -> Limit (cost=0.64..0.69 rows=1 width=4) + -> Merge Append (cost=0.64..394.50 rows=7865 width=4) Sort Key: minmaxtest.f1 - -> Index Only Scan using minmaxtesti on minmaxtest minmaxtest_1 + -> Index Only Scan using minmaxtesti on minmaxtest minmaxtest_1 (cost=0.14..16.59 rows=254 width=4) Index Cond: (f1 IS NOT NULL) - -> Index Only Scan using minmaxtest1i on minmaxtest1 minmaxtest_2 + -> Index Only Scan using minmaxtest1i on minmaxtest1 minmaxtest_2 (cost=0.15..92.55 rows=2537 width=4) Index Cond: (f1 IS NOT NULL) - -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3 + -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest_3 (cost=0.15..92.55 rows=2537 width=4) Index Cond: (f1 IS NOT NULL) - -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4 + -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest_4 (cost=0.14..74.79 rows=2537 width=4) InitPlan 2 (returns $1) - -> Limit - -> Merge Append + -> Limit (cost=0.64..0.69 rows=1 width=4) + -> Merge Append (cost=0.64..394.50 rows=7865 width=4) Sort Key: minmaxtest_5.f1 DESC - -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_6 + -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_6 (cost=0.14..16.59 rows=254 width=4) Index Cond: (f1 IS NOT NULL) - -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest_7 + -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest_7 (cost=0.15..92.55 rows=2537 width=4) Index Cond: (f1 IS NOT NULL) - -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8 + -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8 (cost=0.15..92.55 rows=2537 width=4) Index Cond: (f1 IS NOT NULL) - -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9 - -> Result + -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9 (cost=0.14..74.79 rows=2537 width=4) + -> Result (cost=1.38..1.39 rows=1 width=8) (25 rows) select distinct min(f1), max(f1) from minmaxtest; @@ -2454,7 +2454,7 @@ SELECT format('%60s', i%2) AS v, i/4 AS c, i/8 AS d, - (random() * (10000/8))::int as e --the same as d but no correlation with p + mod(i * 7703, 10000/8)::int as e --the same as d but no correlation with p INTO btg FROM generate_series(1, 10000) i; @@ -2464,127 +2464,127 @@ ANALYZE btg; SET enable_hashagg=off; SET max_parallel_workers= 0; SET max_parallel_workers_per_gather = 0; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, v; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1240.79..1390.80 rows=5001 width=73) Group Key: p, v - -> Sort + -> Sort (cost=1240.79..1265.79 rows=10000 width=65) Sort Key: p, v - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=65) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1240.79..1390.80 rows=5001 width=73) Group Key: p, v - -> Sort + -> Sort (cost=1240.79..1265.79 rows=10000 width=65) Sort Key: p, v - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=65) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, c; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1335.43..1510.44 rows=5001 width=77) Group Key: p, c, v - -> Sort + -> Sort (cost=1335.43..1360.43 rows=10000 width=69) Sort Key: p, c, v - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=69) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, c ORDER BY v, p, c; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1759.31..1934.32 rows=5001 width=77) Group Key: v, p, c - -> Sort + -> Sort (cost=1759.31..1784.31 rows=10000 width=69) Sort Key: v, p, c - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=69) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, d, c; - QUERY PLAN ------------------------------- - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1460.43..1660.44 rows=5001 width=81) Group Key: p, d, c, v - -> Sort + -> Sort (cost=1460.43..1485.43 rows=10000 width=73) Sort Key: p, d, c, v - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=73) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, d, c ORDER BY v, p, d ,c; - QUERY PLAN ------------------------------- - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1893.47..2093.48 rows=5001 width=81) Group Key: v, p, d, c - -> Sort + -> Sort (cost=1893.47..1918.47 rows=10000 width=73) Sort Key: v, p, d, c - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=73) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, d, c ORDER BY p, v, d ,c; - QUERY PLAN ------------------------------- - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1484.11..1684.12 rows=5001 width=81) Group Key: p, v, d, c - -> Sort + -> Sort (cost=1484.11..1509.11 rows=10000 width=73) Sort Key: p, v, d, c - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=73) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, d, e; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1326.27..1501.28 rows=5001 width=20) Group Key: p, d, e - -> Sort + -> Sort (cost=1326.27..1351.27 rows=10000 width=12) Sort Key: p, d, e - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=12) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, e, d; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1326.27..1501.28 rows=5001 width=20) Group Key: p, e, d - -> Sort + -> Sort (cost=1326.27..1351.27 rows=10000 width=12) Sort Key: p, e, d - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=12) (5 rows) CREATE STATISTICS btg_dep ON d, e, p FROM btg; ANALYZE btg; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, d, e; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1326.27..1551.27 rows=10000 width=20) Group Key: p, d, e - -> Sort + -> Sort (cost=1326.27..1351.27 rows=10000 width=12) Sort Key: p, d, e - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=12) (5 rows) -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, e, d; - QUERY PLAN ------------------------------ - GroupAggregate + QUERY PLAN +---------------------------------------------------------------------- + GroupAggregate (cost=1326.27..1551.27 rows=10000 width=20) Group Key: p, e, d - -> Sort + -> Sort (cost=1326.27..1351.27 rows=10000 width=12) Sort Key: p, e, d - -> Seq Scan on btg + -> Seq Scan on btg (cost=0.00..243.00 rows=10000 width=12) (5 rows) -- GROUP BY optimization by reorder columns by index scan diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index c6e0d7ba2b7..671a9daa696 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -425,7 +425,7 @@ explain (costs off) select min(f1), max(f1) from minmaxtest; -- DISTINCT doesn't do anything useful here, but it shouldn't fail -explain (costs off) +explain (costs on) select distinct min(f1), max(f1) from minmaxtest; select distinct min(f1), max(f1) from minmaxtest; @@ -1033,7 +1033,7 @@ SELECT format('%60s', i%2) AS v, i/4 AS c, i/8 AS d, - (random() * (10000/8))::int as e --the same as d but no correlation with p + mod(i * 7703, 10000/8)::int as e --the same as d but no correlation with p INTO btg FROM generate_series(1, 10000) i; @@ -1047,40 +1047,40 @@ SET enable_hashagg=off; SET max_parallel_workers= 0; SET max_parallel_workers_per_gather = 0; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, v; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, c; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, c ORDER BY v, p, c; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, d, c; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, d, c ORDER BY v, p, d ,c; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY v, p, d, c ORDER BY p, v, d ,c; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, d, e; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, e, d; CREATE STATISTICS btg_dep ON d, e, p FROM btg; ANALYZE btg; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, d, e; -EXPLAIN (COSTS off) +EXPLAIN (COSTS on) SELECT count(*) FROM btg GROUP BY p, e, d; -- 2.34.1