| Revision | eb2d8f38945415d347e580f8dedaa860ae5bd181 (tree) |
|---|---|
| Time | 2020-02-14 16:31:26 |
| 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.
expected/ut-A.out (diff)
pg_hint_plan.c (diff) sql/ut-A.sql (diff)| @@ -4289,6 +4289,11 @@ CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ | ||
| 4289 | 4289 | ORDER BY t_1.c1 LIMIT 1; |
| 4290 | 4290 | $$ LANGUAGE SQL IMMUTABLE; |
| 4291 | 4291 | --No.13-4-1 |
| 4292 | +-- recall_planner() is reduced to constant while planning using the | |
| 4293 | +-- hint defined in the function. Then the outer query is planned based | |
| 4294 | +-- on the following hint. pg_hint_plan shows the log for the function | |
| 4295 | +-- but the resulting explain output doesn't contain the corresponding | |
| 4296 | +-- plan. | |
| 4292 | 4297 | /*+HashJoin(t_1 t_2)*/ |
| 4293 | 4298 | EXPLAIN (COSTS false) |
| 4294 | 4299 | SELECT recall_planner() FROM s1.t1 t_1 |
| @@ -4296,7 +4301,7 @@ EXPLAIN (COSTS false) | ||
| 4296 | 4301 | ORDER BY t_1.c1; |
| 4297 | 4302 | LOG: pg_hint_plan: |
| 4298 | 4303 | used hint: |
| 4299 | -HashJoin(t_1 t_2) | |
| 4304 | +IndexScan(t_1) | |
| 4300 | 4305 | not used hint: |
| 4301 | 4306 | duplication hint: |
| 4302 | 4307 | error hint: |
| @@ -4320,6 +4325,7 @@ error hint: | ||
| 4320 | 4325 | (7 rows) |
| 4321 | 4326 | |
| 4322 | 4327 | --No.13-4-2 |
| 4328 | +--See description for No.13-4-1 | |
| 4323 | 4329 | /*+HashJoin(st_1 st_2)*/ |
| 4324 | 4330 | EXPLAIN (COSTS false) |
| 4325 | 4331 | SELECT recall_planner() FROM s1.t1 st_1 |
| @@ -4327,8 +4333,8 @@ EXPLAIN (COSTS false) | ||
| 4327 | 4333 | ORDER BY st_1.c1; |
| 4328 | 4334 | LOG: pg_hint_plan: |
| 4329 | 4335 | used hint: |
| 4336 | +IndexScan(t_1) | |
| 4330 | 4337 | not used hint: |
| 4331 | -HashJoin(st_1 st_2) | |
| 4332 | 4338 | duplication hint: |
| 4333 | 4339 | error hint: |
| 4334 | 4340 |
| @@ -4351,6 +4357,7 @@ error hint: | ||
| 4351 | 4357 | (7 rows) |
| 4352 | 4358 | |
| 4353 | 4359 | --No.13-4-3 |
| 4360 | +--See description for No.13-4-1 | |
| 4354 | 4361 | /*+HashJoin(t_1 t_2)*/ |
| 4355 | 4362 | EXPLAIN (COSTS false) |
| 4356 | 4363 | SELECT recall_planner() FROM s1.t1 st_1 |
| @@ -4358,7 +4365,7 @@ EXPLAIN (COSTS false) | ||
| 4358 | 4365 | ORDER BY st_1.c1; |
| 4359 | 4366 | LOG: pg_hint_plan: |
| 4360 | 4367 | used hint: |
| 4361 | -HashJoin(t_1 t_2) | |
| 4368 | +IndexScan(t_1) | |
| 4362 | 4369 | not used hint: |
| 4363 | 4370 | duplication hint: |
| 4364 | 4371 | error hint: |
| @@ -4381,6 +4388,7 @@ error hint: | ||
| 4381 | 4388 | (6 rows) |
| 4382 | 4389 | |
| 4383 | 4390 | --No.13-4-4 |
| 4391 | +--See description for No.13-4-1 | |
| 4384 | 4392 | /*+HashJoin(st_1 st_2)*/ |
| 4385 | 4393 | EXPLAIN (COSTS false) |
| 4386 | 4394 | SELECT recall_planner() FROM s1.t1 t_1 |
| @@ -4388,8 +4396,8 @@ EXPLAIN (COSTS false) | ||
| 4388 | 4396 | ORDER BY t_1.c1; |
| 4389 | 4397 | LOG: pg_hint_plan: |
| 4390 | 4398 | used hint: |
| 4399 | +IndexScan(t_1) | |
| 4391 | 4400 | not used hint: |
| 4392 | -HashJoin(st_1 st_2) | |
| 4393 | 4401 | duplication hint: |
| 4394 | 4402 | error hint: |
| 4395 | 4403 |
| @@ -4411,18 +4419,18 @@ error hint: | ||
| 4411 | 4419 | (6 rows) |
| 4412 | 4420 | |
| 4413 | 4421 | --No.13-4-5 |
| 4422 | +-- See description for No.13-4-1. No joins in ths plan, so | |
| 4423 | +-- pg_hint_plan doesn't complain on the wrongly written error hint. | |
| 4414 | 4424 | /*+HashJoin(t_1 t_1)*/ |
| 4415 | 4425 | EXPLAIN (COSTS false) |
| 4416 | 4426 | SELECT recall_planner() FROM s1.t1 t_1 |
| 4417 | 4427 | ORDER BY t_1.c1; |
| 4418 | -INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" | |
| 4419 | -DETAIL: Relation name "t_1" is duplicated. | |
| 4420 | 4428 | LOG: pg_hint_plan: |
| 4421 | 4429 | used hint: |
| 4430 | +IndexScan(t_1) | |
| 4422 | 4431 | not used hint: |
| 4423 | 4432 | duplication hint: |
| 4424 | 4433 | error hint: |
| 4425 | -HashJoin(t_1 t_1) | |
| 4426 | 4434 | |
| 4427 | 4435 | LOG: pg_hint_plan: |
| 4428 | 4436 | used hint: |
| @@ -4446,6 +4454,13 @@ EXPLAIN (COSTS false) | ||
| 4446 | 4454 | SELECT recall_planner_one_t() FROM s1.t1 t_1 |
| 4447 | 4455 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
| 4448 | 4456 | ORDER BY t_1.c1; |
| 4457 | +LOG: pg_hint_plan: | |
| 4458 | +used hint: | |
| 4459 | +IndexScan(t_1) | |
| 4460 | +not used hint: | |
| 4461 | +duplication hint: | |
| 4462 | +error hint: | |
| 4463 | + | |
| 4449 | 4464 | QUERY PLAN |
| 4450 | 4465 | --------------------------------------------- |
| 4451 | 4466 | Merge Join |
| @@ -4463,8 +4478,8 @@ EXPLAIN (COSTS false) | ||
| 4463 | 4478 | ORDER BY t_1.c1; |
| 4464 | 4479 | LOG: pg_hint_plan: |
| 4465 | 4480 | used hint: |
| 4481 | +IndexScan(t_1) | |
| 4466 | 4482 | not used hint: |
| 4467 | -HashJoin(t_1 t_1) | |
| 4468 | 4483 | duplication hint: |
| 4469 | 4484 | error hint: |
| 4470 | 4485 |
| @@ -4490,19 +4505,18 @@ HashJoin(t_1 t_1) | ||
| 4490 | 4505 | DROP FUNCTION recall_planner_one_t(int); |
| 4491 | 4506 | ERROR: function recall_planner_one_t(integer) does not exist |
| 4492 | 4507 | --No.13-4-7 |
| 4508 | +-- See description for No.13-4-1. Complains on the wrongly written hint. | |
| 4493 | 4509 | /*+HashJoin(t_1 t_1)*/ |
| 4494 | 4510 | EXPLAIN (COSTS false) |
| 4495 | 4511 | SELECT recall_planner() FROM s1.t1 t_1 |
| 4496 | 4512 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
| 4497 | 4513 | ORDER BY t_1.c1; |
| 4498 | -INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" | |
| 4499 | -DETAIL: Relation name "t_1" is duplicated. | |
| 4500 | 4514 | LOG: pg_hint_plan: |
| 4501 | 4515 | used hint: |
| 4516 | +IndexScan(t_1) | |
| 4502 | 4517 | not used hint: |
| 4503 | 4518 | duplication hint: |
| 4504 | 4519 | error hint: |
| 4505 | -HashJoin(t_1 t_1) | |
| 4506 | 4520 | |
| 4507 | 4521 | INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)" |
| 4508 | 4522 | DETAIL: Relation name "t_1" is duplicated. |
| @@ -4531,14 +4545,11 @@ EXPLAIN (COSTS false) | ||
| 4531 | 4545 | ORDER BY t_1.c1; |
| 4532 | 4546 | INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" |
| 4533 | 4547 | DETAIL: Conflict join method hint. |
| 4534 | -INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" | |
| 4535 | -DETAIL: Conflict join method hint. | |
| 4536 | 4548 | LOG: pg_hint_plan: |
| 4537 | 4549 | used hint: |
| 4538 | -HashJoin(t_1 t_2) | |
| 4550 | +IndexScan(t_1) | |
| 4539 | 4551 | not used hint: |
| 4540 | 4552 | duplication hint: |
| 4541 | -MergeJoin(t_1 t_2) | |
| 4542 | 4553 | error hint: |
| 4543 | 4554 | |
| 4544 | 4555 | LOG: pg_hint_plan: |
| @@ -1825,7 +1825,7 @@ get_query_string(ParseState *pstate, Query *query, Query **jumblequery) | ||
| 1825 | 1825 | * case of DESCRIBE message handling or EXECUTE command. We may still see a |
| 1826 | 1826 | * candidate top-level query in pstate in the case. |
| 1827 | 1827 | */ |
| 1828 | - if (!p && pstate) | |
| 1828 | + if (pstate && pstate->p_sourcetext) | |
| 1829 | 1829 | p = pstate->p_sourcetext; |
| 1830 | 1830 | |
| 1831 | 1831 | /* We don't see a query string, return NULL */ |
| @@ -3094,6 +3094,7 @@ pg_hint_plan_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) | ||
| 3094 | 3094 | */ |
| 3095 | 3095 | recurse_level++; |
| 3096 | 3096 | prev_hint_str = current_hint_str; |
| 3097 | + current_hint_str = NULL; | |
| 3097 | 3098 | |
| 3098 | 3099 | /* |
| 3099 | 3100 | * 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 |
Fork