firtst release
Revision | 4e3c9871ca22d4a5916c1d659787bca3cb09a184 (tree) |
---|---|
Time | 2020-02-14 15:57:35 |
Author | Kyotaro Horiguchi <horikyota.ntt@gmai...> |
Commiter | Kyotaro Horiguchi |
Fix behavior of nested planning
Issue: https://github.com/ossc-db/pg_hint_plan/issues/39
When planning is nested by function call, the hints in the function
should affect the corresponding query. Maybe 9599067 introduced
that. Fix the nested planning behavior.
@@ -4415,6 +4415,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ | ||
4415 | 4415 | ORDER BY t_1.c1 LIMIT 1; |
4416 | 4416 | $$ LANGUAGE SQL IMMUTABLE; |
4417 | 4417 | --No.13-4-1 |
4418 | +-- recall_planner() is reduced to constant while planning using the | |
4419 | +-- hint defined in the function. Then the outer query is planned based | |
4420 | +-- on the following hint. pg_hint_plan shows the log for the function | |
4421 | +-- but the resulting explain output doesn't contain the corresponding | |
4422 | +-- plan. | |
4418 | 4423 | /*+HashJoin(t_1 t_2)*/ |
4419 | 4424 | EXPLAIN (COSTS false) |
4420 | 4425 | SELECT recall_planner() FROM s1.t1 t_1 |
@@ -4422,7 +4427,7 @@ EXPLAIN (COSTS false) | ||
4422 | 4427 | ORDER BY t_1.c1; |
4423 | 4428 | LOG: pg_hint_plan: |
4424 | 4429 | used hint: |
4425 | -HashJoin(t_1 t_2) | |
4430 | +IndexScan(t_1) | |
4426 | 4431 | not used hint: |
4427 | 4432 | duplication hint: |
4428 | 4433 | error hint: |
@@ -4446,6 +4451,7 @@ error hint: | ||
4446 | 4451 | (7 rows) |
4447 | 4452 | |
4448 | 4453 | --No.13-4-2 |
4454 | +--See description for No.13-4-1 | |
4449 | 4455 | /*+HashJoin(st_1 st_2)*/ |
4450 | 4456 | EXPLAIN (COSTS false) |
4451 | 4457 | SELECT recall_planner() FROM s1.t1 st_1 |
@@ -4453,8 +4459,8 @@ EXPLAIN (COSTS false) | ||
4453 | 4459 | ORDER BY st_1.c1; |
4454 | 4460 | LOG: pg_hint_plan: |
4455 | 4461 | used hint: |
4462 | +IndexScan(t_1) | |
4456 | 4463 | not used hint: |
4457 | -HashJoin(st_1 st_2) | |
4458 | 4464 | duplication hint: |
4459 | 4465 | error hint: |
4460 | 4466 |
@@ -4477,6 +4483,7 @@ error hint: | ||
4477 | 4483 | (7 rows) |
4478 | 4484 | |
4479 | 4485 | --No.13-4-3 |
4486 | +--See description for No.13-4-1 | |
4480 | 4487 | /*+HashJoin(t_1 t_2)*/ |
4481 | 4488 | EXPLAIN (COSTS false) |
4482 | 4489 | SELECT recall_planner() FROM s1.t1 st_1 |
@@ -4484,7 +4491,7 @@ EXPLAIN (COSTS false) | ||
4484 | 4491 | ORDER BY st_1.c1; |
4485 | 4492 | LOG: pg_hint_plan: |
4486 | 4493 | used hint: |
4487 | -HashJoin(t_1 t_2) | |
4494 | +IndexScan(t_1) | |
4488 | 4495 | not used hint: |
4489 | 4496 | duplication hint: |
4490 | 4497 | error hint: |
@@ -4507,6 +4514,7 @@ error hint: | ||
4507 | 4514 | (6 rows) |
4508 | 4515 | |
4509 | 4516 | --No.13-4-4 |
4517 | +--See description for No.13-4-1 | |
4510 | 4518 | /*+HashJoin(st_1 st_2)*/ |
4511 | 4519 | EXPLAIN (COSTS false) |
4512 | 4520 | SELECT recall_planner() FROM s1.t1 t_1 |
@@ -4514,8 +4522,8 @@ EXPLAIN (COSTS false) | ||
4514 | 4522 | ORDER BY t_1.c1; |
4515 | 4523 | LOG: pg_hint_plan: |
4516 | 4524 | used hint: |
4525 | +IndexScan(t_1) | |
4517 | 4526 | not used hint: |
4518 | -HashJoin(st_1 st_2) | |
4519 | 4527 | duplication hint: |
4520 | 4528 | error hint: |
4521 | 4529 |
@@ -4537,18 +4545,18 @@ error hint: | ||
4537 | 4545 | (6 rows) |
4538 | 4546 | |
4539 | 4547 | --No.13-4-5 |
4548 | +-- See description for No.13-4-1. No joins in ths plan, so | |
4549 | +-- pg_hint_plan doesn't complain on the wrongly written error hint. | |
4540 | 4550 | /*+HashJoin(t_1 t_1)*/ |
4541 | 4551 | EXPLAIN (COSTS false) |
4542 | 4552 | SELECT recall_planner() FROM s1.t1 t_1 |
4543 | 4553 | ORDER BY t_1.c1; |
4544 | -INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" | |
4545 | -DETAIL: Relation name "t_1" is duplicated. | |
4546 | 4554 | LOG: pg_hint_plan: |
4547 | 4555 | used hint: |
4556 | +IndexScan(t_1) | |
4548 | 4557 | not used hint: |
4549 | 4558 | duplication hint: |
4550 | 4559 | error hint: |
4551 | -HashJoin(t_1 t_1) | |
4552 | 4560 | |
4553 | 4561 | LOG: pg_hint_plan: |
4554 | 4562 | used hint: |
@@ -4572,6 +4580,13 @@ EXPLAIN (COSTS false) | ||
4572 | 4580 | SELECT recall_planner_one_t() FROM s1.t1 t_1 |
4573 | 4581 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4574 | 4582 | ORDER BY t_1.c1; |
4583 | +LOG: pg_hint_plan: | |
4584 | +used hint: | |
4585 | +IndexScan(t_1) | |
4586 | +not used hint: | |
4587 | +duplication hint: | |
4588 | +error hint: | |
4589 | + | |
4575 | 4590 | QUERY PLAN |
4576 | 4591 | --------------------------------------------- |
4577 | 4592 | Merge Join |
@@ -4589,8 +4604,8 @@ EXPLAIN (COSTS false) | ||
4589 | 4604 | ORDER BY t_1.c1; |
4590 | 4605 | LOG: pg_hint_plan: |
4591 | 4606 | used hint: |
4607 | +IndexScan(t_1) | |
4592 | 4608 | not used hint: |
4593 | -HashJoin(t_1 t_1) | |
4594 | 4609 | duplication hint: |
4595 | 4610 | error hint: |
4596 | 4611 |
@@ -4616,19 +4631,18 @@ HashJoin(t_1 t_1) | ||
4616 | 4631 | DROP FUNCTION recall_planner_one_t(int); |
4617 | 4632 | ERROR: function recall_planner_one_t(integer) does not exist |
4618 | 4633 | --No.13-4-7 |
4634 | +-- See description for No.13-4-1. Complains on the wrongly written hint. | |
4619 | 4635 | /*+HashJoin(t_1 t_1)*/ |
4620 | 4636 | EXPLAIN (COSTS false) |
4621 | 4637 | SELECT recall_planner() FROM s1.t1 t_1 |
4622 | 4638 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4623 | 4639 | ORDER BY t_1.c1; |
4624 | -INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" | |
4625 | -DETAIL: Relation name "t_1" is duplicated. | |
4626 | 4640 | LOG: pg_hint_plan: |
4627 | 4641 | used hint: |
4642 | +IndexScan(t_1) | |
4628 | 4643 | not used hint: |
4629 | 4644 | duplication hint: |
4630 | 4645 | error hint: |
4631 | -HashJoin(t_1 t_1) | |
4632 | 4646 | |
4633 | 4647 | INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" |
4634 | 4648 | DETAIL: Relation name "t_1" is duplicated. |
@@ -4657,14 +4671,11 @@ EXPLAIN (COSTS false) | ||
4657 | 4671 | ORDER BY t_1.c1; |
4658 | 4672 | INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" |
4659 | 4673 | DETAIL: Conflict join method hint. |
4660 | -INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" | |
4661 | -DETAIL: Conflict join method hint. | |
4662 | 4674 | LOG: pg_hint_plan: |
4663 | 4675 | used hint: |
4664 | -HashJoin(t_1 t_2) | |
4676 | +IndexScan(t_1) | |
4665 | 4677 | not used hint: |
4666 | 4678 | duplication hint: |
4667 | -MergeJoin(t_1 t_2) | |
4668 | 4679 | error hint: |
4669 | 4680 | |
4670 | 4681 | LOG: pg_hint_plan: |
@@ -1816,7 +1816,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery) | ||
1816 | 1816 | * case of DESCRIBE message handling or EXECUTE command. We may still see a |
1817 | 1817 | * candidate top-level query in pstate in the case. |
1818 | 1818 | */ |
1819 | - if (!p && pstate) | |
1819 | + if (pstate && pstate->p_sourcetext) | |
1820 | 1820 | p = pstate->p_sourcetext; |
1821 | 1821 | |
1822 | 1822 | /* We don't see a query string, return NULL */ |
@@ -3085,6 +3085,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) | ||
3085 | 3085 | */ |
3086 | 3086 | recurse_level++; |
3087 | 3087 | prev_hint_str = current_hint_str; |
3088 | + current_hint_str = NULL; | |
3088 | 3089 | |
3089 | 3090 | /* |
3090 | 3091 | * Use PG_TRY mechanism to recover GUC parameters and current_hint_state to |
@@ -1152,6 +1152,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ | ||
1152 | 1152 | $$ LANGUAGE SQL IMMUTABLE; |
1153 | 1153 | |
1154 | 1154 | --No.13-4-1 |
1155 | +-- recall_planner() is reduced to constant while planning using the | |
1156 | +-- hint defined in the function. Then the outer query is planned based | |
1157 | +-- on the following hint. pg_hint_plan shows the log for the function | |
1158 | +-- but the resulting explain output doesn't contain the corresponding | |
1159 | +-- plan. | |
1155 | 1160 | /*+HashJoin(t_1 t_2)*/ |
1156 | 1161 | EXPLAIN (COSTS false) |
1157 | 1162 | SELECT recall_planner() FROM s1.t1 t_1 |
@@ -1159,6 +1164,7 @@ EXPLAIN (COSTS false) | ||
1159 | 1164 | ORDER BY t_1.c1; |
1160 | 1165 | |
1161 | 1166 | --No.13-4-2 |
1167 | +--See description for No.13-4-1 | |
1162 | 1168 | /*+HashJoin(st_1 st_2)*/ |
1163 | 1169 | EXPLAIN (COSTS false) |
1164 | 1170 | SELECT recall_planner() FROM s1.t1 st_1 |
@@ -1166,6 +1172,7 @@ EXPLAIN (COSTS false) | ||
1166 | 1172 | ORDER BY st_1.c1; |
1167 | 1173 | |
1168 | 1174 | --No.13-4-3 |
1175 | +--See description for No.13-4-1 | |
1169 | 1176 | /*+HashJoin(t_1 t_2)*/ |
1170 | 1177 | EXPLAIN (COSTS false) |
1171 | 1178 | SELECT recall_planner() FROM s1.t1 st_1 |
@@ -1173,6 +1180,7 @@ EXPLAIN (COSTS false) | ||
1173 | 1180 | ORDER BY st_1.c1; |
1174 | 1181 | |
1175 | 1182 | --No.13-4-4 |
1183 | +--See description for No.13-4-1 | |
1176 | 1184 | /*+HashJoin(st_1 st_2)*/ |
1177 | 1185 | EXPLAIN (COSTS false) |
1178 | 1186 | SELECT recall_planner() FROM s1.t1 t_1 |
@@ -1180,6 +1188,8 @@ EXPLAIN (COSTS false) | ||
1180 | 1188 | ORDER BY t_1.c1; |
1181 | 1189 | |
1182 | 1190 | --No.13-4-5 |
1191 | +-- See description for No.13-4-1. No joins in ths plan, so | |
1192 | +-- pg_hint_plan doesn't complain on the wrongly written error hint. | |
1183 | 1193 | /*+HashJoin(t_1 t_1)*/ |
1184 | 1194 | EXPLAIN (COSTS false) |
1185 | 1195 | SELECT recall_planner() FROM s1.t1 t_1 |
@@ -1205,6 +1215,7 @@ EXPLAIN (COSTS false) | ||
1205 | 1215 | DROP FUNCTION recall_planner_one_t(int); |
1206 | 1216 | |
1207 | 1217 | --No.13-4-7 |
1218 | +-- See description for No.13-4-1. Complains on the wrongly written hint. | |
1208 | 1219 | /*+HashJoin(t_1 t_1)*/ |
1209 | 1220 | EXPLAIN (COSTS false) |
1210 | 1221 | SELECT recall_planner() FROM s1.t1 t_1 |