Allow executor startup pruning to prune all child nodes.

Previously, if the startup pruning logic proved that all child nodes
of an Append or MergeAppend could be pruned, we still kept one, just
to keep EXPLAIN from failing.  The previous commit removed the
ruleutils.c limitation that required this kluge, so drop it.  That
results in less-confusing EXPLAIN output, as per a complaint from
Yuzuko Hosoya.

David Rowley

Discussion: https://postgr.es/m/001001d4f44b$2a2cca50$7e865ef0$@lab.ntt.co.jp

2 files changed, 53 insertions, 45 deletions

diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 57680f1d292..6dc50e0aa4e 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2005,20 +2005,17 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
 (19 rows)
 
 -- Try some params whose values do not belong to any partition.
--- We'll still get a single subplan in this case, but it should not be scanned.
 select explain_parallel_append('execute ab_q5 (33, 44, 55)');
-                            explain_parallel_append                            
--------------------------------------------------------------------------------
+                  explain_parallel_append                  
+-----------------------------------------------------------
  Finalize Aggregate (actual rows=N loops=N)
    ->  Gather (actual rows=N loops=N)
          Workers Planned: 2
          Workers Launched: N
          ->  Partial Aggregate (actual rows=N loops=N)
                ->  Parallel Append (actual rows=N loops=N)
-                     Subplans Removed: 8
-                     ->  Parallel Seq Scan on ab_a1_b1 ab_1 (never executed)
-                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-(9 rows)
+                     Subplans Removed: 9
+(7 rows)
 
 -- Test Parallel Append with PARAM_EXEC Params
 select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
@@ -2854,16 +2851,13 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
          Filter: (b = ANY (ARRAY[$1, $2]))
 (4 rows)
 
--- Try with no matching partitions. One subplan should remain in this case,
--- but it shouldn't be executed.
+-- Try with no matching partitions.
 explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
-                      QUERY PLAN                      
-------------------------------------------------------
+           QUERY PLAN           
+--------------------------------
  Append (actual rows=0 loops=1)
-   Subplans Removed: 1
-   ->  Seq Scan on listp_1_1 listp_1 (never executed)
-         Filter: (b = ANY (ARRAY[$1, $2]))
-(4 rows)
+   Subplans Removed: 2
+(2 rows)
 
 deallocate q1;
 -- Test more complex cases where a not-equal condition further eliminates partitions.
@@ -2879,15 +2873,12 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
 (4 rows)
 
 -- Both partitions allowed by IN clause, then both excluded again by <> clauses.
--- One subplan will remain in this case, but it should not be executed.
 explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
-                               QUERY PLAN                                
--------------------------------------------------------------------------
+           QUERY PLAN           
+--------------------------------
  Append (actual rows=0 loops=1)
-   Subplans Removed: 1
-   ->  Seq Scan on listp_1_1 listp_1 (never executed)
-         Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
-(4 rows)
+   Subplans Removed: 2
+(2 rows)
 
 -- Ensure Params that evaluate to NULL properly prune away all partitions
 explain (analyze, costs off, summary off, timing off)
@@ -2971,13 +2962,11 @@ select * from stable_qual_pruning
 explain (analyze, costs off, summary off, timing off)
 select * from stable_qual_pruning
   where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
-                                                        QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
+           QUERY PLAN           
+--------------------------------
  Append (actual rows=0 loops=1)
-   Subplans Removed: 2
-   ->  Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (never executed)
-         Filter: (a = ANY ('{"Mon Feb 01 00:00:00 2010 PST","Wed Jan 01 00:00:00 2020 PST"}'::timestamp with time zone[]))
-(4 rows)
+   Subplans Removed: 3
+(2 rows)
 
 explain (analyze, costs off, summary off, timing off)
 select * from stable_qual_pruning
@@ -3159,14 +3148,12 @@ execute mt_q1(25);
 
 -- Ensure MergeAppend behaves correctly when no subplans match
 explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
-                                    QUERY PLAN                                    
-----------------------------------------------------------------------------------
+              QUERY PLAN              
+--------------------------------------
  Merge Append (actual rows=0 loops=1)
    Sort Key: ma_test.b
-   Subplans Removed: 2
-   ->  Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
-         Filter: ((a >= $1) AND ((a % 10) = 5))
-(5 rows)
+   Subplans Removed: 3
+(3 rows)
 
 execute mt_q1(35);
  a 
@@ -3174,6 +3161,21 @@ execute mt_q1(35);
 (0 rows)
 
 deallocate mt_q1;
+set plan_cache_mode = force_generic_plan;
+prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
+-- Ensure output list looks sane when the MergeAppend has no subplans.
+explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
+                 QUERY PLAN                 
+--------------------------------------------
+ Limit (actual rows=0 loops=1)
+   Output: ma_test.a, ma_test.b
+   ->  Merge Append (actual rows=0 loops=1)
+         Sort Key: ma_test.b
+         Subplans Removed: 3
+(5 rows)
+
+deallocate mt_q2;
+reset plan_cache_mode;
 -- ensure initplan params properly prune partitions
 explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
                                           QUERY PLAN                                           
@@ -3591,19 +3593,18 @@ from (
      ) s(a, b, c)
 where s.a = $1 and s.b = $2 and s.c = (select 1);
 explain (costs off) execute q (1, 1);
-                          QUERY PLAN                           
----------------------------------------------------------------
+                     QUERY PLAN                     
+----------------------------------------------------
  Append
    InitPlan 1 (returns $0)
      ->  Result
-   Subplans Removed: 1
    ->  Seq Scan on p1 p
-         Filter: ((a = $1) AND (b = $2) AND (c = $0))
+         Filter: ((a = 1) AND (b = 1) AND (c = $0))
    ->  Seq Scan on q111 q1
-         Filter: ((a = $1) AND (b = $2) AND (c = $0))
+         Filter: ((a = 1) AND (b = 1) AND (c = $0))
    ->  Result
-         One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = $0))
-(10 rows)
+         One-Time Filter: (1 = $0)
+(9 rows)
 
 execute q (1, 1);
  a | b | c 
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 41f0b6f1102..e00984edd45 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -477,7 +477,6 @@ select explain_parallel_append('execute ab_q5 (1, 1, 1)');
 select explain_parallel_append('execute ab_q5 (2, 3, 3)');
 
 -- Try some params whose values do not belong to any partition.
--- We'll still get a single subplan in this case, but it should not be scanned.
 select explain_parallel_append('execute ab_q5 (33, 44, 55)');
 
 -- Test Parallel Append with PARAM_EXEC Params
@@ -702,8 +701,7 @@ explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
 
 explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
 
--- Try with no matching partitions. One subplan should remain in this case,
--- but it shouldn't be executed.
+-- Try with no matching partitions.
 explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
 
 deallocate q1;
@@ -715,7 +713,6 @@ prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <>
 explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
 
 -- Both partitions allowed by IN clause, then both excluded again by <> clauses.
--- One subplan will remain in this case, but it should not be executed.
 explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
 
 -- Ensure Params that evaluate to NULL properly prune away all partitions
@@ -841,6 +838,16 @@ execute mt_q1(35);
 
 deallocate mt_q1;
 
+set plan_cache_mode = force_generic_plan;
+
+prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
+
+-- Ensure output list looks sane when the MergeAppend has no subplans.
+explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
+
+deallocate mt_q2;
+reset plan_cache_mode;
+
 -- ensure initplan params properly prune partitions
 explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;