質問編集履歴
2
追記
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -5,8 +5,8 @@
|
|
|
5
5
|
```
|
|
6
6
|
SELECT *
|
|
7
7
|
FROM (((Atbl LEFT JOIN MHtbl ON Atbl.keyA = MHtbl.MHkey)
|
|
8
|
-
LEFT JOIN MRtbl as MRtbl(id2, MRkey, datal_2, data2_2) ON Atbl.
|
|
8
|
+
LEFT JOIN MRtbl as MRtbl(id2, MRkey, datal_2, data2_2) ON Atbl.keyB = MRtbl.MRkey)
|
|
9
|
-
LEFT JOIN gtbl ON Atbl.keyA =
|
|
9
|
+
LEFT JOIN gtbl ON Atbl.keyA = gtbl.gkey)
|
|
10
10
|
LEFT JOIN ttbl ON Atbl.keyA = ttbl.tkey
|
|
11
11
|
WHERE (Aname_1 LIKE 'saito%' OR Aname_2 LIKE 'saito%' OR gname LIKE 'saito%' OR tname LIKE 'saito%')
|
|
12
12
|
ORDER BY TO_NUMBER(Sort, '999999')
|
|
@@ -27,4 +27,88 @@
|
|
|
27
27
|
ttbl:4万レコード ほどあり、
|
|
28
28
|
結合テーブルの総数は1億レコードほどになります…
|
|
29
29
|
|
|
30
|
-
どうかよろしくお願いします。
|
|
30
|
+
どうかよろしくお願いします。
|
|
31
|
+
|
|
32
|
+
|
|
33
|
+
### 修正
|
|
34
|
+
申し訳ありません。
|
|
35
|
+
上記SQL3行目の
|
|
36
|
+
```
|
|
37
|
+
LEFT JOIN MRtbl as ~~ ON Atbl.keyA = MRtbl.MRkey)
|
|
38
|
+
```
|
|
39
|
+
Atbl.keyAですが、Atbl.keyBの間違いでした。
|
|
40
|
+
|
|
41
|
+
gtabl.gkeyはgtbl.gkeyの間違いです。
|
|
42
|
+
|
|
43
|
+
|
|
44
|
+
### 追記情報
|
|
45
|
+
結合条件のデータ型は、
|
|
46
|
+
Atbl.keyA ：text型
|
|
47
|
+
Atbl.keyB ：character(100)
|
|
48
|
+
MHtbl.MHkey：character varying(100)
|
|
49
|
+
MRtbl.MRkey：character varying(30)
|
|
50
|
+
gtbl.gkey ：text型
|
|
51
|
+
ttbl.tkey ：text型
|
|
52
|
+
となります。
|
|
53
|
+
氏名フィールドは全てcharacter(100)です。
|
|
54
|
+
|
|
55
|
+
### INDEX
|
|
56
|
+
以下、現在張っているINDEXです。
|
|
57
|
+
aidはユニークキーです。
|
|
58
|
+
```
|
|
59
|
+
・Atbl
|
|
60
|
+
CREATE UNIQUE INDEX Atbl_index1 ON Atbl(aid);
|
|
61
|
+
CREATE INDEX Atbl_index2 ON Atbl(keyA);
|
|
62
|
+
CREATE INDEX Atbl_index3 ON Atbl(keyB);
|
|
63
|
+
CREATE UNIQUE INDEX Atbl_index4 ON Atbl(Sort);
|
|
64
|
+
CREATE INDEX Atbl_index5 ON Atbl(Aname_1);
|
|
65
|
+
CREATE INDEX Atbl_index6 ON Atbl(Aname_2);
|
|
66
|
+
|
|
67
|
+
・MHtbl
|
|
68
|
+
CREATE INDEX MHtbl_index1 ON MHtbl(MHkey);
|
|
69
|
+
|
|
70
|
+
・MRtbl
|
|
71
|
+
CREATE INDEX MRtbl_index1 ON MRtbl(MRkey);
|
|
72
|
+
|
|
73
|
+
・gtbl
|
|
74
|
+
CREATE INDEX gtbl_index1 ON gtbl(gkey);
|
|
75
|
+
CREATE INDEX gtbl_index2 ON gtbl(gname);
|
|
76
|
+
CREATE INDEX gtbl_index3 ON gtbl(gkey,gname);
|
|
77
|
+
|
|
78
|
+
・ttbl
|
|
79
|
+
CREATE INDEX ttbl_index1 ON ttbl(tkey);
|
|
80
|
+
CREATE INDEX ttbl_index2 ON ttbl(tname);
|
|
81
|
+
CREATE INDEX ttbl_index3 ON ttbl(tkey,tname);
|
|
82
|
+
```
|
|
83
|
+
|
|
84
|
+
### 実行計画
|
|
85
|
+
以下、上記SQLの実行計画です。
|
|
86
|
+
上記では特に書いていませんが、条件で絞る際に氏名フィールドをtrimして調べています。
|
|
87
|
+
```
|
|
88
|
+
Sort (cost=849229.02..849282.91 rows=21558 width=6000) (actual time=239590.374..239590.384 rows=121 loops=1)"
|
|
89
|
+
Sort Key: (to_number((Atbl.Sort)::text, '999999'::text))"
|
|
90
|
+
Sort Method: quicksort Memory: 497kB"
|
|
91
|
+
-> Hash Left Join (cost=31280.86..681220.78 rows=21558 width=6000) (actual time=238839.000..239589.440 rows=121 loops=1)"
|
|
92
|
+
Hash Cond: (Atbl.keyB = (MRtbl.MRkey)::bpchar)"
|
|
93
|
+
-> Merge Left Join (cost=0.00..602188.71 rows=21558 width=5092) (actual time=199890.723..206467.397 rows=121 loops=1)"
|
|
94
|
+
Merge Cond: (Atbl.keyA = (MHtbl.MHkey)::text)"
|
|
95
|
+
-> Merge Left Join (cost=0.00..591727.41 rows=21558 width=3037) (actual time=199886.538..206456.358 rows=121 loops=1)"
|
|
96
|
+
Merge Cond: (Atbl.keyA = ttbl.tkey)"
|
|
97
|
+
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))"
|
|
98
|
+
-> Merge Left Join (cost=0.00..536115.96 rows=452180 width=2443) (actual time=0.349..1138.254 rows=688962 loops=1)"
|
|
99
|
+
Merge Cond: (Atbl.keyA = gtbl.gkey)"
|
|
100
|
+
-> 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)"
|
|
101
|
+
-> Materialize (cost=0.00..4056.78 rows=30870 width=594) (actual time=0.046..93.040 rows=495976 loops=1)"
|
|
102
|
+
-> 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)"
|
|
103
|
+
-> Materialize (cost=0.00..5719.51 rows=43746 width=594) (actual time=0.041..2557.181 rows=60456515 loops=1)"
|
|
104
|
+
-> 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)"
|
|
105
|
+
-> Materialize (cost=0.00..10337.11 rows=4085 width=2055) (actual time=0.040..9.145 rows=4085 loops=1)"
|
|
106
|
+
-> 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)"
|
|
107
|
+
-> Hash (cost=8650.27..8650.27 rows=178527 width=908) (actual time=32294.156..32294.156 rows=178527 loops=1)"
|
|
108
|
+
Buckets: 1024 Batches: 256 Memory Usage: 223kB"
|
|
109
|
+
-> Seq Scan on MRtbl (cost=0.00..8650.27 rows=178527 width=908) (actual time=0.023..134.435 rows=178527 loops=1)"
|
|
110
|
+
Total runtime: 239590.967 ms"
|
|
111
|
+
```
|
|
112
|
+
```
|
|
113
|
+
WHERE (trim(Aname_1) LIKE 'saito%' OR trim(Aname_2) LIKE 'saito%' OR trim(gname) LIKE 'saito%' OR trim(tname) LIKE 'saito%')
|
|
114
|
+
```
|
1
誤字
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,5 +1,5 @@
|
|
|
1
1
|
### SQLの高速化
|
|
2
|
-
PostgreSQLにて以下のような多重joinを行
|
|
2
|
+
PostgreSQLにて以下のような多重joinを行い、複数のテーブルにまたがるORにおいて、検索スピードが遅く困っております。このままのテーブル構造でどうにか早くしたいです。
|
|
3
3
|
|
|
4
4
|
|
|
5
5
|
```
|