Accept plan changes in inherit regression tests

The accepted plans are actually new in the upstream, testing planning on
partitioned tables. The changes are just adding "Remote Subquery" node
at the top, to distribute the query to all datanodes.

The plans look reasonable and correct in general, but as a sanity check
I've also reproduced them on XL 9.5 using plain inheritance (because
partitioning is new in PostgreSQL 10). Naturally there are differences
)e.g. with partitioning the planner includes only leaf partitions in
the plan, while with inheritance we include the whole inheritance tree)
but otherwise the plans seem to be close enough.

1 files changed, 214 insertions, 194 deletions

diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 8fcd39a0dc..b30b38a986 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1941,63 +1941,69 @@ create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
 create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
 create table part_null_xy partition of list_parted for values in (null, 'xy');
 explain (costs off) select * from list_parted;
-           QUERY PLAN           
---------------------------------
- Append
-   ->  Seq Scan on part_ab_cd
-   ->  Seq Scan on part_ef_gh
-   ->  Seq Scan on part_null_xy
-(4 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_ab_cd
+         ->  Seq Scan on part_ef_gh
+         ->  Seq Scan on part_null_xy
+(5 rows)
 
 explain (costs off) select * from list_parted where a is null;
-           QUERY PLAN           
---------------------------------
- Append
-   ->  Seq Scan on part_null_xy
-         Filter: (a IS NULL)
-(3 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_null_xy
+               Filter: (a IS NULL)
+(4 rows)
 
 explain (costs off) select * from list_parted where a is not null;
-           QUERY PLAN            
----------------------------------
- Append
-   ->  Seq Scan on part_ab_cd
-         Filter: (a IS NOT NULL)
-   ->  Seq Scan on part_ef_gh
-         Filter: (a IS NOT NULL)
-   ->  Seq Scan on part_null_xy
-         Filter: (a IS NOT NULL)
-(7 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_ab_cd
+               Filter: (a IS NOT NULL)
+         ->  Seq Scan on part_ef_gh
+               Filter: (a IS NOT NULL)
+         ->  Seq Scan on part_null_xy
+               Filter: (a IS NOT NULL)
+(8 rows)
 
 explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
-                        QUERY PLAN                        
-----------------------------------------------------------
- Append
-   ->  Seq Scan on part_ab_cd
-         Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-   ->  Seq Scan on part_ef_gh
-         Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-(5 rows)
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_ab_cd
+               Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
+         ->  Seq Scan on part_ef_gh
+               Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
+(6 rows)
 
 explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
-                                      QUERY PLAN                                       
----------------------------------------------------------------------------------------
- Append
-   ->  Seq Scan on part_ab_cd
-         Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-   ->  Seq Scan on part_ef_gh
-         Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-   ->  Seq Scan on part_null_xy
-         Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-(7 rows)
+                                         QUERY PLAN                                          
+---------------------------------------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_ab_cd
+               Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
+         ->  Seq Scan on part_ef_gh
+               Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
+         ->  Seq Scan on part_null_xy
+               Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
+(8 rows)
 
 explain (costs off) select * from list_parted where a = 'ab';
-                QUERY PLAN                
-------------------------------------------
- Append
-   ->  Seq Scan on part_ab_cd
-         Filter: ((a)::text = 'ab'::text)
-(3 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_ab_cd
+               Filter: ((a)::text = 'ab'::text)
+(4 rows)
 
 create table range_list_parted (
 	a	int,
@@ -2017,55 +2023,59 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab');
 create table part_40_inf_cd partition of part_40_inf for values in ('cd');
 create table part_40_inf_null partition of part_40_inf for values in (null);
 explain (costs off) select * from range_list_parted;
-             QUERY PLAN             
-------------------------------------
- Append
-   ->  Seq Scan on part_1_10_ab
-   ->  Seq Scan on part_1_10_cd
-   ->  Seq Scan on part_10_20_ab
-   ->  Seq Scan on part_10_20_cd
-   ->  Seq Scan on part_21_30_ab
-   ->  Seq Scan on part_21_30_cd
-   ->  Seq Scan on part_40_inf_ab
-   ->  Seq Scan on part_40_inf_cd
-   ->  Seq Scan on part_40_inf_null
-(10 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_1_10_ab
+         ->  Seq Scan on part_1_10_cd
+         ->  Seq Scan on part_10_20_ab
+         ->  Seq Scan on part_10_20_cd
+         ->  Seq Scan on part_21_30_ab
+         ->  Seq Scan on part_21_30_cd
+         ->  Seq Scan on part_40_inf_ab
+         ->  Seq Scan on part_40_inf_cd
+         ->  Seq Scan on part_40_inf_null
+(11 rows)
 
 explain (costs off) select * from range_list_parted where a = 5;
-           QUERY PLAN           
---------------------------------
- Append
-   ->  Seq Scan on part_1_10_ab
-         Filter: (a = 5)
-   ->  Seq Scan on part_1_10_cd
-         Filter: (a = 5)
-(5 rows)
+                QUERY PLAN                
+------------------------------------------
+ Remote Subquery Scan on all (datanode_1)
+   ->  Append
+         ->  Seq Scan on part_1_10_ab
+               Filter: (a = 5)
+         ->  Seq Scan on part_1_10_cd
+               Filter: (a = 5)
+(6 rows)
 
 explain (costs off) select * from range_list_parted where b = 'ab';
-             QUERY PLAN             
-------------------------------------
- Append
-   ->  Seq Scan on part_1_10_ab
-         Filter: (b = 'ab'::bpchar)
-   ->  Seq Scan on part_10_20_ab
-         Filter: (b = 'ab'::bpchar)
-   ->  Seq Scan on part_21_30_ab
-         Filter: (b = 'ab'::bpchar)
-   ->  Seq Scan on part_40_inf_ab
-         Filter: (b = 'ab'::bpchar)
-(9 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_1_10_ab
+               Filter: (b = 'ab'::bpchar)
+         ->  Seq Scan on part_10_20_ab
+               Filter: (b = 'ab'::bpchar)
+         ->  Seq Scan on part_21_30_ab
+               Filter: (b = 'ab'::bpchar)
+         ->  Seq Scan on part_40_inf_ab
+               Filter: (b = 'ab'::bpchar)
+(10 rows)
 
 explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
-                           QUERY PLAN                            
------------------------------------------------------------------
- Append
-   ->  Seq Scan on part_1_10_ab
-         Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-   ->  Seq Scan on part_10_20_ab
-         Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-   ->  Seq Scan on part_21_30_ab
-         Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-(7 rows)
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_1_10_ab
+               Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+         ->  Seq Scan on part_10_20_ab
+               Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+         ->  Seq Scan on part_21_30_ab
+               Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+(8 rows)
 
 /* Should select no rows because range partition key cannot be null */
 explain (costs off) select * from range_list_parted where a is null;
@@ -2077,48 +2087,51 @@ explain (costs off) select * from range_list_parted where a is null;
 
 /* Should only select rows from the null-accepting partition */
 explain (costs off) select * from range_list_parted where b is null;
-             QUERY PLAN             
-------------------------------------
- Append
-   ->  Seq Scan on part_40_inf_null
-         Filter: (b IS NULL)
-(3 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_40_inf_null
+               Filter: (b IS NULL)
+(4 rows)
 
 explain (costs off) select * from range_list_parted where a is not null and a < 67;
-                   QUERY PLAN                   
-------------------------------------------------
- Append
-   ->  Seq Scan on part_1_10_ab
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_1_10_cd
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_10_20_ab
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_10_20_cd
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_21_30_ab
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_21_30_cd
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_40_inf_ab
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_40_inf_cd
-         Filter: ((a IS NOT NULL) AND (a < 67))
-   ->  Seq Scan on part_40_inf_null
-         Filter: ((a IS NOT NULL) AND (a < 67))
-(19 rows)
+                      QUERY PLAN                      
+------------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_1_10_ab
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_1_10_cd
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_10_20_ab
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_10_20_cd
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_21_30_ab
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_21_30_cd
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_40_inf_ab
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_40_inf_cd
+               Filter: ((a IS NOT NULL) AND (a < 67))
+         ->  Seq Scan on part_40_inf_null
+               Filter: ((a IS NOT NULL) AND (a < 67))
+(20 rows)
 
 explain (costs off) select * from range_list_parted where a >= 30;
-             QUERY PLAN             
-------------------------------------
- Append
-   ->  Seq Scan on part_40_inf_ab
-         Filter: (a >= 30)
-   ->  Seq Scan on part_40_inf_cd
-         Filter: (a >= 30)
-   ->  Seq Scan on part_40_inf_null
-         Filter: (a >= 30)
-(7 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on part_40_inf_ab
+               Filter: (a >= 30)
+         ->  Seq Scan on part_40_inf_cd
+               Filter: (a >= 30)
+         ->  Seq Scan on part_40_inf_null
+               Filter: (a >= 30)
+(8 rows)
 
 drop table list_parted;
 drop table range_list_parted;
@@ -2132,84 +2145,91 @@ create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20
 create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
 create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (unbounded, unbounded, unbounded);
 explain (costs off) select * from mcrparted where a = 0;	-- scans mcrparted0
-          QUERY PLAN          
-------------------------------
- Append
-   ->  Seq Scan on mcrparted0
-         Filter: (a = 0)
-(3 rows)
+                QUERY PLAN                
+------------------------------------------
+ Remote Subquery Scan on all (datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted0
+               Filter: (a = 0)
+(4 rows)
 
 explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;	-- scans mcrparted1
-                 QUERY PLAN                  
----------------------------------------------
- Append
-   ->  Seq Scan on mcrparted1
-         Filter: ((a = 10) AND (abs(b) < 5))
-(3 rows)
+                    QUERY PLAN                     
+---------------------------------------------------
+ Remote Subquery Scan on all (datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted1
+               Filter: ((a = 10) AND (abs(b) < 5))
+(4 rows)
 
 explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;	-- scans mcrparted1, mcrparted2
-                 QUERY PLAN                  
----------------------------------------------
- Append
-   ->  Seq Scan on mcrparted1
-         Filter: ((a = 10) AND (abs(b) = 5))
-   ->  Seq Scan on mcrparted2
-         Filter: ((a = 10) AND (abs(b) = 5))
-(5 rows)
+                    QUERY PLAN                     
+---------------------------------------------------
+ Remote Subquery Scan on all (datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted1
+               Filter: ((a = 10) AND (abs(b) = 5))
+         ->  Seq Scan on mcrparted2
+               Filter: ((a = 10) AND (abs(b) = 5))
+(6 rows)
 
 explain (costs off) select * from mcrparted where abs(b) = 5;	-- scans all partitions
-          QUERY PLAN          
-------------------------------
- Append
-   ->  Seq Scan on mcrparted0
-         Filter: (abs(b) = 5)
-   ->  Seq Scan on mcrparted1
-         Filter: (abs(b) = 5)
-   ->  Seq Scan on mcrparted2
-         Filter: (abs(b) = 5)
-   ->  Seq Scan on mcrparted3
-         Filter: (abs(b) = 5)
-   ->  Seq Scan on mcrparted5
-         Filter: (abs(b) = 5)
-(11 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted0
+               Filter: (abs(b) = 5)
+         ->  Seq Scan on mcrparted1
+               Filter: (abs(b) = 5)
+         ->  Seq Scan on mcrparted2
+               Filter: (abs(b) = 5)
+         ->  Seq Scan on mcrparted3
+               Filter: (abs(b) = 5)
+         ->  Seq Scan on mcrparted5
+               Filter: (abs(b) = 5)
+(12 rows)
 
 explain (costs off) select * from mcrparted where a > -1;	-- scans all partitions
-             QUERY PLAN              
--------------------------------------
- Append
-   ->  Seq Scan on mcrparted0
-         Filter: (a > '-1'::integer)
-   ->  Seq Scan on mcrparted1
-         Filter: (a > '-1'::integer)
-   ->  Seq Scan on mcrparted2
-         Filter: (a > '-1'::integer)
-   ->  Seq Scan on mcrparted3
-         Filter: (a > '-1'::integer)
-   ->  Seq Scan on mcrparted4
-         Filter: (a > '-1'::integer)
-   ->  Seq Scan on mcrparted5
-         Filter: (a > '-1'::integer)
-(13 rows)
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Remote Subquery Scan on all (datanode_1,datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted0
+               Filter: (a > '-1'::integer)
+         ->  Seq Scan on mcrparted1
+               Filter: (a > '-1'::integer)
+         ->  Seq Scan on mcrparted2
+               Filter: (a > '-1'::integer)
+         ->  Seq Scan on mcrparted3
+               Filter: (a > '-1'::integer)
+         ->  Seq Scan on mcrparted4
+               Filter: (a > '-1'::integer)
+         ->  Seq Scan on mcrparted5
+               Filter: (a > '-1'::integer)
+(14 rows)
 
 explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10;	-- scans mcrparted4
-                        QUERY PLAN                         
------------------------------------------------------------
- Append
-   ->  Seq Scan on mcrparted4
-         Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
-(3 rows)
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Remote Subquery Scan on all (datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted4
+               Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+(4 rows)
 
 explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5
-               QUERY PLAN                
------------------------------------------
- Append
-   ->  Seq Scan on mcrparted3
-         Filter: ((c > 20) AND (a = 20))
-   ->  Seq Scan on mcrparted4
-         Filter: ((c > 20) AND (a = 20))
-   ->  Seq Scan on mcrparted5
-         Filter: ((c > 20) AND (a = 20))
-(7 rows)
+                  QUERY PLAN                   
+-----------------------------------------------
+ Remote Subquery Scan on all (datanode_2)
+   ->  Append
+         ->  Seq Scan on mcrparted3
+               Filter: ((c > 20) AND (a = 20))
+         ->  Seq Scan on mcrparted4
+               Filter: ((c > 20) AND (a = 20))
+         ->  Seq Scan on mcrparted5
+               Filter: ((c > 20) AND (a = 20))
+(8 rows)
 
 drop table mcrparted;
 -- check that partitioned table Appends cope with being referenced in