POSTGRESQL 検索スピードが遅い

テーブル結合検索、インデックス使えない原因を
Aテーブルは200000件のデータがあり、Bテーブルは4000000件のデータがあります。
テーブルA（ヘッダ）
A001　　NUMBER(10) NOT NULL,
A002　　NUMBER(8) NOT NULL,
A003　　CHARACTER(2) NOT NULL,
...

PRIMARY KEY(A001)
INDEX A1(A002, A003)

テーブルB（明細）
B001　　NUMBER(10) NOT NULL,
B002　　NUMBER(3) NOT NULL,
B003　　NUMBER(7) NOT NULL,
...

PRIMARY KEY(B001,B002)
INDEX A1(B001)

SELECT A.A001, B.B003 FROM A, B
WHERE A.A001 = B.B001
AND A.A002 >= 20090728 AND A.A002 <= 20090801
AND A.A003 = '01'

実行計画を見ると、Bテーブルのインデックスが使わなかった。

同じＳＱＬで、条件だけ変わると、Bテーブルのインデックスが使った。
SELECT A.A001, B.B003 FROM A, B
WHERE A.A001 = B.B001
AND A.A002 >= 20090728 AND A.A002 <= 20090731
AND A.A003 = '01'

原因を知りたいです。
また、他のＨＰからテーブルの結合の説明がありました、
直積結合の回避
* 直積結合は、結合対象の2つのテーブルの全レコードの組み合わせを戻す処理
* SQLが複雑で、結合条件や絞込み条件に漏れがあると直積結合が選択されるかも
tableA = 1000 件、 tableB = 2 万件 の直積結合 1,000 * 20,000 = 20,000,000 2000 万件処理されてしまう が、結果が正しいと気付かない事も。 データ量が増加すれば、致命的な性能劣化に

参考ＵＲＬ：
http://www.slideshare.net/kwappa/20090107-postgr …

上記のこと教えていただきたいんです、お願いします。

No.4 回答者： yamada59 回答日時： 2009/09/02 21:26

根本的な問題の解決には回答番号: No.2 で marimari01 さんが言うようにデータ型を適切なものに変更する必要があります。

ただ、テーブル定義の変更が困難な場合には統計情報のエントリ数を増やして対応できる場合もあります。

PostgreSQL では、データの分布状況などの統計情報をもとに実行計画を作成しますが、データの分布が著しく偏っている場合にはデフォルトのエントリ数 (8.3 までは 10、8.4 では 100) では適切な実行計画を作成できない場合があります。

統計情報のエントリ数を増やすには ALTER TABLE ... SET STATISTICS を実行します。以下の例ではエントリ数を 200 に増やしています。

ALTER TABLE ar06kakh ALTER ar06005 SET STATISTICS 200;

エントリ数を増やして ANALYZE を実行後に実行計画を確認して調整するといいと思います。

エントリ数を増やすと ANALYZE の実行時間が長くなるので注意してください。エントリ数を増やしても効果がないようであれば、インデックスが使用されやすくなるようにプランナコスト定数などのパラメータを調整するか、データ型を適切なものに変更するしかないですね。

No.3 回答者： marimari01 回答日時： 2009/09/01 23:40

先の回答でNUMBERと記述しましたが、NUMERICと読み替えてください

No.2 回答者： marimari01 回答日時： 2009/09/01 23:38

最初のexplainで

＞and a.ar06005 >= 20060830 and a.ar06005 <= 20060831
＞and a.ar06009 = '01'
で絞り込めるレコード数の推定件数が
＞" -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..93.60 rows=46 width=344) (actual time=0.046..43.635 rows=336
より、46件と見積もっています

次に、
＞and a.ar06005 >= 20060830 and a.ar06005 <= 20060901
＞and a.ar06009 = '01'
では、
＞" -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..5958.64 rows=3278 width=344) (actual time=0.229..189.109 rows=500 loops=1)"
上記の結果から3278件と見積もっています

ここで 3278/46=71.2倍ですね
次に
20060901-20060830=71です
ar06005が1増加すると46row増えると見積もっているのではないでしょうか？

そして、
＞"Total runtime: 4188.728 ms"
に71倍すると
297399.688msとなり
＞"Total runtime: 166043.673 ms"
よりコストが増える
つまり、Nested Loop よりHash Join の方がコストが少ないと判断したのではないでしょうか？

結論ですが、
20060830 や 20060901（列：ar06005） はNUMBER型でDATE型ではないため、
オプティマイザは、20060832～20060900の間にもデータが有ると判断して、実行計画を立てていると思います
言い方を変えると、このテーブルのデータは著しく偏っているといえます
NUMBERを型DATE型に変えるとNestedLoopを利用してくれると思います

No.1 回答者： marimari01 回答日時： 2009/08/24 13:03

データが著しく偏っていると、

インデックスを使用するより、全件検索したほうが早いとオプティマイザが
判断することはあります
8/1のデータが異常に多いとかね

実際の実行計画を貼り付けてもらえると、
わかりやすいかも・・・

この回答への補足

explain analyze
select a.* from ar06kakh a , ar07kakm b
where a.ar06001 = b.ar07001
and a.ar06005 >= 20060830 and a.ar06005 <= 20060831
and a.ar06009 = '01'
上記の条件で実行計画をやると、下記の結果が出てきます。
"Nested Loop (cost=0.00..12432.40 rows=1022 width=344) (actual time=37.445..4186.452 rows=6636 loops=1)"
" -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..93.60 rows=46 width=344) (actual time=0.046..43.635 rows=336 loops=1)"
" Index Cond: ((ar06005 >= 20060830::numeric) AND (ar06005 <= 20060831::numeric) AND (ar06009 = '01'::bpchar))"
" -> Index Scan using ar07_key17 on ar07kakm b (cost=0.00..257.28 rows=876 width=8) (actual time=5.605..12.306 rows=20 loops=336)"
" Index Cond: (b.ar07001 = a.ar06001)"
"Total runtime: 4188.728 ms"

explain analyze
select a.* from ar06kakh a , ar07kakm b
where a.ar06001 = b.ar07001
and a.ar06005 >= 20060830 and a.ar06005 <= 20060901
and a.ar06009 = '01'
同じSQL、検索期間だけ変わると、下記のような結果が出てきます。
"Hash Join (cost=6147.61..362891.32 rows=72833 width=344) (actual time=543.516..166040.292 rows=9678 loops=1)"
" Hash Cond: (b.ar07001 = a.ar06001)"
" -> Seq Scan on ar07kakm b (cost=0.00..302154.25 rows=4192225 width=8) (actual time=29.452..106880.430 rows=4192225 loops=1)"
" -> Hash (cost=5958.64..5958.64 rows=3278 width=344) (actual time=203.584..203.584 rows=500 loops=1)"
" -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..5958.64 rows=3278 width=344) (actual time=0.229..189.109 rows=500 loops=1)"
" Index Cond: ((ar06005 >= 20060830::numeric) AND (ar06005 <= 20060901::numeric) AND (ar06009 = '01'::bpchar))"
"Total runtime: 166043.673 ms"

因みに、索引は下記のようです。
ar06_key0：AR06001
…
ar06_key13：AR06005、AR06009
ar07_key0：AR07001、AR07002
…
ar07_key17：AR07001
また、AR06005のデータは日付を「YYYYMMDD」の形式のNUMERICです。
AR06005の日付毎にデータの件数は大体同じぐらいです（100～200件）。
AR06001のAR07KAKMのデータが1～300件ぐらいです。

補足日時：2009/08/31 13:56