Fix for union-on-inheritance case

setup_hint_enforcement may pick up a wrong hint when multiple
subqueries access inheritance tables. This leads to failure to apply
hints on other than the first subquery.

diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out
index 8250bb3..bfdc9c8 100644 (file)
--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -8342,6 +8342,384 @@ SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = '
  p1_c2   |        0 |        2
 (2 rows)
 
+-- Subqueries on inheritance tables under UNION
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Append
+         ->  Seq Scan on p1
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c1
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c2
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c3
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c4
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c1_c1
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c1_c2
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c3_c1
+               Filter: (val < 1000)
+         ->  Seq Scan on p1_c3_c2
+               Filter: (val < 1000)
+   ->  Result
+         ->  Append
+               ->  Seq Scan on p2
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c1
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c2
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c3
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c4
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c1_c1
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c1_c2
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c3_c1
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c3_c2
+                     Filter: (id < 1000)
+(40 rows)
+
+/*+ IndexScan(p1 p1_val2) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG:  available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
+LOG:  available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
+LOG:  available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
+LOG:  available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
+LOG:  available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
+LOG:  available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
+LOG:  available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
+LOG:  available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
+LOG:  available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(p1 p1_val2)
+not used hint:
+duplication hint:
+error hint:
+
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Append
+   ->  Append
+         ->  Index Scan using p1_val3 on p1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c1_val3 on p1_c1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c2_val3 on p1_c2
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c3_val3 on p1_c3
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c4_val3 on p1_c4
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c1_c1_val3 on p1_c1_c1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c1_c2_val3 on p1_c1_c2
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c3_c1_val3 on p1_c3_c1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c3_c2_val3 on p1_c3_c2
+               Index Cond: (val < 1000)
+   ->  Result
+         ->  Append
+               ->  Seq Scan on p2
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c1
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c2
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c3
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c4
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c1_c1
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c1_c2
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c3_c1
+                     Filter: (id < 1000)
+               ->  Seq Scan on p2_c3_c2
+                     Filter: (id < 1000)
+(40 rows)
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG:  available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
+LOG:  available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
+LOG:  available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
+LOG:  available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
+LOG:  available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
+LOG:  available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
+LOG:  available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
+LOG:  available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
+LOG:  available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
+LOG:  available indexes for IndexScan(p2): p2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
+LOG:  available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(p1 p1_val2)
+IndexScan(p2 p2_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Append
+   ->  Append
+         ->  Index Scan using p1_val3 on p1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c1_val3 on p1_c1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c2_val3 on p1_c2
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c3_val3 on p1_c3
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c4_val3 on p1_c4
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c1_c1_val3 on p1_c1_c1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c1_c2_val3 on p1_c1_c2
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c3_c1_val3 on p1_c3_c1
+               Index Cond: (val < 1000)
+         ->  Index Scan using p1_c3_c2_val3 on p1_c3_c2
+               Index Cond: (val < 1000)
+   ->  Result
+         ->  Append
+               ->  Index Scan using p2_id_val_idx on p2
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c1_id_val_idx on p2_c1
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c2_id_val_idx on p2_c2
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c3_id_val_idx on p2_c3
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c4_id_val_idx on p2_c4
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
+                     Index Cond: (id < 1000)
+               ->  Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
+                     Index Cond: (id < 1000)
+(40 rows)
+
+-- union all case
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+                  QUERY PLAN                   
+-----------------------------------------------
+ HashAggregate
+   Group Key: p1.val
+   ->  Append
+         ->  Append
+               ->  Seq Scan on p1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c2
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c3
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c4
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c1_c1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c1_c2
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c3_c1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c3_c2
+                     Filter: (val < 1000)
+         ->  Result
+               ->  Append
+                     ->  Seq Scan on p2
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c1
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c2
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c3
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c4
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c1_c1
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c1_c2
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c3_c1
+                           Filter: (id < 1000)
+                     ->  Seq Scan on p2_c3_c2
+                           Filter: (id < 1000)
+(42 rows)
+
+/*+ IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG:  available indexes for IndexScan(p2): p2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
+LOG:  available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(p2 p2_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ HashAggregate
+   Group Key: p1.val
+   ->  Append
+         ->  Append
+               ->  Seq Scan on p1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c2
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c3
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c4
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c1_c1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c1_c2
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c3_c1
+                     Filter: (val < 1000)
+               ->  Seq Scan on p1_c3_c2
+                     Filter: (val < 1000)
+         ->  Result
+               ->  Append
+                     ->  Index Scan using p2_id_val_idx on p2
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c1_id_val_idx on p2_c1
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c2_id_val_idx on p2_c2
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c3_id_val_idx on p2_c3
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c4_id_val_idx on p2_c4
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
+                           Index Cond: (id < 1000)
+(42 rows)
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+LOG:  available indexes for IndexScan(p2): p2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
+LOG:  available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
+LOG:  available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
+LOG:  available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
+LOG:  available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
+LOG:  available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
+LOG:  available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
+LOG:  available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
+LOG:  available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
+LOG:  available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
+LOG:  available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
+LOG:  available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
+LOG:  pg_hint_plan:
+used hint:
+IndexScan(p1 p1_val2)
+IndexScan(p2 p2_id_val_idx)
+not used hint:
+duplication hint:
+error hint:
+
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ HashAggregate
+   Group Key: p1.val
+   ->  Append
+         ->  Append
+               ->  Index Scan using p1_val3 on p1
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c1_val3 on p1_c1
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c2_val3 on p1_c2
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c3_val3 on p1_c3
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c4_val3 on p1_c4
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c1_c1_val3 on p1_c1_c1
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c1_c2_val3 on p1_c1_c2
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c3_c1_val3 on p1_c3_c1
+                     Index Cond: (val < 1000)
+               ->  Index Scan using p1_c3_c2_val3 on p1_c3_c2
+                     Index Cond: (val < 1000)
+         ->  Result
+               ->  Append
+                     ->  Index Scan using p2_id_val_idx on p2
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c1_id_val_idx on p2_c1
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c2_id_val_idx on p2_c2
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c3_id_val_idx on p2_c3
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c4_id_val_idx on p2_c4
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
+                           Index Cond: (id < 1000)
+                     ->  Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
+                           Index Cond: (id < 1000)
+(42 rows)
+
 --
 -- Rows hint tests
 --

diff --git a/pg_hint_plan.c b/pg_hint_plan.c
index c139c06..f32be0c 100644 (file)
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -348,6 +348,7 @@ struct HintState
        int                             init_paratup_cost;      /* parallel_tuple_cost */
        int                             init_parasetup_cost;/* parallel_setup_cost */
 
