[pgsql-jp: 29514] Re: インデックスの利用について

[Tatsuo Ishii]

石井です．

> PostgreSQL 7.3.2を使用していて少し疑問に思いましたので
> お教え下さい。
> 
> 以下のようなテーブルがあります。
> 
> table1
> create table table1(
>     t1_key_p1  varchar(10),
>     t1_key_p2  integer,
>     t1_key_p3  integer,
>     t1_key_s1  varchar(20),
>     t1_key_s2  varchar(50),
>     t1_data1   integer,
>     t1_data2   integer,
>     primary key (t1_key_p1, t1_key_p2, t1_key_p3)
> );
> create index table1_key1 on table1 
>     (t1_key_p1, t1_key_s1, t1_key_s2, t1_key_p2);
> 
> データ件数は約100万件です。vaccum full, analyzeはしてあります。
> 
> このテーブルに対し
> 
> explain select t1_key_s2 from table1
>  where t1_key_p1 = 'aaa'
>  and t1_key_s1 = 'bbb'
>  and t1_key_s2 >= '999'
>  group by t1_key_p1, t1_key_s1, t1_key_s2;
> 
> を実行すると
> 
> Group  (cost=56581.99..56745.32 rows=1633 width=31)
>  ->  Sort  (cost=56581.99..56622.82 rows=16333 width=31)
>   Sort Key: t1_key_p1, t1_key_s1, t1_key_s2
>   ->  Seq Scan on table1 (cost=0.00..55439.06 rows=16333 width=31)
>         Filter: ((t1_key_p1 = 'aaa'::character varying)
>             AND (t1_key_s1 = 'bbb'::character varying)
>             AND (t1_key_s2 >= '999'::character varying))
> 
> Seq Scan(?)になり非常に遅くなります。そこで
> 
> set enable_seqscan = false;
> 
> として同じクエリーを実行すると
> 
> Group  (cost=0.00..63985.26 rows=1633 width=31)
>  ->  Index Scan using table1_key1 on table1
>   (cost=0.00..63862.77 rows=16333 width=31)
>    Index Cond: ((t1_key_p1 = 'aaa'::character varying)
>      AND (t1_key_s1 = 'bbb'::character varying)
>      AND (t1_key_s2 >= '999'::character varying))
> 
> となり早いです。
> (ちなみに結果は1件です。)
> 
> postgresql.confを書き換えるのは他に影響がありそうですし、
> 毎回"set enable_seqscan = false"をするしかないでしょうか？

あるいは set enable_sort = false にするか．どちらにしてもあまりおすす
めできない方法なので，もうちょっとなんとかならないか考えてみました（ち
なみに7.4ではsortを使わずにgroup byができるようになったので，デフォル
トのままでもかなり良くなっています）．

(1) 統計情報の改善

この記事のスレッドを見ると，

>  where t1_key_p1 = 'aaa'
>  and t1_key_s1 = 'bbb'
>  and t1_key_s2 >= '999'


のWHERE句の抽出結果が実際には1であるとのことです．しかしオプティマイザ
の見積もりは(cost=0.00..63862.77 rows=16333 width=31)にあるように，
16333であり，このあたり非常に解離があるように思います．つまり，

>     t1_key_p1  varchar(10),
>     t1_key_s1  varchar(20),
>     t1_key_s2  varchar(50),

のどれかあるいは全部が非常に癖のあるデータ分布になっていて，オプティマ
イザが対応できていないように思われます．そこで，まずはanalyzeするとき
のデータのサンプリングをもっときめ細かくし，少しでもオプティマイザの見
積もりが現実に近づくようにするのが先決問題です．具体的には，

ALTER TABLE table1 ALTER COLUMN t1_key_p1 SET STATISTICS 20;
ALTER TABLE table1 ALTER COLUMN t1_key_s1 SET STATISTICS 20;
ALTER TABLE table1 ALTER COLUMN t1_key_s2 SET STATISTICS 20;

を実行してから再度ANAKYZEをします．

20というのはデフォルトの約2倍のきめ細かさで統計情報を取ってくることを
意味します．これで効果がなければ，もっと数値を大きくしてみてください．
ただし，あまり大きくするとANALYZEに時間がかかるようになります．

(2) effective_cache_sizeの調整

# effective_cache_size = 1000

という行がpostgresql.confにあります．これは，カーネルのバッファキャッ
シュの大きさの見積もり値で，通常 effective_cache_size * 8192 がバイト
に換算した値になります．つまりデフォルト値は8MBです．この値の範囲にター
ゲットテーブルが収まっていると，オプティマイザはより積極的にインデック
スを使うようになります．実際にインデックスを使った方が速いということは，
effective_cache_size が実情よりも低すぎる設定になっていると考えられま
す．問題のテーブルの大きさが実際どの位なのかわかりませんが，
とりあえずeffective_cache_size を10000位にしてみてはどうでしょう．

(3) limitの追加

問い合わせの出力行が高々n行である場合，LIMIT nを追加するとインデックス
が使われるようになる場合があります．つまり，

select t1_key_s2 from table1
where t1_key_p1 = 'aaa'
  and t1_key_s1 = 'bbb'
  and t1_key_s2 >= '999'
  group by t1_key_p1, t1_key_s1, t1_key_s2
LIMIT 10;

などとするわけですね．

以上，お試し下さい（(1)-(3)は併用も可能です）．
--
Tatsuo Ishii