From 5cc02f04ec536c532c19adc3552a13835db76c77 Mon Sep 17 00:00:00 2001 From: Mitsuru Hasegawa Date: Tue, 12 Jun 2012 17:56:10 +0900 Subject: [PATCH] =?utf8?q?VALUES=E3=81=ABSeqScan=E4=BB=A5=E5=A4=96?= =?utf8?q?=E3=81=AE=E3=82=B9=E3=82=AD=E3=83=A3=E3=83=B3=E3=83=92=E3=83=B3?= =?utf8?q?=E3=83=88=E3=82=92=E6=8C=87=E5=AE=9A=E3=81=99=E3=82=8B=E3=81=A8?= =?utf8?q?=E7=95=B0=E5=B8=B8=E7=B5=82=E4=BA=86=E3=81=99=E3=82=8B=E3=83=90?= =?utf8?q?=E3=82=B0=E3=82=92=E4=BF=AE=E6=AD=A3=E3=81=97=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- expected/pg_hint_plan.out | 43 +++++++++++++++++++++++++++++++++++++++++++ pg_hint_plan.c | 5 +++++ sql/pg_hint_plan.sql | 8 ++++++++ 3 files changed, 56 insertions(+) diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index 5acd1ab..0cac1da 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -494,3 +494,46 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id Index Cond: (id = t2.id) (12 rows) +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; + QUERY PLAN +----------------------------------------------- + Nested Loop + -> Values Scan on "*VALUES*" + -> Index Scan using t1_pkey on t1 + Index Cond: (id = "*VALUES*".column1) +(4 rows) + +/*HashJoin(t1 t2)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; +INFO: hint syntax error at or near "t1 t2)" +DETAIL: Relation "t2" does not exist. + QUERY PLAN +----------------------------------------------- + Nested Loop + -> Values Scan on "*VALUES*" + -> Index Scan using t1_pkey on t1 + Index Cond: (id = "*VALUES*".column1) +(4 rows) + +/*HashJoin(t1 *VALUES*)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; + QUERY PLAN +------------------------------------------- + Hash Join + Hash Cond: (t1.id = "*VALUES*".column1) + -> Seq Scan on t1 + -> Hash + -> Values Scan on "*VALUES*" +(5 rows) + +/*HashJoin(t1 *VALUES*) IndexScan(t1) IndexScan(*VALUES*)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; + QUERY PLAN +------------------------------------------- + Hash Join + Hash Cond: (t1.id = "*VALUES*".column1) + -> Index Scan using t1_pkey on t1 + -> Hash + -> Values Scan on "*VALUES*" +(5 rows) + diff --git a/pg_hint_plan.c b/pg_hint_plan.c index b7dc10e..57ade7d 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -1607,7 +1607,12 @@ rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_re RelOptInfo *rel = (RelOptInfo *) lfirst(l); RangeTblEntry *rte = root->simple_rte_array[rel->relid]; + /* + * スキャン方式が選択できるリレーションのみ、スキャンパスを再生成 + * する。 + */ if (rel->reloptkind != RELOPT_BASEREL || + rte->rtekind == RTE_VALUES || RelnameCmp(&hint->relname, &rte->eref->aliasname) != 0) continue; diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql index 4ca6f90..83838bf 100644 --- a/sql/pg_hint_plan.sql +++ b/sql/pg_hint_plan.sql @@ -92,3 +92,11 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; /*Leading(t3 t4 t4)*/ EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id; + +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; +/*HashJoin(t1 t2)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; +/*HashJoin(t1 *VALUES*)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; +/*HashJoin(t1 *VALUES*) IndexScan(t1) IndexScan(*VALUES*)*/ +EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id; -- 2.11.0