+       PlannerInfo        *current_root;               /* PlannerInfo for the followings */
        Index                   parent_relid;           /* inherit parent of table relid */
        ScanMethodHint *parent_scan_hint;       /* scan hint for the parent */
        ParallelHint   *parent_parallel_hint; /* parallel hint for the parent */
@@ -965,6 +966,7 @@ HintStateCreate(void)
        hstate->init_min_para_size = 0;
        hstate->init_paratup_cost = 0;
        hstate->init_parasetup_cost = 0;
+       hstate->current_root = NULL;
        hstate->parent_relid = 0;
        hstate->parent_scan_hint = NULL;
        hstate->parent_parallel_hint = NULL;
@@ -3627,6 +3629,10 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel,
                return 0;
        }
 
+       /* Forget about the parent of another subquery */
+       if (root != current_hint_state->current_root)
+               current_hint_state->parent_relid = 0;
+
        /* Find the parent for this relation other than the registered parent */
        foreach (l, root->append_rel_list)
        {
@@ -3635,7 +3641,10 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel,
                if (appinfo->child_relid == rel->relid)
                {
                        if (current_hint_state->parent_relid != appinfo->parent_relid)
+                       {
                                new_parent_relid = appinfo->parent_relid;
+                               current_hint_state->current_root = root;
+                       }
                        break;
                }
        }

diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql
index df19bf7..1afa2da 100644 (file)
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -1004,6 +1004,36 @@ SELECT pg_sleep(1);
 -- the index scan happened while planning.
 SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
 
+-- Subqueries on inheritance tables under UNION
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION ALL
+SELECT val::int FROM p2 WHERE id < 1000;
+
+-- union all case
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
+UNION
+SELECT val::int FROM p2 WHERE id < 1000;
+
 --
 -- Rows hint tests
 --