[pgsql-jp: 30292] Re: indexを使用しない/ 使用する違いは？

[Genshirou Taki]

g_takiです。


On Mon, 30 Jun 2003 22:27:04 +0900 (JST)
sugita ＠ sra.co.jp wrote:

>   杉田です。
> 
> From: Genshirou Taki <g_taki ＠ max-corporation.com>
> Subject: [pgsql-jp: 30290] Re: indexを使用しない/ 使用する違いは？
> Date: Mon, 30 Jun 2003 22:07:21 +0900
> 
> ;;; createdbからまったく同じ手順でデータ投入まで行い、
> ;;; どちらも削除は一切行っておらず、
> ;;; 異なるのは、OSや、DBのバージョンなどだけです。
> ;;; 
> ;;; テスト環境と本番環境というつもりだったのですが、
> ;;; 都合上、まったく同じ環境を整えることができませんでした。
> 
>   この条件では、DB のバージョンが違うと結果が異なる可能性は少なそうですが、両
> 方でのクエリーと EXPLAIN ANLAYZE の結果を見せて頂く事が可能ならば何か分かるか
> も知れません。実際、DB のバージョンによって、クエリーにより、得手不得手は在り
> 得ます。

[pgsql-jp: 30286]で書いたように、以下のようになったわけですが、

##########
explain時のSQL文
explain select * from haisyutu_map WHERE id_area = 80;

環境Aの結果
Index Scan using haisyutu_map_pkey on haisyutu_map  (cost=0.00..17.07
rows=5 width=40)
   Index Cond: (id_area = 80)

環境Bの結果
Seq Scan on haisyutu_map  (cost=0.00..78.25 rows=353 width=40)
################3

勝手に、indexを使用してくれた環境で、vacuumdb -a -f -z　ではなく、
psql 上で　analyze をかけたところ、indexを使わなくなってしまいました。

vacuumdb と analyze とでは、働きが違うのかな？とこれから調べるところです。

> 
> ;;; 杉田さんのおっしゃられた”分布傾向”という言葉の意味をちょっとgoogleで調
> ;;; べただけでは理解できなかったのですが、
> 
>   ビュー pg_stats の correlation です。
CSEというツールで覗いてみたところ、
(コマンドなどをなかなか覚えなくなるのは問題ですが、便利に使わせていただ
いております。)

最大が -1 から 1 の値をとるようですが、
これをそれぞれの環境で比較するということですね？

これから値の意味や、見方などを調べてみようと思います。
> 
> ;;; まったく同じ手順でデータを投入しても(削除は行っていない状態)、indexを使
> ;;; 用する/しないで違いが出るので、マニュアルでindexを使うように支持しなさい
> ;;; ということでしょうか？
> 
>   いえ、それはプランナを制御するという範疇です。
> 
> ;;; また、100万件以上入るのは別のテーブルで、
> ;;; 現時点で件数がはっきりしている小さな(3000件)テーブルで今回調べたところ、
> ;;; 違いが発覚したしだいです。
> ;;; どちらも、運用段階では、insertは発生せず、更新のみありえるという条件はあ
> ;;; ります。
> 
>   どちらも ANALYZE & VACUUM ないし VAUUM ANALYZE はされていますよね。3000 件 
> なので、ANALYZE のサンプリング数と同じデータ件数ですし。
> 

はい。行いました。


index、パフォーマンス、プランナといった言葉で調べたところ、

以下のcreate文のようにprimary_keyを設定しただけでは、
explain select * from haisyutu_map WHERE id_area = 80;といったSQLのとき
にindexを使ってくれないのでは？という考えに至りました。

create table haisyutu_map(
id_area         integer,
id_material     integer,
img_total       integer,
中略
primary key(id_area, id_material)
);

そこで、create index aaaaa on haisyutu_map(id_area);として、
手動でindexを追加したところ、
どちらの環境でも確実にindexを使用し、速度も格段に向上しました。


indexを作成する際の指針として、
「SQL文のWHEREなどの条件に使用する列をindexにする」としました。
indexを何パターンもつくることのデメリットを見つけられなかったので、今の
ところこれで良いのか？と思うのですが、この考え方は正しいのでしょうか？

たとえば、例に挙げてあるhaisyutu_mapというtableでは、
1:id_area で絞り込むので、作る。
2:id_area と id_material 両方を条件に絞るので、primary_keyで作られる
  indexを使用
3:id_materialからしぼることは行わないので、
  create index bbbb opn haisyutu_map(id_material) は作っていません。
といった具合なのですが。


パフォーマンスチューニングは定石はありますが、経験がモノを言う分野だと思
うので、もっと勉強しなくてはと実感しました。
いままで非常に小さなデータしか扱ったことがなかったので、マシンパワーに頼っ
ていましたが、今回indexをいじって、劇的に速度が変わり重要性を思い知らさ
れました。

> 
> Kenji Sugita                                      

-- 
Genshirou Taki <g_taki ＠ max-corporation.com>