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

[Tatsuo Ishii]

石井です．

> > (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'の値が40
で始めてインデックスが使われるようになったのですね．興味深い報告でした．

後は，table1の実際のサイズがどの位なのかわかるとよいですね．table1のサ
イズを知るには，いくつかの方法があります．

(1) contrib/pgstattupleを使う．正確かつ簡単にサイズを知ることができる
    が，実行のたびにテーブルを全スキャンするので，実運用中などで負荷が
    気になる場合はおすすめしません．

(2) contrib/dbsizeを使う．比較的正確かつ簡単にサイズを知ることができる
    し全スキャンをしないので(1)よりもずっと負荷が低い．しかし情報量は
    (1)より少ない．

(3) 最近VACUUMを実行した時点のサイズでよければ，以下の問い合わせでサイ
    ズを知ることができる．

    SELECT relpages FROM pg_class WHERE relname = 'table1';

    結果の単位はブロック数です．

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

pg_statisticの占める容量はたいしたものではないし，SET STATISTICSの値が
20から40に増えたところでANALYZEの負荷がさほど増えるとは思えないので，
この例で言えばトレードオフと言うほどではないと思います．
まあ，DB内の全テーブルのぜんぶのSET STATISTICSの値を増やせば影響がある
と思いますが．
--
Tatsuo Ishii