From 4cb972806327a837b42fdee614c19428b27785e4 Mon Sep 17 00:00:00 2001 From: Sergey Soloviev 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