[pgsql-jp: 29521] Re: インデックスの利用について
shiina
shiina @ senpo.com
2003年 3月 31日 (月) 16:43:02 JST
椎名です。
石井さん、丁寧な説明まで付けていただいてありがとうございます。
> あるいは 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ぐらいでもいいのかなと思っています。
言葉どおりトレードオフなのでしょうが、このあたりはどのように判断
なさっているのかお聞かせ願えないでしょうか?
pgsql-jp メーリングリストの案内