firtst release
Revision | 1ebd8cda012b78cdbff640ea2e4c04a746204162 (tree) |
---|---|
Zeit | 2013-09-17 15:01:00 |
Autor | Takashi Suzuki <suzuki.takashi@metr...> |
Commiter | Takashi Suzuki |
A-13-4の試験の内容を項目表にあうように変更した。
@@ -4236,18 +4236,26 @@ error hint: | ||
4236 | 4236 | ---- |
4237 | 4237 | ---- No. A-13-4 output of debugging log on hint status |
4238 | 4238 | ---- |
4239 | +CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ | |
4240 | + SELECT /*+ IndexScan(t_1) */t_1.c1 | |
4241 | + FROM s1.t1 t_1 | |
4242 | + JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4243 | + ORDER BY t_1.c1 LIMIT 1; | |
4244 | +$$ LANGUAGE SQL IMMUTABLE; | |
4239 | 4245 | --No.13-4-1 |
4240 | 4246 | /*+HashJoin(t_1 t_2)*/ |
4241 | 4247 | EXPLAIN (COSTS false) |
4242 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4248 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4243 | 4249 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4244 | 4250 | ORDER BY t_1.c1; |
4245 | -NOTICE: nested_planner(2) | |
4246 | -NOTICE: nested_planner(1) | |
4247 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4248 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4249 | - ORDER BY t_1.c1 LIMIT 1" | |
4250 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4251 | +LOG: pg_hint_plan: | |
4252 | +used hint: | |
4253 | +HashJoin(t_1 t_2) | |
4254 | +not used hint: | |
4255 | +duplication hint: | |
4256 | +error hint: | |
4257 | + | |
4258 | +CONTEXT: SQL function "recall_planner" during startup | |
4251 | 4259 | LOG: pg_hint_plan: |
4252 | 4260 | used hint: |
4253 | 4261 | HashJoin(t_1 t_2) |
@@ -4269,15 +4277,17 @@ error hint: | ||
4269 | 4277 | --No.13-4-2 |
4270 | 4278 | /*+HashJoin(st_1 st_2)*/ |
4271 | 4279 | EXPLAIN (COSTS false) |
4272 | - SELECT nested_planner(2) FROM s1.t1 st_1 | |
4280 | + SELECT recall_planner() FROM s1.t1 st_1 | |
4273 | 4281 | JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1) |
4274 | 4282 | ORDER BY st_1.c1; |
4275 | -NOTICE: nested_planner(2) | |
4276 | -NOTICE: nested_planner(1) | |
4277 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4278 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4279 | - ORDER BY t_1.c1 LIMIT 1" | |
4280 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4283 | +LOG: pg_hint_plan: | |
4284 | +used hint: | |
4285 | +not used hint: | |
4286 | +HashJoin(st_1 st_2) | |
4287 | +duplication hint: | |
4288 | +error hint: | |
4289 | + | |
4290 | +CONTEXT: SQL function "recall_planner" during startup | |
4281 | 4291 | LOG: pg_hint_plan: |
4282 | 4292 | used hint: |
4283 | 4293 | HashJoin(st_1 st_2) |
@@ -4299,15 +4309,17 @@ error hint: | ||
4299 | 4309 | --No.13-4-3 |
4300 | 4310 | /*+HashJoin(t_1 t_2)*/ |
4301 | 4311 | EXPLAIN (COSTS false) |
4302 | - SELECT nested_planner(2) FROM s1.t1 st_1 | |
4312 | + SELECT recall_planner() FROM s1.t1 st_1 | |
4303 | 4313 | JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1) |
4304 | 4314 | ORDER BY st_1.c1; |
4305 | -NOTICE: nested_planner(2) | |
4306 | -NOTICE: nested_planner(1) | |
4307 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4308 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4309 | - ORDER BY t_1.c1 LIMIT 1" | |
4310 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4315 | +LOG: pg_hint_plan: | |
4316 | +used hint: | |
4317 | +HashJoin(t_1 t_2) | |
4318 | +not used hint: | |
4319 | +duplication hint: | |
4320 | +error hint: | |
4321 | + | |
4322 | +CONTEXT: SQL function "recall_planner" during startup | |
4311 | 4323 | LOG: pg_hint_plan: |
4312 | 4324 | used hint: |
4313 | 4325 | not used hint: |
@@ -4328,15 +4340,17 @@ error hint: | ||
4328 | 4340 | --No.13-4-4 |
4329 | 4341 | /*+HashJoin(st_1 st_2)*/ |
4330 | 4342 | EXPLAIN (COSTS false) |
4331 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4343 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4332 | 4344 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4333 | 4345 | ORDER BY t_1.c1; |
4334 | -NOTICE: nested_planner(2) | |
4335 | -NOTICE: nested_planner(1) | |
4336 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4337 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4338 | - ORDER BY t_1.c1 LIMIT 1" | |
4339 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4346 | +LOG: pg_hint_plan: | |
4347 | +used hint: | |
4348 | +not used hint: | |
4349 | +HashJoin(st_1 st_2) | |
4350 | +duplication hint: | |
4351 | +error hint: | |
4352 | + | |
4353 | +CONTEXT: SQL function "recall_planner" during startup | |
4340 | 4354 | LOG: pg_hint_plan: |
4341 | 4355 | used hint: |
4342 | 4356 | not used hint: |
@@ -4357,14 +4371,19 @@ error hint: | ||
4357 | 4371 | --No.13-4-5 |
4358 | 4372 | /*+HashJoin(t_1 t_1)*/ |
4359 | 4373 | EXPLAIN (COSTS false) |
4360 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4374 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4361 | 4375 | ORDER BY t_1.c1; |
4362 | -NOTICE: nested_planner(2) | |
4363 | -NOTICE: nested_planner(1) | |
4364 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4365 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4366 | - ORDER BY t_1.c1 LIMIT 1" | |
4367 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4376 | +INFO: hint syntax error at or near "HashJoin(t_1 t_1)" | |
4377 | +DETAIL: Relation name "t_1" is duplicated. | |
4378 | +CONTEXT: SQL function "recall_planner" during startup | |
4379 | +LOG: pg_hint_plan: | |
4380 | +used hint: | |
4381 | +not used hint: | |
4382 | +duplication hint: | |
4383 | +error hint: | |
4384 | +HashJoin(t_1 t_1) | |
4385 | + | |
4386 | +CONTEXT: SQL function "recall_planner" during startup | |
4368 | 4387 | LOG: pg_hint_plan: |
4369 | 4388 | used hint: |
4370 | 4389 | not used hint: |
@@ -4378,54 +4397,15 @@ error hint: | ||
4378 | 4397 | (1 row) |
4379 | 4398 | |
4380 | 4399 | --No.13-4-6 |
4381 | -CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$ | |
4382 | -DECLARE | |
4383 | - new_cnt int; | |
4384 | -BEGIN | |
4385 | - RAISE NOTICE 'nested_planner_one_t(%)', cnt; | |
4386 | - | |
4387 | - IF cnt <= 1 THEN | |
4388 | - RETURN 0; | |
4389 | - END IF; | |
4390 | - | |
4391 | - SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) INTO new_cnt | |
4400 | +CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$ | |
4401 | + SELECT /*+ IndexScan(t_1) */t_1.c1 | |
4392 | 4402 | FROM s1.t1 t_1 |
4393 | 4403 | ORDER BY t_1.c1 LIMIT 1; |
4394 | - | |
4395 | - RETURN new_cnt; | |
4396 | -END; | |
4397 | -$$ LANGUAGE plpgsql IMMUTABLE; | |
4404 | +$$ LANGUAGE SQL IMMUTABLE; | |
4398 | 4405 | EXPLAIN (COSTS false) |
4399 | - SELECT nested_planner_one_t(2) FROM s1.t1 t_1 | |
4406 | + SELECT recall_planner_one_t() FROM s1.t1 t_1 | |
4400 | 4407 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4401 | 4408 | ORDER BY t_1.c1; |
4402 | -NOTICE: nested_planner_one_t(2) | |
4403 | -LOG: pg_hint_plan: | |
4404 | -used hint: | |
4405 | -IndexScan(t_1) | |
4406 | -not used hint: | |
4407 | -duplication hint: | |
4408 | -error hint: | |
4409 | - | |
4410 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4411 | - ORDER BY t_1.c1 LIMIT 1" | |
4412 | -PL/pgSQL function "nested_planner_one_t" line 11 at SQL statement | |
4413 | -NOTICE: nested_planner_one_t(1) | |
4414 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4415 | - ORDER BY t_1.c1 LIMIT 1" | |
4416 | -PL/pgSQL function "nested_planner_one_t" line 11 at SQL statement | |
4417 | -LOG: pg_hint_plan: | |
4418 | -used hint: | |
4419 | -not used hint: | |
4420 | -IndexScan(t_1) | |
4421 | -duplication hint: | |
4422 | -error hint: | |
4423 | - | |
4424 | -CONTEXT: SQL statement "SELECT 0" | |
4425 | -PL/pgSQL function "nested_planner_one_t" line 8 at RETURN | |
4426 | -SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4427 | - ORDER BY t_1.c1 LIMIT 1" | |
4428 | -PL/pgSQL function "nested_planner_one_t" line 11 at SQL statement | |
4429 | 4409 | QUERY PLAN |
4430 | 4410 | ---------------------------------------- |
4431 | 4411 | Merge Join |
@@ -4438,14 +4418,17 @@ PL/pgSQL function "nested_planner_one_t" line 11 at SQL statement | ||
4438 | 4418 | |
4439 | 4419 | /*+HashJoin(t_1 t_1)*/ |
4440 | 4420 | EXPLAIN (COSTS false) |
4441 | - SELECT nested_planner_one_t(2) FROM s1.t1 t_1 | |
4421 | + SELECT recall_planner_one_t() FROM s1.t1 t_1 | |
4442 | 4422 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4443 | 4423 | ORDER BY t_1.c1; |
4444 | -NOTICE: nested_planner_one_t(2) | |
4445 | -NOTICE: nested_planner_one_t(1) | |
4446 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4447 | - ORDER BY t_1.c1 LIMIT 1" | |
4448 | -PL/pgSQL function "nested_planner_one_t" line 11 at SQL statement | |
4424 | +LOG: pg_hint_plan: | |
4425 | +used hint: | |
4426 | +not used hint: | |
4427 | +HashJoin(t_1 t_1) | |
4428 | +duplication hint: | |
4429 | +error hint: | |
4430 | + | |
4431 | +CONTEXT: SQL function "recall_planner_one_t" during startup | |
4449 | 4432 | INFO: hint syntax error at or near "HashJoin(t_1 t_1)" |
4450 | 4433 | DETAIL: Relation name "t_1" is duplicated. |
4451 | 4434 | LOG: pg_hint_plan: |
@@ -4465,19 +4448,25 @@ HashJoin(t_1 t_1) | ||
4465 | 4448 | -> Seq Scan on t2 t_2 |
4466 | 4449 | (6 rows) |
4467 | 4450 | |
4468 | -DROP FUNCTION nested_planner_one_t(int); | |
4451 | +DROP FUNCTION recall_planner_one_t(int); | |
4452 | +ERROR: function recall_planner_one_t(integer) does not exist | |
4469 | 4453 | --No.13-4-7 |
4470 | 4454 | /*+HashJoin(t_1 t_1)*/ |
4471 | 4455 | EXPLAIN (COSTS false) |
4472 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4456 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4473 | 4457 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4474 | 4458 | ORDER BY t_1.c1; |
4475 | -NOTICE: nested_planner(2) | |
4476 | -NOTICE: nested_planner(1) | |
4477 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4478 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4479 | - ORDER BY t_1.c1 LIMIT 1" | |
4480 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4459 | +INFO: hint syntax error at or near "HashJoin(t_1 t_1)" | |
4460 | +DETAIL: Relation name "t_1" is duplicated. | |
4461 | +CONTEXT: SQL function "recall_planner" during startup | |
4462 | +LOG: pg_hint_plan: | |
4463 | +used hint: | |
4464 | +not used hint: | |
4465 | +duplication hint: | |
4466 | +error hint: | |
4467 | +HashJoin(t_1 t_1) | |
4468 | + | |
4469 | +CONTEXT: SQL function "recall_planner" during startup | |
4481 | 4470 | INFO: hint syntax error at or near "HashJoin(t_1 t_1)" |
4482 | 4471 | DETAIL: Relation name "t_1" is duplicated. |
4483 | 4472 | LOG: pg_hint_plan: |
@@ -4500,17 +4489,23 @@ HashJoin(t_1 t_1) | ||
4500 | 4489 | --No.13-4-8 |
4501 | 4490 | /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/ |
4502 | 4491 | EXPLAIN (COSTS false) |
4503 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4492 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4504 | 4493 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4505 | 4494 | ORDER BY t_1.c1; |
4506 | 4495 | INFO: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" |
4507 | 4496 | DETAIL: Conflict join method hint. |
4508 | -NOTICE: nested_planner(2) | |
4509 | -NOTICE: nested_planner(1) | |
4510 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4511 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4512 | - ORDER BY t_1.c1 LIMIT 1" | |
4513 | -PL/pgSQL function "nested_planner" line 12 at SQL statement | |
4497 | +INFO: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" | |
4498 | +DETAIL: Conflict join method hint. | |
4499 | +CONTEXT: SQL function "recall_planner" during startup | |
4500 | +LOG: pg_hint_plan: | |
4501 | +used hint: | |
4502 | +HashJoin(t_1 t_2) | |
4503 | +not used hint: | |
4504 | +duplication hint: | |
4505 | +MergeJoin(t_1 t_2) | |
4506 | +error hint: | |
4507 | + | |
4508 | +CONTEXT: SQL function "recall_planner" during startup | |
4514 | 4509 | LOG: pg_hint_plan: |
4515 | 4510 | used hint: |
4516 | 4511 | HashJoin(t_1 t_2) |
@@ -4544,29 +4544,26 @@ error hint: | ||
4544 | 4544 | ---- |
4545 | 4545 | ---- No. A-13-4 output of debugging log on hint status |
4546 | 4546 | ---- |
4547 | +CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ | |
4548 | + SELECT /*+ IndexScan(t_1) */t_1.c1 | |
4549 | + FROM s1.t1 t_1 | |
4550 | + JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4551 | + ORDER BY t_1.c1 LIMIT 1; | |
4552 | +$$ LANGUAGE SQL IMMUTABLE; | |
4547 | 4553 | --No.13-4-1 |
4548 | 4554 | /*+HashJoin(t_1 t_2)*/ |
4549 | 4555 | EXPLAIN (COSTS false) |
4550 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4556 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4551 | 4557 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4552 | 4558 | ORDER BY t_1.c1; |
4553 | -NOTICE: nested_planner(2) | |
4554 | -NOTICE: nested_planner(1) | |
4555 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4556 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4557 | - ORDER BY t_1.c1 LIMIT 1" | |
4558 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4559 | 4559 | LOG: pg_hint_plan: |
4560 | 4560 | used hint: |
4561 | -IndexScan(t_1) | |
4561 | +HashJoin(t_1 t_2) | |
4562 | 4562 | not used hint: |
4563 | 4563 | duplication hint: |
4564 | 4564 | error hint: |
4565 | 4565 | |
4566 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4567 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4568 | - ORDER BY t_1.c1 LIMIT 1" | |
4569 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4566 | +CONTEXT: SQL function "recall_planner" during startup | |
4570 | 4567 | LOG: pg_hint_plan: |
4571 | 4568 | used hint: |
4572 | 4569 | HashJoin(t_1 t_2) |
@@ -4588,26 +4585,17 @@ error hint: | ||
4588 | 4585 | --No.13-4-2 |
4589 | 4586 | /*+HashJoin(st_1 st_2)*/ |
4590 | 4587 | EXPLAIN (COSTS false) |
4591 | - SELECT nested_planner(2) FROM s1.t1 st_1 | |
4588 | + SELECT recall_planner() FROM s1.t1 st_1 | |
4592 | 4589 | JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1) |
4593 | 4590 | ORDER BY st_1.c1; |
4594 | -NOTICE: nested_planner(2) | |
4595 | -NOTICE: nested_planner(1) | |
4596 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4597 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4598 | - ORDER BY t_1.c1 LIMIT 1" | |
4599 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4600 | 4591 | LOG: pg_hint_plan: |
4601 | 4592 | used hint: |
4602 | -IndexScan(t_1) | |
4603 | 4593 | not used hint: |
4594 | +HashJoin(st_1 st_2) | |
4604 | 4595 | duplication hint: |
4605 | 4596 | error hint: |
4606 | 4597 | |
4607 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4608 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4609 | - ORDER BY t_1.c1 LIMIT 1" | |
4610 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4598 | +CONTEXT: SQL function "recall_planner" during startup | |
4611 | 4599 | LOG: pg_hint_plan: |
4612 | 4600 | used hint: |
4613 | 4601 | HashJoin(st_1 st_2) |
@@ -4629,26 +4617,17 @@ error hint: | ||
4629 | 4617 | --No.13-4-3 |
4630 | 4618 | /*+HashJoin(t_1 t_2)*/ |
4631 | 4619 | EXPLAIN (COSTS false) |
4632 | - SELECT nested_planner(2) FROM s1.t1 st_1 | |
4620 | + SELECT recall_planner() FROM s1.t1 st_1 | |
4633 | 4621 | JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1) |
4634 | 4622 | ORDER BY st_1.c1; |
4635 | -NOTICE: nested_planner(2) | |
4636 | -NOTICE: nested_planner(1) | |
4637 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4638 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4639 | - ORDER BY t_1.c1 LIMIT 1" | |
4640 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4641 | 4623 | LOG: pg_hint_plan: |
4642 | 4624 | used hint: |
4643 | -IndexScan(t_1) | |
4625 | +HashJoin(t_1 t_2) | |
4644 | 4626 | not used hint: |
4645 | 4627 | duplication hint: |
4646 | 4628 | error hint: |
4647 | 4629 | |
4648 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4649 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4650 | - ORDER BY t_1.c1 LIMIT 1" | |
4651 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4630 | +CONTEXT: SQL function "recall_planner" during startup | |
4652 | 4631 | LOG: pg_hint_plan: |
4653 | 4632 | used hint: |
4654 | 4633 | not used hint: |
@@ -4669,26 +4648,17 @@ error hint: | ||
4669 | 4648 | --No.13-4-4 |
4670 | 4649 | /*+HashJoin(st_1 st_2)*/ |
4671 | 4650 | EXPLAIN (COSTS false) |
4672 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4651 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4673 | 4652 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4674 | 4653 | ORDER BY t_1.c1; |
4675 | -NOTICE: nested_planner(2) | |
4676 | -NOTICE: nested_planner(1) | |
4677 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4678 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4679 | - ORDER BY t_1.c1 LIMIT 1" | |
4680 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4681 | 4654 | LOG: pg_hint_plan: |
4682 | 4655 | used hint: |
4683 | -IndexScan(t_1) | |
4684 | 4656 | not used hint: |
4657 | +HashJoin(st_1 st_2) | |
4685 | 4658 | duplication hint: |
4686 | 4659 | error hint: |
4687 | 4660 | |
4688 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4689 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4690 | - ORDER BY t_1.c1 LIMIT 1" | |
4691 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4661 | +CONTEXT: SQL function "recall_planner" during startup | |
4692 | 4662 | LOG: pg_hint_plan: |
4693 | 4663 | used hint: |
4694 | 4664 | not used hint: |
@@ -4709,25 +4679,19 @@ error hint: | ||
4709 | 4679 | --No.13-4-5 |
4710 | 4680 | /*+HashJoin(t_1 t_1)*/ |
4711 | 4681 | EXPLAIN (COSTS false) |
4712 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4682 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4713 | 4683 | ORDER BY t_1.c1; |
4714 | -NOTICE: nested_planner(2) | |
4715 | -NOTICE: nested_planner(1) | |
4716 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4717 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4718 | - ORDER BY t_1.c1 LIMIT 1" | |
4719 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4684 | +INFO: hint syntax error at or near "HashJoin(t_1 t_1)" | |
4685 | +DETAIL: Relation name "t_1" is duplicated. | |
4686 | +CONTEXT: SQL function "recall_planner" during startup | |
4720 | 4687 | LOG: pg_hint_plan: |
4721 | 4688 | used hint: |
4722 | -IndexScan(t_1) | |
4723 | 4689 | not used hint: |
4724 | 4690 | duplication hint: |
4725 | 4691 | error hint: |
4692 | +HashJoin(t_1 t_1) | |
4726 | 4693 | |
4727 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4728 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4729 | - ORDER BY t_1.c1 LIMIT 1" | |
4730 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4694 | +CONTEXT: SQL function "recall_planner" during startup | |
4731 | 4695 | LOG: pg_hint_plan: |
4732 | 4696 | used hint: |
4733 | 4697 | not used hint: |
@@ -4741,53 +4705,15 @@ error hint: | ||
4741 | 4705 | (1 row) |
4742 | 4706 | |
4743 | 4707 | --No.13-4-6 |
4744 | -CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$ | |
4745 | -DECLARE | |
4746 | - new_cnt int; | |
4747 | -BEGIN | |
4748 | - RAISE NOTICE 'nested_planner_one_t(%)', cnt; | |
4749 | - | |
4750 | - IF cnt <= 1 THEN | |
4751 | - RETURN 0; | |
4752 | - END IF; | |
4753 | - | |
4754 | - SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) INTO new_cnt | |
4708 | +CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$ | |
4709 | + SELECT /*+ IndexScan(t_1) */t_1.c1 | |
4755 | 4710 | FROM s1.t1 t_1 |
4756 | 4711 | ORDER BY t_1.c1 LIMIT 1; |
4757 | - | |
4758 | - RETURN new_cnt; | |
4759 | -END; | |
4760 | -$$ LANGUAGE plpgsql IMMUTABLE; | |
4712 | +$$ LANGUAGE SQL IMMUTABLE; | |
4761 | 4713 | EXPLAIN (COSTS false) |
4762 | - SELECT nested_planner_one_t(2) FROM s1.t1 t_1 | |
4714 | + SELECT recall_planner_one_t() FROM s1.t1 t_1 | |
4763 | 4715 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4764 | 4716 | ORDER BY t_1.c1; |
4765 | -NOTICE: nested_planner_one_t(2) | |
4766 | -NOTICE: nested_planner_one_t(1) | |
4767 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4768 | - ORDER BY t_1.c1 LIMIT 1" | |
4769 | -PL/pgSQL function nested_planner_one_t(integer) line 11 at SQL statement | |
4770 | -LOG: pg_hint_plan: | |
4771 | -used hint: | |
4772 | -not used hint: | |
4773 | -IndexScan(t_1) | |
4774 | -duplication hint: | |
4775 | -error hint: | |
4776 | - | |
4777 | -CONTEXT: PL/pgSQL function nested_planner_one_t(integer) line 8 at RETURN | |
4778 | -SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4779 | - ORDER BY t_1.c1 LIMIT 1" | |
4780 | -PL/pgSQL function nested_planner_one_t(integer) line 11 at SQL statement | |
4781 | -LOG: pg_hint_plan: | |
4782 | -used hint: | |
4783 | -IndexScan(t_1) | |
4784 | -not used hint: | |
4785 | -duplication hint: | |
4786 | -error hint: | |
4787 | - | |
4788 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4789 | - ORDER BY t_1.c1 LIMIT 1" | |
4790 | -PL/pgSQL function nested_planner_one_t(integer) line 11 at SQL statement | |
4791 | 4717 | QUERY PLAN |
4792 | 4718 | --------------------------------------------- |
4793 | 4719 | Merge Join |
@@ -4800,24 +4726,17 @@ PL/pgSQL function nested_planner_one_t(integer) line 11 at SQL statement | ||
4800 | 4726 | |
4801 | 4727 | /*+HashJoin(t_1 t_1)*/ |
4802 | 4728 | EXPLAIN (COSTS false) |
4803 | - SELECT nested_planner_one_t(2) FROM s1.t1 t_1 | |
4729 | + SELECT recall_planner_one_t() FROM s1.t1 t_1 | |
4804 | 4730 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4805 | 4731 | ORDER BY t_1.c1; |
4806 | -NOTICE: nested_planner_one_t(2) | |
4807 | -NOTICE: nested_planner_one_t(1) | |
4808 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4809 | - ORDER BY t_1.c1 LIMIT 1" | |
4810 | -PL/pgSQL function nested_planner_one_t(integer) line 11 at SQL statement | |
4811 | 4732 | LOG: pg_hint_plan: |
4812 | 4733 | used hint: |
4813 | -IndexScan(t_1) | |
4814 | 4734 | not used hint: |
4735 | +HashJoin(t_1 t_1) | |
4815 | 4736 | duplication hint: |
4816 | 4737 | error hint: |
4817 | 4738 | |
4818 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) FROM s1.t1 t_1 | |
4819 | - ORDER BY t_1.c1 LIMIT 1" | |
4820 | -PL/pgSQL function nested_planner_one_t(integer) line 11 at SQL statement | |
4739 | +CONTEXT: SQL function "recall_planner_one_t" during startup | |
4821 | 4740 | INFO: hint syntax error at or near "HashJoin(t_1 t_1)" |
4822 | 4741 | DETAIL: Relation name "t_1" is duplicated. |
4823 | 4742 | LOG: pg_hint_plan: |
@@ -4837,30 +4756,25 @@ HashJoin(t_1 t_1) | ||
4837 | 4756 | -> Seq Scan on t2 t_2 |
4838 | 4757 | (6 rows) |
4839 | 4758 | |
4840 | -DROP FUNCTION nested_planner_one_t(int); | |
4759 | +DROP FUNCTION recall_planner_one_t(int); | |
4760 | +ERROR: function recall_planner_one_t(integer) does not exist | |
4841 | 4761 | --No.13-4-7 |
4842 | 4762 | /*+HashJoin(t_1 t_1)*/ |
4843 | 4763 | EXPLAIN (COSTS false) |
4844 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4764 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4845 | 4765 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4846 | 4766 | ORDER BY t_1.c1; |
4847 | -NOTICE: nested_planner(2) | |
4848 | -NOTICE: nested_planner(1) | |
4849 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4850 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4851 | - ORDER BY t_1.c1 LIMIT 1" | |
4852 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4767 | +INFO: hint syntax error at or near "HashJoin(t_1 t_1)" | |
4768 | +DETAIL: Relation name "t_1" is duplicated. | |
4769 | +CONTEXT: SQL function "recall_planner" during startup | |
4853 | 4770 | LOG: pg_hint_plan: |
4854 | 4771 | used hint: |
4855 | -IndexScan(t_1) | |
4856 | 4772 | not used hint: |
4857 | 4773 | duplication hint: |
4858 | 4774 | error hint: |
4775 | +HashJoin(t_1 t_1) | |
4859 | 4776 | |
4860 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4861 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4862 | - ORDER BY t_1.c1 LIMIT 1" | |
4863 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4777 | +CONTEXT: SQL function "recall_planner" during startup | |
4864 | 4778 | INFO: hint syntax error at or near "HashJoin(t_1 t_1)" |
4865 | 4779 | DETAIL: Relation name "t_1" is duplicated. |
4866 | 4780 | LOG: pg_hint_plan: |
@@ -4883,28 +4797,23 @@ HashJoin(t_1 t_1) | ||
4883 | 4797 | --No.13-4-8 |
4884 | 4798 | /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/ |
4885 | 4799 | EXPLAIN (COSTS false) |
4886 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
4800 | + SELECT recall_planner() FROM s1.t1 t_1 | |
4887 | 4801 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
4888 | 4802 | ORDER BY t_1.c1; |
4889 | 4803 | INFO: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" |
4890 | 4804 | DETAIL: Conflict join method hint. |
4891 | -NOTICE: nested_planner(2) | |
4892 | -NOTICE: nested_planner(1) | |
4893 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4894 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4895 | - ORDER BY t_1.c1 LIMIT 1" | |
4896 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4805 | +INFO: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)" | |
4806 | +DETAIL: Conflict join method hint. | |
4807 | +CONTEXT: SQL function "recall_planner" during startup | |
4897 | 4808 | LOG: pg_hint_plan: |
4898 | 4809 | used hint: |
4899 | -IndexScan(t_1) | |
4810 | +HashJoin(t_1 t_2) | |
4900 | 4811 | not used hint: |
4901 | 4812 | duplication hint: |
4813 | +MergeJoin(t_1 t_2) | |
4902 | 4814 | error hint: |
4903 | 4815 | |
4904 | -CONTEXT: SQL statement "SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) FROM s1.t1 t_1 | |
4905 | - JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
4906 | - ORDER BY t_1.c1 LIMIT 1" | |
4907 | -PL/pgSQL function nested_planner(integer) line 12 at SQL statement | |
4816 | +CONTEXT: SQL function "recall_planner" during startup | |
4908 | 4817 | LOG: pg_hint_plan: |
4909 | 4818 | used hint: |
4910 | 4819 | HashJoin(t_1 t_2) |
@@ -1114,82 +1114,76 @@ EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1; | ||
1114 | 1114 | ---- |
1115 | 1115 | ---- No. A-13-4 output of debugging log on hint status |
1116 | 1116 | ---- |
1117 | +CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$ | |
1118 | + SELECT /*+ IndexScan(t_1) */t_1.c1 | |
1119 | + FROM s1.t1 t_1 | |
1120 | + JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) | |
1121 | + ORDER BY t_1.c1 LIMIT 1; | |
1122 | +$$ LANGUAGE SQL IMMUTABLE; | |
1117 | 1123 | |
1118 | 1124 | --No.13-4-1 |
1119 | 1125 | /*+HashJoin(t_1 t_2)*/ |
1120 | 1126 | EXPLAIN (COSTS false) |
1121 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
1127 | + SELECT recall_planner() FROM s1.t1 t_1 | |
1122 | 1128 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
1123 | 1129 | ORDER BY t_1.c1; |
1124 | 1130 | |
1125 | 1131 | --No.13-4-2 |
1126 | 1132 | /*+HashJoin(st_1 st_2)*/ |
1127 | 1133 | EXPLAIN (COSTS false) |
1128 | - SELECT nested_planner(2) FROM s1.t1 st_1 | |
1134 | + SELECT recall_planner() FROM s1.t1 st_1 | |
1129 | 1135 | JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1) |
1130 | 1136 | ORDER BY st_1.c1; |
1131 | 1137 | |
1132 | 1138 | --No.13-4-3 |
1133 | 1139 | /*+HashJoin(t_1 t_2)*/ |
1134 | 1140 | EXPLAIN (COSTS false) |
1135 | - SELECT nested_planner(2) FROM s1.t1 st_1 | |
1141 | + SELECT recall_planner() FROM s1.t1 st_1 | |
1136 | 1142 | JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1) |
1137 | 1143 | ORDER BY st_1.c1; |
1138 | 1144 | |
1139 | 1145 | --No.13-4-4 |
1140 | 1146 | /*+HashJoin(st_1 st_2)*/ |
1141 | 1147 | EXPLAIN (COSTS false) |
1142 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
1148 | + SELECT recall_planner() FROM s1.t1 t_1 | |
1143 | 1149 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
1144 | 1150 | ORDER BY t_1.c1; |
1145 | 1151 | |
1146 | 1152 | --No.13-4-5 |
1147 | 1153 | /*+HashJoin(t_1 t_1)*/ |
1148 | 1154 | EXPLAIN (COSTS false) |
1149 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
1155 | + SELECT recall_planner() FROM s1.t1 t_1 | |
1150 | 1156 | ORDER BY t_1.c1; |
1151 | 1157 | |
1152 | 1158 | --No.13-4-6 |
1153 | -CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$ | |
1154 | -DECLARE | |
1155 | - new_cnt int; | |
1156 | -BEGIN | |
1157 | - RAISE NOTICE 'nested_planner_one_t(%)', cnt; | |
1158 | - | |
1159 | - IF cnt <= 1 THEN | |
1160 | - RETURN 0; | |
1161 | - END IF; | |
1162 | - | |
1163 | - SELECT /*+ IndexScan(t_1) */ nested_planner_one_t(cnt - 1) INTO new_cnt | |
1159 | +CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$ | |
1160 | + SELECT /*+ IndexScan(t_1) */t_1.c1 | |
1164 | 1161 | FROM s1.t1 t_1 |
1165 | 1162 | ORDER BY t_1.c1 LIMIT 1; |
1166 | - | |
1167 | - RETURN new_cnt; | |
1168 | -END; | |
1169 | -$$ LANGUAGE plpgsql IMMUTABLE; | |
1163 | +$$ LANGUAGE SQL IMMUTABLE; | |
1170 | 1164 | |
1171 | 1165 | EXPLAIN (COSTS false) |
1172 | - SELECT nested_planner_one_t(2) FROM s1.t1 t_1 | |
1166 | + SELECT recall_planner_one_t() FROM s1.t1 t_1 | |
1173 | 1167 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
1174 | 1168 | ORDER BY t_1.c1; |
1175 | 1169 | /*+HashJoin(t_1 t_1)*/ |
1176 | 1170 | EXPLAIN (COSTS false) |
1177 | - SELECT nested_planner_one_t(2) FROM s1.t1 t_1 | |
1171 | + SELECT recall_planner_one_t() FROM s1.t1 t_1 | |
1178 | 1172 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
1179 | 1173 | ORDER BY t_1.c1; |
1180 | 1174 | |
1181 | -DROP FUNCTION nested_planner_one_t(int); | |
1175 | +DROP FUNCTION recall_planner_one_t(int); | |
1182 | 1176 | |
1183 | 1177 | --No.13-4-7 |
1184 | 1178 | /*+HashJoin(t_1 t_1)*/ |
1185 | 1179 | EXPLAIN (COSTS false) |
1186 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
1180 | + SELECT recall_planner() FROM s1.t1 t_1 | |
1187 | 1181 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
1188 | 1182 | ORDER BY t_1.c1; |
1189 | 1183 | |
1190 | 1184 | --No.13-4-8 |
1191 | 1185 | /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/ |
1192 | 1186 | EXPLAIN (COSTS false) |
1193 | - SELECT nested_planner(2) FROM s1.t1 t_1 | |
1187 | + SELECT recall_planner() FROM s1.t1 t_1 | |
1194 | 1188 | JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1) |
1195 | 1189 | ORDER BY t_1.c1; |