• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision4600e5e5a3ebe1cd6fc4385e6f003bfad87e3842 (tree)
Time2014-09-18 11:59:59
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-9.1.out
+++ b/expected/ut-S-9.1.out
@@ -4847,7 +4847,7 @@ error hint:
48474847 (30 rows)
48484848
48494849 ----
4850----- No. S-3-8 inheritance table select type
4850+---- No. S-3-8 inheritance table select/update type
48514851 ----
48524852 -- No. S-3-8-1
48534853 EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
@@ -4903,6 +4903,99 @@ error hint:
49034903 Index Cond: (c1 = 1)
49044904 (6 rows)
49054905
4906+-- No. S-3-8-3
4907+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
4908+ QUERY PLAN
4909+--------------------------
4910+ Update on p1
4911+ -> Seq Scan on p1
4912+ Filter: (c1 = 1)
4913+(3 rows)
4914+
4915+/*+IndexScan(p1)*/
4916+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
4917+LOG: pg_hint_plan:
4918+used hint:
4919+IndexScan(p1)
4920+not used hint:
4921+duplication hint:
4922+error hint:
4923+
4924+ QUERY PLAN
4925+-----------------------------------
4926+ Update on p1
4927+ -> Index Scan using p1_i on p1
4928+ Index Cond: (c1 = 1)
4929+(3 rows)
4930+
4931+/*+IndexScan(p1 p1_pkey)*/
4932+EXPLAIN (COSTS false) UPDATE ONLY s1.p1 SET c4 = c4 WHERE c1 = 1;
4933+LOG: available indexes for IndexScan(p1): p1_pkey
4934+LOG: pg_hint_plan:
4935+used hint:
4936+IndexScan(p1 p1_pkey)
4937+not used hint:
4938+duplication hint:
4939+error hint:
4940+
4941+ QUERY PLAN
4942+--------------------------------------
4943+ Update on p1
4944+ -> Index Scan using p1_pkey on p1
4945+ Index Cond: (c1 = 1)
4946+(3 rows)
4947+
4948+-- No. S-3-8-4
4949+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
4950+ QUERY PLAN
4951+---------------------------
4952+ Update on p1
4953+ -> Seq Scan on p1
4954+ Filter: (c1 = 1)
4955+ -> Seq Scan on p1c1 p1
4956+ Filter: (c1 = 1)
4957+(5 rows)
4958+
4959+/*+IndexScan(p1)*/
4960+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
4961+LOG: pg_hint_plan:
4962+used hint:
4963+IndexScan(p1)
4964+not used hint:
4965+duplication hint:
4966+error hint:
4967+
4968+ QUERY PLAN
4969+------------------------------------------
4970+ Update on p1
4971+ -> Index Scan using p1_i on p1
4972+ Index Cond: (c1 = 1)
4973+ -> Index Scan using p1c1_i on p1c1 p1
4974+ Index Cond: (c1 = 1)
4975+(5 rows)
4976+
4977+/*+IndexScan(p1 p1_pkey)*/
4978+EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
4979+LOG: available indexes for IndexScan(p1): p1_pkey
4980+LOG: available indexes for IndexScan(p1c1): p1c1_pkey
4981+LOG: available indexes for IndexScan(p1c2): p1c2_pkey
4982+LOG: available indexes for IndexScan(p1c3): p1c3_pkey
4983+LOG: pg_hint_plan:
4984+used hint:
4985+IndexScan(p1 p1_pkey)
4986+not used hint:
4987+duplication hint:
4988+error hint:
4989+
4990+ QUERY PLAN
4991+---------------------------------------------
4992+ Update on p1
4993+ -> Index Scan using p1_pkey on p1
4994+ Index Cond: (c1 = 1)
4995+ -> Index Scan using p1c1_pkey on p1c1 p1
4996+ Index Cond: (c1 = 1)
4997+(5 rows)
4998+
49064999 ----
49075000 ---- No. S-3-9 inheritance table number
49085001 ----
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -2345,7 +2345,7 @@ standard_planner_proc:
23452345 * Return scan method hint which matches given aliasname.
23462346 */
23472347 static ScanMethodHint *
2348-find_scan_hint(PlannerInfo *root, RelOptInfo *rel)
2348+find_scan_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
23492349 {
23502350 RangeTblEntry *rte;
23512351 int i;
@@ -2355,10 +2355,10 @@ find_scan_hint(PlannerInfo *root, RelOptInfo *rel)
23552355 * - not a base relation
23562356 * - not an ordinary relation (such as join and subquery)
23572357 */
2358- if (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION)
2358+ if (rel && (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION))
23592359 return NULL;
23602360
2361- rte = root->simple_rte_array[rel->relid];
2361+ rte = root->simple_rte_array[relid];
23622362
23632363 /* We can't force scan method of foreign tables */
23642364 if (rte->relkind == RELKIND_FOREIGN_TABLE)
@@ -2748,7 +2748,7 @@ static void
27482748 pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
27492749 bool inhparent, RelOptInfo *rel)
27502750 {
2751- ScanMethodHint *hint;
2751+ ScanMethodHint *hint = NULL;
27522752
27532753 if (prev_get_relation_info)
27542754 (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
@@ -2764,17 +2764,114 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
27642764 {
27652765 /* store does relids of parent table. */
27662766 current_hint->parent_relid = rel->relid;
2767- current_hint->parent_rel_oid = relationObjectId;
2767+
2768+ }
2769+ else
2770+ {
2771+ /*
2772+ * Inheritance planner doesn't request information for the parent
2773+ * relation so we should check if this relation has a parent. We can
2774+ * ignore nested inheritace case because inheritance planner doesn't
2775+ * meet it.
2776+ */
2777+ ListCell *l;
2778+ foreach (l, root->append_rel_list)
2779+ {
2780+ AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
2781+
2782+ if (appinfo->child_relid == rel->relid)
2783+ {
2784+ if (current_hint->parent_relid != appinfo->parent_relid)
2785+ {
2786+ inhparent = true;
2787+ current_hint->parent_relid = appinfo->parent_relid;
2788+ }
2789+ break;
2790+ }
2791+ }
2792+
2793+ }
2794+
2795+ if (inhparent)
2796+ {
2797+ Relation parent_rel;
2798+ List *indexoidlist;
2799+ ListCell *l;
2800+ Oid parentrel_oid;
2801+
2802+ /*
2803+ * Get and apply the hint for theparent_rel if the new parent has been
2804+ * found. This relation should be an ordinary relation so calling
2805+ * find_scan_hint with rel == NULL is safe.
2806+ */
2807+ if ((hint = find_scan_hint(root, current_hint->parent_relid,
2808+ NULL)) == NULL)
2809+ {
2810+ set_scan_config_options(current_hint->init_scan_mask,
2811+ current_hint->context);
2812+ }
2813+ else
2814+ {
2815+ set_scan_config_options(hint->enforce_mask, current_hint->context);
2816+ hint->base.state = HINT_STATE_USED;
2817+ }
2818+
2819+ current_hint->parent_hint = hint;
2820+
2821+ /* Resolve index name mask (if any) using this parent. */
2822+ if (hint && hint->indexnames)
2823+ {
2824+ parentrel_oid =
2825+ root->simple_rte_array[current_hint->parent_relid]->relid;
2826+ parent_rel = heap_open(parentrel_oid, NoLock);
2827+
2828+ /*
2829+ * Search for indexes match the hint for this parent
2830+ */
2831+ indexoidlist = RelationGetIndexList(parent_rel);
2832+
2833+ foreach(l, indexoidlist)
2834+ {
2835+ Oid indexoid = lfirst_oid(l);
2836+ char *indexname = get_rel_name(indexoid);
2837+ bool use_index = false;
2838+ ListCell *lc;
2839+ ParentIndexInfo *parent_index_info;
2840+
2841+ foreach(lc, hint->indexnames)
2842+ {
2843+ if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
2844+ {
2845+ use_index = true;
2846+ break;
2847+ }
2848+ }
2849+ if (!use_index)
2850+ continue;
2851+
2852+ parent_index_info = get_parent_index_info(indexoid,
2853+ parentrel_oid);
2854+ current_hint->parent_index_infos =
2855+ lappend(current_hint->parent_index_infos, parent_index_info);
2856+ }
2857+ heap_close(parent_rel, NoLock);
2858+ }
2859+
2860+ if (current_hint->parent_relid == rel->relid)
2861+ {
2862+ /* This rel is a inheritance parent, which won't be scanned. */
2863+ return;
2864+ }
27682865 }
2769- else if (current_hint->parent_relid != 0)
2866+
2867+ if (current_hint->parent_hint != 0)
27702868 {
27712869 /*
2772- * We use the same GUC parameter if this table is the child table of a
2773- * table called pg_hint_plan_get_relation_info just before that.
2870+ * If inheritance parent is registered, check if it is really my
2871+ * parent.
27742872 */
27752873 ListCell *l;
27762874
2777- /* append_rel_list contains all append rels; ignore others */
27782875 foreach(l, root->append_rel_list)
27792876 {
27802877 AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
@@ -2787,21 +2884,19 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
27872884 delete_indexes(current_hint->parent_hint, rel,
27882885 relationObjectId);
27892886
2887+ /* Scan fixation status is the same to the parent. */
27902888 return;
27912889 }
27922890 }
27932891
2794- /* This rel is not inherit table. */
2892+ /* This rel is not a child of the current parent. */
27952893 current_hint->parent_relid = 0;
27962894 current_hint->parent_rel_oid = InvalidOid;
27972895 current_hint->parent_hint = NULL;
27982896 }
27992897
2800- /*
2801- * If scan method hint was given, reset GUC parameters which control
2802- * planner behavior about choosing scan methods.
2803- */
2804- if ((hint = find_scan_hint(root, rel)) == NULL)
2898+ /* This table doesn't have a parent. Apply its own hints */
2899+ if ((hint = find_scan_hint(root, rel->relid, rel)) == NULL)
28052900 {
28062901 set_scan_config_options(current_hint->init_scan_mask,
28072902 current_hint->context);
@@ -2810,45 +2905,7 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
28102905 set_scan_config_options(hint->enforce_mask, current_hint->context);
28112906 hint->base.state = HINT_STATE_USED;
28122907
2813- if (inhparent)
2814- {
2815- Relation relation;
2816- List *indexoidlist;
2817- ListCell *l;
2818-
2819- current_hint->parent_hint = hint;
2820-
2821- relation = heap_open(relationObjectId, NoLock);
2822- indexoidlist = RelationGetIndexList(relation);
2823-
2824- foreach(l, indexoidlist)
2825- {
2826- Oid indexoid = lfirst_oid(l);
2827- char *indexname = get_rel_name(indexoid);
2828- bool use_index = false;
2829- ListCell *lc;
2830- ParentIndexInfo *parent_index_info;
2831-
2832- foreach(lc, hint->indexnames)
2833- {
2834- if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
2835- {
2836- use_index = true;
2837- break;
2838- }
2839- }
2840- if (!use_index)
2841- continue;
2842-
2843- parent_index_info = get_parent_index_info(indexoid,
2844- relationObjectId);
2845- current_hint->parent_index_infos =
2846- lappend(current_hint->parent_index_infos, parent_index_info);
2847- }
2848- heap_close(relation, NoLock);
2849- }
2850- else
2851- delete_indexes(hint, rel, InvalidOid);
2908+ delete_indexes(hint, rel, InvalidOid);
28522909 }
28532910
28542911 /*
@@ -3317,7 +3374,7 @@ rebuild_scan_path(HintState *hstate, PlannerInfo *root, int level,
33173374 * planner if scan method hint is not specified, otherwise use
33183375 * specified hints and mark the hint as used.
33193376 */
3320- if ((hint = find_scan_hint(root, rel)) == NULL)
3377+ if ((hint = find_scan_hint(root, rel->relid, rel)) == NULL)
33213378 set_scan_config_options(hstate->init_scan_mask,
33223379 hstate->context);
33233380 else
@@ -3403,7 +3460,7 @@ add_paths_to_joinrel_wrapper(PlannerInfo *root,
34033460 JoinMethodHint *join_hint;
34043461 int save_nestlevel;
34053462
3406- if ((scan_hint = find_scan_hint(root, innerrel)) != NULL)
3463+ if ((scan_hint = find_scan_hint(root, innerrel->relid, innerrel)) != NULL)
34073464 {
34083465 set_scan_config_options(scan_hint->enforce_mask, current_hint->context);
34093466 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,21 @@ 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+
937+
923938 ----
924939 ---- No. S-3-9 inheritance table number
925940 ----
Show on old repository browser