| リビジョン | 199d2f9d6b34530e8b0480542577e5d46adbb08d (tree) |
|---|---|
| 日時 | 2013-11-18 19:16:35 |
| 作者 | Shigeru HANADA <shigeru.hanada@gmai...> |
| コミッター | Shigeru HANADA |
Rowsヒントの基本的なテストを追加
expected/pg_hint_plan.out (diff) sql/pg_hint_plan.sql (diff)| @@ -8028,3 +8028,99 @@ error hint: | ||
| 8028 | 8028 | CONTEXT: SQL statement "SELECT /*+SeqScan(t1)*/ t1.id FROM t1 WHERE t1.id = 1" |
| 8029 | 8029 | PL/pgSQL function inline_code_block line 5 at SQL statement |
| 8030 | 8030 | DROP EXTENSION pg_hint_plan; |
| 8031 | +-- Rows hint tests | |
| 8032 | +-- value types | |
| 8033 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 8034 | + QUERY PLAN | |
| 8035 | +------------------------------------------------------------------------------ | |
| 8036 | + Merge Join (cost=0.00..90.08 rows=1000 width=16) | |
| 8037 | + Merge Cond: (t1.id = t2.id) | |
| 8038 | + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) | |
| 8039 | + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) | |
| 8040 | +(4 rows) | |
| 8041 | + | |
| 8042 | +/*+ Rows(t1 t2 #99) */ | |
| 8043 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 8044 | +LOG: pg_hint_plan: | |
| 8045 | +used hint: | |
| 8046 | +Rows(t1 t2 #99) | |
| 8047 | +not used hint: | |
| 8048 | +duplication hint: | |
| 8049 | +error hint: | |
| 8050 | + | |
| 8051 | + QUERY PLAN | |
| 8052 | +------------------------------------------------------------------------------ | |
| 8053 | + Merge Join (cost=0.00..90.08 rows=99 width=16) | |
| 8054 | + Merge Cond: (t1.id = t2.id) | |
| 8055 | + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) | |
| 8056 | + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) | |
| 8057 | +(4 rows) | |
| 8058 | + | |
| 8059 | +/*+ Rows(t1 t2 +99) */ | |
| 8060 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 8061 | +LOG: pg_hint_plan: | |
| 8062 | +used hint: | |
| 8063 | +Rows(t1 t2 +99) | |
| 8064 | +not used hint: | |
| 8065 | +duplication hint: | |
| 8066 | +error hint: | |
| 8067 | + | |
| 8068 | + QUERY PLAN | |
| 8069 | +------------------------------------------------------------------------------ | |
| 8070 | + Merge Join (cost=0.00..90.08 rows=1099 width=16) | |
| 8071 | + Merge Cond: (t1.id = t2.id) | |
| 8072 | + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) | |
| 8073 | + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) | |
| 8074 | +(4 rows) | |
| 8075 | + | |
| 8076 | +/*+ Rows(t1 t2 -99) */ | |
| 8077 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 8078 | +LOG: pg_hint_plan: | |
| 8079 | +used hint: | |
| 8080 | +Rows(t1 t2 -99) | |
| 8081 | +not used hint: | |
| 8082 | +duplication hint: | |
| 8083 | +error hint: | |
| 8084 | + | |
| 8085 | + QUERY PLAN | |
| 8086 | +------------------------------------------------------------------------------ | |
| 8087 | + Merge Join (cost=0.00..90.08 rows=901 width=16) | |
| 8088 | + Merge Cond: (t1.id = t2.id) | |
| 8089 | + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) | |
| 8090 | + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) | |
| 8091 | +(4 rows) | |
| 8092 | + | |
| 8093 | +/*+ Rows(t1 t2 *99) */ | |
| 8094 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 8095 | +LOG: pg_hint_plan: | |
| 8096 | +used hint: | |
| 8097 | +Rows(t1 t2 *99) | |
| 8098 | +not used hint: | |
| 8099 | +duplication hint: | |
| 8100 | +error hint: | |
| 8101 | + | |
| 8102 | + QUERY PLAN | |
| 8103 | +------------------------------------------------------------------------------ | |
| 8104 | + Merge Join (cost=0.00..90.08 rows=99000 width=16) | |
| 8105 | + Merge Cond: (t1.id = t2.id) | |
| 8106 | + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) | |
| 8107 | + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) | |
| 8108 | +(4 rows) | |
| 8109 | + | |
| 8110 | +/*+ Rows(t1 t2 /99) */ | |
| 8111 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 8112 | +LOG: pg_hint_plan: | |
| 8113 | +used hint: | |
| 8114 | +Rows(t1 t2 /99) | |
| 8115 | +not used hint: | |
| 8116 | +duplication hint: | |
| 8117 | +error hint: | |
| 8118 | + | |
| 8119 | + QUERY PLAN | |
| 8120 | +------------------------------------------------------------------------------ | |
| 8121 | + Merge Join (cost=0.00..90.08 rows=10 width=16) | |
| 8122 | + Merge Cond: (t1.id = t2.id) | |
| 8123 | + -> Index Scan using t1_pkey on t1 (cost=0.00..318.25 rows=10000 width=8) | |
| 8124 | + -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8) | |
| 8125 | +(4 rows) | |
| 8126 | + |
| @@ -847,8 +847,15 @@ $$; | ||
| 847 | 847 | DROP EXTENSION pg_hint_plan; |
| 848 | 848 | |
| 849 | 849 | -- Rows hint tests |
| 850 | +-- value types | |
| 850 | 851 | EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); |
| 851 | -/*+ Rows(t1 t2 #10) */ | |
| 852 | +/*+ Rows(t1 t2 #99) */ | |
| 852 | 853 | EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); |
| 853 | -/*+ Rows(t1 t2 *10) */ | |
| 854 | +/*+ Rows(t1 t2 +99) */ | |
| 855 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 856 | +/*+ Rows(t1 t2 -99) */ | |
| 857 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 858 | +/*+ Rows(t1 t2 *99) */ | |
| 859 | +EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); | |
| 860 | +/*+ Rows(t1 t2 /99) */ | |
| 854 | 861 | EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); |
Fork