Move resolution of AlternativeSubPlan choices to the planner.

When commit bd3daddaf introduced AlternativeSubPlans, I had some
ambitions towards allowing the choice of subplan to change during
execution.  That has not happened, or even been thought about, in the
ensuing twelve years; so it seems like a failed experiment.  So let's
rip that out and resolve the choice of subplan at the end of planning
(in setrefs.c) rather than during executor startup.  This has a number
of positive benefits:

* Removal of a few hundred lines of executor code, since
AlternativeSubPlans need no longer be supported there.

* Removal of executor-startup overhead (particularly, initialization
of subplans that won't be used).

* Removal of incidental costs of having a larger plan tree, such as
tree-scanning and copying costs in the plancache; not to mention
setrefs.c's own costs of processing the discarded subplans.

* EXPLAIN no longer has to print a weird (and undocumented)
representation of an AlternativeSubPlan choice; it sees only the
subplan actually used.  This should mean less confusion for users.

* Since setrefs.c knows which subexpression of a plan node it's
working on at any instant, it's possible to adjust the estimated
number of executions of the subplan based on that.  For example,
we should usually estimate more executions of a qual expression
than a targetlist expression.  The implementation used here is
pretty simplistic, because we don't want to expend a lot of cycles
on the issue; but it's better than ignoring the point entirely,
as the executor had to.

That last point might possibly result in shifting the choice
between hashed and non-hashed EXISTS subplans in a few cases,
but in general this patch isn't meant to change planner choices.
Since we're doing the resolution so late, it's really impossible
to change any plan choices outside the AlternativeSubPlan itself.

Patch by me; thanks to David Rowley for review.

Discussion: https://postgr.es/m/1992952.1592785225@sss.pgh.pa.us

4 files changed, 82 insertions, 36 deletions

diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e1..ff157ceb1c1 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -50,14 +50,12 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
  Insert on insertconflicttest
    Conflict Resolution: UPDATE
    Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key
-   Conflict Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
+   Conflict Filter: (SubPlan 1)
    ->  Result
    SubPlan 1
      ->  Index Only Scan using both_index_expr_key on insertconflicttest ii
            Index Cond: (key = excluded.key)
-   SubPlan 2
-     ->  Seq Scan on insertconflicttest ii_1
-(10 rows)
+(8 rows)
 
 -- Neither collation nor operator class specifications are required --
 -- supplying them merely *limits* matches to indexes with matching opclasses
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index b81923f2e74..9d56cdacf37 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -875,6 +875,53 @@ select * from int8_tbl where q1 in (select c1 from inner_text);
 
 rollback;  -- to get rid of the bogus operator
 --
+-- Test resolution of hashed vs non-hashed implementation of EXISTS subplan
+--
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Aggregate
+   ->  Seq Scan on tenk1 t
+         Filter: ((hashed SubPlan 2) OR (ten < 0))
+         SubPlan 2
+           ->  Index Only Scan using tenk1_unique1 on tenk1 k
+(5 rows)
+
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+ count 
+-------
+ 10000
+(1 row)
+
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+  and thousand = 1;
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1 t
+         Recheck Cond: (thousand = 1)
+         Filter: ((SubPlan 1) OR (ten < 0))
+         ->  Bitmap Index Scan on tenk1_thous_tenthous
+               Index Cond: (thousand = 1)
+         SubPlan 1
+           ->  Index Only Scan using tenk1_unique1 on tenk1 k
+                 Index Cond: (unique1 = t.unique2)
+(9 rows)
+
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+  and thousand = 1;
+ count 
+-------
+    10
+(1 row)
+
+--
 -- Test case for planner bug with nested EXISTS handling
 --
 select a.thousand from tenk1 a, tenk1 b
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 5de53f2782a..caed1c19ec7 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -1869,9 +1869,7 @@ EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5);
    SubPlan 1
      ->  Index Only Scan using ref_tbl_pkey on ref_tbl r
            Index Cond: (a = b.a)
-   SubPlan 2
-     ->  Seq Scan on ref_tbl r_1
-(7 rows)
+(5 rows)
 
 EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
                         QUERY PLAN                         
@@ -1885,9 +1883,7 @@ EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
    SubPlan 1
      ->  Index Only Scan using ref_tbl_pkey on ref_tbl r_1
            Index Cond: (a = b.a)
