Support PostgreSQL 14

[pghintplan/pg_hint_plan.git] / expected / ut-A.out

diff --git a/expected/ut-A.out b/expected/ut-A.out
index d5f7dff..e2a7985 100644 (file)
--- a/expected/ut-A.out
+++ b/expected/ut-A.out
@@ -3239,7 +3239,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3257,13 +3256,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3274,7 +3276,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 SET pg_hint_plan.parse_messages TO error;
 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
@@ -3295,7 +3297,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3313,13 +3314,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3330,7 +3334,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -3370,7 +3374,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3388,13 +3391,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3405,7 +3411,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 SET pg_hint_plan.parse_messages TO error;
 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
@@ -3426,7 +3432,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3444,13 +3449,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3461,7 +3469,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 EXPLAIN (COSTS false) EXECUTE p1;
              QUERY PLAN             
@@ -3490,7 +3498,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3508,13 +3515,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3525,7 +3535,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 SET pg_hint_plan.parse_messages TO error;
 EXPLAIN (COSTS false) EXECUTE p2;
@@ -3577,7 +3587,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3595,13 +3604,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3612,7 +3624,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 -- No. A-12-1-4
 -- No. A-12-2-4
@@ -3630,7 +3642,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3648,13 +3659,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3665,7 +3679,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 SET pg_hint_plan.parse_messages TO error;
 EXPLAIN (COSTS false) EXECUTE p2;
@@ -3695,7 +3709,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3713,13 +3726,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | on
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3730,7 +3746,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 DEALLOCATE p1;
 SET pg_hint_plan.parse_messages TO LOG;
@@ -3775,7 +3791,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3793,13 +3808,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | off
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | off
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3810,7 +3828,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -3847,7 +3865,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3865,13 +3882,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | off
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | off
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3882,7 +3902,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
           QUERY PLAN          
@@ -3921,7 +3941,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -3939,13 +3958,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | off
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | off
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -3956,7 +3978,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 BEGIN;
 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
@@ -3996,7 +4018,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -4014,13 +4035,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | off
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | off
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -4031,7 +4055,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
           QUERY PLAN          
@@ -4071,7 +4095,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -4089,13 +4112,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | off
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | off
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -4106,7 +4132,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
@@ -4147,7 +4173,6 @@ SELECT name, setting FROM settings;
  constraint_exclusion           | partition
  cursor_tuple_fraction          | 0.1
  default_statistics_target      | 100
- force_parallel_mode            | off
  from_collapse_limit            | 8
  jit                            | on
  join_collapse_limit            | 8
@@ -4165,13 +4190,16 @@ SELECT name, setting FROM settings;
  parallel_tuple_cost            | 0.1
  random_page_cost               | 4
  seq_page_cost                  | 1
+ enable_async_append            | on
  enable_bitmapscan              | on
  enable_gathermerge             | on
  enable_hashagg                 | on
  enable_hashjoin                | on
+ enable_incremental_sort        | on
  enable_indexonlyscan           | on
  enable_indexscan               | off
  enable_material                | on
+ enable_memoize                 | on
  enable_mergejoin               | off
  enable_nestloop                | on
  enable_parallel_append         | on
@@ -4182,7 +4210,7 @@ SELECT name, setting FROM settings;
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(46 rows)
+(48 rows)
 
 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
           QUERY PLAN          
@@ -4338,6 +4366,9 @@ BEGIN
     RETURN new_cnt;
 END;
 $$ LANGUAGE plpgsql IMMUTABLE;
+-- The function called at the bottom desn't use a hint, the immediate
+-- caller level should restore its own hint. So, the first LOG from
+-- pg_hint_plan should use the IndexScan(t_1) hint
 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
 NOTICE:  nested_planner(5)
 NOTICE:  nested_planner(4)
@@ -4345,7 +4376,12 @@ NOTICE:  nested_planner(3)
 NOTICE:  nested_planner(2)
 NOTICE:  nested_planner(1)
 LOG:  pg_hint_plan:
