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

[shiina]

椎名です。

石井さん、丁寧な説明まで付けていただいてありがとうございます。

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

やはりそうですよね。
毎回このような指定をするのもどうかなと思っていました。

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

もし参考になればと思い調べてみました。

デーブル件数 1,046,698

select t1_key_p1, count(t1_key_p1) from table1
group by t1_key_p1;
 t1_key_p1 | count
-----------+--------
 aaa       | 365865
...
(5 rows)

select t1_key_s1, count(t1_key_s1) from table1
where t1_key_p1 = 'aaa'
group by t1_key_p1, t1_key_s1;

 t1_key_s1 | count
-----------+-------
 bbb       | 93498
...
(286 rows)

select t1_key_s2, count(t1_key_s2) from table1
where t1_key_p1 = 'aaa' and t1_key_s1 = 'bbb'
group by t1_key_p1, t1_key_s1, t1_key_s2;

 t1_key_s2 | count
-----------+-------
...
 N43       |     1
(83672 rows)
ここまで来るとほとんど1〜2件のデータだけでした
癖のある分布なのかもしれません

> 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に時間がかかるようになります．

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;
ANALYZE table1;
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;
結果が'Seq Scan'なのか確認する

これを'SET STATISTICS'の値を大きくして繰り返してみました
最初は10づつやっていたのですが、100づつになって最後は1000になっ
てしまいました
1000以上を指定してもダメなようです。

WARNING:  ALTER TABLE: lowering statistics target to 1000


1000の時のexplainの結果です

 Group  (cost=56332.22..56495.82 rows=1636 width=30)
        (actual time=80067.11..80067.11 rows=1 loops=1)
   ->  Sort  (cost=56332.22..56373.12 rows=16360 width=30)
             (actual time=80067.08..80067.09 rows=1 loops=1)
         Sort Key: t1_key_p1, t1_key_s1, t1_key_s2
       ->  Seq Scan on table1  (cost=0.00..55187.19 rows=16360 width=30)
               (actual time=52867.49..80056.57 rows=1 loops=1)
         Filter: ((t1_key_p1 = 'aaa'::character varying)
              AND (t1_key_s1 = 'bbb'::character varying)
              AND (t1_key_s2 >= '999'::character varying))
 Total runtime: 80067.42 msec


> (2) effective_cache_sizeの調整
> 
> とりあえずeffective_cache_size を10000位にしてみてはどうでしょう．

'effective_cache_size'はデフォルトのままでした。
10000にして再起動したところインデックスを使うようになりました。

 Group  (cost=0.00..54308.46 rows=1636 width=30)
        (actual time=66.49..66.49 rows=1 loops=1)
   ->  Index Scan using table1_key1 on table1
        (cost=0.00..54185.76 rows=16360 width=30)
        (actual time=66.45..66.47 rows=1 loops=1)
     Index Cond: ((t1_key_p1 = 'aaa'::character varying)
              AND (t1_key_s1 = 'bbb'::character varying)
              AND (t1_key_s2 >= '999'::character varying))
 Total runtime: 66.89 msec
(4 rows)


そこでまた'SET STATISTICS'の値を10から増やしていったところ40でイ
ンデックスを使うようになりました。

チューニングがあまかったということですね。
'effective_cache_size'は10000のままでいいとして、
マニュアルを見ると'SET STATISTICS'の値は"プランナの推定精度と
ANALYZE の処理時間と pg_statistic の占める容量とのトレードオフ"
となっていますので100ぐらいでもいいのかなと思っています。
言葉どおりトレードオフなのでしょうが、このあたりはどのように判断
なさっているのかお聞かせ願えないでしょうか？