postgreSQLにて多重JOINテーブルの検索について

Question

SQLの高速化

PostgreSQLにて以下のような多重joinを行い、複数のテーブルにまたがるORにおいて、検索スピードが遅く困っております。このままのテーブル構造でどうにか早くしたいです。

SELECT * 
FROM (((Atbl LEFT JOIN MHtbl ON Atbl.keyA = MHtbl.MHkey) 
LEFT JOIN MRtbl as MRtbl(id2, MRkey, datal_2, data2_2) ON Atbl.keyB = MRtbl.MRkey) 
LEFT JOIN gtbl ON Atbl.keyA = gtbl.gkey) 
LEFT JOIN ttbl ON Atbl.keyA = ttbl.tkey 
WHERE (Aname_1 LIKE 'saito%' OR  Aname_2 LIKE 'saito%' OR  gname LIKE 'saito%' OR  tname LIKE 'saito%')    
ORDER BY TO_NUMBER(Sort, '999999')

Atblがベースとなり、Atbl、MHtbl,MRtbl,gtbl,ttblの5つのテーブルをJOINするようなSQLです。
Atblに2つ、gtblに1つ、ttblに1つある氏名のフィールドの内、どこか一つにでもsaitoから始まる文字列が存在する場合は、結合したすべてのフィールドを取ってきたのです。
Atblとその他のテーブルは一対多になります。
結合key、氏名フィールドには全てINDEXを張っています。
（また別問題ですが、なぜかINDEX使ってくれない時もある…）

因みに今後増加する可能性がありますが、
Atbl：20万レコード
MHtbl：5000レコード
MRtbl:17万レコード
gtbl:3万レコード
ttbl:4万レコード　ほどあり、
結合テーブルの総数は1億レコードほどになります…

どうかよろしくお願いします。

修正

申し訳ありません。
上記SQL3行目の

LEFT JOIN MRtbl as ~~ ON Atbl.keyA = MRtbl.MRkey) 

Atbl.keyAですが、Atbl.keyBの間違いでした。

gtabl.gkeyはgtbl.gkeyの間違いです。

追記情報

結合条件のデータ型は、
Atbl.keyA ：text型
Atbl.keyB ：character(100)
MHtbl.MHkey：character varying(100)
MRtbl.MRkey：character varying(30)
gtbl.gkey ：text型
ttbl.tkey ：text型
となります。
氏名フィールドは全てcharacter(100)です。

INDEX

以下、現在張っているINDEXです。
aidはユニークキーです。

・Atbl
CREATE UNIQUE INDEX Atbl_index1 ON Atbl(aid);
CREATE INDEX Atbl_index2 ON Atbl(keyA); 
CREATE INDEX Atbl_index3 ON Atbl(keyB);
CREATE UNIQUE INDEX Atbl_index4 ON Atbl(Sort);
CREATE INDEX Atbl_index5 ON Atbl(Aname_1);
CREATE INDEX Atbl_index6 ON Atbl(Aname_2);

・MHtbl
CREATE INDEX MHtbl_index1 ON MHtbl(MHkey);

・MRtbl
CREATE INDEX MRtbl_index1 ON MRtbl(MRkey);

・gtbl
CREATE INDEX gtbl_index1 ON gtbl(gkey);
CREATE INDEX gtbl_index2 ON gtbl(gname);
CREATE INDEX gtbl_index3 ON gtbl(gkey,gname);

・ttbl
CREATE INDEX ttbl_index1 ON ttbl(tkey);
CREATE INDEX ttbl_index2 ON ttbl(tname);
CREATE INDEX ttbl_index3 ON ttbl(tkey,tname);

実行計画

以下、上記SQLの実行計画です。
上記では特に書いていませんが、条件で絞る際に氏名フィールドをtrimして調べています。

