0004-fix-tests-for-IndexAggregate.patch
text/x-patch
Filename: 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 0004
Subject: fix tests for IndexAggregate
| File | + | − |
|---|---|---|
| src/test/regress/expected/aggregates.out | 286 | 5 |
| src/test/regress/expected/groupingsets.out | 16 | 22 |
| src/test/regress/expected/partition_aggregate.out | 71 | 128 |
| src/test/regress/expected/select_parallel.out | 6 | 10 |
| src/test/regress/expected/sysviews.out | 2 | 1 |
| src/test/regress/sql/aggregates.sql | 143 | 4 |
From 4cb972806327a837b42fdee614c19428b27785e4 Mon Sep 17 00:00:00 2001
From: Sergey Soloviev <sergey.soloviev@tantorlabs.ru>
Date: Wed, 3 Dec 2025 17:44:14 +0300
Subject: [PATCH 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
---
src/test/regress/expected/aggregates.out | 291 +++++++++++++++++-
src/test/regress/expected/groupingsets.out | 38 +--
.../regress/expected/partition_aggregate.out | 199 +++++-------
src/test/regress/expected/select_parallel.out | 16 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/aggregates.sql | 147 ++++++++-
6 files changed, 524 insertions(+), 170 deletions(-)
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index be0e1573183..2e0aead49ac 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1477,7 +1477,7 @@ explain (costs off) select * from t1 group by a,b,c,d;
explain (costs off) select * from only t1 group by a,b,c,d;
QUERY PLAN
----------------------
- HashAggregate
+ IndexAggregate
Group Key: a, b
-> Seq Scan on t1
(3 rows)
@@ -3214,6 +3214,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)
@@ -3651,10 +3652,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
@@ -3727,6 +3960,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
@@ -3796,31 +4030,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)
@@ -3833,3 +4110,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/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/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index c30304b99c7..956abf9dc71 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -187,25 +187,19 @@ SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 O
Sort
Sort Key: pagg_tab.c, (sum(pagg_tab.a)), (avg(pagg_tab.b))
-> Append
- -> GroupAggregate
+ -> IndexAggregate
Group Key: pagg_tab.c
Filter: (avg(pagg_tab.d) < '15'::numeric)
- -> Sort
- Sort Key: pagg_tab.c
- -> Seq Scan on pagg_tab_p1 pagg_tab
- -> GroupAggregate
+ -> Seq Scan on pagg_tab_p1 pagg_tab
+ -> IndexAggregate
Group Key: pagg_tab_1.c
Filter: (avg(pagg_tab_1.d) < '15'::numeric)
- -> Sort
- Sort Key: pagg_tab_1.c
- -> Seq Scan on pagg_tab_p2 pagg_tab_1
- -> GroupAggregate
+ -> Seq Scan on pagg_tab_p2 pagg_tab_1
+ -> IndexAggregate
Group Key: pagg_tab_2.c
Filter: (avg(pagg_tab_2.d) < '15'::numeric)
- -> Sort
- Sort Key: pagg_tab_2.c
- -> Seq Scan on pagg_tab_p3 pagg_tab_2
-(21 rows)
+ -> Seq Scan on pagg_tab_p3 pagg_tab_2
+(15 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
@@ -221,31 +215,18 @@ SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 O
-- When GROUP BY clause does not match; partial aggregation is performed for each partition.
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
-------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------
Sort
Sort Key: pagg_tab.a, (sum(pagg_tab.b)), (avg(pagg_tab.b))
- -> Finalize GroupAggregate
+ -> IndexAggregate
Group Key: pagg_tab.a
Filter: (avg(pagg_tab.d) < '15'::numeric)
- -> Merge Append
- Sort Key: pagg_tab.a
- -> Partial GroupAggregate
- Group Key: pagg_tab.a
- -> Sort
- Sort Key: pagg_tab.a
- -> Seq Scan on pagg_tab_p1 pagg_tab
- -> Partial GroupAggregate
- Group Key: pagg_tab_1.a
- -> Sort
- Sort Key: pagg_tab_1.a
- -> Seq Scan on pagg_tab_p2 pagg_tab_1
- -> Partial GroupAggregate
- Group Key: pagg_tab_2.a
- -> Sort
- Sort Key: pagg_tab_2.a
- -> Seq Scan on pagg_tab_p3 pagg_tab_2
-(22 rows)
+ -> 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
+(9 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
@@ -267,24 +248,19 @@ EXPLAIN (COSTS OFF)
SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
QUERY PLAN
------------------------------------------------------
- Merge Append
+ Sort
Sort Key: pagg_tab.c
- -> Group
- Group Key: pagg_tab.c
- -> Sort
- Sort Key: pagg_tab.c
+ -> Append
+ -> IndexAggregate
+ Group Key: pagg_tab.c
-> Seq Scan on pagg_tab_p1 pagg_tab
- -> Group
- Group Key: pagg_tab_1.c
- -> Sort
- Sort Key: pagg_tab_1.c
+ -> IndexAggregate
+ Group Key: pagg_tab_1.c
-> Seq Scan on pagg_tab_p2 pagg_tab_1
- -> Group
- Group Key: pagg_tab_2.c
- -> Sort
- Sort Key: pagg_tab_2.c
+ -> IndexAggregate
+ Group Key: pagg_tab_2.c
-> Seq Scan on pagg_tab_p3 pagg_tab_2
-(17 rows)
+(12 rows)
SELECT c FROM pagg_tab GROUP BY c ORDER BY 1;
c
@@ -305,31 +281,18 @@ 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;
- QUERY PLAN
-------------------------------------------------------------
- Group
+ QUERY PLAN
+------------------------------------------------
+ IndexAggregate
Group Key: pagg_tab.a
- -> Merge Append
- Sort Key: pagg_tab.a
- -> Group
- Group Key: pagg_tab.a
- -> Sort
- Sort Key: pagg_tab.a
- -> Seq Scan on pagg_tab_p1 pagg_tab
- Filter: (a < 3)
- -> Group
- Group Key: pagg_tab_1.a
- -> Sort
- Sort Key: pagg_tab_1.a
- -> Seq Scan on pagg_tab_p2 pagg_tab_1
- Filter: (a < 3)
- -> Group
- Group Key: pagg_tab_2.a
- -> Sort
- Sort Key: pagg_tab_2.a
- -> Seq Scan on pagg_tab_p3 pagg_tab_2
- Filter: (a < 3)
-(22 rows)
+ -> 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
@@ -345,24 +308,19 @@ SELECT count(*) FROM pagg_tab GROUP BY c ORDER BY c LIMIT 1;
QUERY PLAN
------------------------------------------------------------
Limit
- -> Merge Append
+ -> Sort
Sort Key: pagg_tab.c
- -> GroupAggregate
- Group Key: pagg_tab.c
- -> Sort
- Sort Key: pagg_tab.c
+ -> Append
+ -> IndexAggregate
+ Group Key: pagg_tab.c
-> Seq Scan on pagg_tab_p1 pagg_tab
- -> GroupAggregate
- Group Key: pagg_tab_1.c
- -> Sort
- Sort Key: pagg_tab_1.c
+ -> IndexAggregate
+ Group Key: pagg_tab_1.c
-> Seq Scan on pagg_tab_p2 pagg_tab_1
- -> GroupAggregate
- Group Key: pagg_tab_2.c
- -> Sort
- Sort Key: pagg_tab_2.c
+ -> IndexAggregate
+ Group Key: pagg_tab_2.c
-> Seq Scan on pagg_tab_p3 pagg_tab_2
-(18 rows)
+(13 rows)
SELECT count(*) FROM pagg_tab GROUP BY c ORDER BY c LIMIT 1;
count
@@ -556,43 +514,30 @@ SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2
SET enable_hashagg 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
--------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
Sort
Sort Key: t1.y, (sum(t1.x)), (count(*))
- -> Finalize GroupAggregate
+ -> IndexAggregate
Group Key: t1.y
Filter: (avg(t1.x) > '10'::numeric)
- -> Merge Append
- Sort Key: t1.y
- -> Partial GroupAggregate
- Group Key: t1.y
- -> Sort
- Sort Key: t1.y
- -> Hash Join
- Hash Cond: (t1.x = t2.y)
- -> Seq Scan on pagg_tab1_p1 t1
- -> Hash
- -> Seq Scan on pagg_tab2_p1 t2
- -> Partial GroupAggregate
- Group Key: t1_1.y
- -> Sort
- Sort Key: t1_1.y
- -> Hash Join
- Hash Cond: (t1_1.x = t2_1.y)
- -> Seq Scan on pagg_tab1_p2 t1_1
- -> Hash
- -> Seq Scan on pagg_tab2_p2 t2_1
- -> Partial GroupAggregate
- Group Key: t1_2.y
- -> Sort
- Sort Key: t1_2.y
- -> Hash Join
- Hash Cond: (t2_2.y = t1_2.x)
- -> Seq Scan on pagg_tab2_p3 t2_2
- -> Hash
- -> Seq Scan on pagg_tab1_p3 t1_2
-(34 rows)
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1_1.x = t2_1.y)
+ -> Seq Scan on pagg_tab1_p1 t1_1
+ -> Hash
+ -> Seq Scan on pagg_tab2_p1 t2_1
+ -> Hash Join
+ Hash Cond: (t1_2.x = t2_2.y)
+ -> Seq Scan on pagg_tab1_p2 t1_2
+ -> Hash
+ -> Seq Scan on pagg_tab2_p2 t2_2
+ -> Hash Join
+ Hash Cond: (t2_3.y = t1_3.x)
+ -> Seq Scan on pagg_tab2_p3 t2_3
+ -> Hash
+ -> Seq Scan on pagg_tab1_p3 t1_3
+(21 rows)
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;
y | sum | count
@@ -839,16 +784,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/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 933921d1860..aee7469dc1e 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -881,20 +881,16 @@ select * from
(select string4, count(unique2)
from tenk1 group by string4 order by string4) ss
right join (values (1),(2),(3)) v(x) on true;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------
Nested Loop Left Join
-> Values Scan on "*VALUES*"
- -> Finalize GroupAggregate
+ -> IndexAggregate
Group Key: tenk1.string4
- -> Gather Merge
+ -> Gather
Workers Planned: 4
- -> Partial GroupAggregate
- Group Key: tenk1.string4
- -> Sort
- Sort Key: tenk1.string4
- -> Parallel Seq Scan on tenk1
-(11 rows)
+ -> Parallel Seq Scan on tenk1
+(7 rows)
select * from
(select string4, count(unique2)
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 3b37fafa65b..1c6ae8982ab 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 77ca6ffa3a9..73f5e97164c 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1374,6 +1374,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
@@ -1605,12 +1606,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
@@ -1639,6 +1728,7 @@ analyze agg_data_20k;
-- Produce results with sorting.
set enable_hashagg = false;
+set enable_indexagg = false;
set jit_above_cost = 0;
@@ -1710,23 +1800,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;
@@ -1736,3 +1871,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;
--
2.43.0