-no hint
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
 LOG:  pg_hint_plan:
 used hint:
 IndexScan(t_1)
@@ -4372,7 +4408,9 @@ error hint:
  Index Only Scan using t1_i1 on t1 t_1
 (1 row)
 
-/*+SeqScan(t_2)*/
+-- The top level uses SeqScan(t_1), but the function should use only
+-- the hint in the function.
+/*+SeqScan(t_1) SeqScan(t_2)*/
 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
 NOTICE:  nested_planner(5)
 NOTICE:  nested_planner(4)
@@ -4409,15 +4447,18 @@ error hint:
 
 LOG:  pg_hint_plan:
 used hint:
+SeqScan(t_1)
 not used hint:
 SeqScan(t_2)
 duplication hint:
 error hint:
 
-              QUERY PLAN               
----------------------------------------
- Index Only Scan using t1_i1 on t1 t_1
-(1 row)
+        QUERY PLAN        
+--------------------------
+ Sort
+   Sort Key: c1
+   ->  Seq Scan on t1 t_1
+(3 rows)
 
 ----
 ---- No. A-13-4 output of debugging log on hint status
@@ -4429,6 +4470,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
         ORDER BY t_1.c1 LIMIT 1;
 $$ LANGUAGE SQL IMMUTABLE;
 --No.13-4-1
+-- recall_planner() is reduced to constant while planning using the
+-- hint defined in the function. Then the outer query is planned based
+-- on the following hint. pg_hint_plan shows the log for the function
+-- but the resulting explain output doesn't contain the corresponding
+-- plan.
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -4436,7 +4482,7 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 LOG:  pg_hint_plan:
 used hint:
-HashJoin(t_1 t_2)
+IndexScan(t_1)
 not used hint:
 duplication hint:
 error hint:
@@ -4460,6 +4506,7 @@ error hint:
 (7 rows)
 
 --No.13-4-2
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -4467,8 +4514,8 @@ EXPLAIN (COSTS false)
   ORDER BY st_1.c1;
 LOG:  pg_hint_plan:
 used hint:
+IndexScan(t_1)
 not used hint:
-HashJoin(st_1 st_2)
 duplication hint:
 error hint:
 
@@ -4491,6 +4538,7 @@ error hint:
 (7 rows)
 
 --No.13-4-3
+--See description for No.13-4-1
 /*+HashJoin(t_1 t_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 st_1
@@ -4498,7 +4546,7 @@ EXPLAIN (COSTS false)
   ORDER BY st_1.c1;
 LOG:  pg_hint_plan:
 used hint:
-HashJoin(t_1 t_2)
+IndexScan(t_1)
 not used hint:
 duplication hint:
 error hint:
@@ -4521,6 +4569,7 @@ error hint:
 (6 rows)
 
 --No.13-4-4
+--See description for No.13-4-1
 /*+HashJoin(st_1 st_2)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
@@ -4528,8 +4577,8 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 LOG:  pg_hint_plan:
 used hint:
+IndexScan(t_1)
 not used hint:
-HashJoin(st_1 st_2)
 duplication hint:
 error hint:
 
@@ -4551,18 +4600,18 @@ error hint:
 (6 rows)
 
 --No.13-4-5
+-- See description for No.13-4-1. No joins in ths plan, so
+-- pg_hint_plan doesn't complain on the wrongly written error hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
   ORDER BY t_1.c1;
-INFO:  pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
-DETAIL:  Relation name "t_1" is duplicated.
 LOG:  pg_hint_plan:
 used hint:
+IndexScan(t_1)
 not used hint:
 duplication hint:
 error hint:
-HashJoin(t_1 t_1)
 
 LOG:  pg_hint_plan:
 used hint:
@@ -4586,6 +4635,13 @@ EXPLAIN (COSTS false)
  SELECT recall_planner_one_t() FROM s1.t1 t_1
    JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
   ORDER BY t_1.c1;
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
                  QUERY PLAN                  
 ---------------------------------------------
  Merge Join
@@ -4603,8 +4659,8 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 LOG:  pg_hint_plan:
 used hint:
+IndexScan(t_1)
 not used hint:
-HashJoin(t_1 t_1)
 duplication hint:
 error hint:
 
@@ -4630,19 +4686,18 @@ HashJoin(t_1 t_1)
 DROP FUNCTION recall_planner_one_t(int);
 ERROR:  function recall_planner_one_t(integer) does not exist
 --No.13-4-7
+-- See description for No.13-4-1. Complains on the wrongly written hint.
 /*+HashJoin(t_1 t_1)*/
 EXPLAIN (COSTS false)
  SELECT recall_planner() FROM s1.t1 t_1
    JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
   ORDER BY t_1.c1;
