外部テーブルのスキャン方式を指定するとエラー終了する

バグを修正した。
リグレッションテスト用に生成されたファイルの削除を
pgxsの仕組みを使用するように変更した。
継承テーブルの結合方式の試験を追加した。

diff --git a/Makefile b/Makefile
index bf497d6..81080cd 100644 (file)
--- a/Makefile
+++ b/Makefile
@@ -5,7 +5,9 @@
 #
 
 MODULES = pg_hint_plan
-REGRESS = init base_plan pg_hint_plan prepare
+REGRESS = init base_plan pg_hint_plan prepare fdw
+
+EXTRA_CLEAN = sql/fdw.sql expected/base_plan.out expected/prepare.out expected/fdw.out
 
 PG_CONFIG = pg_config
 PGXS := $(shell $(PG_CONFIG) --pgxs)
@@ -16,11 +18,6 @@ expected/base_plan.out: expected/base_plan-$(MAJORVERSION).out
 expected/prepare.out: expected/prepare-$(MAJORVERSION).out
        cp expected/prepare-$(MAJORVERSION).out expected/prepare.out
 
-.PHONY: subclean
-clean: subclean
-subclean:
-       rm -f expected/base_plan.out expected/prepare.out
-
 installcheck: expected/base_plan.out expected/prepare.out
 
 # pg_hint_plan.c includes core.c and make_join_rel.c

diff --git a/data/data.csv b/data/data.csv
new file mode 100644 (file)
index 0000000..43006b3
--- /dev/null
+++ b/data/data.csv
@@ -0,0 +1,10 @@
+1,1
+2,2
+3,3
+4,4
+5,5
+6,6
+7,7
+8,8
+9,9
+10,10

diff --git a/doc/pg_hint_plan-ja.html b/doc/pg_hint_plan-ja.html
index ca7e0c6..2cdf77b 100644 (file)
--- a/doc/pg_hint_plan-ja.html
+++ b/doc/pg_hint_plan-ja.html
@@ -175,7 +175,7 @@ postgres-#   ORDER BY a.aid;
 postgres=# </pre>
 </dd>
 <dt>オブジェクト名の引用</dt>
