0007-demonstrate-disabled-optimization-20220328.patch
text/x-patch
Filename: 0007-demonstrate-disabled-optimization-20220328.patch
Type: text/x-patch
Part: 6
Message:
Re: POC: GROUP BY optimization
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0007
Subject: demonstrate disabled optimization
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/expected/postgres_fdw.out | 2 | 2 |
| contrib/postgres_fdw/sql/postgres_fdw.sql | 1 | 1 |
| src/backend/optimizer/path/pathkeys.c | 1 | 1 |
| src/backend/utils/misc/guc.c | 1 | 1 |
| src/test/regress/expected/aggregates.out | 84 | 84 |
| src/test/regress/sql/aggregates.sql | 13 | 13 |
From 4dd50166459d6fb1c131d215cf79237dfdfd4b05 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.vondra@postgresql.org>
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