
テーブル結合検索、インデックス使えない原因を
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テーブルのインデックスが使わなかった。
同じSQLで、条件だけ変わると、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'
原因を知りたいです。
また、他のHPからテーブルの結合の説明がありました、
直積結合の回避
* 直積結合は、結合対象の2つのテーブルの全レコードの組み合わせを戻す処理
* SQLが複雑で、結合条件や絞込み条件に漏れがあると直積結合が選択されるかも
tableA = 1000 件、 tableB = 2 万件 の直積結合 1,000 * 20,000 = 20,000,000 2000 万件処理されてしまう が、結果が正しいと気付かない事も。 データ量が増加すれば、致命的な性能劣化に
参考URL:
http://www.slideshare.net/kwappa/20090107-postgr …
上記のこと教えていただきたいんです、お願いします。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.4
- 回答日時:
根本的な問題の解決には回答番号: 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.2
- 回答日時:
最初の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
- 回答日時:
データが著しく偏っていると、
インデックスを使用するより、全件検索したほうが早いとオプティマイザが
判断することはあります
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件ぐらいです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Oracle 下記のsqlで取得されるレコード以外を取得する方法ありますでしょうか。 SELECT B.番号, B 2 2022/04/20 23:21
- MySQL my_itemsテーブルのIDにAUTO_INCREMENT を追加ができるかで 1 2023/01/03 09:09
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- Ruby pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください 5 2023/03/08 09:57
- MySQL MySQLのテーブル作成で 自信がありません。 2 2022/08/28 05:35
- Oracle 質問です。 下記のテーブルとデータがあり、 取得想定結果のように出力したいです。 下記のsqlだと0 2 2023/05/23 19:10
- Oracle SQL update方法 2 2022/06/22 14:07
- Access(アクセス) アクセス 削除するレコードを含んだテーブルを指定してくださいのエラー対処方法 1 2022/11/24 15:01
- PostgreSQL DBFluteについて質問です。 環境:PostgreSQL java8 前提:webアプリケーショ 1 2022/07/07 00:49
- MySQL MYSQL エラー 2 2022/10/18 11:37
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SELECT 文の NULL列は?
-
単純なselectが遅くなるのです...
-
SQLでUPSERTを一度に複数行やる...
-
SQLにて指定日付より前、かつ最...
-
Viewからの検索速度について
-
MS Access から PostgreSQL へ...
-
2つのテーブルで引き算 postgres
-
reindex と update のデッドロック
-
同一カラムに複数条件指定
-
SQLについて何ですが
-
テーブルの結合について
-
javaでデータベース上のテーブ...
-
単位時間ごとのレコードのカウント
-
他テーブルの集計結果を別テー...
-
インデックスについて
-
XREAサーバでテーブル作成方法
-
PostgreSQLのリンクテーブル?...
-
PostgreSQLの断片化の状況を確...
-
group by で重複行を、縦横に展...
-
テーブルに存在しない列をselec...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SELECT 文の NULL列は?
-
テーブルに存在しない列をselec...
-
SQLでUPSERTを一度に複数行やる...
-
SQLにて指定日付より前、かつ最...
-
単純なselectが遅くなるのです...
-
PostgreSQLの断片化の状況を確...
-
2つのテーブルで引き算 postgres
-
MS Access から PostgreSQL へ...
-
最新レコードを抽出し外部結合...
-
javaでデータベース上のテーブ...
-
Postgresqlのレポート機能について
-
デットロック回避策(autocommit...
-
PostgreSQL レコードからアイテ...
-
Postgresのデータ領域の拡張に...
-
重複を許すキーの構文がわかり...
-
PostgreSQLのリンクテーブル?...
-
異なるデータベースでのINSERT...
-
テーブルを作ろうとしたら。
-
同一カラムに複数条件指定
-
テーブルにcsvファイルをインポ...
おすすめ情報