-<dd>ã\83\92ã\83³ã\83\88ã\81«è¨\98è¿°ã\81\99ã\82\8bã\82ªã\83\96ã\82¸ã\82§ã\82¯ã\83\88å\90\8dã\82\84å\88¥å\90\8dã\81\8cé\96\89ã\81\98æ\8b¬å¼§ï¼\88)ï¼\89ã\80\81äº\8cé\87\8då¼\95ç\94¨ç¬¦ï¼\88"ï¼\89ã\80\81空ç\99½ï¼\88ã\82¹ã\83\9aã\83¼ã\82¹ã\82\84ã\82¿ã\83\96ã\81ªã\81©ï¼\89ã\82\92å\90«ã\82\80å ´å\90\88ã\81¯ã\80\81é\80\9a常ã\81®SQLæ\96\87ã\81§ä½¿ã\81\86å ´å\90\88ã\81¨å\90\8cã\81\98ã\82\88ã\81\86ã\81«ã\83\80ã\83\96ã\83«ã\82¯ã\82©ã\83¼ã\83\88(")ã\81§å\9b²ã\82\93ã\81§ã\81\8fã\81 ã\81\95ã\81\84ï¼\88äº\8cé\87\8då¼\95ç\94¨ç¬¦ã\82\92å\90«ã\82\81ã\81\9fã\81\84å ´å\90\88ã\81¯ã\80\81äº\8cé\87\8då¼\95ç\94¨ç¬¦ã\82\922ã\81¤å\85¥å\8a\9bã\81\97ã\81¾ã\81\99ï¼\89。</dd>
+<dd>ã\83\92ã\83³ã\83\88ã\81«è¨\98è¿°ã\81\99ã\82\8bã\82ªã\83\96ã\82¸ã\82§ã\82¯ã\83\88å\90\8dã\82\84å\88¥å\90\8dã\81\8cé\96\89ã\81\98æ\8b¬å¼§ï¼\88)ï¼\89ã\80\81äº\8cé\87\8då¼\95ç\94¨ç¬¦ï¼\88"ï¼\89ã\80\81空ç\99½ï¼\88ã\82¹ã\83\9aã\83¼ã\82¹ã\80\81ã\82¿ã\83\96ã\80\81æ\94¹è¡\8cã\81®ã\81\84ã\81\9aã\82\8cã\81\8bï¼\89ã\82\92å\90«ã\82\80å ´å\90\88ã\81¯ã\80\81é\80\9a常ã\81®SQLæ\96\87ã\81§ä½¿ã\81\86å ´å\90\88ã\81¨å\90\8cã\81\98ã\82\88ã\81\86ã\81«ã\83\80ã\83\96ã\83«ã\82¯ã\82©ã\83¼ã\83\88(")ã\81§å\9b²ã\82\93ã\81§ã\81\8fã\81 ã\81\95ã\81\84ã\80\82äº\8cé\87\8då¼\95ç\94¨ç¬¦ã\81§æ\8b¬ã\81£ã\81\9fã\82ªã\83\96ã\82¸ã\82§ã\82¯ã\83\88å\90\8dã\81«äº\8cé\87\8då¼\95ç\94¨ç¬¦ã\82\92å\90«ã\82\81ã\81\9fã\81\84å ´å\90\88ã\81¯ã\80\812ã\81¤ç¶\9aã\81\91ã\81¦äº\8cé\87\8då¼\95ç\94¨ç¬¦ã\82\92è¨\98è¿°ã\81\97ã\81¦ã\81\8fã\81 ã\81\95ã\81\84。</dd>
 <dt>同一名称テーブルの区別</dt>
 <dd>スキーマ違いや同一テーブルの複数回使用などでクエリ中に同一名称のテーブルが複数回出現する場合は、テーブルに別名をつけてそれぞれのテーブルを区別してください。以下の例の1つ目のSQL文では、MergeJoin(t1 t1)をヒントに指定したとき、ヒント対象のオブジェクトが特定できずにエラーになっています。2つ目のSQL文では、各テーブルにptやstという別名をつけているため、実行計画作成時にヒントで指定した通りにMerge Joinを選択しています。
 </p>

diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out
index 6debba8..219f7ff 100644 (file)
--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -3543,6 +3543,133 @@ error hint:
          Index Cond: (id < 10)
 (34 rows)
 
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: (public.p1.id = t1.id)
+   ->  Append
+         ->  Seq Scan on p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Seq Scan on p1_c1 p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Seq Scan on p1_c2 p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Seq Scan on p1_c3 p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Tid Scan on p1_c4 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c1_c1 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c1_c2 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c3_c1 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c3_c2 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+   ->  Materialize
+         ->  Index Scan using t1_pkey on t1
+               Index Cond: (id < 10)
+(29 rows)
+
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+MergeJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Merge Join
+   Merge Cond: (public.p1.id = t1.id)
+   ->  Sort
+         Sort Key: public.p1.id
+         ->  Append
+               ->  Seq Scan on p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Seq Scan on p1_c1 p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Seq Scan on p1_c2 p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Seq Scan on p1_c3 p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Tid Scan on p1_c4 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c1_c1 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c1_c2 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c3_c1 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c3_c2 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+(30 rows)
+
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+HashJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Hash Join
+   Hash Cond: (public.p1.id = t1.id)
+   ->  Append
+         ->  Seq Scan on p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Seq Scan on p1_c1 p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Seq Scan on p1_c2 p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Seq Scan on p1_c3 p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+         ->  Tid Scan on p1_c4 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c1_c1 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c1_c2 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c3_c1 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+         ->  Tid Scan on p1_c3_c2 p1
+               TID Cond: (ctid = '(1,1)'::tid)
+               Filter: ((id >= 50) AND (id <= 51))
+   ->  Hash
+         ->  Index Scan using t1_pkey on t1
+               Index Cond: (id < 10)
+(29 rows)
+
 SET constraint_exclusion TO on;
 /*+SeqScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -3675,6 +3802,94 @@ error hint:
          Index Cond: (id < 10)
 (19 rows)
 
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: (public.p1.id = t1.id)
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+   ->  Materialize
+         ->  Append
+               ->  Seq Scan on p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Seq Scan on p1_c1 p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Tid Scan on p1_c1_c1 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c1_c2 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+(16 rows)
+
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+MergeJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Merge Join
+   Merge Cond: (public.p1.id = t1.id)
+   ->  Sort
+         Sort Key: public.p1.id
+         ->  Append
+               ->  Seq Scan on p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Seq Scan on p1_c1 p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Tid Scan on p1_c1_c1 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c1_c2 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+(17 rows)
+
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+HashJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Hash Join
+   Hash Cond: (t1.id = public.p1.id)
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+   ->  Hash
+         ->  Append
+               ->  Seq Scan on p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Seq Scan on p1_c1 p1
+                     Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+               ->  Tid Scan on p1_c1_c1 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+               ->  Tid Scan on p1_c1_c2 p1
+                     TID Cond: (ctid = '(1,1)'::tid)
+                     Filter: ((id >= 50) AND (id <= 51))
+(16 rows)
+
 SET constraint_exclusion TO off;
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
                            QUERY PLAN                            
@@ -3757,6 +3972,65 @@ error hint:
    Filter: ((id >= 50) AND (id <= 51))
 (3 rows)
 
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on p1
+         Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: ((id < 10) AND (id = p1.id))
+(5 rows)
+
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+MergeJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Merge Join
+   Merge Cond: (p1.id = t1.id)
+   ->  Sort
+         Sort Key: p1.id
+         ->  Seq Scan on p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+(8 rows)
+
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+HashJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Hash Join
+   Hash Cond: (t1.id = p1.id)
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+   ->  Hash
+         ->  Seq Scan on p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(7 rows)
+
 SET constraint_exclusion TO on;
 /*+SeqScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
@@ -3823,6 +4097,65 @@ error hint:
    Filter: ((id >= 50) AND (id <= 51))
 (3 rows)
 
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+NestLoop(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on p1
+         Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: ((id < 10) AND (id = p1.id))
+(5 rows)
+
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+MergeJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Merge Join
+   Merge Cond: (p1.id = t1.id)
+   ->  Sort
+         Sort Key: p1.id
+         ->  Seq Scan on p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+(8 rows)
+
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+LOG:  pg_hint_plan:
+used hint:
+HashJoin(p1 t1)
+not used hint:
+duplication hint:
+error hint:
+
+                                 QUERY PLAN                                  
+-----------------------------------------------------------------------------
+ Hash Join
+   Hash Cond: (t1.id = p1.id)
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id < 10)
+   ->  Hash
+         ->  Seq Scan on p1
+               Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
+(7 rows)
+
 SET constraint_exclusion TO off;
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
                               QUERY PLAN                               
@@ -4088,9 +4421,6 @@ error hint:
 (10 rows)
 
 -- sub query Leading hint test
-LOAD 'pg_hint_plan';
-SET pg_hint_plan.debug_print TO on;
-SET client_min_messages TO LOG;
 SET from_collapse_limit TO 100;
 SET geqo_threshold TO 100;
 EXPLAIN (COSTS false)

diff --git a/input/fdw.source b/input/fdw.source
new file mode 100644 (file)
index 0000000..95f7895
--- /dev/null
+++ b/input/fdw.source
@@ -0,0 +1,15 @@
+LOAD 'pg_hint_plan';
+SET pg_hint_plan.debug_print TO on;
+SET client_min_messages TO LOG;
+SET pg_hint_plan.enable TO on;
+
+CREATE EXTENSION file_fdw;
+CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
+CREATE USER MAPPING FOR PUBLIC SERVER file_server;
+CREATE FOREIGN TABLE ft1 (id int, val int) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/data.csv');
+
+-- foreign table test
+SELECT * FROM ft1;
+EXPLAIN (COSTS false) SELECT * FROM t1, ft1 WHERE t1.id = ft1.id;
+/*+SeqScan(t1)SeqScan(ft1)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, ft1 WHERE t1.id = ft1.id;

diff --git a/output/fdw.source b/output/fdw.source
new file mode 100644 (file)
index 0000000..71464cf
--- /dev/null
+++ b/output/fdw.source
@@ -0,0 +1,54 @@
+LOAD 'pg_hint_plan';
+SET pg_hint_plan.debug_print TO on;
+SET client_min_messages TO LOG;
+SET pg_hint_plan.enable TO on;
+CREATE EXTENSION file_fdw;
+CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
+CREATE USER MAPPING FOR PUBLIC SERVER file_server;
+CREATE FOREIGN TABLE ft1 (id int, val int) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/data.csv');
+-- foreign table test
+SELECT * FROM ft1;
+ id | val 
+----+-----
+  1 |   1
+  2 |   2
+  3 |   3
+  4 |   4
+  5 |   5
+  6 |   6
+  7 |   7
+  8 |   8
+  9 |   9
+ 10 |  10
+(10 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM t1, ft1 WHERE t1.id = ft1.id;
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Nested Loop
+   ->  Foreign Scan on ft1
+         Foreign File: @abs_srcdir@/data/data.csv
+   ->  Index Scan using t1_pkey on t1
+         Index Cond: (id = ft1.id)
+(5 rows)
+
+/*+SeqScan(t1)SeqScan(ft1)*/
+EXPLAIN (COSTS false) SELECT * FROM t1, ft1 WHERE t1.id = ft1.id;
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+SeqScan(ft1)
+duplication hint:
+error hint:
+
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
+ Hash Join
+   Hash Cond: (t1.id = ft1.id)
+   ->  Seq Scan on t1
+   ->  Hash
+         ->  Foreign Scan on ft1
+               Foreign File: @abs_srcdir@/data/data.csv
+(6 rows)
+

