diff options
| author | Richard Guo <rguo@postgresql.org> | 2024-10-09 17:19:04 +0900 |
|---|---|---|
| committer | Richard Guo <rguo@postgresql.org> | 2024-10-09 17:19:04 +0900 |
| commit | 67a54b9e83d331eadd3a595e6c3bfec06288d2c4 (patch) | |
| tree | 98bde85da279ae7a3f0441323e32d00eb8f46697 /src/test | |
| parent | 828e94c9d2fd87c06a75354361543119d9937068 (diff) | |
Allow pushdown of HAVING clauses with grouping sets
In some cases, we may want to transfer a HAVING clause into WHERE in
hopes of eliminating tuples before aggregation instead of after.
Previously, we couldn't do this if there were any nonempty grouping
sets, because we didn't have a way to tell if the HAVING clause
referenced any columns that were nullable by the grouping sets, and
moving such a clause into WHERE could potentially change the results.
Now, with expressions marked nullable by grouping sets with the RT
index of the RTE_GROUP RTE, it is much easier to identify those
clauses that reference any nullable-by-grouping-sets columns: we just
need to check if the RT index of the RTE_GROUP RTE is present in the
clause. For other HAVING clauses, they can be safely pushed down.
Author: Richard Guo
Discussion: https://postgr.es/m/CAMbWs4-NpzPgtKU=hgnvyn+J-GanxQCjrUi7piNzZ=upiCV=2Q@mail.gmail.com
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 21 | ||||
| -rw-r--r-- | src/test/regress/sql/groupingsets.sql | 5 |
2 files changed, 26 insertions, 0 deletions
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 717383c4f3a..d7c9b44605d 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -860,6 +860,27 @@ explain (costs off) -> Seq Scan on gstest2 (10 rows) +-- test pushdown of HAVING clause that does not reference any columns that are nullable by grouping sets +explain (costs off) +select a, b, count(*) from gstest2 group by grouping sets ((a, b), (a)) having a > 1 and b > 1; + QUERY PLAN +--------------------------------- + GroupAggregate + Group Key: a, b + Group Key: a + Filter: (b > 1) + -> Sort + Sort Key: a, b + -> Seq Scan on gstest2 + Filter: (a > 1) +(8 rows) + +select a, b, count(*) from gstest2 group by grouping sets ((a, b), (a)) having a > 1 and b > 1; + a | b | count +---+---+------- + 2 | 2 | 1 +(1 row) + -- HAVING with GROUPING queries select ten, grouping(ten) from onek group by grouping sets(ten) having grouping(ten) >= 0 diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index 660ca33efc1..21cd3121940 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -279,6 +279,11 @@ explain (costs off) select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; +-- test pushdown of HAVING clause that does not reference any columns that are nullable by grouping sets +explain (costs off) +select a, b, count(*) from gstest2 group by grouping sets ((a, b), (a)) having a > 1 and b > 1; +select a, b, count(*) from gstest2 group by grouping sets ((a, b), (a)) having a > 1 and b > 1; + -- HAVING with GROUPING queries select ten, grouping(ten) from onek group by grouping sets(ten) having grouping(ten) >= 0 |