jiro okazaki
okaza****@focus*****
2009年 5月 21日 (木) 14:19:51 JST
Focus systemsの岡崎と申します。 ludiaを使って全文検索システムを構築しています。 ludiaで大量の条件を使ってクエリした場合にシーケンシャルスキャンにかわっ てしまい、処理時間がインデックス利用時より大幅にかかる、という現象が発生 しました。 原因を突き止めるため、最小構成を以下のように作り原因を追いかけました。 -- テスト用スキーマ create table item ( id serial primary key, name text not null ); create table prop ( id serial primary key, value text not null, source integer references item (id) on delete cascade ); create index prop_value_ft_idx on prop using fulltextu (value); -- ここまで ludia.sen_index_flagsは16です。 itemとpropはitem.idとprop.sourceで1:Nで関連を持っています。 propに大量のデータがはいっており(70万行、valueにASCIIで1024文字ずつの データが入っています)、次のようなクエリを投げています。 select * form item where id in (select source from prop where value %% '0' or value %% '1' or ...); そして実行計画を調べてみると条件件数が少ない間は以下のようにインデック スベースで実行されます。 Hash IN Join (cost=45078.64..63462.26 rows=7662 width=9) Hash Cond: (i.id = prop.source) -> Seq Scan on item i (cost=0.00..10432.00 rows=700000 width=9) -> Hash (cost=44982.87..44982.87 rows=7662 width=4) -> Bitmap Heap Scan on prop (cost=128.29..44982.87 rows=7662 width=4) Recheck Cond: ((value %% '0'::text) OR (value %% '1'::text) OR ... -> BitmapOr (cost=128.29..128.29 rows=7700 width=0) -> Bitmap Index Scan on prop_value_ft_index (cost=0.00..9.75 rows=700 width=0) Index Cond: (value %% '0'::text) -> Bitmap Index Scan on prop_value_ft_index (cost=0.00..9.75 rows=700 width=0) Index Cond: (value %% '1'::text) ... しかし、件数が多くなるとシーケンシャルスキャンに変わって処理されてしまい ます。 Hash IN Join (cost=175287535.89..175310271.60 rows=442871 width=9) Hash Cond: (i.id = prop.source) -> Seq Scan on item i (cost=0.00..10432.00 rows=700000 width=9) -> Hash (cost=175282000.00..175282000.00 rows=442871 width=4) -> Seq Scan on prop (cost=0.00..175282000.00 rows=442871 width=4) Filter: ((value %% '0'::text) OR (value %% '1'::text) OR ... 実際にそのほうが早いのであれば問題ないのですが、シーケンシャルスキャンに なった場合、段違いに実行時間がかかってしまいます。 explainで調べたところ、989件以下であればインデックススキャンを、990件以 上であればシーケンシャルスキャンになるようでしたので、検索条件を988件、 989件、990件でそれぞれexplain analyzeして時間を計測してみたところ、 988件: 152309.244 ms 989件: 183351.101 ms 990件: 977404.325 ms(!!) となりました。 実際の実行にかかるコストの違いは明らかで、インデックススキャンでは検索条 件の数だけpgs2*scan()関数が呼ばれるのに対して、シーケンシャルスキャンで はpgs2contain0が条件数*テーブルの全行数分だけかかります。 ソースを追いかけたところ、Bitmap heap scanのコスト推定で下位層の selectivityの値の合計を使っており、そのためフェッチにかかるコストを計算 しているため、シーケンシャルスキャンしたほうが早い、とプランナに評価され ていたようです。 現在のludiaではcontsel関数を使ってselectivityを計算しており、その値は 0.001。つまり0.1%が帰ってくると推定されます。そのため条件が増えていき、 たとえば条件数が1000に近づけばコスト的にはどうやってもシーケンシャルス キャンの方が早いと推定されることになります。 結局のところ、bitmap heap scan on propでのデータベースページのフェッチコ ストが支配的なのが原因でした。 本当は適正値を求めるように選択性評価関数をちゃんと用意するべきなんでしょ うが、まだsennaの中身まではちゃんと追いかけられていないので急場をしのぐ ためにselectivityを下げることで逃げを打つことにしました。 逃げをうったコードのパッチを作りましたので添付しておきます。 パッチを当てるとパラメータのludia.selectivityとludia.join_selectivityを 参照するようになります。 ludia.selectivityで制約選択性予測関数で返す値を、ludia.join_selectivity で結合選択性予測関数で返す値を実数で設定できるようにしました。 デフォルト値はそれぞれもとの予測関数の値と同じ0.001にしています。 以上です。 -- 株式会社 フォーカスシステムズ okaza****@focus***** 岡崎 次朗 (おかざき じろう) -------------- next part -------------- テキスト形式以外の添付ファイルを保管しました... ファイル名: selectivity.patch 型: text/x-patch サイズ: 7685 バイト 説明: 無しDownload