diff --git a/pg_hint_plan.c b/pg_hint_plan.c
index fc0212b..e148dd9 100644 (file)
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -1611,6 +1611,10 @@ find_scan_hint(PlannerInfo *root, RelOptInfo *rel)
 
        rte = root->simple_rte_array[rel->relid];
 
+       /* 外部表はスキャン方式が選択できない。 */
+       if (rte->relkind == RELKIND_FOREIGN_TABLE)
+               return NULL;
+
        for (i = 0; i < global->nscan_hints; i++)
        {
                ScanMethodHint *hint = global->scan_hints[i];
@@ -1956,6 +1960,12 @@ rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_re
                if (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION)
                        continue;
 
+               rte = root->simple_rte_array[rel->relid];
+
+               /* 外部表はスキャン方式が選択できない。 */
+               if (rte->relkind == RELKIND_FOREIGN_TABLE)
+                       continue;
+
                /*
                 * scan method hint が指定されていなければ、初期値のGUCパラメータでscan
                 * path を再生成する。
@@ -1970,7 +1980,6 @@ rebuild_scan_path(PlanHint *plan, PlannerInfo *root, int level, List *initial_re
 
                list_free_deep(rel->pathlist);
                rel->pathlist = NIL;
-               rte = root->simple_rte_array[rel->relid];
                if (rte->inh)
                {
                        /* It's an "append relation", process accordingly */

diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql
index f11da5d..c18c506 100644 (file)
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -365,6 +365,12 @@ EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND
 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
 /*+TidScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
 SET constraint_exclusion TO on;
 /*+SeqScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -374,6 +380,12 @@ EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND
 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
 /*+TidScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
 
 SET constraint_exclusion TO off;
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
@@ -388,6 +400,12 @@ EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.c
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
 /*+TidScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
 SET constraint_exclusion TO on;
 /*+SeqScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
@@ -397,6 +415,12 @@ EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.c
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
 /*+TidScan(p1)*/
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
+/*+NestLoop(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+MergeJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
+/*+HashJoin(p1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
 
 SET constraint_exclusion TO off;
 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -430,9 +454,6 @@ EXPLAIN (COSTS false) SELECT * FROM t1 """t1 )      ", t2 "t        2 """, t3 "T3" WHERE """
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
 
 -- sub query Leading hint test
-LOAD 'pg_hint_plan';
-SET pg_hint_plan.debug_print TO on;
-SET client_min_messages TO LOG;
 SET from_collapse_limit TO 100;
 SET geqo_threshold TO 100;
 EXPLAIN (COSTS false)