• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision7cd601eb6e8d0dfa9ce9757626ac2c279698a4c7 (tree)
Time2014-09-05 13:20:04
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Fixed a bug that rows hint can be omitted on some condition.

A hint for higer level may be canceled by unnecessary reestimation
caused by a rows hint for lower level.

During making a joinrel, if there's a rows hint just matches the
joinrelids, and has already been applied on an earlier try for the same
joinrelids, and if there's another rows hint which is applicable on
either component of the join, the change of rownums made earlier is
canceled by the 'another' hint causing reestimation of the joinrel.
Finally the effect of the former hint disappears.

Change Summary

Incremental Difference

--- a/expected/R_2-4-1.out
+++ b/expected/R_2-4-1.out
@@ -24,7 +24,7 @@
2424
2525 QUERY PLAN
2626 -----------------------------------------------------------------------------
27- Nested Loop (cost=xxx rows=1 width=73)
27+ Nested Loop (cost=xxx rows=20 width=73)
2828 -> Nested Loop (cost=xxx rows=2 width=59)
2929 -> Values Scan on "*VALUES*" (cost=xxx rows=1 width=44)
3030 -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=15)
--- a/expected/ut-R.out
+++ b/expected/ut-R.out
@@ -2055,24 +2055,24 @@ error hint:
20552055 -- No. R-2-4-1
20562056 \o results/R_2-4-1.out.log
20572057 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
2058-/*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #2)*/
2058+/*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #20)*/
20592059 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
20602060 LOG: pg_hint_plan:
20612061 used hint:
20622062 not used hint:
20632063 Leading(t3 t1 t2)
20642064 Rows(t1 t3 #2)
2065-Rows(t1 t2 t3 #2)
2065+Rows(t1 t2 t3 #20)
20662066 duplication hint:
20672067 error hint:
20682068
2069-/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #2)*/
2069+/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #20)*/
20702070 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
20712071 LOG: pg_hint_plan:
20722072 used hint:
20732073 Leading(*VALUES* t1 t2)
20742074 Rows(*VALUES* t1 #2)
2075-Rows(*VALUES* t1 t2 #2)
2075+Rows(*VALUES* t1 t2 #20)
20762076 not used hint:
20772077 duplication hint:
20782078 error hint:
--- a/make_join_rel.c
+++ b/make_join_rel.c
@@ -62,9 +62,6 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
6262 RelOptInfo *joinrel;
6363 List *restrictlist;
6464
65- RowsHint *rows_hint = NULL;
66- int i;
67-
6865 /* We should never try to join two overlapping sets of rels. */
6966 Assert(!bms_overlap(rel1->relids, rel2->relids));
7067
@@ -116,61 +113,83 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
116113 joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo,
117114 &restrictlist);
118115
119- /* Apply appropriate Rows hint to the join node, if any. */
120- for (i = 0; i < current_hint->num_hints[HINT_TYPE_ROWS]; i++)
116+ /* !!! START: HERE IS THE PART WHICH ADDED FOR PG_HINT_PLAN !!! */
121117 {
122- rows_hint = current_hint->rows_hints[i];
123-
124- /*
125- * This Rows hint is invalid for some reason, or it contains no
126- * aliasname which exists in the query.
127- */
128- if (!rows_hint->joinrelids ||
129- rows_hint->base.state == HINT_STATE_ERROR)
130- continue;
118+ RowsHint *rows_hint = NULL;
119+ int i;
120+ RowsHint *justforme = NULL;
121+ RowsHint *domultiply = NULL;
131122
132- if (bms_equal(joinrelids, rows_hint->joinrelids))
123+ /* Search for applicable rows hint for this join node */
124+ for (i = 0; i < current_hint->num_hints[HINT_TYPE_ROWS]; i++)
133125 {
126+ rows_hint = current_hint->rows_hints[i];
127+
134128 /*
135- * This join RelOptInfo is exactly a Rows hint specifies, so adjust
136- * rows estimateion with the hint's content. Here we never have
137- * another hint which has same relation combination, so we can skip
138- * rest of hints.
129+ * Skip this rows_hint if it is invalid from the first or it
130+ * doesn't target any join rels.
139131 */
140- if (rows_hint->base.state == HINT_STATE_NOTUSED)
141- joinrel->rows = adjust_rows(joinrel->rows, rows_hint);
132+ if (!rows_hint->joinrelids ||
133+ rows_hint->base.state == HINT_STATE_ERROR)
134+ continue;
135+
136+ if (bms_equal(joinrelids, rows_hint->joinrelids))
137+ {
138+ /*
139+ * This joinrel is just the target of this rows_hint, so tweak
140+ * rows estimation according to the hint.
141+ */
142+ justforme = rows_hint;
143+ }
144+ else if (!(bms_is_subset(rows_hint->joinrelids, rel1->relids) ||
145+ bms_is_subset(rows_hint->joinrelids, rel2->relids)) &&
146+ bms_is_subset(rows_hint->joinrelids, joinrelids) &&
147+ rows_hint->value_type == RVT_MULTI)
148+ {
149+ /*
150+ * If the rows_hint's target relids is not a subset of both of
151+ * component rels and is a subset of this joinrel, ths hint's
152+ * targets spread over both component rels. This menas that
153+ * this hint has been never applied so far and this joinrel is
154+ * the first (and only) chance to fire in current join tree.
155+ * Only the multiplication hint has the cumulative nature so we
156+ * apply only RVT_MULTI in this way.
157+ */
158+ domultiply = rows_hint;
159+ }
142160 }
143- else if (bms_is_subset(rows_hint->joinrelids, rel1->relids) ||
144- bms_is_subset(rows_hint->joinrelids, rel2->relids))
161+
162+ if (justforme)
145163 {
146164 /*
147- * Otherwise if the relation combination specified in thee Rows
148- * hint is subset of the set of join elements, re-estimate rows and
149- * costs again to reflect the adjustment done in down. This is
150- * necessary for the first permutation of the combination the
151- * relations, but it's difficult to determine that this is the
152- * first, so do this everytime.
165+ * If a hint just for me is found, no other adjust method is
166+ * useles, but this cannot be more than twice becuase this joinrel
167+ * is already adjusted by this hint.
153168 */
154- set_joinrel_size_estimates(root, joinrel, rel1, rel2, sjinfo,
155- restrictlist);
169+ if (justforme->base.state == HINT_STATE_NOTUSED)
170+ joinrel->rows = adjust_rows(joinrel->rows, justforme);
156171 }
157- else if (bms_is_subset(rows_hint->joinrelids, joinrelids))
172+ else
158173 {
159- /*
160- * If the combination specifed in the Rows hints is subset of the
161- * join relation and spreads over both children,
162- *
163- * We do adjust rows estimation only when the value type was
164- * multiplication, because other value types are meanless.
165- */
166- if (rows_hint->value_type == RVT_MULTI)
174+ if (domultiply)
167175 {
176+ /*
177+ * If we have multiple routes up to this joinrel which are not
178+ * applicable this hint, this multiply hint will applied more
179+ * than twice. But there's no means to know of that,
180+ * re-estimate the row number of this joinrel always just
181+ * before applying the hint. This is a bit different from
182+ * normal planner behavior but it doesn't harm so much.
183+ */
168184 set_joinrel_size_estimates(root, joinrel, rel1, rel2, sjinfo,
169185 restrictlist);
170- joinrel->rows = adjust_rows(joinrel->rows, rows_hint);
186+
187+ joinrel->rows = adjust_rows(joinrel->rows, domultiply);
171188 }
189+
172190 }
173191 }
192+ /* !!! END: HERE IS THE PART WHICH ADDED FOR PG_HINT_PLAN !!! */
174193
175194 /*
176195 * If we've already proven this join is empty, we needn't consider any
--- a/sql/ut-R.sql
+++ b/sql/ut-R.sql
@@ -1021,9 +1021,9 @@ EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
10211021 -- No. R-2-4-1
10221022 \o results/R_2-4-1.out.log
10231023 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
1024-/*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #2)*/
1024+/*+ Leading(t3 t1 t2) Rows(t3 t1 #2)Rows(t3 t1 t2 #20)*/
10251025 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
1026-/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #2)*/
1026+/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #20)*/
10271027 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
10281028 \o
10291029 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-4-1.out.log > results/R_2-4-1.out
Show on old repository browser