teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問postgreSQLにて多重JOINテーブルの検索について

編集履歴

質問編集履歴

2

追記

2019/03/07 01:55

投稿

ynaK235
ynaK235

スコア14

test CHANGED
File without changes
test CHANGED
@@ -12,9 +12,9 @@
12
12
 
13
13
  FROM (((Atbl LEFT JOIN MHtbl ON Atbl.keyA = MHtbl.MHkey)
14
14
 
15
- LEFT JOIN MRtbl as MRtbl(id2, MRkey, datal_2, data2_2) ON Atbl.keyA = MRtbl.MRkey)
15
+ LEFT JOIN MRtbl as MRtbl(id2, MRkey, datal_2, data2_2) ON Atbl.keyB = MRtbl.MRkey)
16
-
16
+
17
- LEFT JOIN gtbl ON Atbl.keyA = gtabl.gkey)
17
+ LEFT JOIN gtbl ON Atbl.keyA = gtbl.gkey)
18
18
 
19
19
  LEFT JOIN ttbl ON Atbl.keyA = ttbl.tkey
20
20
 
@@ -57,3 +57,171 @@
57
57
 
58
58
 
59
59
  どうかよろしくお願いします。
60
+
61
+
62
+
63
+
64
+
65
+ ### 修正
66
+
67
+ 申し訳ありません。
68
+
69
+ 上記SQL3行目の
70
+
71
+ ```
72
+
73
+ LEFT JOIN MRtbl as ~~ ON Atbl.keyA = MRtbl.MRkey)
74
+
75
+ ```
76
+
77
+ Atbl.keyAですが、Atbl.keyBの間違いでした。
78
+
79
+
80
+
81
+ gtabl.gkeyはgtbl.gkeyの間違いです。
82
+
83
+
84
+
85
+
86
+
87
+ ### 追記情報
88
+
89
+ 結合条件のデータ型は、
90
+
91
+ Atbl.keyA ：text型
92
+
93
+ Atbl.keyB ：character(100)
94
+
95
+ MHtbl.MHkey：character varying(100)
96
+
97
+ MRtbl.MRkey：character varying(30)
98
+
99
+ gtbl.gkey ：text型
100
+
101
+ ttbl.tkey ：text型
102
+
103
+ となります。
104
+
105
+ 氏名フィールドは全てcharacter(100)です。
106
+
107
+
108
+
109
+ ### INDEX
110
+
111
+ 以下、現在張っているINDEXです。
112
+
113
+ aidはユニークキーです。
114
+
115
+ ```
116
+
117
+ ・Atbl
118
+
119
+ CREATE UNIQUE INDEX Atbl_index1 ON Atbl(aid);
120
+
121
+ CREATE INDEX Atbl_index2 ON Atbl(keyA);
122
+
123
+ CREATE INDEX Atbl_index3 ON Atbl(keyB);
124
+
125
+ CREATE UNIQUE INDEX Atbl_index4 ON Atbl(Sort);
126
+
127
+ CREATE INDEX Atbl_index5 ON Atbl(Aname_1);
128
+
129
+ CREATE INDEX Atbl_index6 ON Atbl(Aname_2);
130
+
131
+
132
+
133
+ ・MHtbl
134
+
135
+ CREATE INDEX MHtbl_index1 ON MHtbl(MHkey);
136
+
137
+
138
+
139
+ ・MRtbl
140
+
141
+ CREATE INDEX MRtbl_index1 ON MRtbl(MRkey);
142
+
143
+
144
+
145
+ ・gtbl
146
+
147
+ CREATE INDEX gtbl_index1 ON gtbl(gkey);
148
+
149
+ CREATE INDEX gtbl_index2 ON gtbl(gname);
150
+
151
+ CREATE INDEX gtbl_index3 ON gtbl(gkey,gname);
152
+
153
+
154
+
155
+ ・ttbl
156
+
157
+ CREATE INDEX ttbl_index1 ON ttbl(tkey);
158
+
159
+ CREATE INDEX ttbl_index2 ON ttbl(tname);
160
+
161
+ CREATE INDEX ttbl_index3 ON ttbl(tkey,tname);
162
+
163
+ ```
164
+
165
+
166
+
167
+ ### 実行計画
168
+
169
+ 以下、上記SQLの実行計画です。
170
+
171
+ 上記では特に書いていませんが、条件で絞る際に氏名フィールドをtrimして調べています。
172
+
173
+ ```
174
+
175
+ Sort (cost=849229.02..849282.91 rows=21558 width=6000) (actual time=239590.374..239590.384 rows=121 loops=1)"
176
+
177
+ Sort Key: (to_number((Atbl.Sort)::text, '999999'::text))"
178
+
179
+ Sort Method: quicksort Memory: 497kB"
180
+
181
+ -> Hash Left Join (cost=31280.86..681220.78 rows=21558 width=6000) (actual time=238839.000..239589.440 rows=121 loops=1)"
182
+
183
+ Hash Cond: (Atbl.keyB = (MRtbl.MRkey)::bpchar)"
184
+
185
+ -> Merge Left Join (cost=0.00..602188.71 rows=21558 width=5092) (actual time=199890.723..206467.397 rows=121 loops=1)"
186
+
187
+ Merge Cond: (Atbl.keyA = (MHtbl.MHkey)::text)"
188
+
189
+ -> Merge Left Join (cost=0.00..591727.41 rows=21558 width=3037) (actual time=199886.538..206456.358 rows=121 loops=1)"
190
+
191
+ Merge Cond: (Atbl.keyA = ttbl.tkey)"
192
+
193
+ Filter: ((btrim((Atbl.tname)::text) ~~ 'saito%'::text) OR (btrim((Atbl.tsyoyuname)::text) ~~ 'saito%'::text) OR (btrim((ttbl.tname)::text) ~~ 'saito%'::text) OR (btrim((gtbl.gname)::text) ~~ 'saito%'::text))"
194
+
195
+ -> Merge Left Join (cost=0.00..536115.96 rows=452180 width=2443) (actual time=0.349..1138.254 rows=688962 loops=1)"
196
+
197
+ Merge Cond: (Atbl.keyA = gtbl.gkey)"
198
+
199
+ -> Index Scan using Atbl_index2 on Atbl (cost=0.00..524833.13 rows=208214 width=1849) (actual time=0.299..653.764 rows=208214 loops=1)"
200
+
201
+ -> Materialize (cost=0.00..4056.78 rows=30870 width=594) (actual time=0.046..93.040 rows=495976 loops=1)"
202
+
203
+ -> Index Scan using gtbl_index1 on gtbl (cost=0.00..3979.60 rows=30870 width=594) (actual time=0.043..43.229 rows=30870 loops=1)"
204
+
205
+ -> Materialize (cost=0.00..5719.51 rows=43746 width=594) (actual time=0.041..2557.181 rows=60456515 loops=1)"
206
+
207
+ -> Index Scan using ttbl_index1 on ttbl (cost=0.00..5610.14 rows=43746 width=594) (actual time=0.039..51.374 rows=43746 loops=1)"
208
+
209
+ -> Materialize (cost=0.00..10337.11 rows=4085 width=2055) (actual time=0.040..9.145 rows=4085 loops=1)"
210
+
211
+ -> Index Scan using MHtbl_index1 on MHtbl (cost=0.00..10326.90 rows=4085 width=2055) (actual time=0.036..8.630 rows=4085 loops=1)"
212
+
213
+ -> Hash (cost=8650.27..8650.27 rows=178527 width=908) (actual time=32294.156..32294.156 rows=178527 loops=1)"
214
+
215
+ Buckets: 1024 Batches: 256 Memory Usage: 223kB"
216
+
217
+ -> Seq Scan on MRtbl (cost=0.00..8650.27 rows=178527 width=908) (actual time=0.023..134.435 rows=178527 loops=1)"
218
+
219
+ Total runtime: 239590.967 ms"
220
+
221
+ ```
222
+
223
+ ```
224
+
225
+ WHERE (trim(Aname_1) LIKE 'saito%' OR trim(Aname_2) LIKE 'saito%' OR trim(gname) LIKE 'saito%' OR trim(tname) LIKE 'saito%')
226
+
227
+ ```

1

誤字

2019/03/07 01:55

投稿

ynaK235
ynaK235

スコア14

test CHANGED
File without changes
test CHANGED
@@ -1,6 +1,6 @@
1
1
  ### SQLの高速化
2
2
 
3
- PostgreSQLにて以下のような多重joinを行うテーブルにおいて、検索スピードが遅く困っております。このままのテーブル構造でどうにか早くしたいです。
3
+ PostgreSQLにて以下のような多重joinを行い、複数のテーブルにまたがるORにおいて、検索スピードが遅く困っております。このままのテーブル構造でどうにか早くしたいです。
4
4
 
5
5
 
6
6