Sort  (cost=849229.02..849282.91 rows=21558 width=6000) (actual time=239590.374..239590.384 rows=121 loops=1)"
  Sort Key: (to_number((Atbl.Sort)::text, '999999'::text))"
  Sort Method: quicksort  Memory: 497kB"
  ->  Hash Left Join  (cost=31280.86..681220.78 rows=21558 width=6000) (actual time=238839.000..239589.440 rows=121 loops=1)"
        Hash Cond: (Atbl.keyB = (MRtbl.MRkey)::bpchar)"
        ->  Merge Left Join  (cost=0.00..602188.71 rows=21558 width=5092) (actual time=199890.723..206467.397 rows=121 loops=1)"
              Merge Cond: (Atbl.keyA = (MHtbl.MHkey)::text)"
              ->  Merge Left Join  (cost=0.00..591727.41 rows=21558 width=3037) (actual time=199886.538..206456.358 rows=121 loops=1)"
                    Merge Cond: (Atbl.keyA = ttbl.tkey)"
                    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))"
                    ->  Merge Left Join  (cost=0.00..536115.96 rows=452180 width=2443) (actual time=0.349..1138.254 rows=688962 loops=1)"
                          Merge Cond: (Atbl.keyA = gtbl.gkey)"
                          ->  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)"
                          ->  Materialize  (cost=0.00..4056.78 rows=30870 width=594) (actual time=0.046..93.040 rows=495976 loops=1)"
                                ->  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)"
                    ->  Materialize  (cost=0.00..5719.51 rows=43746 width=594) (actual time=0.041..2557.181 rows=60456515 loops=1)"
                          ->  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)"
              ->  Materialize  (cost=0.00..10337.11 rows=4085 width=2055) (actual time=0.040..9.145 rows=4085 loops=1)"
                    ->  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)"
        ->  Hash  (cost=8650.27..8650.27 rows=178527 width=908) (actual time=32294.156..32294.156 rows=178527 loops=1)"
              Buckets: 1024  Batches: 256  Memory Usage: 223kB"
              ->  Seq Scan on MRtbl  (cost=0.00..8650.27 rows=178527 width=908) (actual time=0.023..134.435 rows=178527 loops=1)"
Total runtime: 239590.967 ms"

WHERE   (trim(Aname_1) LIKE 'saito%' OR  trim(Aname_2) LIKE 'saito%' OR  trim(gname) LIKE 'saito%' OR  trim(tname) LIKE 'saito%') 

Comments

[退会済みユーザー]

結合条件に使っている項目のデータ型を示すのと、既に設定してあるインデックスの列挙をよろしく。また、EXPLAINなどで該当SQLの評価結果もあれば。

[sazi]

>結合key、氏名フィールドには全てINDEXを張っています。
インデックスの項目の並びは性能に影響しますよ。
性能に関する質問なら、EXPLAINの結果とそこに現れるインデックスの定義内容は情報として必須です。

[ynaK235]

ご助言ありがとうございます。
情報を追記いたしました。
宜しくお願いいたします。

Answer 1

質問のSQLだと、
Atbl.keyA=gtbl.gkey=ttbl.tkey
の関係となるはずなので、以下のようなSQLで実行計画を確認してみましょう。

select * from Atbl
WHERE Atbl.keyA in (
        select keyA from Atbl where Aname_1 LIKE 'saito%' 
      union all 
        select keyA from Atbl where Aname_2 LIKE 'saito%' 
      union all 
        select gkey from gtbl where gname LIKE 'saito%' 
      union all 
        select tkey from ttbl where tname LIKE 'saito%' 
      )
ORDER BY TO_NUMBER(Sort, '999999')

このサブクエリー部分とorder by 部分のインデックスを適切にできると、後は結合条件で使用されるインデックスとの効率の良い組合せを考えればいい事になります。

Comments

[ynaK235]

ご回答ありがとうございます。
上記のSQLを試したところに以下のような実行結果になりました。INDEXは使用されているように見えるのですが…
```
"Sort (cost=544.29..544.59 rows=120 width=1849) (actual time=0.637..0.640 rows=74 loops=1)"
" Sort Key: (to_number((public.Atbl.Sort)::text, '999999'::text))"
" Sort Method: quicksort Memory: 165kB"
" -> Nested Loop (cost=33.70..540.14 rows=120 width=1849) (actual time=0.168..0.422 rows=74 loops=1)"
" -> HashAggregate (cost=33.70..33.93 rows=23 width=38) (actual time=0.155..0.161 rows=56 loops=1)"
" -> Append (cost=0.00..33.64 rows=23 width=38) (actual time=0.021..0.132 rows=115 loops=1)"
" -> Index Scan using Atbl_index5 on Atbl (cost=0.00..8.41 rows=15 width=38) (actual time=0.021..0.081 rows=73 loops=1)"
" Index Cond: ((Aname_1 >= 'saito'::bpchar) AND (Aname_1 < 'saitp'::bpchar))"
" Filter: (Aname_1 ~~ 'saito%'::text)"
" -> Index Scan using Atbl_index6 on Atbl (cost=0.00..8.41 rows=1 width=38) (actual time=0.006..0.006 rows=0 loops=1)"
" Index Cond: ((Aname_2 >= 'saito'::bpchar) AND (Aname_2 < 'saitp'::bpchar))"
" Filter: (Aname_2 ~~ 'saito%'::text)"
" -> Index Scan using gtbl_index2 on gtbl (cost=0.00..8.29 rows=3 width=38) (actual time=0.009..0.016 rows=15 loops=1)"
" Index Cond: ((tname >= 'saito'::bpchar) AND (tname < 'saitp'::bpchar))"
" Filter: (tname ~~ 'saito%'::text)"
" -> Index Scan using ttbl_index2 on ttbl (cost=0.00..8.30 rows=4 width=38) (actual time=0.008..0.022 rows=27 loops=1)"
" Index Cond: ((gname >= 'saito'::bpchar) AND (gname < 'saitp'::bpchar))"
" Filter: (gname ~~ 'saito%'::text)"
" -> Index Scan using Atbl_index2 on Atbl (cost=0.00..21.92 rows=5 width=1849) (actual time=0.002..0.002 rows=1 loops=56)"
" Index Cond: (tkey = public.Atbl.tkey)"
"Total runtime: 0.863 ms"
```

