OSDN -- 오픈 소스 소프트웨어 개발 및 다운로드
Translation Status of 한국말 translation status for ko
  • R/O
  • HTTP
  • SSH
  • HTTPS
Fork

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisiona284f79d3f9a0ea76c74d15e42f13704a7db089e (tree)
Time2014-09-18 11:58:11
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Fix a bug that index restriction by name doesn't work for UPDATEs on
inheritance parent.

Inheritance planner doesn't claim for the inheritance parent relation,
so pg_hint_plan_get_relation_info cannot have the chance to prepare
the index spec information for chlid relations. This change make the
function to try to find parent relation even if it is called for the
children with inhparent == false from the first.

Change Summary

Incremental Difference

--- a/expected/ut-S.out
+++ b/expected/ut-S.out
@@ -4948,7 +4948,7 @@ error hint:
49484948 (30 rows)
49494949
49504950 ----
4951----- No. S-3-8 inheritance table select type
4951+---- No. S-3-8 inheritance table select/update type
49524952 ----
49534953 -- No. S-3-8-1
49544954 EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
@@ -5004,6 +5004,99 @@ error hint:
50045004 Index Cond: (c1 = 1)
50055005 (6 rows)
50065006
5007+-- No. S-3-8-3
5008+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
5009+ QUERY PLAN
5010+--------------------------
5011+ Update on p1
5012+ -> Seq Scan on p1
5013+ Filter: (c1 = 1)
5014+(3 rows)
5015+
5016+/*+IndexScan(p1)*/
5017+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
5018+LOG: pg_hint_plan:
5019+used hint:
5020+IndexScan(p1)
5021+not used hint:
5022+duplication hint:
5023+error hint:
5024+
5025+ QUERY PLAN
5026+-----------------------------------
5027+ Update on p1
5028+ -> Index Scan using p1_i on p1
5029+ Index Cond: (c1 = 1)
5030+(3 rows)
5031+
5032+/*+IndexScan(p1 p1_pkey)*/
5033+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
5034+LOG: available indexes for IndexScan(p1): p1_pkey
5035+LOG: pg_hint_plan:
5036+used hint:
5037+IndexScan(p1 p1_pkey)
5038+not used hint:
5039+duplication hint:
5040+error hint:
5041+
5042+ QUERY PLAN
5043+--------------------------------------
5044+ Update on p1
5045+ -> Index Scan using p1_pkey on p1
5046+ Index Cond: (c1 = 1)
5047+(3 rows)
5048+
5049+-- No. S-3-8-4
5050+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
5051+ QUERY PLAN
5052+---------------------------
5053+ Update on p1
5054+ -> Seq Scan on p1
5055+ Filter: (c1 = 1)
5056+ -> Seq Scan on p1c1 p1
5057+ Filter: (c1 = 1)
5058+(5 rows)
5059+
5060+/*+IndexScan(p1)*/
5061+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
5062+LOG: pg_hint_plan:
5063+used hint:
5064+IndexScan(p1)
5065+not used hint:
5066+duplication hint:
5067+error hint:
5068+
5069+ QUERY PLAN
5070+------------------------------------------
5071+ Update on p1
5072+ -> Index Scan using p1_i on p1
5073+ Index Cond: (c1 = 1)
5074+ -> Index Scan using p1c1_i on p1c1 p1
5075+ Index Cond: (c1 = 1)
5076+(5 rows)
5077+
5078+/*+IndexScan(p1 p1_pkey)*/
5079+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
5080+LOG: available indexes for IndexScan(p1): p1_pkey
5081+LOG: available indexes for IndexScan(p1c1): p1c1_pkey
5082+LOG: available indexes for IndexScan(p1c2): p1c2_pkey
5083+LOG: available indexes for IndexScan(p1c3): p1c3_pkey
5084+LOG: pg_hint_plan:
5085+used hint:
5086+IndexScan(p1 p1_pkey)
5087+not used hint:
5088+duplication hint:
5089+error hint:
5090+
5091+ QUERY PLAN
5092+---------------------------------------------
5093+ Update on p1
5094+ -> Index Scan using p1_pkey on p1
5095+ Index Cond: (c1 = 1)
5096+ -> Index Scan using p1c1_pkey on p1c1 p1
5097+ Index Cond: (c1 = 1)
5098+(5 rows)
5099+
50075100 ----
50085101 ---- No. S-3-9 inheritance table number
50095102 ----
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -2520,7 +2520,7 @@ standard_planner_proc:
25202520 * Return scan method hint which matches given aliasname.
25212521 */
25222522 static ScanMethodHint *
2523-find_scan_hint(PlannerInfo *root, RelOptInfo *rel)
2523+find_scan_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
25242524 {
25252525 RangeTblEntry *rte;
25262526 int i;
@@ -2530,10 +2530,10 @@ find_scan_hint(PlannerInfo *root, RelOptInfo *rel)
25302530 * - not a base relation
25312531 * - not an ordinary relation (such as join and subquery)
25322532 */
2533- if (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION)
2533+ if (rel && (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION))
25342534 return NULL;
25352535
2536- rte = root->simple_rte_array[rel->relid];
2536+ rte = root->simple_rte_array[relid];
25372537
25382538 /* We can't force scan method of foreign tables */
25392539 if (rte->relkind == RELKIND_FOREIGN_TABLE)
@@ -2923,105 +2923,134 @@ static void
29232923 pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
29242924 bool inhparent, RelOptInfo *rel)
29252925 {
2926- ScanMethodHint *hint;
2926+ ScanMethodHint *hint = NULL;
2927+ ListCell *l;
2928+ Index new_parent_relid = 0;
29272929
29282930 if (prev_get_relation_info)
29292931 (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
29302932
29312933 /*
2932- * Do nothing if we don't have valid hint in this context or current
2933- * nesting depth is nesting depth of SPI calls.
2934+ * Do nothing if we don't have a valid hint in this context or current
2935+ * nesting depth is at SPI calls.
29342936 */
29352937 if (!current_hint || nested_level > 0)
29362938 return;
29372939
2940+ /*
2941+ * We could register the parent relation of the following children here
2942+ * when inhparent == true but inheritnce planner doesn't request
2943+ * information for inheritance parents. We also cannot distinguish the
2944+ * caller so we should always find the parents without this function being
2945+ * called for them.
2946+ */
29382947 if (inhparent)
2948+ return;
2949+
2950+ /* Find the parent for this relation */
2951+ foreach (l, root->append_rel_list)
2952+ {
2953+ AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
2954+
2955+ if (appinfo->child_relid == rel->relid)
2956+ {
2957+ if (current_hint->parent_relid != appinfo->parent_relid)
2958+ new_parent_relid = appinfo->parent_relid;
2959+ break;
2960+ }
2961+ }
2962+
2963+ if (!l)
29392964 {
2940- /* store does relids of parent table. */
2941- current_hint->parent_relid = rel->relid;
2965+ /* This relation doesn't have a parent. Cancel current_hint. */
2966+ current_hint->parent_relid = 0;
2967+ current_hint->parent_hint = NULL;
29422968 }
2943- else if (current_hint->parent_relid != 0)
2969+
2970+ if (new_parent_relid > 0)
29442971 {
29452972 /*
2946- * We use the same GUC parameter if this table is the child table of a
2947- * table called pg_hint_plan_get_relation_info just before that.
2973+ * Here we found a parent relation different from the remembered one.
2974+ * Remember it, apply the scan mask of it and then resolve the index
2975+ * restriction in order to be used by its children.
29482976 */
2949- ListCell *l;
2977+ int scanmask = current_hint->init_scan_mask;
2978+ ScanMethodHint *parent_hint;
29502979
2951- /* append_rel_list contains all append rels; ignore others */
2952- foreach(l, root->append_rel_list)
2980+ current_hint->parent_relid = new_parent_relid;
2981+
2982+ /*
2983+ * Get and apply the hint for the new parent relation. It should be an
2984+ * ordinary relation so calling find_scan_hint with rel == NULL is
2985+ * safe.
2986+ */
2987+ current_hint->parent_hint = parent_hint =
2988+ find_scan_hint(root, current_hint->parent_relid, NULL);
2989+
2990+ if (parent_hint)
29532991 {
2954- AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
2992+ scanmask = current_hint->parent_hint->enforce_mask;
2993+ parent_hint->base.state = HINT_STATE_USED;
29552994
2956- /* This rel is child table. */
2957- if (appinfo->parent_relid == current_hint->parent_relid &&
2958- appinfo->child_relid == rel->relid)
2995+ /* Resolve index name mask (if any) using the parent. */
2996+ if (parent_hint->indexnames)
29592997 {
2960- if (current_hint->parent_hint)
2961- delete_indexes(current_hint->parent_hint, rel,
2962- relationObjectId);
2998+ Oid parentrel_oid;
2999+ Relation parent_rel;
29633000
2964- return;
3001+ parentrel_oid =
3002+ root->simple_rte_array[current_hint->parent_relid]->relid;
3003+ parent_rel = heap_open(parentrel_oid, NoLock);
3004+
3005+ /* Search the parent relation for indexes match the hint spec */
3006+ foreach(l, RelationGetIndexList(parent_rel))
3007+ {
3008+ Oid indexoid = lfirst_oid(l);
3009+ char *indexname = get_rel_name(indexoid);
3010+ ListCell *lc;
3011+ ParentIndexInfo *parent_index_info;
3012+
3013+ foreach(lc, parent_hint->indexnames)
3014+ {
3015+ if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
3016+ break;
3017+ }
3018+ if (!lc)
3019+ continue;
3020+
3021+ parent_index_info =
3022+ get_parent_index_info(indexoid, parentrel_oid);
3023+ current_hint->parent_index_infos =
3024+ lappend(current_hint->parent_index_infos, parent_index_info);
3025+ }
3026+ heap_close(parent_rel, NoLock);
29653027 }
29663028 }
2967-
2968- /* This rel is not inherit table. */
2969- current_hint->parent_relid = 0;
2970- current_hint->parent_hint = NULL;
3029+
3030+ set_scan_config_options(scanmask, current_hint->context);
29713031 }
29723032
2973- /*
2974- * If scan method hint was given, reset GUC parameters which control
2975- * planner behavior about choosing scan methods.
2976- */
2977- if ((hint = find_scan_hint(root, rel)) == NULL)
3033+ if (current_hint->parent_hint != 0)
29783034 {
2979- set_scan_config_options(current_hint->init_scan_mask,
2980- current_hint->context);
3035+ delete_indexes(current_hint->parent_hint, rel,
3036+ relationObjectId);
3037+
3038+ /* Scan fixation status is the same to the parent. */
29813039 return;
29823040 }
2983- set_scan_config_options(hint->enforce_mask, current_hint->context);
2984- hint->base.state = HINT_STATE_USED;
29853041
2986- if (inhparent)
3042+ /* This table doesn't have a parent hint. Apply its own hint if any. */
3043+ if ((hint = find_scan_hint(root, rel->relid, rel)) != NULL)
29873044 {
2988- Relation relation;
2989- List *indexoidlist;
2990- ListCell *l;
2991-
2992- current_hint->parent_hint = hint;
2993-
2994- relation = heap_open(relationObjectId, NoLock);
2995- indexoidlist = RelationGetIndexList(relation);
2996-
2997- foreach(l, indexoidlist)
2998- {
2999- Oid indexoid = lfirst_oid(l);
3000- char *indexname = get_rel_name(indexoid);
3001- bool use_index = false;
3002- ListCell *lc;
3003- ParentIndexInfo *parent_index_info;
3004-
3005- foreach(lc, hint->indexnames)
3006- {
3007- if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
3008- {
3009- use_index = true;
3010- break;
3011- }
3012- }
3013- if (!use_index)
3014- continue;
3045+ set_scan_config_options(hint->enforce_mask, current_hint->context);
3046+ hint->base.state = HINT_STATE_USED;
30153047
3016- parent_index_info = get_parent_index_info(indexoid,
3017- relationObjectId);
3018- current_hint->parent_index_infos =
3019- lappend(current_hint->parent_index_infos, parent_index_info);
3020- }
3021- heap_close(relation, NoLock);
3048+ delete_indexes(hint, rel, InvalidOid);
30223049 }
30233050 else
3024- delete_indexes(hint, rel, InvalidOid);
3051+ set_scan_config_options(current_hint->init_scan_mask,
3052+ current_hint->context);
3053+ return;
30253054 }
30263055
30273056 /*
@@ -3515,7 +3544,7 @@ rebuild_scan_path(HintState *hstate, PlannerInfo *root, int level,
35153544 * planner if scan method hint is not specified, otherwise use
35163545 * specified hints and mark the hint as used.
35173546 */
3518- if ((hint = find_scan_hint(root, rel)) == NULL)
3547+ if ((hint = find_scan_hint(root, rel->relid, rel)) == NULL)
35193548 set_scan_config_options(hstate->init_scan_mask,
35203549 hstate->context);
35213550 else
@@ -3601,7 +3630,7 @@ add_paths_to_joinrel_wrapper(PlannerInfo *root,
36013630 JoinMethodHint *join_hint;
36023631 int save_nestlevel;
36033632
3604- if ((scan_hint = find_scan_hint(root, innerrel)) != NULL)
3633+ if ((scan_hint = find_scan_hint(root, innerrel->relid, innerrel)) != NULL)
36053634 {
36063635 set_scan_config_options(scan_hint->enforce_mask, current_hint->context);
36073636 scan_hint->base.state = HINT_STATE_USED;
--- a/sql/ut-S.sql
+++ b/sql/ut-S.sql
@@ -907,7 +907,7 @@ SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
907907 SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
908908
909909 ----
910----- No. S-3-8 inheritance table select type
910+---- No. S-3-8 inheritance table select/update type
911911 ----
912912
913913 -- No. S-3-8-1
@@ -920,6 +920,20 @@ EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
920920 /*+IndexScan(p1)*/
921921 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
922922
923+-- No. S-3-8-3
924+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
925+/*+IndexScan(p1)*/
926+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
927+/*+IndexScan(p1 p1_pkey)*/
928+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
929+
930+-- No. S-3-8-4
931+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
932+/*+IndexScan(p1)*/
933+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
934+/*+IndexScan(p1 p1_pkey)*/
935+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
936+
923937 ----
924938 ---- No. S-3-9 inheritance table number
925939 ----
Show on old repository browser