v2-0004-fix-tests-for-IndexAggregate.patch
text/x-patch
Filename: v2-0004-fix-tests-for-IndexAggregate.patch
Type: text/x-patch
Part: 3
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 v2-0004
Subject: fix tests for IndexAggregate
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/expected/postgres_fdw.out | 25 | 31 |
| src/test/regress/expected/aggregates.out | 289 | 10 |
| src/test/regress/expected/eager_aggregate.out | 99 | 0 |
| src/test/regress/expected/groupingsets.out | 16 | 22 |
| src/test/regress/expected/join.out | 4 | 4 |
| src/test/regress/expected/matview.out | 6 | 8 |
| src/test/regress/expected/partition_aggregate.out | 177 | 50 |
| src/test/regress/expected/partition_join.out | 81 | 98 |
| src/test/regress/expected/select_parallel.out | 15 | 20 |
| src/test/regress/expected/sysviews.out | 2 | 1 |
| src/test/regress/sql/aggregates.sql | 143 | 4 |
| src/test/regress/sql/eager_aggregate.sql | 41 | 0 |
| src/test/regress/sql/partition_aggregate.sql | 31 | 1 |
| src/test/regress/sql/select_parallel.sql | 3 | 0 |
From f30da61435d89cdd6e1b3e2ba1a9fa047f6785c2 Mon Sep 17 00:00:00 2001
From: Sergey Soloviev <sergey.soloviev@tantorlabs.ru>
Date: Wed, 10 Dec 2025 17:09:42 +0300
Subject: [PATCH v2 4/4] fix tests for IndexAggregate
After adding IndexAggregate node some test output changed and tests
broke. This patch updates expected output.
Also it adds some IndexAggregate specific tests into aggregates.sql and
partition_aggregate.sql.
---
.../postgres_fdw/expected/postgres_fdw.out | 56 ++--
src/test/regress/expected/aggregates.out | 299 +++++++++++++++++-
src/test/regress/expected/eager_aggregate.out | 99 ++++++
src/test/regress/expected/groupingsets.out | 38 +--
src/test/regress/expected/join.out | 8 +-
src/test/regress/expected/matview.out | 14 +-
.../regress/expected/partition_aggregate.out | 227 ++++++++++---
src/test/regress/expected/partition_join.out | 179 +++++------
src/test/regress/expected/select_parallel.out | 35 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/aggregates.sql | 147 ++++++++-
src/test/regress/sql/eager_aggregate.sql | 41 +++
src/test/regress/sql/partition_aggregate.sql | 32 +-
src/test/regress/sql/select_parallel.sql | 3 +
14 files changed, 932 insertions(+), 249 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 48e3185b227..d1cb3e8802c 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2324,29 +2324,26 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.
-- Aggregate after UNION, for testing setrefs
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, (avg((t1.c1 + t2.c1)))
- -> Sort
- Output: t1.c1, (avg((t1.c1 + t2.c1)))
- Sort Key: t1.c1
+ -> IndexAggregate
+ Output: t1.c1, avg((t1.c1 + t2.c1))
+ Group Key: t1.c1
-> HashAggregate
- Output: t1.c1, avg((t1.c1 + t2.c1))
- Group Key: t1.c1
- -> HashAggregate
- Output: t1.c1, t2.c1
- Group Key: t1.c1, t2.c1
- -> Append
- -> Foreign Scan
- Output: t1.c1, t2.c1
- Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1"))))
- -> Foreign Scan
- Output: t1_1.c1, t2_1.c1
- Relations: (public.ft1 t1_1) INNER JOIN (public.ft2 t2_1)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1"))))
-(20 rows)
+ Output: t1.c1, t2.c1
+ Group Key: t1.c1, t2.c1
+ -> Append
+ -> Foreign Scan
+ Output: t1.c1, t2.c1
+ Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1"))))
+ -> Foreign Scan
+ Output: t1_1.c1, t2_1.c1
+ Relations: (public.ft1 t1_1) INNER JOIN (public.ft2 t2_1)
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1"))))
+(17 rows)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
t1c1 | avg
@@ -3057,18 +3054,15 @@ select c2 * (random() <= 1)::int as sum1, sum(c1) * c2 as sum2 from ft1 group by
-- Aggregate with unshippable GROUP BY clause are not pushed
explain (verbose, costs off)
select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::int order by 1;
- QUERY PLAN
-------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+------------------------------------------------------------------------
+ IndexAggregate
Output: ((c2 * ((random() <= '1'::double precision))::integer))
- Sort Key: ((ft2.c2 * ((random() <= '1'::double precision))::integer))
- -> HashAggregate
- Output: ((c2 * ((random() <= '1'::double precision))::integer))
- Group Key: (ft2.c2 * ((random() <= '1'::double precision))::integer)
- -> Foreign Scan on public.ft2
- Output: (c2 * ((random() <= '1'::double precision))::integer)
- Remote SQL: SELECT c2 FROM "S 1"."T 1"
-(9 rows)
+ Group Key: (ft2.c2 * ((random() <= '1'::double precision))::integer)
+ -> Foreign Scan on public.ft2
+ Output: (c2 * ((random() <= '1'::double precision))::integer)
+ Remote SQL: SELECT c2 FROM "S 1"."T 1"
+(6 rows)
-- GROUP BY clause in various forms, cardinal, alias and constant expression
explain (verbose, costs off)
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index cae8e7bca31..c33eaa0c0ec 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1714,12 +1714,10 @@ EXPLAIN (COSTS OFF) SELECT a, COUNT(a) OVER (PARTITION BY a) FROM t1 GROUP BY AL
----------------------------------
WindowAgg
Window: w1 AS (PARTITION BY a)
- -> Sort
- Sort Key: a
- -> HashAggregate
- Group Key: a
- -> Seq Scan on t1
-(7 rows)
+ -> IndexAggregate
+ Group Key: a
+ -> Seq Scan on t1
+(5 rows)
-- all cols
EXPLAIN (COSTS OFF) SELECT *, count(*) FROM t1 GROUP BY ALL;
@@ -3270,6 +3268,7 @@ FROM generate_series(1, 100) AS i;
CREATE INDEX btg_x_y_idx ON btg(x, y);
ANALYZE btg;
SET enable_hashagg = off;
+SET enable_indexagg = off;
SET enable_seqscan = off;
-- Utilize the ordering of index scan to avoid a Sort operation
EXPLAIN (COSTS OFF)
@@ -3707,10 +3706,242 @@ select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
ba | 0 | 1
(2 rows)
+
+--
+-- Index Aggregation tests
+--
+set enable_hashagg = false;
+set enable_sort = false;
+set enable_indexagg = true;
+set enable_indexscan = false;
+-- require ordered output
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT unique1, SUM(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: unique1, (sum(two))
+ -> IndexAggregate
+ Output: unique1, sum(two)
+ Group Key: tenk1.unique1
+ -> Seq Scan on public.tenk1
+ Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
+(7 rows)
+
+SELECT unique1, SUM(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+ unique1 | sum
+---------+-----
+ 0 | 0
+ 1 | 1
+ 2 | 0
+ 3 | 1
+ 4 | 0
+ 5 | 1
+ 6 | 0
+ 7 | 1
+ 8 | 0
+ 9 | 1
+(10 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT even, sum(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: even, (sum(two))
+ -> IndexAggregate
+ Output: even, sum(two)
+ Group Key: tenk1.even
+ -> Seq Scan on public.tenk1
+ Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
+(7 rows)
+
+SELECT even, sum(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+ even | sum
+------+-----
+ 1 | 0
+ 3 | 100
+ 5 | 0
+ 7 | 100
+ 9 | 0
+ 11 | 100
+ 13 | 0
+ 15 | 100
+ 17 | 0
+ 19 | 100
+(10 rows)
+
+-- multiple grouping columns
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT even, odd, sum(unique1) FROM tenk1
+GROUP BY 1, 2
+ORDER BY 1, 2
+LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: even, odd, (sum(unique1))
+ -> IndexAggregate
+ Output: even, odd, sum(unique1)
+ Group Key: tenk1.even, tenk1.odd
+ -> Seq Scan on public.tenk1
+ Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
+(7 rows)
+
+SELECT even, odd, sum(unique1) FROM tenk1
+GROUP BY 1, 2
+ORDER BY 1, 2
+LIMIT 10;
+ even | odd | sum
+------+-----+--------
+ 1 | 0 | 495000
+ 3 | 2 | 495100
+ 5 | 4 | 495200
+ 7 | 6 | 495300
+ 9 | 8 | 495400
+ 11 | 10 | 495500
+ 13 | 12 | 495600
+ 15 | 14 | 495700
+ 17 | 16 | 495800
+ 19 | 18 | 495900
+(10 rows)
+
+-- mixing columns between group by and order by
+begin;
+create temp table tmp(x int, y int);
+insert into tmp values (1, 8), (2, 7), (3, 6), (4, 5);
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 1, 2;
+ QUERY PLAN
+-------------------------------
+ IndexAggregate
+ Output: x, y, sum(x)
+ Group Key: tmp.x, tmp.y
+ -> Seq Scan on pg_temp.tmp
+ Output: x, y
+(5 rows)
+
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 1, 2;
+ x | y | sum
+---+---+-----
+ 1 | 8 | 1
+ 2 | 7 | 2
+ 3 | 6 | 3
+ 4 | 5 | 4
+(4 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 2, 1;
+ QUERY PLAN
+-------------------------------
+ IndexAggregate
+ Output: x, y, sum(x)
+ Group Key: tmp.y, tmp.x
+ -> Seq Scan on pg_temp.tmp
+ Output: x, y
+(5 rows)
+
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 2, 1;
+ x | y | sum
+---+---+-----
+ 4 | 5 | 4
+ 3 | 6 | 3
+ 2 | 7 | 2
+ 1 | 8 | 1
+(4 rows)
+
+--
+-- Index Aggregation Spill tests
+--
+set enable_indexagg = true;
+set enable_sort=false;
+set enable_hashagg = false;
+set work_mem='64kB';
+select unique1, count(*), sum(twothousand) from tenk1
+group by unique1
+having sum(fivethous) > 4975
+order by sum(twothousand);
+ unique1 | count | sum
+---------+-------+------
+ 4976 | 1 | 976
+ 4977 | 1 | 977
+ 4978 | 1 | 978
+ 4979 | 1 | 979
+ 4980 | 1 | 980
+ 4981 | 1 | 981
+ 4982 | 1 | 982
+ 4983 | 1 | 983
+ 4984 | 1 | 984
+ 4985 | 1 | 985
+ 4986 | 1 | 986
+ 4987 | 1 | 987
+ 4988 | 1 | 988
+ 4989 | 1 | 989
+ 4990 | 1 | 990
+ 4991 | 1 | 991
+ 4992 | 1 | 992
+ 4993 | 1 | 993
+ 4994 | 1 | 994
+ 4995 | 1 | 995
+ 4996 | 1 | 996
+ 4997 | 1 | 997
+ 4998 | 1 | 998
+ 4999 | 1 | 999
+ 9976 | 1 | 1976
+ 9977 | 1 | 1977
+ 9978 | 1 | 1978
+ 9979 | 1 | 1979
+ 9980 | 1 | 1980
+ 9981 | 1 | 1981
+ 9982 | 1 | 1982
+ 9983 | 1 | 1983
+ 9984 | 1 | 1984
+ 9985 | 1 | 1985
+ 9986 | 1 | 1986
+ 9987 | 1 | 1987
+ 9988 | 1 | 1988
+ 9989 | 1 | 1989
+ 9990 | 1 | 1990
+ 9991 | 1 | 1991
+ 9992 | 1 | 1992
+ 9993 | 1 | 1993
+ 9994 | 1 | 1994
+ 9995 | 1 | 1995
+ 9996 | 1 | 1996
+ 9997 | 1 | 1997
+ 9998 | 1 | 1998
+ 9999 | 1 | 1999
+(48 rows)
+
+set work_mem to default;
+set enable_sort to default;
+set enable_hashagg to default;
+set enable_indexagg to default;
--
-- Hash Aggregation Spill tests
--
set enable_sort=false;
+set enable_indexagg = false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
group by unique1
@@ -3783,6 +4014,7 @@ select g from generate_series(0, 19999) g;
analyze agg_data_20k;
-- Produce results with sorting.
set enable_hashagg = false;
+set enable_indexagg = false;
set jit_above_cost = 0;
explain (costs off)
select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
@@ -3852,31 +4084,74 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
from agg_data_2k group by g/2;
set enable_sort = true;
set work_mem to default;
+-- Produce results with index aggregation
+set enable_sort = false;
+set enable_hashagg = false;
+set enable_indexagg = true;
+set jit_above_cost = 0;
+explain (costs off)
+select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
+ from agg_data_20k group by g%10000;
+ QUERY PLAN
+--------------------------------
+ IndexAggregate
+ Group Key: (g % 10000)
+ -> Seq Scan on agg_data_20k
+(3 rows)
+
+create table agg_index_1 as
+select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
+ from agg_data_20k group by g%10000;
+create table agg_index_2 as
+select * from
+ (values (100), (300), (500)) as r(a),
+ lateral (
+ select (g/2)::numeric as c1,
+ array_agg(g::numeric) as c2,
+ count(*) as c3
+ from agg_data_2k
+ where g < r.a
+ group by g/2) as s;
+set jit_above_cost to default;
+create table agg_index_3 as
+select (g/2)::numeric as c1, sum(7::int4) as c2, count(*) as c3
+ from agg_data_2k group by g/2;
+create table agg_index_4 as
+select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
+ from agg_data_2k group by g/2;
-- Compare group aggregation results to hash aggregation results
(select * from agg_hash_1 except select * from agg_group_1)
union all
-(select * from agg_group_1 except select * from agg_hash_1);
+(select * from agg_group_1 except select * from agg_hash_1)
+ union all
+(select * from agg_index_1 except select * from agg_group_1);
c1 | c2 | c3
----+----+----
(0 rows)
(select * from agg_hash_2 except select * from agg_group_2)
union all
-(select * from agg_group_2 except select * from agg_hash_2);
+(select * from agg_group_2 except select * from agg_hash_2)
+ union all
+(select * from agg_index_2 except select * from agg_group_2);
a | c1 | c2 | c3
---+----+----+----
(0 rows)
(select * from agg_hash_3 except select * from agg_group_3)
union all
-(select * from agg_group_3 except select * from agg_hash_3);
+(select * from agg_group_3 except select * from agg_hash_3)
+ union all
+(select * from agg_index_3 except select * from agg_group_3);
c1 | c2 | c3
----+----+----
(0 rows)
(select * from agg_hash_4 except select * from agg_group_4)
union all
-(select * from agg_group_4 except select * from agg_hash_4);
+(select * from agg_group_4 except select * from agg_hash_4)
+ union all
+(select * from agg_index_4 except select * from agg_group_4);
c1 | c2 | c3
----+----+----
(0 rows)
@@ -3889,3 +4164,7 @@ drop table agg_hash_1;
drop table agg_hash_2;
drop table agg_hash_3;
drop table agg_hash_4;
+drop table agg_index_1;
+drop table agg_index_2;
+drop table agg_index_3;
+drop table agg_index_4;
diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out
index 5ac966186f7..247915da8f6 100644
--- a/src/test/regress/expected/eager_aggregate.out
+++ b/src/test/regress/expected/eager_aggregate.out
@@ -62,6 +62,7 @@ GROUP BY t1.a ORDER BY t1.a;
-- Produce results with sorting aggregation
SET enable_hashagg TO off;
+SET enable_indexagg TO off;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.a, avg(t2.c)
FROM eager_agg_t1 t1
@@ -110,6 +111,50 @@ GROUP BY t1.a ORDER BY t1.a;
(9 rows)
RESET enable_hashagg;
+RESET enable_indexagg;
+-- Produce results with index aggregation
+SET enable_hashagg TO off;
+SET enable_sort TO off;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, avg(t2.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+GROUP BY t1.a ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------
+ IndexAggregate
+ Output: t1.a, avg(t2.c)
+ Group Key: t1.a
+ -> Hash Join
+ Output: t1.a, t2.c
+ Hash Cond: (t2.b = t1.b)
+ -> Seq Scan on public.eager_agg_t2 t2
+ Output: t2.a, t2.b, t2.c
+ -> Hash
+ Output: t1.a, t1.b
+ -> Seq Scan on public.eager_agg_t1 t1
+ Output: t1.a, t1.b
+(12 rows)
+
+SELECT t1.a, avg(t2.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+GROUP BY t1.a ORDER BY t1.a;
+ a | avg
+---+-----
+ 1 | 496
+ 2 | 497
+ 3 | 498
+ 4 | 499
+ 5 | 500
+ 6 | 501
+ 7 | 502
+ 8 | 503
+ 9 | 504
+(9 rows)
+
+RESET enable_hashagg;
+RESET enable_sort;
--
-- Test eager aggregation over join rel
--
@@ -170,6 +215,7 @@ GROUP BY t1.a ORDER BY t1.a;
-- Produce results with sorting aggregation
SET enable_hashagg TO off;
+SET enable_indexagg TO off;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.a, avg(t2.c + t3.c)
FROM eager_agg_t1 t1
@@ -227,6 +273,59 @@ GROUP BY t1.a ORDER BY t1.a;
(9 rows)
RESET enable_hashagg;
+RESET enable_indexagg;
+-- Produce results with index aggregation
+SET enable_hashagg TO off;
+SET enable_sort TO off;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, avg(t2.c + t3.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+ JOIN eager_agg_t3 t3 ON t2.a = t3.a
+GROUP BY t1.a ORDER BY t1.a;
+ QUERY PLAN
+------------------------------------------------------------
+ IndexAggregate
+ Output: t1.a, avg((t2.c + t3.c))
+ Group Key: t1.a
+ -> Hash Join
+ Output: t1.a, t2.c, t3.c
+ Hash Cond: (t2.b = t1.b)
+ -> Hash Join
+ Output: t2.c, t2.b, t3.c
+ Hash Cond: (t3.a = t2.a)
+ -> Seq Scan on public.eager_agg_t3 t3
+ Output: t3.a, t3.b, t3.c
+ -> Hash
+ Output: t2.c, t2.b, t2.a
+ -> Seq Scan on public.eager_agg_t2 t2
+ Output: t2.c, t2.b, t2.a
+ -> Hash
+ Output: t1.a, t1.b
+ -> Seq Scan on public.eager_agg_t1 t1
+ Output: t1.a, t1.b
+(19 rows)
+
+SELECT t1.a, avg(t2.c + t3.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+ JOIN eager_agg_t3 t3 ON t2.a = t3.a
+GROUP BY t1.a ORDER BY t1.a;
+ a | avg
+---+-----
+ 1 | 497
+ 2 | 499
+ 3 | 501
+ 4 | 503
+ 5 | 505
+ 6 | 507
+ 7 | 509
+ 8 | 511
+ 9 | 513
+(9 rows)
+
+RESET enable_hashagg;
+RESET enable_sort;
--
-- Test that eager aggregation works for outer join
--
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 39d35a195bc..46b80db6806 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -506,18 +506,15 @@ cross join lateral (select (select i1.q1) as x) ss
group by ss.x;
QUERY PLAN
----------------------------------------------------------
- GroupAggregate
+ IndexAggregate
Output: GROUPING((SubPlan expr_1)), ((SubPlan expr_2))
- Group Key: ((SubPlan expr_2))
- -> Sort
- Output: ((SubPlan expr_2)), i1.q1
- Sort Key: ((SubPlan expr_2))
- -> Seq Scan on public.int8_tbl i1
- Output: (SubPlan expr_2), i1.q1
- SubPlan expr_2
- -> Result
- Output: i1.q1
-(11 rows)
+ Group Key: (SubPlan expr_2)
+ -> Seq Scan on public.int8_tbl i1
+ Output: (SubPlan expr_2), i1.q1
+ SubPlan expr_2
+ -> Result
+ Output: i1.q1
+(8 rows)
select grouping(ss.x)
from int8_tbl i1
@@ -536,21 +533,18 @@ cross join lateral (select (select i1.q1) as x) ss
group by ss.x;
QUERY PLAN
------------------------------------------------
- GroupAggregate
+ IndexAggregate
Output: (SubPlan expr_1), ((SubPlan expr_3))
- Group Key: ((SubPlan expr_3))
- -> Sort
- Output: ((SubPlan expr_3)), i1.q1
- Sort Key: ((SubPlan expr_3))
- -> Seq Scan on public.int8_tbl i1
- Output: (SubPlan expr_3), i1.q1
- SubPlan expr_3
- -> Result
- Output: i1.q1
+ Group Key: (SubPlan expr_3)
+ -> Seq Scan on public.int8_tbl i1
+ Output: (SubPlan expr_3), i1.q1
+ SubPlan expr_3
+ -> Result
+ Output: i1.q1
SubPlan expr_1
-> Result
Output: GROUPING((SubPlan expr_2))
-(14 rows)
+(11 rows)
select (select grouping(ss.x))
from int8_tbl i1
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index edde9e99893..a6e11fb64e7 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6211,13 +6211,13 @@ select d.* from d left join (select distinct * from b) s
explain (costs off)
select d.* from d left join (select * from b group by b.id, b.c_id) s
on d.a = s.id;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+----------------------------
Merge Right Join
Merge Cond: (b.id = d.a)
- -> Group
+ -> IndexAggregate
Group Key: b.id
- -> Index Scan using b_pkey on b
+ -> Seq Scan on b
-> Sort
Sort Key: d.a
-> Seq Scan on d
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 0355720dfc6..8c939c8f397 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -55,14 +55,12 @@ SELECT * FROM mvtest_tm ORDER BY type;
-- create various views
EXPLAIN (costs off)
CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type;
- QUERY PLAN
-----------------------------------
- Sort
- Sort Key: mvtest_t.type
- -> HashAggregate
- Group Key: mvtest_t.type
- -> Seq Scan on mvtest_t
-(5 rows)
+ QUERY PLAN
+----------------------------
+ IndexAggregate
+ Group Key: mvtest_t.type
+ -> Seq Scan on mvtest_t
+(3 rows)
CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type;
SELECT * FROM mvtest_tvm;
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index c30304b99c7..9e29fe17f45 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -177,8 +177,9 @@ SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
---+-----
(0 rows)
--- Test GroupAggregate paths by disabling hash aggregates.
+-- Test GroupAggregate paths by disabling hash and index aggregates.
SET enable_hashagg TO false;
+SET enable_indexagg TO false;
-- When GROUP BY clause matches full aggregation is performed for each partition.
EXPLAIN (COSTS OFF)
SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
@@ -370,6 +371,136 @@ SELECT count(*) FROM pagg_tab GROUP BY c ORDER BY c LIMIT 1;
250
(1 row)
+RESET enable_hashagg;
+RESET enable_indexagg;
+-- Test IndexAggregate paths by disabling hash and group aggregates.
+SET enable_sort TO false;
+SET enable_hashagg TO false;
+-- When GROUP BY clause matches full aggregation is performed for each partition.
+EXPLAIN (COSTS OFF)
+SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Disabled: true
+ Sort Key: pagg_tab.c, (sum(pagg_tab.a)), (avg(pagg_tab.b))
+ -> Append
+ -> IndexAggregate
+ Group Key: pagg_tab.c
+ Filter: (avg(pagg_tab.d) < '15'::numeric)
+ -> Seq Scan on pagg_tab_p1 pagg_tab
+ -> IndexAggregate
+ Group Key: pagg_tab_1.c
+ Filter: (avg(pagg_tab_1.d) < '15'::numeric)
+ -> Seq Scan on pagg_tab_p2 pagg_tab_1
+ -> IndexAggregate
+ Group Key: pagg_tab_2.c
+ Filter: (avg(pagg_tab_2.d) < '15'::numeric)
+ -> Seq Scan on pagg_tab_p3 pagg_tab_2
+(16 rows)
+
+SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+ c | sum | avg | count
+------+------+---------------------+-------
+ 0000 | 2000 | 12.0000000000000000 | 250
+ 0001 | 2250 | 13.0000000000000000 | 250
+ 0002 | 2500 | 14.0000000000000000 | 250
+ 0006 | 2500 | 12.0000000000000000 | 250
+ 0007 | 2750 | 13.0000000000000000 | 250
+ 0008 | 2000 | 14.0000000000000000 | 250
+(6 rows)
+
+-- When GROUP BY clause does not match; full aggregation by top node.
+EXPLAIN (COSTS OFF)
+SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Disabled: true
+ Sort Key: pagg_tab.a, (sum(pagg_tab.b)), (avg(pagg_tab.b))
+ -> IndexAggregate
+ Group Key: pagg_tab.a
+ Filter: (avg(pagg_tab.d) < '15'::numeric)
+ -> Append
+ -> Seq Scan on pagg_tab_p1 pagg_tab_1
+ -> Seq Scan on pagg_tab_p2 pagg_tab_2
+ -> Seq Scan on pagg_tab_p3 pagg_tab_3
+(10 rows)
+
+SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+ a | sum | avg | count
+----+------+---------------------+-------
+ 0 | 1500 | 10.0000000000000000 | 150
+ 1 | 1650 | 11.0000000000000000 | 150
+ 2 | 1800 | 12.0000000000000000 | 150
+ 3 | 1950 | 13.0000000000000000 | 150
+ 4 | 2100 | 14.0000000000000000 | 150
+ 10 | 1500 | 10.0000000000000000 | 150
+ 11 | 1650 | 11.0000000000000000 | 150
+ 12 | 1800 | 12.0000000000000000 | 150
+ 13 | 1950 | 13.0000000000000000 | 150
+ 14 | 2100 | 14.0000000000000000 | 150
+(10 rows)
+
+-- Test partitionwise grouping without any aggregates
+EXPLAIN (COSTS OFF)
+SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
+ QUERY PLAN
+------------------------------------------------
+ Merge Append
+ Sort Key: pagg_tab.c
+ -> IndexAggregate
+ Group Key: pagg_tab.c
+ -> Seq Scan on pagg_tab_p1 pagg_tab
+ -> IndexAggregate
+ Group Key: pagg_tab_1.c
+ -> Seq Scan on pagg_tab_p2 pagg_tab_1
+ -> IndexAggregate
+ Group Key: pagg_tab_2.c
+ -> Seq Scan on pagg_tab_p3 pagg_tab_2
+(11 rows)
+
+SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
+ c
+------
+ 0000
+ 0001
+ 0002
+ 0003
+ 0004
+ 0005
+ 0006
+ 0007
+ 0008
+ 0009
+ 0010
+ 0011
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1;
+ QUERY PLAN
+------------------------------------------------
+ IndexAggregate
+ Group Key: pagg_tab.a
+ -> Append
+ -> Seq Scan on pagg_tab_p1 pagg_tab_1
+ Filter: (a < 3)
+ -> Seq Scan on pagg_tab_p2 pagg_tab_2
+ Filter: (a < 3)
+ -> Seq Scan on pagg_tab_p3 pagg_tab_3
+ Filter: (a < 3)
+(9 rows)
+
+SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1;
+ a
+---
+ 0
+ 1
+ 2
+(3 rows)
+
+RESET enable_sort;
RESET enable_hashagg;
-- ROLLUP, partitionwise aggregation does not apply
EXPLAIN (COSTS OFF)
@@ -554,6 +685,7 @@ SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2
-- When GROUP BY clause does not match; partial aggregation is performed for each partition.
-- Also test GroupAggregate paths by disabling hash aggregates.
SET enable_hashagg TO false;
+SET enable_indexagg TO false;
EXPLAIN (COSTS OFF)
SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3;
QUERY PLAN
@@ -606,6 +738,7 @@ SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2
(6 rows)
RESET enable_hashagg;
+RESET enable_indexagg;
-- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for
-- aggregation
-- LEFT JOIN, should produce partial partitionwise aggregation plan as
@@ -761,27 +894,25 @@ SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y G
-- But right now we are unable to do partitionwise join in this case.
EXPLAIN (COSTS OFF)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
- QUERY PLAN
---------------------------------------------------------------------
- Sort
- Sort Key: pagg_tab1.x, pagg_tab2.y
- -> HashAggregate
- Group Key: pagg_tab1.x, pagg_tab2.y
- -> Hash Left Join
- Hash Cond: (pagg_tab1.x = pagg_tab2.y)
- Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
+ QUERY PLAN
+--------------------------------------------------------------
+ IndexAggregate
+ Group Key: pagg_tab1.x, pagg_tab2.y
+ -> Hash Left Join
+ Hash Cond: (pagg_tab1.x = pagg_tab2.y)
+ Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
+ -> Append
+ -> Seq Scan on pagg_tab1_p1 pagg_tab1_1
+ Filter: (x < 20)
+ -> Seq Scan on pagg_tab1_p2 pagg_tab1_2
+ Filter: (x < 20)
+ -> Hash
-> Append
- -> Seq Scan on pagg_tab1_p1 pagg_tab1_1
- Filter: (x < 20)
- -> Seq Scan on pagg_tab1_p2 pagg_tab1_2
- Filter: (x < 20)
- -> Hash
- -> Append
- -> Seq Scan on pagg_tab2_p2 pagg_tab2_1
- Filter: (y > 10)
- -> Seq Scan on pagg_tab2_p3 pagg_tab2_2
- Filter: (y > 10)
-(18 rows)
+ -> Seq Scan on pagg_tab2_p2 pagg_tab2_1
+ Filter: (y > 10)
+ -> Seq Scan on pagg_tab2_p3 pagg_tab2_2
+ Filter: (y > 10)
+(16 rows)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
x | y | count
@@ -801,27 +932,25 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
-- But right now we are unable to do partitionwise join in this case.
EXPLAIN (COSTS OFF)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
- QUERY PLAN
---------------------------------------------------------------------
- Sort
- Sort Key: pagg_tab1.x, pagg_tab2.y
- -> HashAggregate
- Group Key: pagg_tab1.x, pagg_tab2.y
- -> Hash Full Join
- Hash Cond: (pagg_tab1.x = pagg_tab2.y)
- Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
+ QUERY PLAN
+--------------------------------------------------------------
+ IndexAggregate
+ Group Key: pagg_tab1.x, pagg_tab2.y
+ -> Hash Full Join
+ Hash Cond: (pagg_tab1.x = pagg_tab2.y)
+ Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
+ -> Append
+ -> Seq Scan on pagg_tab1_p1 pagg_tab1_1
+ Filter: (x < 20)
+ -> Seq Scan on pagg_tab1_p2 pagg_tab1_2
+ Filter: (x < 20)
+ -> Hash
-> Append
- -> Seq Scan on pagg_tab1_p1 pagg_tab1_1
- Filter: (x < 20)
- -> Seq Scan on pagg_tab1_p2 pagg_tab1_2
- Filter: (x < 20)
- -> Hash
- -> Append
- -> Seq Scan on pagg_tab2_p2 pagg_tab2_1
- Filter: (y > 10)
- -> Seq Scan on pagg_tab2_p3 pagg_tab2_2
- Filter: (y > 10)
-(18 rows)
+ -> Seq Scan on pagg_tab2_p2 pagg_tab2_1
+ Filter: (y > 10)
+ -> Seq Scan on pagg_tab2_p3 pagg_tab2_2
+ Filter: (y > 10)
+(16 rows)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
x | y | count
@@ -839,16 +968,14 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI
-- Empty join relation because of empty outer side, no partitionwise agg plan
EXPLAIN (COSTS OFF)
SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2;
- QUERY PLAN
-----------------------------------------------
- GroupAggregate
+ QUERY PLAN
+----------------------------------------
+ IndexAggregate
Group Key: pagg_tab1.y
- -> Sort
- Sort Key: pagg_tab1.y
- -> Result
- Replaces: Join on b, pagg_tab1
- One-Time Filter: false
-(7 rows)
+ -> Result
+ Replaces: Join on b, pagg_tab1
+ One-Time Filter: false
+(5 rows)
SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2;
x | y | count
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 17d27ef3d46..9292d94baab 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -647,52 +647,39 @@ EXPLAIN (COSTS OFF)
SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
WHERE a BETWEEN 490 AND 510
GROUP BY 1, 2 ORDER BY 1, 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------
- Group
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
+ IndexAggregate
Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
- -> Merge Append
- Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
- -> Group
- Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
+ -> Append
+ -> Merge Full Join
+ Merge Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
+ Filter: ((COALESCE(prt1_1.a, p2_1.a) >= 490) AND (COALESCE(prt1_1.a, p2_1.a) <= 510))
-> Sort
- Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
- -> Merge Full Join
- Merge Cond: ((prt1.a = p2.a) AND (prt1.b = p2.b))
- Filter: ((COALESCE(prt1.a, p2.a) >= 490) AND (COALESCE(prt1.a, p2.a) <= 510))
- -> Sort
- Sort Key: prt1.a, prt1.b
- -> Seq Scan on prt1_p1 prt1
- -> Sort
- Sort Key: p2.a, p2.b
- -> Seq Scan on prt2_p1 p2
- -> Group
- Group Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
+ Sort Key: prt1_1.a, prt1_1.b
+ -> Seq Scan on prt1_p1 prt1_1
-> Sort
- Sort Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
- -> Merge Full Join
- Merge Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
- Filter: ((COALESCE(prt1_1.a, p2_1.a) >= 490) AND (COALESCE(prt1_1.a, p2_1.a) <= 510))
- -> Sort
- Sort Key: prt1_1.a, prt1_1.b
- -> Seq Scan on prt1_p2 prt1_1
- -> Sort
- Sort Key: p2_1.a, p2_1.b
- -> Seq Scan on prt2_p2 p2_1
- -> Group
- Group Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
+ Sort Key: p2_1.a, p2_1.b
+ -> Seq Scan on prt2_p1 p2_1
+ -> Merge Full Join
+ Merge Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
+ Filter: ((COALESCE(prt1_2.a, p2_2.a) >= 490) AND (COALESCE(prt1_2.a, p2_2.a) <= 510))
-> Sort
- Sort Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
- -> Merge Full Join
- Merge Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
- Filter: ((COALESCE(prt1_2.a, p2_2.a) >= 490) AND (COALESCE(prt1_2.a, p2_2.a) <= 510))
- -> Sort
- Sort Key: prt1_2.a, prt1_2.b
- -> Seq Scan on prt1_p3 prt1_2
- -> Sort
- Sort Key: p2_2.a, p2_2.b
- -> Seq Scan on prt2_p3 p2_2
-(43 rows)
+ Sort Key: prt1_2.a, prt1_2.b
+ -> Seq Scan on prt1_p2 prt1_2
+ -> Sort
+ Sort Key: p2_2.a, p2_2.b
+ -> Seq Scan on prt2_p2 p2_2
+ -> Merge Full Join
+ Merge Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
+ Filter: ((COALESCE(prt1_3.a, p2_3.a) >= 490) AND (COALESCE(prt1_3.a, p2_3.a) <= 510))
+ -> Sort
+ Sort Key: prt1_3.a, prt1_3.b
+ -> Seq Scan on prt1_p3 prt1_3
+ -> Sort
+ Sort Key: p2_3.a, p2_3.b
+ -> Seq Scan on prt2_p3 p2_3
+(30 rows)
SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
WHERE a BETWEEN 490 AND 510
@@ -1555,41 +1542,39 @@ ANALYZE plt1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
- QUERY PLAN
---------------------------------------------------------------------------------
- GroupAggregate
+ QUERY PLAN
+--------------------------------------------------------------------------
+ IndexAggregate
Group Key: t1.c, t3.c
- -> Sort
- Sort Key: t1.c, t3.c
- -> Append
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_p1 t1_1
- -> Hash
- -> Seq Scan on plt2_p1 t2_1
+ Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Seq Scan on plt1_p1 t1_1
-> Hash
- -> Seq Scan on plt1_e_p1 t3_1
+ -> Seq Scan on plt2_p1 t2_1
+ -> Hash
+ -> Seq Scan on plt1_e_p1 t3_1
+ -> Hash Join
+ Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_p2 t1_2
- -> Hash
- -> Seq Scan on plt2_p2 t2_2
+ Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on plt1_p2 t1_2
-> Hash
- -> Seq Scan on plt1_e_p2 t3_2
+ -> Seq Scan on plt2_p2 t2_2
+ -> Hash
+ -> Seq Scan on plt1_e_p2 t3_2
+ -> Hash Join
+ Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_p3 t1_3
- -> Hash
- -> Seq Scan on plt2_p3 t2_3
+ Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
+ -> Seq Scan on plt1_p3 t1_3
-> Hash
- -> Seq Scan on plt1_e_p3 t3_3
-(32 rows)
+ -> Seq Scan on plt2_p3 t2_3
+ -> Hash
+ -> Seq Scan on plt1_e_p3 t3_3
+(30 rows)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
avg | avg | avg | c | c | c
@@ -1703,41 +1688,39 @@ ANALYZE pht1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
- QUERY PLAN
---------------------------------------------------------------------------------
- GroupAggregate
+ QUERY PLAN
+--------------------------------------------------------------------------
+ IndexAggregate
Group Key: t1.c, t3.c
- -> Sort
- Sort Key: t1.c, t3.c
- -> Append
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
- -> Seq Scan on pht1_p1 t1_1
- -> Hash
- -> Seq Scan on pht2_p1 t2_1
+ Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Seq Scan on pht1_p1 t1_1
-> Hash
- -> Seq Scan on pht1_e_p1 t3_1
+ -> Seq Scan on pht2_p1 t2_1
+ -> Hash
+ -> Seq Scan on pht1_e_p1 t3_1
+ -> Hash Join
+ Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
- -> Seq Scan on pht1_p2 t1_2
- -> Hash
- -> Seq Scan on pht2_p2 t2_2
+ Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on pht1_p2 t1_2
-> Hash
- -> Seq Scan on pht1_e_p2 t3_2
+ -> Seq Scan on pht2_p2 t2_2
+ -> Hash
+ -> Seq Scan on pht1_e_p2 t3_2
+ -> Hash Join
+ Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
- -> Seq Scan on pht1_p3 t1_3
- -> Hash
- -> Seq Scan on pht2_p3 t2_3
+ Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
+ -> Seq Scan on pht1_p3 t1_3
-> Hash
- -> Seq Scan on pht1_e_p3 t3_3
-(32 rows)
+ -> Seq Scan on pht2_p3 t2_3
+ -> Hash
+ -> Seq Scan on pht1_e_p3 t3_3
+(30 rows)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
avg | avg | avg | c | c | c
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 933921d1860..ea40dfa9a30 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -706,18 +706,14 @@ alter table tenk2 reset (parallel_workers);
set enable_hashagg = false;
explain (costs off)
select count(*) from tenk1 group by twenty;
- QUERY PLAN
-----------------------------------------------------
- Finalize GroupAggregate
+ QUERY PLAN
+----------------------------------------
+ IndexAggregate
Group Key: twenty
- -> Gather Merge
+ -> Gather
Workers Planned: 4
- -> Partial GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Parallel Seq Scan on tenk1
-(9 rows)
+ -> Parallel Seq Scan on tenk1
+(5 rows)
select count(*) from tenk1 group by twenty;
count
@@ -772,19 +768,15 @@ drop function sp_simple_func(integer);
-- test handling of SRFs in targetlist (bug in 10.0)
explain (costs off)
select count(*), generate_series(1,2) from tenk1 group by twenty;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------
ProjectSet
- -> Finalize GroupAggregate
+ -> IndexAggregate
Group Key: twenty
- -> Gather Merge
+ -> Gather
Workers Planned: 4
- -> Partial GroupAggregate
- Group Key: twenty
- -> Sort
- Sort Key: twenty
- -> Parallel Seq Scan on tenk1
-(10 rows)
+ -> Parallel Seq Scan on tenk1
+(6 rows)
select count(*), generate_series(1,2) from tenk1 group by twenty;
count | generate_series
@@ -833,6 +825,7 @@ select count(*), generate_series(1,2) from tenk1 group by twenty;
-- test gather merge with parallel leader participation disabled
set parallel_leader_participation = off;
+set enable_indexagg = off;
explain (costs off)
select count(*) from tenk1 group by twenty;
QUERY PLAN
@@ -876,6 +869,7 @@ select count(*) from tenk1 group by twenty;
reset parallel_leader_participation;
--test rescan behavior of gather merge
set enable_material = false;
+set enable_indexagg = false;
explain (costs off)
select * from
(select string4, count(unique2)
@@ -917,6 +911,7 @@ select * from
(12 rows)
reset enable_material;
+reset enable_indexagg;
reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
explain (costs off)
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 0411db832f1..d32bec316d3 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -157,6 +157,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
+ enable_indexagg | on
enable_indexonlyscan | on
enable_indexscan | on
enable_material | on
@@ -173,7 +174,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(25 rows)
+(26 rows)
-- There are always wait event descriptions for various types. InjectionPoint
-- may be present or absent, depending on history since last postmaster start.
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 850f5a5787f..f72eb367112 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1392,6 +1392,7 @@ CREATE INDEX btg_x_y_idx ON btg(x, y);
ANALYZE btg;
SET enable_hashagg = off;
+SET enable_indexagg = off;
SET enable_seqscan = off;
-- Utilize the ordering of index scan to avoid a Sort operation
@@ -1623,12 +1624,100 @@ select v||'a', case v||'a' when 'aa' then 1 else 0 end, count(*)
select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
from unnest(array['a','b']) u(v)
group by v||'a' order by 1;
+
+--
+-- Index Aggregation tests
+--
+
+set enable_hashagg = false;
+set enable_sort = false;
+set enable_indexagg = true;
+set enable_indexscan = false;
+
+-- require ordered output
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT unique1, SUM(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+
+SELECT unique1, SUM(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT even, sum(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+
+SELECT even, sum(two) FROM tenk1
+GROUP BY 1
+ORDER BY 1
+LIMIT 10;
+
+-- multiple grouping columns
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT even, odd, sum(unique1) FROM tenk1
+GROUP BY 1, 2
+ORDER BY 1, 2
+LIMIT 10;
+
+SELECT even, odd, sum(unique1) FROM tenk1
+GROUP BY 1, 2
+ORDER BY 1, 2
+LIMIT 10;
+
+-- mixing columns between group by and order by
+begin;
+
+create temp table tmp(x int, y int);
+insert into tmp values (1, 8), (2, 7), (3, 6), (4, 5);
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 1, 2;
+
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 1, 2;
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 2, 1;
+
+SELECT x, y, sum(x) FROM tmp
+GROUP BY 1, 2
+ORDER BY 2, 1;
+
+--
+-- Index Aggregation Spill tests
+--
+
+set enable_indexagg = true;
+set enable_sort=false;
+set enable_hashagg = false;
+set work_mem='64kB';
+
+select unique1, count(*), sum(twothousand) from tenk1
+group by unique1
+having sum(fivethous) > 4975
+order by sum(twothousand);
+
+set work_mem to default;
+set enable_sort to default;
+set enable_hashagg to default;
+set enable_indexagg to default;
--
-- Hash Aggregation Spill tests
--
set enable_sort=false;
+set enable_indexagg = false;
set work_mem='64kB';
select unique1, count(*), sum(twothousand) from tenk1
@@ -1657,6 +1746,7 @@ analyze agg_data_20k;
-- Produce results with sorting.
set enable_hashagg = false;
+set enable_indexagg = false;
set jit_above_cost = 0;
@@ -1728,23 +1818,68 @@ select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
set enable_sort = true;
set work_mem to default;
+-- Produce results with index aggregation
+
+set enable_sort = false;
+set enable_hashagg = false;
+set enable_indexagg = true;
+
+set jit_above_cost = 0;
+
+explain (costs off)
+select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
+ from agg_data_20k group by g%10000;
+
+create table agg_index_1 as
+select g%10000 as c1, sum(g::numeric) as c2, count(*) as c3
+ from agg_data_20k group by g%10000;
+
+create table agg_index_2 as
+select * from
+ (values (100), (300), (500)) as r(a),
+ lateral (
+ select (g/2)::numeric as c1,
+ array_agg(g::numeric) as c2,
+ count(*) as c3
+ from agg_data_2k
+ where g < r.a
+ group by g/2) as s;
+
+set jit_above_cost to default;
+
+create table agg_index_3 as
+select (g/2)::numeric as c1, sum(7::int4) as c2, count(*) as c3
+ from agg_data_2k group by g/2;
+
+create table agg_index_4 as
+select (g/2)::numeric as c1, array_agg(g::numeric) as c2, count(*) as c3
+ from agg_data_2k group by g/2;
+
-- Compare group aggregation results to hash aggregation results
(select * from agg_hash_1 except select * from agg_group_1)
union all
-(select * from agg_group_1 except select * from agg_hash_1);
+(select * from agg_group_1 except select * from agg_hash_1)
+ union all
+(select * from agg_index_1 except select * from agg_group_1);
(select * from agg_hash_2 except select * from agg_group_2)
union all
-(select * from agg_group_2 except select * from agg_hash_2);
+(select * from agg_group_2 except select * from agg_hash_2)
+ union all
+(select * from agg_index_2 except select * from agg_group_2);
(select * from agg_hash_3 except select * from agg_group_3)
union all
-(select * from agg_group_3 except select * from agg_hash_3);
+(select * from agg_group_3 except select * from agg_hash_3)
+ union all
+(select * from agg_index_3 except select * from agg_group_3);
(select * from agg_hash_4 except select * from agg_group_4)
union all
-(select * from agg_group_4 except select * from agg_hash_4);
+(select * from agg_group_4 except select * from agg_hash_4)
+ union all
+(select * from agg_index_4 except select * from agg_group_4);
drop table agg_group_1;
drop table agg_group_2;
@@ -1754,3 +1889,7 @@ drop table agg_hash_1;
drop table agg_hash_2;
drop table agg_hash_3;
drop table agg_hash_4;
+drop table agg_index_1;
+drop table agg_index_2;
+drop table agg_index_3;
+drop table agg_index_4;
diff --git a/src/test/regress/sql/eager_aggregate.sql b/src/test/regress/sql/eager_aggregate.sql
index abe6d6ae09f..f9f4b5dcebd 100644
--- a/src/test/regress/sql/eager_aggregate.sql
+++ b/src/test/regress/sql/eager_aggregate.sql
@@ -35,6 +35,7 @@ GROUP BY t1.a ORDER BY t1.a;
-- Produce results with sorting aggregation
SET enable_hashagg TO off;
+SET enable_indexagg TO off;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.a, avg(t2.c)
@@ -48,6 +49,25 @@ SELECT t1.a, avg(t2.c)
GROUP BY t1.a ORDER BY t1.a;
RESET enable_hashagg;
+RESET enable_indexagg;
+
+-- Produce results with index aggregation
+SET enable_hashagg TO off;
+SET enable_sort TO off;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, avg(t2.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+GROUP BY t1.a ORDER BY t1.a;
+
+SELECT t1.a, avg(t2.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+GROUP BY t1.a ORDER BY t1.a;
+
+RESET enable_hashagg;
+RESET enable_sort;
--
@@ -71,6 +91,7 @@ GROUP BY t1.a ORDER BY t1.a;
-- Produce results with sorting aggregation
SET enable_hashagg TO off;
+SET enable_indexagg TO off;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.a, avg(t2.c + t3.c)
@@ -86,7 +107,27 @@ SELECT t1.a, avg(t2.c + t3.c)
GROUP BY t1.a ORDER BY t1.a;
RESET enable_hashagg;
+RESET enable_indexagg;
+-- Produce results with index aggregation
+SET enable_hashagg TO off;
+SET enable_sort TO off;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, avg(t2.c + t3.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+ JOIN eager_agg_t3 t3 ON t2.a = t3.a
+GROUP BY t1.a ORDER BY t1.a;
+
+SELECT t1.a, avg(t2.c + t3.c)
+ FROM eager_agg_t1 t1
+ JOIN eager_agg_t2 t2 ON t1.b = t2.b
+ JOIN eager_agg_t3 t3 ON t2.a = t3.a
+GROUP BY t1.a ORDER BY t1.a;
+
+RESET enable_hashagg;
+RESET enable_sort;
--
-- Test that eager aggregation works for outer join
diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql
index 7c725e2663a..75ee7b30fc1 100644
--- a/src/test/regress/sql/partition_aggregate.sql
+++ b/src/test/regress/sql/partition_aggregate.sql
@@ -55,8 +55,9 @@ EXPLAIN (COSTS OFF)
SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
--- Test GroupAggregate paths by disabling hash aggregates.
+-- Test GroupAggregate paths by disabling hash and index aggregates.
SET enable_hashagg TO false;
+SET enable_indexagg TO false;
-- When GROUP BY clause matches full aggregation is performed for each partition.
EXPLAIN (COSTS OFF)
@@ -81,6 +82,33 @@ EXPLAIN (COSTS OFF)
SELECT count(*) FROM pagg_tab GROUP BY c ORDER BY c LIMIT 1;
SELECT count(*) FROM pagg_tab GROUP BY c ORDER BY c LIMIT 1;
+RESET enable_hashagg;
+RESET enable_indexagg;
+
+-- Test IndexAggregate paths by disabling hash and group aggregates.
+SET enable_sort TO false;
+SET enable_hashagg TO false;
+
+-- When GROUP BY clause matches full aggregation is performed for each partition.
+EXPLAIN (COSTS OFF)
+SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+
+-- When GROUP BY clause does not match; full aggregation by top node.
+EXPLAIN (COSTS OFF)
+SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+
+-- Test partitionwise grouping without any aggregates
+EXPLAIN (COSTS OFF)
+SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
+SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
+EXPLAIN (COSTS OFF)
+SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1;
+SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1;
+
+
+RESET enable_sort;
RESET enable_hashagg;
-- ROLLUP, partitionwise aggregation does not apply
@@ -135,10 +163,12 @@ SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2
-- When GROUP BY clause does not match; partial aggregation is performed for each partition.
-- Also test GroupAggregate paths by disabling hash aggregates.
SET enable_hashagg TO false;
+SET enable_indexagg TO false;
EXPLAIN (COSTS OFF)
SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3;
SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3;
RESET enable_hashagg;
+RESET enable_indexagg;
-- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for
-- aggregation
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 71a75bc86ea..5f398219166 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -318,6 +318,7 @@ select count(*), generate_series(1,2) from tenk1 group by twenty;
-- test gather merge with parallel leader participation disabled
set parallel_leader_participation = off;
+set enable_indexagg = off;
explain (costs off)
select count(*) from tenk1 group by twenty;
@@ -328,6 +329,7 @@ reset parallel_leader_participation;
--test rescan behavior of gather merge
set enable_material = false;
+set enable_indexagg = false;
explain (costs off)
select * from
@@ -341,6 +343,7 @@ select * from
right join (values (1),(2),(3)) v(x) on true;
reset enable_material;
+reset enable_indexagg;
reset enable_hashagg;
--
2.43.0