[sazi]

質問に追記された方ではtrim()しているのでインデックスが使用されていませんね。
trim()を外すか、trim()しないと駄目なのであれば、Trim(Aname_1）のような式インデックスをそれぞれ追加して下さい。

[sazi]

それで元のSQLが高速になれば良いですけど、そうでない場合は条件をこの回答の記述にしてみて下さい。
また、単に条件の書き換えだけでも今より早くなるかもしれません。

[ynaK235]

式インデックスなるものを初めて知りました！！！
しかし、追加してみたところ、INDEX　SCANにはなるのですがかなり遅くなってしまいました。。。

元のINDEXの状態で、条件を教えて頂いたUNIONに変更してみましたが、わずかに早くなったかな？程度でした。。。。。

[sazi]

追記された項目の属性を見ると、結合する項目の属性が違うから、結合が遅いんだと思います。
試しに、MHtblとMRtblを結合から外してみて下さい。格段に高速になると推測します。
推測通りに高速になるなら、属性を揃えた形式の式インデックスの追加ですね。

[ynaK235]

ありがとうございました。
属性をそろえた結果、かなり早くなりました。

[sazi]

実行計画でみると結構な件数同士で、総当たりになってますからね。
そりゃ、時間も掛かるというもんです。
DB設計上、そういう所が他にもないか確認された方が良いですよ。

Answer 2

質問のSELECT文はエラーになりませんか？

LEFT JOIN gtbl ON Atbl.keyA = gtabl.gkey)
                                ↓
LEFT JOIN gtbl ON Atbl.keyA = gtbl.gkey)

では？

WHERE (Aname_1 LIKE 'saito%' OR Aname_2 LIKE 'saito%' OR gname LIKE 'saito%' OR tname LIKE 'saito%')

Aname_1, Aname_2, gname, tname はどのテーブルのカラムですか？

質問に CREATE TABLE, CREATE INDEX, 実行計画 を 対応しているMarkdownの機能 の[コードを入力]で追記してください。

また、不要なカッコを入れてことさらSQLを読みにくくするのは止めましょう
。

Comments

[ynaK235]

ご回答ありがとうございます。
Orlofsky様のおしゃる通りミスでした。申し訳ありません。
Aname_1, Aname_2, gname, tnameはそれぞれ
Atbl,Atbl,gtbl,ttblのテーブルになります。

知識不足で申し訳ありませんが、不要なカッコがどのカッコなのか,
わかりません。。。

Answer 3

EXPLAINでの評価を見てからじっくり答えたいところ。

前方一致検索については
テキスト検索の方法とインデックス | Let's Postgres
とかどうだろう。

ORDER BY TO_NUMBER(Sort, '999999')をするのであれば、
11.7. 式に対するインデックス
とか使えませんかねぇ。
あるいはいちいちTO_NUMBERしなくても済むように構造を変えておくとか。

関係するテーブルの変更が多いのであれば
ANALYZE頑張るとかREINDEX頑張るとか。

19.4. 資源の消費
確保しているメモリがそもそも少ないとかいう話もないかどうか。

Comments

[ynaK235]

ご回答ありがとうございます。
諸事情により、テーブルの構造はできる限り変えたくはありません。
メモリも特別少なくはないと思います。