• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

リビジョン2170a134bd995dac8ea2272286611521d16de41a (tree)
日時2014-01-16 15:52:16
作者Takashi Suzuki <suzuki.takashi@metr...>
コミッターTakashi Suzuki

ログメッセージ

Rowsヒント句のRULEまたはVIEWの試験(R-2-3-*)を追加した。

変更サマリ

差分

--- /dev/null
+++ b/expected/R_2-3-1.out
@@ -0,0 +1,92 @@
1+ QUERY PLAN
2+-----------------------------------------------------------------------------------------------
3+ Aggregate (cost=xxx rows=1 width=4)
4+ -> Nested Loop (cost=xxx rows=1 width=4)
5+ Join Filter: (t1.c1 = t4.c1)
6+ -> Nested Loop (cost=xxx rows=1 width=12)
7+ Join Filter: (t1.c1 = t3.c1)
8+ -> Merge Join (cost=xxx rows=1 width=8)
9+ Merge Cond: (t1.c1 = t2.c1)
10+ -> Nested Loop (cost=xxx rows=6 width=4)
11+ -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=4)
12+ Filter: (ctid = '(1,1)'::tid)
13+ -> Seq Scan on r1 (cost=xxx rows=6 width=0)
14+ Filter: (c1 = 1)
15+ -> Sort (cost=xxx rows=1 width=4)
16+ Sort Key: t2.c1
17+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
18+ Filter: (ctid = '(1,1)'::tid)
19+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
20+ TID Cond: (ctid = '(1,1)'::tid)
21+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
22+ TID Cond: (ctid = '(1,1)'::tid)
23+(20 rows)
24+
25+ QUERY PLAN
26+---------------------------------------------------------------------------------
27+ Aggregate (cost=xxx rows=1 width=4)
28+ -> Nested Loop (cost=xxx rows=2 width=4)
29+ Join Filter: (t1.c1 = t4.c1)
30+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
31+ TID Cond: (ctid = '(1,1)'::tid)
32+ -> Nested Loop (cost=xxx rows=2 width=12)
33+ Join Filter: (t1.c1 = t3.c1)
34+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
35+ TID Cond: (ctid = '(1,1)'::tid)
36+ -> Nested Loop (cost=xxx rows=2 width=8)
37+ Join Filter: (t1.c1 = t2.c1)
38+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
39+ Filter: (ctid = '(1,1)'::tid)
40+ -> Nested Loop (cost=xxx rows=2 width=4)
41+ -> Tid Scan on t1 (cost=xxx rows=1 width=4)
42+ TID Cond: (ctid = '(1,1)'::tid)
43+ -> Seq Scan on r1 (cost=xxx rows=6 width=0)
44+ Filter: (c1 = 1)
45+(18 rows)
46+
47+ QUERY PLAN
48+----------------------------------------------------------------------------------------------------
49+ Aggregate (cost=xxx rows=1 width=4)
50+ -> Nested Loop (cost=xxx rows=1 width=4)
51+ Join Filter: (b1t1.c1 = b1t4.c1)
52+ -> Nested Loop (cost=xxx rows=1 width=12)
53+ Join Filter: (b1t1.c1 = b1t3.c1)
54+ -> Merge Join (cost=xxx rows=1 width=8)
55+ Merge Cond: (b1t1.c1 = b1t2.c1)
56+ -> Nested Loop (cost=xxx rows=6 width=4)
57+ -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=4)
58+ Filter: (ctid = '(1,1)'::tid)
59+ -> Seq Scan on r1_ (cost=xxx rows=6 width=0)
60+ Filter: (c1 = 1)
61+ -> Sort (cost=xxx rows=1 width=4)
62+ Sort Key: b1t2.c1
63+ -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=4)
64+ Filter: (ctid = '(1,1)'::tid)
65+ -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=4)
66+ TID Cond: (ctid = '(1,1)'::tid)
67+ -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=4)
68+ TID Cond: (ctid = '(1,1)'::tid)
69+(20 rows)
70+
71+ QUERY PLAN
72+-------------------------------------------------------------------------------------
73+ Aggregate (cost=xxx rows=1 width=4)
74+ -> Nested Loop (cost=xxx rows=2 width=4)
75+ Join Filter: (b1t1.c1 = b1t4.c1)
76+ -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=4)
77+ TID Cond: (ctid = '(1,1)'::tid)
78+ -> Nested Loop (cost=xxx rows=2 width=12)
79+ Join Filter: (b1t1.c1 = b1t3.c1)
80+ -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=4)
81+ TID Cond: (ctid = '(1,1)'::tid)
82+ -> Nested Loop (cost=xxx rows=2 width=8)
83+ Join Filter: (b1t1.c1 = b1t2.c1)
84+ -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=4)
85+ Filter: (ctid = '(1,1)'::tid)
86+ -> Nested Loop (cost=xxx rows=2 width=4)
87+ -> Tid Scan on t1 b1t1 (cost=xxx rows=1 width=4)
88+ TID Cond: (ctid = '(1,1)'::tid)
89+ -> Seq Scan on r1_ (cost=xxx rows=6 width=0)
90+ Filter: (c1 = 1)
91+(18 rows)
92+
--- /dev/null
+++ b/expected/R_2-3-2.out
@@ -0,0 +1,172 @@
1+ QUERY PLAN
2+-----------------------------------------------------------------------------------------------
3+ Aggregate (cost=xxx rows=1 width=4)
4+ -> Nested Loop (cost=xxx rows=1 width=4)
5+ Join Filter: (t1.c1 = t4.c1)
6+ -> Nested Loop (cost=xxx rows=1 width=12)
7+ Join Filter: (t1.c1 = t3.c1)
8+ -> Merge Join (cost=xxx rows=1 width=8)
9+ Merge Cond: (t1.c1 = t2.c1)
10+ -> Nested Loop (cost=xxx rows=6 width=4)
11+ -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=4)
12+ Filter: (ctid = '(1,1)'::tid)
13+ -> Seq Scan on r2 (cost=xxx rows=6 width=0)
14+ Filter: (c1 = 1)
15+ -> Sort (cost=xxx rows=1 width=4)
16+ Sort Key: t2.c1
17+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
18+ Filter: (ctid = '(1,1)'::tid)
19+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
20+ TID Cond: (ctid = '(1,1)'::tid)
21+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
22+ TID Cond: (ctid = '(1,1)'::tid)
23+
24+ Aggregate (cost=xxx rows=1 width=4)
25+ -> Nested Loop (cost=xxx rows=1 width=4)
26+ Join Filter: (t1.c1 = t4.c1)
27+ -> Nested Loop (cost=xxx rows=1 width=12)
28+ Join Filter: (t1.c1 = t3.c1)
29+ -> Merge Join (cost=xxx rows=1 width=8)
30+ Merge Cond: (t1.c1 = t2.c1)
31+ -> Nested Loop (cost=xxx rows=6 width=4)
32+ -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=4)
33+ Filter: (ctid = '(1,1)'::tid)
34+ -> Seq Scan on r2 (cost=xxx rows=6 width=0)
35+ Filter: (c1 = 1)
36+ -> Sort (cost=xxx rows=1 width=4)
37+ Sort Key: t2.c1
38+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
39+ Filter: (ctid = '(1,1)'::tid)
40+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
41+ TID Cond: (ctid = '(1,1)'::tid)
42+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
43+ TID Cond: (ctid = '(1,1)'::tid)
44+(41 rows)
45+
46+ QUERY PLAN
47+---------------------------------------------------------------------------------
48+ Aggregate (cost=xxx rows=1 width=4)
49+ -> Nested Loop (cost=xxx rows=2 width=4)
50+ Join Filter: (t1.c1 = t4.c1)
51+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
52+ TID Cond: (ctid = '(1,1)'::tid)
53+ -> Nested Loop (cost=xxx rows=2 width=12)
54+ Join Filter: (t1.c1 = t3.c1)
55+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
56+ TID Cond: (ctid = '(1,1)'::tid)
57+ -> Nested Loop (cost=xxx rows=2 width=8)
58+ Join Filter: (t1.c1 = t2.c1)
59+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
60+ Filter: (ctid = '(1,1)'::tid)
61+ -> Nested Loop (cost=xxx rows=2 width=4)
62+ -> Tid Scan on t1 (cost=xxx rows=1 width=4)
63+ TID Cond: (ctid = '(1,1)'::tid)
64+ -> Seq Scan on r2 (cost=xxx rows=6 width=0)
65+ Filter: (c1 = 1)
66+
67+ Aggregate (cost=xxx rows=1 width=4)
68+ -> Nested Loop (cost=xxx rows=2 width=4)
69+ Join Filter: (t1.c1 = t4.c1)
70+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
71+ TID Cond: (ctid = '(1,1)'::tid)
72+ -> Nested Loop (cost=xxx rows=2 width=12)
73+ Join Filter: (t1.c1 = t3.c1)
74+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
75+ TID Cond: (ctid = '(1,1)'::tid)
76+ -> Nested Loop (cost=xxx rows=2 width=8)
77+ Join Filter: (t1.c1 = t2.c1)
78+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
79+ Filter: (ctid = '(1,1)'::tid)
80+ -> Nested Loop (cost=xxx rows=2 width=4)
81+ -> Tid Scan on t1 (cost=xxx rows=1 width=4)
82+ TID Cond: (ctid = '(1,1)'::tid)
83+ -> Seq Scan on r2 (cost=xxx rows=6 width=0)
84+ Filter: (c1 = 1)
85+(37 rows)
86+
87+ QUERY PLAN
88+----------------------------------------------------------------------------------------------------
89+ Aggregate (cost=xxx rows=1 width=4)
90+ -> Nested Loop (cost=xxx rows=1 width=4)
91+ Join Filter: (b1t1.c1 = b1t4.c1)
92+ -> Nested Loop (cost=xxx rows=1 width=12)
93+ Join Filter: (b1t1.c1 = b1t3.c1)
94+ -> Merge Join (cost=xxx rows=1 width=8)
95+ Merge Cond: (b1t1.c1 = b1t2.c1)
96+ -> Nested Loop (cost=xxx rows=6 width=4)
97+ -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=4)
98+ Filter: (ctid = '(1,1)'::tid)
99+ -> Seq Scan on r2_ (cost=xxx rows=6 width=0)
100+ Filter: (c1 = 1)
101+ -> Sort (cost=xxx rows=1 width=4)
102+ Sort Key: b1t2.c1
103+ -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=4)
104+ Filter: (ctid = '(1,1)'::tid)
105+ -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=4)
106+ TID Cond: (ctid = '(1,1)'::tid)
107+ -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=4)
108+ TID Cond: (ctid = '(1,1)'::tid)
109+
110+ Aggregate (cost=xxx rows=1 width=4)
111+ -> Nested Loop (cost=xxx rows=1 width=4)
112+ Join Filter: (b2t1.c1 = b2t4.c1)
113+ -> Nested Loop (cost=xxx rows=1 width=12)
114+ Join Filter: (b2t1.c1 = b2t3.c1)
115+ -> Merge Join (cost=xxx rows=1 width=8)
116+ Merge Cond: (b2t1.c1 = b2t2.c1)
117+ -> Nested Loop (cost=xxx rows=6 width=4)
118+ -> Index Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=4)
119+ Filter: (ctid = '(1,1)'::tid)
120+ -> Seq Scan on r2_ (cost=xxx rows=6 width=0)
121+ Filter: (c1 = 1)
122+ -> Sort (cost=xxx rows=1 width=4)
123+ Sort Key: b2t2.c1
124+ -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=4)
125+ Filter: (ctid = '(1,1)'::tid)
126+ -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=4)
127+ TID Cond: (ctid = '(1,1)'::tid)
128+ -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=4)
129+ TID Cond: (ctid = '(1,1)'::tid)
130+(41 rows)
131+
132+ QUERY PLAN
133+-------------------------------------------------------------------------------------
134+ Aggregate (cost=xxx rows=1 width=4)
135+ -> Nested Loop (cost=xxx rows=2 width=4)
136+ Join Filter: (b1t1.c1 = b1t4.c1)
137+ -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=4)
138+ TID Cond: (ctid = '(1,1)'::tid)
139+ -> Nested Loop (cost=xxx rows=2 width=12)
140+ Join Filter: (b1t1.c1 = b1t3.c1)
141+ -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=4)
142+ TID Cond: (ctid = '(1,1)'::tid)
143+ -> Nested Loop (cost=xxx rows=2 width=8)
144+ Join Filter: (b1t1.c1 = b1t2.c1)
145+ -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=4)
146+ Filter: (ctid = '(1,1)'::tid)
147+ -> Nested Loop (cost=xxx rows=2 width=4)
148+ -> Tid Scan on t1 b1t1 (cost=xxx rows=1 width=4)
149+ TID Cond: (ctid = '(1,1)'::tid)
150+ -> Seq Scan on r2_ (cost=xxx rows=6 width=0)
151+ Filter: (c1 = 1)
152+
153+ Aggregate (cost=xxx rows=1 width=4)
154+ -> Nested Loop (cost=xxx rows=2 width=4)
155+ Join Filter: (b2t1.c1 = b2t4.c1)
156+ -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=4)
157+ TID Cond: (ctid = '(1,1)'::tid)
158+ -> Nested Loop (cost=xxx rows=2 width=12)
159+ Join Filter: (b2t1.c1 = b2t3.c1)
160+ -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=4)
161+ TID Cond: (ctid = '(1,1)'::tid)
162+ -> Nested Loop (cost=xxx rows=2 width=8)
163+ Join Filter: (b2t1.c1 = b2t2.c1)
164+ -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=4)
165+ Filter: (ctid = '(1,1)'::tid)
166+ -> Nested Loop (cost=xxx rows=2 width=4)
167+ -> Tid Scan on t1 b2t1 (cost=xxx rows=1 width=4)
168+ TID Cond: (ctid = '(1,1)'::tid)
169+ -> Seq Scan on r2_ (cost=xxx rows=6 width=0)
170+ Filter: (c1 = 1)
171+(37 rows)
172+
--- /dev/null
+++ b/expected/R_2-3-3.out
@@ -0,0 +1,252 @@
1+ QUERY PLAN
2+-----------------------------------------------------------------------------------------------
3+ Aggregate (cost=xxx rows=1 width=4)
4+ -> Nested Loop (cost=xxx rows=1 width=4)
5+ Join Filter: (t1.c1 = t4.c1)
6+ -> Nested Loop (cost=xxx rows=1 width=12)
7+ Join Filter: (t1.c1 = t3.c1)
8+ -> Merge Join (cost=xxx rows=1 width=8)
9+ Merge Cond: (t1.c1 = t2.c1)
10+ -> Nested Loop (cost=xxx rows=6 width=4)
11+ -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=4)
12+ Filter: (ctid = '(1,1)'::tid)
13+ -> Seq Scan on r3 (cost=xxx rows=6 width=0)
14+ Filter: (c1 = 1)
15+ -> Sort (cost=xxx rows=1 width=4)
16+ Sort Key: t2.c1
17+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
18+ Filter: (ctid = '(1,1)'::tid)
19+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
20+ TID Cond: (ctid = '(1,1)'::tid)
21+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
22+ TID Cond: (ctid = '(1,1)'::tid)
23+
24+ Aggregate (cost=xxx rows=1 width=4)
25+ -> Nested Loop (cost=xxx rows=1 width=4)
26+ Join Filter: (t1.c1 = t4.c1)
27+ -> Nested Loop (cost=xxx rows=1 width=12)
28+ Join Filter: (t1.c1 = t3.c1)
29+ -> Merge Join (cost=xxx rows=1 width=8)
30+ Merge Cond: (t1.c1 = t2.c1)
31+ -> Nested Loop (cost=xxx rows=6 width=4)
32+ -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=4)
33+ Filter: (ctid = '(1,1)'::tid)
34+ -> Seq Scan on r3 (cost=xxx rows=6 width=0)
35+ Filter: (c1 = 1)
36+ -> Sort (cost=xxx rows=1 width=4)
37+ Sort Key: t2.c1
38+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
39+ Filter: (ctid = '(1,1)'::tid)
40+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
41+ TID Cond: (ctid = '(1,1)'::tid)
42+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
43+ TID Cond: (ctid = '(1,1)'::tid)
44+
45+ Aggregate (cost=xxx rows=1 width=4)
46+ -> Nested Loop (cost=xxx rows=1 width=4)
47+ Join Filter: (t1.c1 = t4.c1)
48+ -> Nested Loop (cost=xxx rows=1 width=12)
49+ Join Filter: (t1.c1 = t3.c1)
50+ -> Merge Join (cost=xxx rows=1 width=8)
51+ Merge Cond: (t1.c1 = t2.c1)
52+ -> Nested Loop (cost=xxx rows=6 width=4)
53+ -> Index Scan using t1_i1 on t1 (cost=xxx rows=1 width=4)
54+ Filter: (ctid = '(1,1)'::tid)
55+ -> Seq Scan on r3 (cost=xxx rows=6 width=0)
56+ Filter: (c1 = 1)
57+ -> Sort (cost=xxx rows=1 width=4)
58+ Sort Key: t2.c1
59+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
60+ Filter: (ctid = '(1,1)'::tid)
61+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
62+ TID Cond: (ctid = '(1,1)'::tid)
63+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
64+ TID Cond: (ctid = '(1,1)'::tid)
65+(62 rows)
66+
67+ QUERY PLAN
68+---------------------------------------------------------------------------------
69+ Aggregate (cost=xxx rows=1 width=4)
70+ -> Nested Loop (cost=xxx rows=2 width=4)
71+ Join Filter: (t1.c1 = t4.c1)
72+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
73+ TID Cond: (ctid = '(1,1)'::tid)
74+ -> Nested Loop (cost=xxx rows=2 width=12)
75+ Join Filter: (t1.c1 = t3.c1)
76+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
77+ TID Cond: (ctid = '(1,1)'::tid)
78+ -> Nested Loop (cost=xxx rows=2 width=8)
79+ Join Filter: (t1.c1 = t2.c1)
80+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
81+ Filter: (ctid = '(1,1)'::tid)
82+ -> Nested Loop (cost=xxx rows=2 width=4)
83+ -> Tid Scan on t1 (cost=xxx rows=1 width=4)
84+ TID Cond: (ctid = '(1,1)'::tid)
85+ -> Seq Scan on r3 (cost=xxx rows=6 width=0)
86+ Filter: (c1 = 1)
87+
88+ Aggregate (cost=xxx rows=1 width=4)
89+ -> Nested Loop (cost=xxx rows=2 width=4)
90+ Join Filter: (t1.c1 = t4.c1)
91+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
92+ TID Cond: (ctid = '(1,1)'::tid)
93+ -> Nested Loop (cost=xxx rows=2 width=12)
94+ Join Filter: (t1.c1 = t3.c1)
95+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
96+ TID Cond: (ctid = '(1,1)'::tid)
97+ -> Nested Loop (cost=xxx rows=2 width=8)
98+ Join Filter: (t1.c1 = t2.c1)
99+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
100+ Filter: (ctid = '(1,1)'::tid)
101+ -> Nested Loop (cost=xxx rows=2 width=4)
102+ -> Tid Scan on t1 (cost=xxx rows=1 width=4)
103+ TID Cond: (ctid = '(1,1)'::tid)
104+ -> Seq Scan on r3 (cost=xxx rows=6 width=0)
105+ Filter: (c1 = 1)
106+
107+ Aggregate (cost=xxx rows=1 width=4)
108+ -> Nested Loop (cost=xxx rows=2 width=4)
109+ Join Filter: (t1.c1 = t4.c1)
110+ -> Tid Scan on t4 (cost=xxx rows=1 width=4)
111+ TID Cond: (ctid = '(1,1)'::tid)
112+ -> Nested Loop (cost=xxx rows=2 width=12)
113+ Join Filter: (t1.c1 = t3.c1)
114+ -> Tid Scan on t3 (cost=xxx rows=1 width=4)
115+ TID Cond: (ctid = '(1,1)'::tid)
116+ -> Nested Loop (cost=xxx rows=2 width=8)
117+ Join Filter: (t1.c1 = t2.c1)
118+ -> Seq Scan on t2 (cost=xxx rows=1 width=4)
119+ Filter: (ctid = '(1,1)'::tid)
120+ -> Nested Loop (cost=xxx rows=2 width=4)
121+ -> Tid Scan on t1 (cost=xxx rows=1 width=4)
122+ TID Cond: (ctid = '(1,1)'::tid)
123+ -> Seq Scan on r3 (cost=xxx rows=6 width=0)
124+ Filter: (c1 = 1)
125+(56 rows)
126+
127+ QUERY PLAN
128+----------------------------------------------------------------------------------------------------
129+ Aggregate (cost=xxx rows=1 width=4)
130+ -> Nested Loop (cost=xxx rows=1 width=4)
131+ Join Filter: (b1t1.c1 = b1t4.c1)
132+ -> Nested Loop (cost=xxx rows=1 width=12)
133+ Join Filter: (b1t1.c1 = b1t3.c1)
134+ -> Merge Join (cost=xxx rows=1 width=8)
135+ Merge Cond: (b1t1.c1 = b1t2.c1)
136+ -> Nested Loop (cost=xxx rows=6 width=4)
137+ -> Index Scan using t1_i1 on t1 b1t1 (cost=xxx rows=1 width=4)
138+ Filter: (ctid = '(1,1)'::tid)
139+ -> Seq Scan on r3_ (cost=xxx rows=6 width=0)
140+ Filter: (c1 = 1)
141+ -> Sort (cost=xxx rows=1 width=4)
142+ Sort Key: b1t2.c1
143+ -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=4)
144+ Filter: (ctid = '(1,1)'::tid)
145+ -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=4)
146+ TID Cond: (ctid = '(1,1)'::tid)
147+ -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=4)
148+ TID Cond: (ctid = '(1,1)'::tid)
149+
150+ Aggregate (cost=xxx rows=1 width=4)
151+ -> Nested Loop (cost=xxx rows=1 width=4)
152+ Join Filter: (b2t1.c1 = b2t4.c1)
153+ -> Nested Loop (cost=xxx rows=1 width=12)
154+ Join Filter: (b2t1.c1 = b2t3.c1)
155+ -> Merge Join (cost=xxx rows=1 width=8)
156+ Merge Cond: (b2t1.c1 = b2t2.c1)
157+ -> Nested Loop (cost=xxx rows=6 width=4)
158+ -> Index Scan using t1_i1 on t1 b2t1 (cost=xxx rows=1 width=4)
159+ Filter: (ctid = '(1,1)'::tid)
160+ -> Seq Scan on r3_ (cost=xxx rows=6 width=0)
161+ Filter: (c1 = 1)
162+ -> Sort (cost=xxx rows=1 width=4)
163+ Sort Key: b2t2.c1
164+ -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=4)
165+ Filter: (ctid = '(1,1)'::tid)
166+ -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=4)
167+ TID Cond: (ctid = '(1,1)'::tid)
168+ -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=4)
169+ TID Cond: (ctid = '(1,1)'::tid)
170+
171+ Aggregate (cost=xxx rows=1 width=4)
172+ -> Nested Loop (cost=xxx rows=1 width=4)
173+ Join Filter: (b3t1.c1 = b3t4.c1)
174+ -> Nested Loop (cost=xxx rows=1 width=12)
175+ Join Filter: (b3t1.c1 = b3t3.c1)
176+ -> Merge Join (cost=xxx rows=1 width=8)
177+ Merge Cond: (b3t1.c1 = b3t2.c1)
178+ -> Nested Loop (cost=xxx rows=6 width=4)
179+ -> Index Scan using t1_i1 on t1 b3t1 (cost=xxx rows=1 width=4)
180+ Filter: (ctid = '(1,1)'::tid)
181+ -> Seq Scan on r3_ (cost=xxx rows=6 width=0)
182+ Filter: (c1 = 1)
183+ -> Sort (cost=xxx rows=1 width=4)
184+ Sort Key: b3t2.c1
185+ -> Seq Scan on t2 b3t2 (cost=xxx rows=1 width=4)
186+ Filter: (ctid = '(1,1)'::tid)
187+ -> Tid Scan on t3 b3t3 (cost=xxx rows=1 width=4)
188+ TID Cond: (ctid = '(1,1)'::tid)
189+ -> Tid Scan on t4 b3t4 (cost=xxx rows=1 width=4)
190+ TID Cond: (ctid = '(1,1)'::tid)
191+(62 rows)
192+
193+ QUERY PLAN
194+-------------------------------------------------------------------------------------
195+ Aggregate (cost=xxx rows=1 width=4)
196+ -> Nested Loop (cost=xxx rows=2 width=4)
197+ Join Filter: (b1t1.c1 = b1t4.c1)
198+ -> Tid Scan on t4 b1t4 (cost=xxx rows=1 width=4)
199+ TID Cond: (ctid = '(1,1)'::tid)
200+ -> Nested Loop (cost=xxx rows=2 width=12)
201+ Join Filter: (b1t1.c1 = b1t3.c1)
202+ -> Tid Scan on t3 b1t3 (cost=xxx rows=1 width=4)
203+ TID Cond: (ctid = '(1,1)'::tid)
204+ -> Nested Loop (cost=xxx rows=2 width=8)
205+ Join Filter: (b1t1.c1 = b1t2.c1)
206+ -> Seq Scan on t2 b1t2 (cost=xxx rows=1 width=4)
207+ Filter: (ctid = '(1,1)'::tid)
208+ -> Nested Loop (cost=xxx rows=2 width=4)
209+ -> Tid Scan on t1 b1t1 (cost=xxx rows=1 width=4)
210+ TID Cond: (ctid = '(1,1)'::tid)
211+ -> Seq Scan on r3_ (cost=xxx rows=6 width=0)
212+ Filter: (c1 = 1)
213+
214+ Aggregate (cost=xxx rows=1 width=4)
215+ -> Nested Loop (cost=xxx rows=2 width=4)
216+ Join Filter: (b2t1.c1 = b2t4.c1)
217+ -> Tid Scan on t4 b2t4 (cost=xxx rows=1 width=4)
218+ TID Cond: (ctid = '(1,1)'::tid)
219+ -> Nested Loop (cost=xxx rows=2 width=12)
220+ Join Filter: (b2t1.c1 = b2t3.c1)
221+ -> Tid Scan on t3 b2t3 (cost=xxx rows=1 width=4)
222+ TID Cond: (ctid = '(1,1)'::tid)
223+ -> Nested Loop (cost=xxx rows=2 width=8)
224+ Join Filter: (b2t1.c1 = b2t2.c1)
225+ -> Seq Scan on t2 b2t2 (cost=xxx rows=1 width=4)
226+ Filter: (ctid = '(1,1)'::tid)
227+ -> Nested Loop (cost=xxx rows=2 width=4)
228+ -> Tid Scan on t1 b2t1 (cost=xxx rows=1 width=4)
229+ TID Cond: (ctid = '(1,1)'::tid)
230+ -> Seq Scan on r3_ (cost=xxx rows=6 width=0)
231+ Filter: (c1 = 1)
232+
233+ Aggregate (cost=xxx rows=1 width=4)
234+ -> Nested Loop (cost=xxx rows=2 width=4)
235+ Join Filter: (b3t1.c1 = b3t4.c1)
236+ -> Tid Scan on t4 b3t4 (cost=xxx rows=1 width=4)
237+ TID Cond: (ctid = '(1,1)'::tid)
238+ -> Nested Loop (cost=xxx rows=2 width=12)
239+ Join Filter: (b3t1.c1 = b3t3.c1)
240+ -> Tid Scan on t3 b3t3 (cost=xxx rows=1 width=4)
241+ TID Cond: (ctid = '(1,1)'::tid)
242+ -> Nested Loop (cost=xxx rows=2 width=8)
243+ Join Filter: (b3t1.c1 = b3t2.c1)
244+ -> Seq Scan on t2 b3t2 (cost=xxx rows=1 width=4)
245+ Filter: (ctid = '(1,1)'::tid)
246+ -> Nested Loop (cost=xxx rows=2 width=4)
247+ -> Tid Scan on t1 b3t1 (cost=xxx rows=1 width=4)
248+ TID Cond: (ctid = '(1,1)'::tid)
249+ -> Seq Scan on r3_ (cost=xxx rows=6 width=0)
250+ Filter: (c1 = 1)
251+(56 rows)
252+
--- /dev/null
+++ b/expected/R_2-3-4.out
@@ -0,0 +1,18 @@
1+ QUERY PLAN
2+--------------------------------------------------------------------------
3+ Hash Join (cost=xxx rows=1000 width=30)
4+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
5+ -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=15)
6+ -> Hash (cost=xxx rows=1000 width=15)
7+ -> Seq Scan on t1 v1t1_1 (cost=xxx rows=1000 width=15)
8+(5 rows)
9+
10+ QUERY PLAN
11+--------------------------------------------------------------------------
12+ Hash Join (cost=xxx rows=1000 width=30)
13+ Hash Cond: (v1t1.c1 = v1t1_1.c1)
14+ -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=15)
15+ -> Hash (cost=xxx rows=1000 width=15)
16+ -> Seq Scan on t1 v1t1_1 (cost=xxx rows=1000 width=15)
17+(5 rows)
18+
--- /dev/null
+++ b/expected/R_2-3-5.out
@@ -0,0 +1,16 @@
1+ QUERY PLAN
2+-----------------------------------------------------------------------------
3+ Nested Loop (cost=xxx rows=1000 width=30)
4+ -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=15)
5+ -> Index Scan using t1_i1 on t1 v1t1_ (cost=xxx rows=1 width=15)
6+ Index Cond: (c1 = v1t1.c1)
7+(4 rows)
8+
9+ QUERY PLAN
10+-----------------------------------------------------------------------------
11+ Nested Loop (cost=xxx rows=1 width=30)
12+ -> Seq Scan on t1 v1t1 (cost=xxx rows=1000 width=15)
13+ -> Index Scan using t1_i1 on t1 v1t1_ (cost=xxx rows=1 width=15)
14+ Index Cond: (c1 = v1t1.c1)
15+(4 rows)
16+
--- /dev/null
+++ b/expected/R_2-3-6.out
@@ -0,0 +1,18 @@
1+ QUERY PLAN
2+--------------------------------------------------------------------------
3+ Hash Join (cost=xxx rows=1000 width=30)
4+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
5+ -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=15)
6+ -> Hash (cost=xxx rows=1000 width=15)
7+ -> Seq Scan on t1 r4t1_1 (cost=xxx rows=1000 width=15)
8+(5 rows)
9+
10+ QUERY PLAN
11+--------------------------------------------------------------------------
12+ Hash Join (cost=xxx rows=1000 width=30)
13+ Hash Cond: (r4t1.c1 = r4t1_1.c1)
14+ -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=15)
15+ -> Hash (cost=xxx rows=1000 width=15)
16+ -> Seq Scan on t1 r4t1_1 (cost=xxx rows=1000 width=15)
17+(5 rows)
18+
--- /dev/null
+++ b/expected/R_2-3-7.out
@@ -0,0 +1,16 @@
1+ QUERY PLAN
2+----------------------------------------------------------------------------
3+ Nested Loop (cost=xxx rows=1000 width=30)
4+ -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=15)
5+ -> Index Scan using t1_i1 on t1 r5t1 (cost=xxx rows=1 width=15)
6+ Index Cond: (c1 = r4t1.c1)
7+(4 rows)
8+
9+ QUERY PLAN
10+----------------------------------------------------------------------------
11+ Nested Loop (cost=xxx rows=1 width=30)
12+ -> Seq Scan on t1 r4t1 (cost=xxx rows=1000 width=15)
13+ -> Index Scan using t1_i1 on t1 r5t1 (cost=xxx rows=1 width=15)
14+ Index Cond: (c1 = r4t1.c1)
15+(4 rows)
16+
--- a/expected/ut-R.out
+++ b/expected/ut-R.out
@@ -1585,3 +1585,466 @@ error hint:
15851585 \o
15861586 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
15871587 \! diff expected/R_2-2-4.out results/R_2-2-4.out
1588+----
1589+---- No. R-2-3 RULE or VIEW
1590+----
1591+-- No. R-2-3-1
1592+\o results/R_2-3-1.out.log
1593+/*+
1594+Leading(r1 t1 t2 t3 t4)
1595+*/
1596+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
1597+LOG: pg_hint_plan:
1598+used hint:
1599+Leading(r1 t1 t2 t3 t4)
1600+not used hint:
1601+duplication hint:
1602+error hint:
1603+
1604+/*+
1605+Leading(r1 t1 t2 t3 t4)
1606+Rows(r1 t1 t2 t3 t4 #2)
1607+Rows(r1 t1 t2 t3 #2)
1608+Rows(r1 t1 t2 #2)
1609+Rows(r1 t1 #2)
1610+*/
1611+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
1612+LOG: pg_hint_plan:
1613+used hint:
1614+Leading(r1 t1 t2 t3 t4)
1615+Rows(r1 t1 #2)
1616+Rows(r1 t1 t2 #2)
1617+Rows(r1 t1 t2 t3 #2)
1618+Rows(r1 t1 t2 t3 t4 #2)
1619+not used hint:
1620+duplication hint:
1621+error hint:
1622+
1623+/*+
1624+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1625+*/
1626+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
1627+LOG: pg_hint_plan:
1628+used hint:
1629+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1630+not used hint:
1631+duplication hint:
1632+error hint:
1633+
1634+/*+
1635+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1636+Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
1637+Rows(r1_ b1t1 b1t2 b1t3 #2)
1638+Rows(r1_ b1t1 b1t2 #2)
1639+Rows(r1_ b1t1 #2)
1640+*/
1641+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
1642+LOG: pg_hint_plan:
1643+used hint:
1644+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
1645+Rows(b1t1 r1_ #2)
1646+Rows(b1t1 b1t2 r1_ #2)
1647+Rows(b1t1 b1t2 b1t3 r1_ #2)
1648+Rows(b1t1 b1t2 b1t3 b1t4 r1_ #2)
1649+not used hint:
1650+duplication hint:
1651+error hint:
1652+
1653+\o
1654+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
1655+\! diff expected/R_2-3-1.out results/R_2-3-1.out
1656+-- No. R-2-3-2
1657+\o results/R_2-3-2.out.log
1658+/*+
1659+Leading(r2 t1 t2 t3 t4)
1660+*/
1661+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
1662+LOG: pg_hint_plan:
1663+used hint:
1664+Leading(r2 t1 t2 t3 t4)
1665+not used hint:
1666+duplication hint:
1667+error hint:
1668+
1669+LOG: pg_hint_plan:
1670+used hint:
1671+Leading(r2 t1 t2 t3 t4)
1672+not used hint:
1673+duplication hint:
1674+error hint:
1675+
1676+/*+
1677+Leading(r2 t1 t2 t3 t4)
1678+Rows(r2 t1 t2 t3 t4 #2)
1679+Rows(r2 t1 t2 t3 #2)
1680+Rows(r2 t1 t2 #2)
1681+Rows(r2 t1 #2)
1682+*/
1683+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
1684+LOG: pg_hint_plan:
1685+used hint:
1686+Leading(r2 t1 t2 t3 t4)
1687+Rows(r2 t1 #2)
1688+Rows(r2 t1 t2 #2)
1689+Rows(r2 t1 t2 t3 #2)
1690+Rows(r2 t1 t2 t3 t4 #2)
1691+not used hint:
1692+duplication hint:
1693+error hint:
1694+
1695+LOG: pg_hint_plan:
1696+used hint:
1697+Leading(r2 t1 t2 t3 t4)
1698+Rows(r2 t1 #2)
1699+Rows(r2 t1 t2 #2)
1700+Rows(r2 t1 t2 t3 #2)
1701+Rows(r2 t1 t2 t3 t4 #2)
1702+not used hint:
1703+duplication hint:
1704+error hint:
1705+
1706+/*+
1707+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1708+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1709+*/
1710+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
1711+LOG: pg_hint_plan:
1712+used hint:
1713+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1714+not used hint:
1715+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1716+duplication hint:
1717+error hint:
1718+
1719+LOG: pg_hint_plan:
1720+used hint:
1721+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1722+not used hint:
1723+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1724+duplication hint:
1725+error hint:
1726+
1727+/*+
1728+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1729+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1730+Rows(r2_ b1t1 #2)
1731+Rows(r2_ b1t1 b1t2 #2)
1732+Rows(r2_ b1t1 b1t2 b1t3 #2)
1733+Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2)
1734+Rows(r2_ b2t1 #2)
1735+Rows(r2_ b2t1 b2t2 #2)
1736+Rows(r2_ b2t1 b2t2 b2t3 #2)
1737+Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2)
1738+*/
1739+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
1740+LOG: pg_hint_plan:
1741+used hint:
1742+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1743+Rows(b1t1 r2_ #2)
1744+Rows(b1t1 b1t2 r2_ #2)
1745+Rows(b1t1 b1t2 b1t3 r2_ #2)
1746+Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2)
1747+not used hint:
1748+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1749+Rows(b2t1 r2_ #2)
1750+Rows(b2t1 b2t2 r2_ #2)
1751+Rows(b2t1 b2t2 b2t3 r2_ #2)
1752+Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2)
1753+duplication hint:
1754+error hint:
1755+
1756+LOG: pg_hint_plan:
1757+used hint:
1758+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
1759+Rows(b2t1 r2_ #2)
1760+Rows(b2t1 b2t2 r2_ #2)
1761+Rows(b2t1 b2t2 b2t3 r2_ #2)
1762+Rows(b2t1 b2t2 b2t3 b2t4 r2_ #2)
1763+not used hint:
1764+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
1765+Rows(b1t1 r2_ #2)
1766+Rows(b1t1 b1t2 r2_ #2)
1767+Rows(b1t1 b1t2 b1t3 r2_ #2)
1768+Rows(b1t1 b1t2 b1t3 b1t4 r2_ #2)
1769+duplication hint:
1770+error hint:
1771+
1772+\o
1773+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
1774+\! diff expected/R_2-3-2.out results/R_2-3-2.out
1775+-- No. R-2-3-3
1776+\o results/R_2-3-3.out.log
1777+/*+
1778+Leading(r3 t1 t2 t3 t4)
1779+*/
1780+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
1781+LOG: pg_hint_plan:
1782+used hint:
1783+Leading(r3 t1 t2 t3 t4)
1784+not used hint:
1785+duplication hint:
1786+error hint:
1787+
1788+LOG: pg_hint_plan:
1789+used hint:
1790+Leading(r3 t1 t2 t3 t4)
1791+not used hint:
1792+duplication hint:
1793+error hint:
1794+
1795+LOG: pg_hint_plan:
1796+used hint:
1797+Leading(r3 t1 t2 t3 t4)
1798+not used hint:
1799+duplication hint:
1800+error hint:
1801+
1802+/*+
1803+Leading(r3 t1 t2 t3 t4)
1804+Rows(r3 t1 t2 t3 t4 #2)
1805+Rows(r3 t1 t2 t3 #2)
1806+Rows(r3 t1 t2 #2)
1807+Rows(r3 t1 #2)
1808+*/
1809+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
1810+LOG: pg_hint_plan:
1811+used hint:
1812+Leading(r3 t1 t2 t3 t4)
1813+Rows(r3 t1 #2)
1814+Rows(r3 t1 t2 #2)
1815+Rows(r3 t1 t2 t3 #2)
1816+Rows(r3 t1 t2 t3 t4 #2)
1817+not used hint:
1818+duplication hint:
1819+error hint:
1820+
1821+LOG: pg_hint_plan:
1822+used hint:
1823+Leading(r3 t1 t2 t3 t4)
1824+Rows(r3 t1 #2)
1825+Rows(r3 t1 t2 #2)
1826+Rows(r3 t1 t2 t3 #2)
1827+Rows(r3 t1 t2 t3 t4 #2)
1828+not used hint:
1829+duplication hint:
1830+error hint:
1831+
1832+LOG: pg_hint_plan:
1833+used hint:
1834+Leading(r3 t1 t2 t3 t4)
1835+Rows(r3 t1 #2)
1836+Rows(r3 t1 t2 #2)
1837+Rows(r3 t1 t2 t3 #2)
1838+Rows(r3 t1 t2 t3 t4 #2)
1839+not used hint:
1840+duplication hint:
1841+error hint:
1842+
1843+/*+
1844+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1845+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1846+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1847+*/
1848+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
1849+LOG: pg_hint_plan:
1850+used hint:
1851+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1852+not used hint:
1853+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1854+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1855+duplication hint:
1856+error hint:
1857+
1858+LOG: pg_hint_plan:
1859+used hint:
1860+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1861+not used hint:
1862+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1863+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1864+duplication hint:
1865+error hint:
1866+
1867+LOG: pg_hint_plan:
1868+used hint:
1869+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1870+not used hint:
1871+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1872+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1873+duplication hint:
1874+error hint:
1875+
1876+/*+
1877+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1878+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1879+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1880+Rows(r3_ b1t1 #2)
1881+Rows(r3_ b1t1 b1t2 #2)
1882+Rows(r3_ b1t1 b1t2 b1t3 #2)
1883+Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2)
1884+Rows(r3_ b2t1 #2)
1885+Rows(r3_ b2t1 b2t2 #2)
1886+Rows(r3_ b2t1 b2t2 b2t3 #2)
1887+Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2)
1888+Rows(r3_ b3t1 #2)
1889+Rows(r3_ b3t1 b3t2 #2)
1890+Rows(r3_ b3t1 b3t2 b3t3 #2)
1891+Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2)
1892+*/
1893+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
1894+LOG: pg_hint_plan:
1895+used hint:
1896+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1897+Rows(b1t1 r3_ #2)
1898+Rows(b1t1 b1t2 r3_ #2)
1899+Rows(b1t1 b1t2 b1t3 r3_ #2)
1900+Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
1901+not used hint:
1902+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1903+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1904+Rows(b2t1 r3_ #2)
1905+Rows(b3t1 r3_ #2)
1906+Rows(b2t1 b2t2 r3_ #2)
1907+Rows(b3t1 b3t2 r3_ #2)
1908+Rows(b2t1 b2t2 b2t3 r3_ #2)
1909+Rows(b3t1 b3t2 b3t3 r3_ #2)
1910+Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
1911+Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
1912+duplication hint:
1913+error hint:
1914+
1915+LOG: pg_hint_plan:
1916+used hint:
1917+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1918+Rows(b2t1 r3_ #2)
1919+Rows(b2t1 b2t2 r3_ #2)
1920+Rows(b2t1 b2t2 b2t3 r3_ #2)
1921+Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
1922+not used hint:
1923+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1924+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1925+Rows(b1t1 r3_ #2)
1926+Rows(b3t1 r3_ #2)
1927+Rows(b1t1 b1t2 r3_ #2)
1928+Rows(b3t1 b3t2 r3_ #2)
1929+Rows(b1t1 b1t2 b1t3 r3_ #2)
1930+Rows(b3t1 b3t2 b3t3 r3_ #2)
1931+Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
1932+Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
1933+duplication hint:
1934+error hint:
1935+
1936+LOG: pg_hint_plan:
1937+used hint:
1938+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
1939+Rows(b3t1 r3_ #2)
1940+Rows(b3t1 b3t2 r3_ #2)
1941+Rows(b3t1 b3t2 b3t3 r3_ #2)
1942+Rows(b3t1 b3t2 b3t3 b3t4 r3_ #2)
1943+not used hint:
1944+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
1945+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
1946+Rows(b1t1 r3_ #2)
1947+Rows(b2t1 r3_ #2)
1948+Rows(b1t1 b1t2 r3_ #2)
1949+Rows(b2t1 b2t2 r3_ #2)
1950+Rows(b1t1 b1t2 b1t3 r3_ #2)
1951+Rows(b2t1 b2t2 b2t3 r3_ #2)
1952+Rows(b1t1 b1t2 b1t3 b1t4 r3_ #2)
1953+Rows(b2t1 b2t2 b2t3 b2t4 r3_ #2)
1954+duplication hint:
1955+error hint:
1956+
1957+\o
1958+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
1959+\! diff expected/R_2-3-3.out results/R_2-3-3.out
1960+-- No. R-2-3-4
1961+\o results/R_2-3-4.out.log
1962+/*+HashJoin(v1t1 v1t1)*/
1963+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
1964+INFO: hint syntax error at or near "HashJoin(v1t1 v1t1)"
1965+DETAIL: Relation name "v1t1" is ambiguous.
1966+LOG: pg_hint_plan:
1967+used hint:
1968+not used hint:
1969+duplication hint:
1970+error hint:
1971+HashJoin(v1t1 v1t1)
1972+
1973+/*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
1974+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
1975+INFO: hint syntax error at or near "HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)"
1976+DETAIL: Relation name "v1t1" is ambiguous.
1977+INFO: hint syntax error at or near "Rows(v1t1 v1t1 #1)"
1978+DETAIL: Relation name "v1t1" is ambiguous.
1979+LOG: pg_hint_plan:
1980+used hint:
1981+not used hint:
1982+duplication hint:
1983+error hint:
1984+HashJoin(v1t1 v1t1)
1985+Rows(v1t1 v1t1 #1)
1986+
1987+\o
1988+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
1989+\! diff expected/R_2-3-4.out results/R_2-3-4.out
1990+-- No. R-2-3-5
1991+\o results/R_2-3-5.out.log
1992+/*+NestLoop(v1t1 v1t1_)*/
1993+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
1994+LOG: pg_hint_plan:
1995+used hint:
1996+NestLoop(v1t1 v1t1_)
1997+not used hint:
1998+duplication hint:
1999+error hint:
2000+
2001+/*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
2002+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
2003+LOG: pg_hint_plan:
2004+used hint:
2005+NestLoop(v1t1 v1t1_)
2006+Rows(v1t1 v1t1_ #1)
2007+not used hint:
2008+duplication hint:
2009+error hint:
2010+
2011+\o
2012+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
2013+\! diff expected/R_2-3-5.out results/R_2-3-5.out
2014+-- No. R-2-3-6
2015+\o results/R_2-3-6.out.log
2016+/*+RowsHashJoin(r4t1 r4t1)*/
2017+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
2018+INFO: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)"
2019+DETAIL: Unrecognized hint keyword "RowsHashJoin".
2020+/*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
2021+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
2022+INFO: hint syntax error at or near "RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)"
2023+DETAIL: Unrecognized hint keyword "RowsHashJoin".
2024+\o
2025+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
2026+\! diff expected/R_2-3-6.out results/R_2-3-6.out
2027+-- No. R-2-3-7
2028+\o results/R_2-3-7.out.log
2029+/*+NestLoop(r4t1 r5t1)*/
2030+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
2031+LOG: pg_hint_plan:
2032+used hint:
2033+NestLoop(r4t1 r5t1)
2034+not used hint:
2035+duplication hint:
2036+error hint:
2037+
2038+/*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
2039+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
2040+LOG: pg_hint_plan:
2041+used hint:
2042+NestLoop(r4t1 r5t1)
2043+Rows(r4t1 r5t1 #1)
2044+not used hint:
2045+duplication hint:
2046+error hint:
2047+
2048+\o
2049+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
2050+\! diff expected/R_2-3-7.out results/R_2-3-7.out
--- a/sql/ut-R.sql
+++ b/sql/ut-R.sql
@@ -861,3 +861,155 @@ SELECT b3t1.c1 FROM s1.t1 b3t1
861861 \o
862862 \! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-2-4.out.log > results/R_2-2-4.out
863863 \! diff expected/R_2-2-4.out results/R_2-2-4.out
864+
865+----
866+---- No. R-2-3 RULE or VIEW
867+----
868+
869+-- No. R-2-3-1
870+\o results/R_2-3-1.out.log
871+/*+
872+Leading(r1 t1 t2 t3 t4)
873+*/
874+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
875+/*+
876+Leading(r1 t1 t2 t3 t4)
877+Rows(r1 t1 t2 t3 t4 #2)
878+Rows(r1 t1 t2 t3 #2)
879+Rows(r1 t1 t2 #2)
880+Rows(r1 t1 #2)
881+*/
882+EXPLAIN UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1;
883+/*+
884+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
885+*/
886+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
887+/*+
888+Leading(r1_ b1t1 b1t2 b1t3 b1t4)
889+Rows(r1_ b1t1 b1t2 b1t3 b1t4 #2)
890+Rows(r1_ b1t1 b1t2 b1t3 #2)
891+Rows(r1_ b1t1 b1t2 #2)
892+Rows(r1_ b1t1 #2)
893+*/
894+EXPLAIN UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1;
895+\o
896+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-1.out.log > results/R_2-3-1.out
897+\! diff expected/R_2-3-1.out results/R_2-3-1.out
898+
899+-- No. R-2-3-2
900+\o results/R_2-3-2.out.log
901+/*+
902+Leading(r2 t1 t2 t3 t4)
903+*/
904+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
905+/*+
906+Leading(r2 t1 t2 t3 t4)
907+Rows(r2 t1 t2 t3 t4 #2)
908+Rows(r2 t1 t2 t3 #2)
909+Rows(r2 t1 t2 #2)
910+Rows(r2 t1 #2)
911+*/
912+EXPLAIN UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1;
913+/*+
914+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
915+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
916+*/
917+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
918+/*+
919+Leading(r2_ b1t1 b1t2 b1t3 b1t4)
920+Leading(r2_ b2t1 b2t2 b2t3 b2t4)
921+Rows(r2_ b1t1 #2)
922+Rows(r2_ b1t1 b1t2 #2)
923+Rows(r2_ b1t1 b1t2 b1t3 #2)
924+Rows(r2_ b1t1 b1t2 b1t3 b1t4 #2)
925+Rows(r2_ b2t1 #2)
926+Rows(r2_ b2t1 b2t2 #2)
927+Rows(r2_ b2t1 b2t2 b2t3 #2)
928+Rows(r2_ b2t1 b2t2 b2t3 b2t4 #2)
929+*/
930+EXPLAIN UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1;
931+\o
932+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-2.out.log > results/R_2-3-2.out
933+\! diff expected/R_2-3-2.out results/R_2-3-2.out
934+
935+-- No. R-2-3-3
936+\o results/R_2-3-3.out.log
937+/*+
938+Leading(r3 t1 t2 t3 t4)
939+*/
940+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
941+/*+
942+Leading(r3 t1 t2 t3 t4)
943+Rows(r3 t1 t2 t3 t4 #2)
944+Rows(r3 t1 t2 t3 #2)
945+Rows(r3 t1 t2 #2)
946+Rows(r3 t1 #2)
947+*/
948+EXPLAIN UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1;
949+/*+
950+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
951+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
952+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
953+*/
954+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
955+/*+
956+Leading(r3_ b1t1 b1t2 b1t3 b1t4)
957+Leading(r3_ b2t1 b2t2 b2t3 b2t4)
958+Leading(r3_ b3t1 b3t2 b3t3 b3t4)
959+Rows(r3_ b1t1 #2)
960+Rows(r3_ b1t1 b1t2 #2)
961+Rows(r3_ b1t1 b1t2 b1t3 #2)
962+Rows(r3_ b1t1 b1t2 b1t3 b1t4 #2)
963+Rows(r3_ b2t1 #2)
964+Rows(r3_ b2t1 b2t2 #2)
965+Rows(r3_ b2t1 b2t2 b2t3 #2)
966+Rows(r3_ b2t1 b2t2 b2t3 b2t4 #2)
967+Rows(r3_ b3t1 #2)
968+Rows(r3_ b3t1 b3t2 #2)
969+Rows(r3_ b3t1 b3t2 b3t3 #2)
970+Rows(r3_ b3t1 b3t2 b3t3 b3t4 #2)
971+*/
972+EXPLAIN UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1;
973+\o
974+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-3.out.log > results/R_2-3-3.out
975+\! diff expected/R_2-3-3.out results/R_2-3-3.out
976+
977+-- No. R-2-3-4
978+\o results/R_2-3-4.out.log
979+/*+HashJoin(v1t1 v1t1)*/
980+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
981+/*+HashJoin(v1t1 v1t1)Rows(v1t1 v1t1 #1)*/
982+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
983+\o
984+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-4.out.log > results/R_2-3-4.out
985+\! diff expected/R_2-3-4.out results/R_2-3-4.out
986+
987+-- No. R-2-3-5
988+\o results/R_2-3-5.out.log
989+/*+NestLoop(v1t1 v1t1_)*/
990+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
991+/*+NestLoop(v1t1 v1t1_)Rows(v1t1 v1t1_ #1)*/
992+EXPLAIN SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
993+\o
994+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-5.out.log > results/R_2-3-5.out
995+\! diff expected/R_2-3-5.out results/R_2-3-5.out
996+
997+-- No. R-2-3-6
998+\o results/R_2-3-6.out.log
999+/*+RowsHashJoin(r4t1 r4t1)*/
1000+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
1001+/*+RowsHashJoin(r4t1 r4t1)Rows(r4t1 r4t1 #1)*/
1002+EXPLAIN SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
1003+\o
1004+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-6.out.log > results/R_2-3-6.out
1005+\! diff expected/R_2-3-6.out results/R_2-3-6.out
1006+
1007+-- No. R-2-3-7
1008+\o results/R_2-3-7.out.log
1009+/*+NestLoop(r4t1 r5t1)*/
1010+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
1011+/*+NestLoop(r4t1 r5t1)Rows(r4t1 r5t1 #1)*/
1012+EXPLAIN SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
1013+\o
1014+\! sed 's/cost=[\.0-9]*/cost=xxx/' results/R_2-3-7.out.log > results/R_2-3-7.out
1015+\! diff expected/R_2-3-7.out results/R_2-3-7.out
旧リポジトリブラウザで表示