-   SubPlan 2
-     ->  Seq Scan on ref_tbl r_2
-(11 rows)
+(9 rows)
 
 DROP TABLE base_tbl, ref_tbl CASCADE;
 NOTICE:  drop cascades to view rw_view1
@@ -2301,8 +2297,8 @@ SELECT * FROM v1 WHERE a=8;
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-                                                        QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
  Update on public.t1
    Update on public.t1
    Update on public.t11 t1_1
@@ -2311,32 +2307,26 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
    ->  Index Scan using t1_a_idx on public.t1
          Output: 100, t1.b, t1.c, t1.ctid
          Index Cond: ((t1.a > 5) AND (t1.a < 7))
-         Filter: ((t1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
+         Filter: ((t1.a <> 6) AND (SubPlan 1) AND snoop(t1.a) AND leakproof(t1.a))
          SubPlan 1
            ->  Append
                  ->  Seq Scan on public.t12 t12_1
                        Filter: (t12_1.a = t1.a)
                  ->  Seq Scan on public.t111 t12_2
                        Filter: (t12_2.a = t1.a)
-         SubPlan 2
-           ->  Append
-                 ->  Seq Scan on public.t12 t12_4
-                       Output: t12_4.a
-                 ->  Seq Scan on public.t111 t12_5
-                       Output: t12_5.a
    ->  Index Scan using t11_a_idx on public.t11 t1_1
          Output: 100, t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
          Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
-         Filter: ((t1_1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+         Filter: ((t1_1.a <> 6) AND (SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
    ->  Index Scan using t12_a_idx on public.t12 t1_2
          Output: 100, t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
          Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
-         Filter: ((t1_2.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+         Filter: ((t1_2.a <> 6) AND (SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
    ->  Index Scan using t111_a_idx on public.t111 t1_3
          Output: 100, t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
          Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
-         Filter: ((t1_3.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-(33 rows)
+         Filter: ((t1_3.a <> 6) AND (SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+(27 rows)
 
 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
 SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
@@ -2351,8 +2341,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
 
 EXPLAIN (VERBOSE, COSTS OFF)
 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-                                               QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
  Update on public.t1
    Update on public.t1
    Update on public.t11 t1_1
@@ -2361,32 +2351,26 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
    ->  Index Scan using t1_a_idx on public.t1
          Output: (t1.a + 1), t1.b, t1.c, t1.ctid
          Index Cond: ((t1.a > 5) AND (t1.a = 8))
-         Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
+         Filter: ((SubPlan 1) AND snoop(t1.a) AND leakproof(t1.a))
          SubPlan 1
            ->  Append
                  ->  Seq Scan on public.t12 t12_1
                        Filter: (t12_1.a = t1.a)
                  ->  Seq Scan on public.t111 t12_2
                        Filter: (t12_2.a = t1.a)
-         SubPlan 2
-           ->  Append
-                 ->  Seq Scan on public.t12 t12_4
-                       Output: t12_4.a
-                 ->  Seq Scan on public.t111 t12_5
-                       Output: t12_5.a
    ->  Index Scan using t11_a_idx on public.t11 t1_1
          Output: (t1_1.a + 1), t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
          Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
-         Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+         Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
    ->  Index Scan using t12_a_idx on public.t12 t1_2
          Output: (t1_2.a + 1), t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
          Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
-         Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+         Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
    ->  Index Scan using t111_a_idx on public.t111 t1_3
          Output: (t1_3.a + 1), t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
          Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
-         Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-(33 rows)
+         Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+(27 rows)
 
 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
 NOTICE:  snooped value: 8
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index cce8ebdb3d9..a25cb6fc5c5 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -510,6 +510,23 @@ select * from int8_tbl where q1 in (select c1 from inner_text);
 rollback;  -- to get rid of the bogus operator
 
 --
+-- Test resolution of hashed vs non-hashed implementation of EXISTS subplan
+--
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
+
+explain (costs off)
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+  and thousand = 1;
+select count(*) from tenk1 t
+where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
+  and thousand = 1;
+
+--
 -- Test case for planner bug with nested EXISTS handling
 --
 select a.thousand from tenk1 a, tenk1 b