-INFO:  pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
-DETAIL:  Relation name "t_1" is duplicated.
 LOG:  pg_hint_plan:
 used hint:
+IndexScan(t_1)
 not used hint:
 duplication hint:
 error hint:
-HashJoin(t_1 t_1)
 
 INFO:  pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
 DETAIL:  Relation name "t_1" is duplicated.
@@ -4671,14 +4726,11 @@ EXPLAIN (COSTS false)
   ORDER BY t_1.c1;
 INFO:  pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
 DETAIL:  Conflict join method hint.
-INFO:  pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
-DETAIL:  Conflict join method hint.
 LOG:  pg_hint_plan:
 used hint:
-HashJoin(t_1 t_2)
+IndexScan(t_1)
 not used hint:
 duplication hint:
-MergeJoin(t_1 t_2)
 error hint:
 
 LOG:  pg_hint_plan:
@@ -4700,3 +4752,87 @@ error hint:
                ->  Seq Scan on t2 t_2
 (7 rows)
 
+--No.14-1-1 plancache invalidation
+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
+CREATE INDEX ON s1.tpc(a);
+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
+EXPLAIN EXECUTE p1;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Seq Scan on tpc  (cost=0.00..17.50 rows=333 width=4)
+   Filter: (a < 999)
+(2 rows)
+
+EXPLAIN EXECUTE p2;
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(tpc)
+not used hint:
+duplication hint:
+error hint:
+
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Index Scan using tpc_a_idx on tpc  (cost=0.28..34.10 rows=333 width=4)
+   Index Cond: (a < 999)
+(2 rows)
+
+EXPLAIN EXECUTE p3(500);
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(tpc)
+not used hint:
+duplication hint:
+error hint:
+
+                     QUERY PLAN                     
+----------------------------------------------------
+ Seq Scan on tpc  (cost=0.00..17.50 rows=5 width=4)
+   Filter: (a = 500)
+(2 rows)
+
+-- The DROP invalidates the plan caches
+DROP TABLE s1.tpc;
+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
+CREATE INDEX ON s1.tpc(a);
+EXPLAIN EXECUTE p1;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Seq Scan on tpc  (cost=0.00..17.50 rows=333 width=4)
+   Filter: (a < 999)
+(2 rows)
+
+EXPLAIN EXECUTE p2;
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(tpc)
+not used hint:
+duplication hint:
+error hint:
+
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Index Scan using tpc_a_idx on tpc  (cost=0.28..34.10 rows=333 width=4)
+   Index Cond: (a < 999)
+(2 rows)
+
+EXPLAIN EXECUTE p3(500);
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(tpc)
+not used hint:
+duplication hint:
+error hint:
+
+                     QUERY PLAN                     
+----------------------------------------------------
+ Seq Scan on tpc  (cost=0.00..17.50 rows=5 width=4)
+   Filter: (a = 500)
+(2 rows)
+
+DEALLOCATE p1;
+DEALLOCATE p2;
+DEALLOCATE p3;
+DROP TABLE s1.tpc;