From: Kyotaro Horiguchi Date: Tue, 4 Dec 2018 04:19:57 +0000 (+0900) Subject: Fix for union-on-inheritance case X-Git-Tag: REL96_1_2_5~10 X-Git-Url: http://git.osdn.net/view?p=pghintplan%2Fpg_hint_plan.git;a=commitdiff_plain;h=3851f4221bc7c3a6f762efd4b40042b179ed614e 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 --- 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 --- 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 --- 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 --