Strip PlaceHolderVars from index operands

When pulling up a subquery, we may need to wrap its targetlist items
in PlaceHolderVars to enforce separate identity or as a result of
outer joins.  However, this causes any upper-level WHERE clauses
referencing these outputs to contain PlaceHolderVars, which prevents
indxpath.c from recognizing that they could be matched to index
columns or index expressions, potentially affecting the planner's
ability to use indexes.

To fix, explicitly strip PlaceHolderVars from index operands.  A
PlaceHolderVar appearing in a relation-scan-level expression is
effectively a no-op.  Nevertheless, to play it safe, we strip only
PlaceHolderVars that are not marked nullable.

The stripping is performed recursively to handle cases where
PlaceHolderVars are nested or interleaved with other node types.  To
minimize performance impact, we first use a lightweight walker to
check for the presence of strippable PlaceHolderVars.  The expensive
mutator is invoked only if a candidate is found, avoiding unnecessary
memory allocation and tree copying in the common case where no
PlaceHolderVars are present.

Back-patch to v18.  Although this issue exists before that, changes in
this version made it common enough to notice.  Given the lack of field
reports for older versions, I am not back-patching further.

Reported-by: Haowu Ge <gehaowu@bitmoe.com>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/62af586c-c270-44f3-9c5e-02c81d537e3d.gehaowu@bitmoe.com
Backpatch-through: 18

2 files changed, 141 insertions, 0 deletions

diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index f047db7c58a..921017489c0 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -463,6 +463,101 @@ select x, y || 'y'
    | 3y
 (8 rows)
 
+-- check that operands wrapped in PlaceHolderVars are capable of index matching
+begin;
+set local enable_bitmapscan = off;
+explain (costs off)
+select x, y
+  from (select unique1 as x, unique2 as y from tenk1) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Sort
+   Sort Key: tenk1.unique1, tenk1.unique2
+   ->  GroupAggregate
+         Group Key: tenk1.unique1
+         Sort Key: tenk1.unique2
+           Group Key: tenk1.unique2
+         ->  Index Scan using tenk1_unique1 on tenk1
+               Index Cond: (unique1 = 1)
+(8 rows)
+
+select x, y
+  from (select unique1 as x, unique2 as y from tenk1) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+ x |  y   
+---+------
+ 1 |     
+   | 2838
+(2 rows)
+
+explain (costs off)
+select x, y
+  from (select unique1::oid as x, unique2 as y from tenk1) as t
+  where x::integer = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Sort
+   Sort Key: ((tenk1.unique1)::oid), tenk1.unique2
+   ->  GroupAggregate
+         Group Key: ((tenk1.unique1)::oid)
+         Sort Key: tenk1.unique2
+           Group Key: tenk1.unique2
+         ->  Sort
+               Sort Key: ((tenk1.unique1)::oid)
+               ->  Index Scan using tenk1_unique1 on tenk1
+                     Index Cond: (((unique1)::oid)::integer = 1)
+(10 rows)
+
+select x, y
+  from (select unique1::oid as x, unique2 as y from tenk1) as t
+  where x::integer = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+ x |  y   
+---+------
+ 1 |     
+   | 2838
+(2 rows)
+
+explain (costs off)
+select x, y
+  from (select t1.unique1 as x, t1.unique2 as y from tenk1 t1, tenk1 t2) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Sort
+   Sort Key: t1.unique1, t1.unique2
+   ->  GroupAggregate
+         Group Key: t1.unique1
+         Sort Key: t1.unique2
+           Group Key: t1.unique2
+         ->  Nested Loop
+               ->  Index Scan using tenk1_unique1 on tenk1 t1
+                     Index Cond: (unique1 = 1)
+               ->  Index Only Scan using tenk1_hundred on tenk1 t2
+(10 rows)
+
+select x, y
+  from (select t1.unique1 as x, t1.unique2 as y from tenk1 t1, tenk1 t2) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+ x |  y   
+---+------
+ 1 |     
+   | 2838
+(2 rows)
+
+rollback;
 -- check qual push-down rules for a subquery with grouping sets
 explain (verbose, costs off)
 select * from (
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 3e010961fab..826ac5f5dbf 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -183,6 +183,52 @@ select x, y || 'y'
   group by grouping sets (x, y)
   order by 1, 2;
 
+-- check that operands wrapped in PlaceHolderVars are capable of index matching
+begin;
+
+set local enable_bitmapscan = off;
+
+explain (costs off)
+select x, y
+  from (select unique1 as x, unique2 as y from tenk1) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+
+select x, y
+  from (select unique1 as x, unique2 as y from tenk1) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+
+explain (costs off)
+select x, y
+  from (select unique1::oid as x, unique2 as y from tenk1) as t
+  where x::integer = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+
+select x, y
+  from (select unique1::oid as x, unique2 as y from tenk1) as t
+  where x::integer = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+
+explain (costs off)
+select x, y
+  from (select t1.unique1 as x, t1.unique2 as y from tenk1 t1, tenk1 t2) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+
+select x, y
+  from (select t1.unique1 as x, t1.unique2 as y from tenk1 t1, tenk1 t2) as t
+  where x = 1
+  group by grouping sets (x, y)
+  order by 1, 2;
+
+rollback;
+
 -- check qual push-down rules for a subquery with grouping sets
 explain (verbose, costs off)
 select * from (