From 199d2f9d6b34530e8b0480542577e5d46adbb08d Mon Sep 17 00:00:00 2001 From: Shigeru HANADA Date: Mon, 18 Nov 2013 19:16:35 +0900 Subject: [PATCH] =?utf8?q?Rows=E3=83=92=E3=83=B3=E3=83=88=E3=81=AE?= =?utf8?q?=E5=9F=BA=E6=9C=AC=E7=9A=84=E3=81=AA=E3=83=86=E3=82=B9=E3=83=88?= =?utf8?q?=E3=82=92=E8=BF=BD=E5=8A=A0?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- expected/pg_hint_plan.out | 96 +++++++++++++++++++++++++++++++++++++++++++++++ sql/pg_hint_plan.sql | 11 +++++- 2 files changed, 105 insertions(+), 2 deletions(-) diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index dd006b4..566dbf2 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -8028,3 +8028,99 @@ error hint: CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1" PL/pgSQL function inline_code_block line 5 at SQL statement DROP EXTENSION pg_hint_plan; +-- Rows hint tests +-- value types +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); + QUERY PLAN +------------------------------------------------------------------------------ + Merge Join (cost=0.00..90.08 rows=1000 width=16) + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) +(4 rows) + +/*+ Rows(t1 t2 #99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +LOG: pg_hint_plan: +used hint: +Rows(t1 t2 #99) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + Merge Join (cost=0.00..90.08 rows=99 width=16) + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) +(4 rows) + +/*+ Rows(t1 t2 +99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +LOG: pg_hint_plan: +used hint: +Rows(t1 t2 +99) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + Merge Join (cost=0.00..90.08 rows=1099 width=16) + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) +(4 rows) + +/*+ Rows(t1 t2 -99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +LOG: pg_hint_plan: +used hint: +Rows(t1 t2 -99) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + Merge Join (cost=0.00..90.08 rows=901 width=16) + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) +(4 rows) + +/*+ Rows(t1 t2 *99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +LOG: pg_hint_plan: +used hint: +Rows(t1 t2 *99) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + Merge Join (cost=0.00..90.08 rows=99000 width=16) + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) +(4 rows) + +/*+ Rows(t1 t2 /99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +LOG: pg_hint_plan: +used hint: +Rows(t1 t2 /99) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------------------------------------ + Merge Join (cost=0.00..90.08 rows=10 width=16) + Merge Cond: (t1.id = t2.id) + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) +(4 rows) + diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql index 6c3e603..7bb89ef 100644 --- a/sql/pg_hint_plan.sql +++ b/sql/pg_hint_plan.sql @@ -847,8 +847,15 @@ $$; DROP EXTENSION pg_hint_plan; -- Rows hint tests +-- value types EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -/*+ Rows(t1 t2 #10) */ +/*+ Rows(t1 t2 #99) */ EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -/*+ Rows(t1 t2 *10) */ +/*+ Rows(t1 t2 +99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +/*+ Rows(t1 t2 -99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +/*+ Rows(t1 t2 *99) */ +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); +/*+ Rows(t1 t2 /99) */ EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- 2.11.0