その中で、え?そうなの?と思った件があったので、ここに書いておこうと思います。
もし、より良い解決策をご存知の方がいらっしゃいましたら、ぜひ教えてください。
やたらとパフォーマンスが悪い箇所があるということで調査した結果、ソースコードのただ1行にすごい時間がかかっていた。
そして、それが繰り返して使用されているということで、レスポンスを時間内に返せないでいた。
my $result = MyData::CDBI->search_where(
{
t_id => \@tid,
code_id => $cid,
disable => 0
},
{
columns => [ 'id' ]
}
);
- @tid は1000個の値を持っている配列である
- MyData::CDBI は、Class::DBI を base としたクラスで Class::DBI::AbstractSearch を use している
- 検索対象のテーブルには100万レコードが入っている
この検索に50秒かかっていたのだ。(←テスト用に用意した大量データの場合。以下同じ)
データベースのほうを見たところ、
- t_id は text型で INDEX を使用していない
- code_id は integer型で INDEXを使用していない
そのため、INDEX がないせいだろうと思って、つけてみた。
CREATE INDEX t_id_idx ON my_table (t_id);
CREATE INDEX code_id_idx ON my_table (code_id);
そして、EXPLAIN ANALYZE をしてみた。
EXPLAIN ANALYZE SELECT id, t_id FROM my_table WHERE disable=0 AND code_id=1 AND t_id IN (IDを1000個);
当然のことながら Index Scan になっていたし、45ms で返ってきていたので、解決したと思った。
EXPLAIN ANALYZE
Bitmap Heap Scan on visitor (cost=9666.11..1256891.51 rows=957593 width=8) (actual time=9.454..38.358 rows=81903 loops=1)
Recheck Cond: (t_id = ANY ('{2001,
・・・(中略)・・・
2998,2999,3000}'::text[]))
Filter: ((code_id = 1) AND (disable = 0))
-> Bitmap Index Scan on t_id_idx (cost=0.00..9426.71 rows=958871 width=0) (actual time=9.145..9.145 rows=82010 loops=1)
Index Cond: (t_id = ANY ('{2001,
・・・(中略)・・・
997,2998,2999,3000}'::text[]))
Total runtime: 45.652 ms
だが、実際にアプリケーションを動かしてみると遅いままだった。
また、Class::DBIの検索部分のみを切り出したコードで試したが、結果は同じく約50秒だった。
そこでミスに気付いた。
検索条件の中の t_id => \@tid, の部分は IN検索ではなくて OR検索だった。
t_id in (1,2,...) ではなく、t_id=1 OR t_id=2 OR ... である。
そこで、EXPLAIN ANALYZE をもう一度やりなおした。
EXPLAIN ANALYZE SELECT id, t_id FROM my_table WHERE code_id=1 AND (
(ext_id = '2000') OR
・・・(中略)・・・
(ext_id = '2999')
) AND disable=0;
約40秒かかっており、確かに遅かった。
OR検索は遅いということだ。
Seq Scan on my_table (cost=0.00..2680689.17 rows=963661 width=8) (actual time=0.129..39667.052 rows=81903 loops=1)
Filter: ((code_id = 1) AND (disable = 0) AND ((t_id = '2000'::text) OR
・・・(中略)・・・
(t_id = '2998'::text) OR (t_id = '2999'::text)))
Total runtime: 39677.908 ms
これのせいだったか~と思い、INを指定する検索に変更した。
my $result = MyData::CDBI->search_where(
{
t_id => {'-in' => \@t_ids},
code_id => $cid,
disable => 0
},
{
columns => [ 'id' ]
}
);
それなのに、またしても秒数に変化なし。
50秒のまま。
INの検索になっていないのかな?と疑う。
だが、DBのログを見たところ、ちゃんとINの検索になっていた。
postgres[3656]: [9-1] LOG: 実行 dbdpg_1: SELECT id
postgres[3656]: [9-2] FROM my_table
postgres[3656]: [9-3] WHERE ( code_id = $1 AND disable = $2 AND t_id IN ( $3, $4, $5, $6, $7, $8, $9, $(略)
IN検索にしたら、「Total runtime: 45.652 ms」に近づくと思っていたのに、残念。
次に疑うべきは、t_id が text型であるせいで遅い?ことだ。
入ってるものはIDなので、integer型に変更することにした。
(単なる過去のミスでtext型だったと思われる。さっさと修正しろよって話ですよね、はい。)
ALTER TABLE my_table ALTER COLUMN t_id TYPE INTEGER USING CAST(t_id AS INTEGER);
さぁ今度こそ!とテストコードを実行したところ、25秒。
速くなったけど、なんか違う。
私が求めているのは、「Total runtime: 45.652 ms」だ。
postgres だと pg_stat_user_tables というテーブルの統計情報に、シーケンシャルスキャンとインデックススキャンの実施回数が記録されている。
そこでは idx_scan の数が1つ増えていた。
どうやらインデックススキャンをしてこの遅さらしい。
もうDBテーブルに心当たりはなく、DB側の問題ではないだろうと推測した。
そして実際にどこで時間がかかっているのか、Class::DBI::AbstractSearch->search_where を見てみることにした。
すると、$sth->execute で時間がかかっていることがわかった。
Class::DBI を使わずに、DBI を使ってベタ書きして試したところ、やはり execute が遅かった。
my $dbh = DBI->connect('dbi:Pg:host=x;dbname=y', 'z', '', {AutoCommit => 0, RaiseError => 0});
my $sql = 'select id from my_table where (code_id = ? and disable = ? and t_id in (?, ?, ?,(略), ?, ?, ?));';
my $sth = $dbh->prepare($sql)
my $result = $sth->execute(1,0,@t_ids)
試しに selectall_arrayref で検索してみたところ問題のない速さだった。
my $result = $dbh->selectall_arrayref($sql);
execute が遅いことは確定。
だが、Class::DBI を使うと、自動的に execute で実行されてしまう。
ということで、set_sql を使って検索をすることで、この execute が遅い問題を回避。
外部から入力する値はないものの、これではイケてない。
my $sql = sprintf('select id from my_table where (code_id = 1 and disable = 0 and t_id in (%s))', join ',', @t_id);
MyData::CDBI->set_sql('by_t_id' => $sql);
一応速度は EXPLAIN ANALYZE で Index Scan と表示されていたときの速度(と思われる速さ)になっている。
まとめると
- search_where を set_sql に変更したこと
- t_id を1000個並べる検索条件を or から in に変更したこと
- t_id をtext型からinteger型に変更したこと
によって速度は変化していった。
set_sql/search_where | or/in | text/int | time(sec) |
---|---|---|---|
search_where | or | text | 49.038347 |
search_where | or | int | 47.85891 |
search_where | in | text | 41.17876 |
search_where | in | int | 22.656974 |
set_sql | or | text | 46.890032 |
set_sql | or | int | 47.396322 |
set_sql | in | text | 7.736877 |
set_sql | in | int | 7.486474 |
ただ、そもそもの問題として、この1000個のIDを渡して検索する箇所というものをなくすべきだと思っている。
そのため、現在はデータ構造を変更するなどして、こういう検索を消すことに力を入れていこうとしている次第である。
.
5 コメント
Write コメントsugimotoです。
Reply丁度先週末に同じことにはまりました。。。
恐らくt_idにはnullもしくは同じ値が多くありませんか?
そのようなデータに対してプレースホルダーを使用した検索はインデックススキャンが使用されないようです。
ここからはあくまで仮説ですがプレースホルダーへのあてはめより実行計画を決定する方が先なんだと思います。
そうするとその時点でt_idに対してはどのスキャンをすべきか統計情報からは判断できずシーケンシャルスキャンを選択して遅いのではと考えてます。
いつまでたってもブログを書かないくせにツッコミだけするtakayamaです
Replyselectall_arrayrefは、内部でprepare->execute->fetchall_arrayrefを行ってるだけですので、
execute自体は問題ではない気がします。
あとの違いがあるとしたら、bind_values を使うかどうかですが、
先程100万件入ったDBを作り実験してましたが、
bind_valueを使ったprepare->executeと使わないprepare->executeではほぼ差がなかったので、
なにか別の問題ではないでしょうか?
実験に使ったテーブルが単純なテーブルなので、テーブル構造等の問題かも?
実験に使ったデータは、INT型のカラムに NULLと0から1000の値をランダムで100万件入れたテーブルです
値の分布はNULLが約9万件 0から1000が約1000件ずつです
bind_value使う
SQL: SELECT * FROM large_test WHERE num IN ( ?, ?, ( 略 ) ?, ? )
count : 908537
10回平均 3.611秒
bind_value使わない
SQL: SELECT * FROM large_test WHERE num IN ( 1, 2, ( 略 ), 999, 1000 );
count2 : 90853
10回平均 3.938秒
ちなみに、Class::DBIのset_sqlはグローバルにメソッドを登録しに行きます。
動的にset_sqlするとメモリリークするわ、スレッド環境だと混じるわなので、お気をつけください。
( Ima::DBIのset_sqlとClass::DBIの_generate_search_sql がポイントです )
set_sqlを使う場合は、
set_sql( mysearch, 'SELECT * FROM moge WHERE hoge = ? AND fuga = ?' )
のようにプレースホルダーを使い、
set_sql自体はプログラムロード時に1回だけ実行されるようにするのが本式ですね。
CDBIのORマッパの機能が必要ならば、
retrive_from_sqlかsth_to_objectsを直接呼んでしまったほうがいいかもしれないですね。
>> sugimotoさん
prepare->executeにEXPLAIN ANALYZEつけて渡してみたのですが、
どうもIndex Scanしてくれてるっぽいです。
> takayamaさん
Replynumって同じデータが多く入ってます?(半分以上がnullとか)
その状況でプレースホルダー使用すると間違いなくシーケンシャルスキャンになってしまいます。
ちなみにClass::DBIとか関係なくDBIのみでも再現します。
>sugimotoさん
Reply100万行のデータに対して値のパターンはnull 0 .. 1000 の1002種類なので、むしろ重複のほうが多いですね。
prepare->executeにEXPLAIN ANALYZEの返り値にpsql上でEXPLAIN ANALYZEしたものと同じ内容が返ってくるのですが、
それ見る限りではIndex Scanでした。
PostgreSQLでインデックスが使用されない問題
Replyhttp://yamagu.mo-blog.jp/weblog/2011/11/postgresql_420b.html
こちらで言及されていました