テーブル定義書作成時のIndex付加について

質問

基礎的なことで申し訳ございませんが、
お力添えよろしくお願いいたします。

CentOs 5 & PostgreSQL 8.4.9にてDB構築しております。
DB構築から、テーブル作成・項目追加・キー設定まで行いまして、
検索する際に、抽出件数が多く負荷のかかりそうなものに
Indexを作成しようと考えております。

■本題
　１．Indexを作成する対象となる項目の、目安としてどのようなものが考えられるでしょうか。
　　・キー対象の項目になっているもの
　　・検索条件の対象となる項目なども当たるでしょうか
　２．項目をまとめて一つのIndexにするメリット・デメリットはどのようなものでしょうか　
　３．テーブル結合したときのIndexの動作
　　　例) 下記のようなテーブルがあった場合
tablea(データ50000件程度)
hoge_id pkey1
hoge_no
hoge_nm
...
tableb(データ150000件程度)
test_id pkey1
hoge_id pkey2
hoge_betu_kb
...
　　　　select t1.hoge_id,
t1.hoge_nm,
t2.hoge_betu_kb
rom tablea as t1
leftjoin tableb as t2
on t1.hoge_id = t2.hoge_id
where t1.hoge_id > '100'
and t1.hoge_nm like 'あいう%'

　　このような場合のIndex作成は、
A. Create index idx_tablea_hoge_id ON tablea (hoge_id);
B. Create index idx_tablea_hoge_id ON tablea (hoge_nm);
C. Create index idx_tableb_hoge_id ON tableb (hoge_id); ←tableaで指定しているのでこれは不要？

　４．A.B.C.のようなIndex作成した際のテーブル定義の書き方
　　テーブル定義書のフォーマットは定められており、お門違いな質問かとは存じてはおりますが、
　　テーブル定義にIndex定義を書く場合に、添付した画像のようなフォーマットの書き方が
　　よく理解できずお気づきの点などございましたら、ご指摘願えたら助かります。
　　（以前は、CSEツールなどで自動出力される定義書のような書き方をしていたもので・・・）

　　　例）tablea
IDX1 IDX2 IDX3
hoge_id 1
hoge_nm 2

このような書き方になるのかどうか？
　　　　　IDX2, IDX3の項目とは？どのようなIndex定義をした際に書き込むことになるのかが判らず。。。
　　　　　基礎から勉強しなおすべきとは存じておりますが。。。

現状の期間あまり時間がないことと、
DB構築を一人で行っており、このような内容を相談できるものがおりませんゆえ、
改めて、Index付加することを考えてみると、いまさら聞けないような内容かもしれませんが
ちゃんと把握したうえで、テーブル構築したいと思いますので
今までの他の皆様の経験からのノウハウなども踏まえて、
ご教授いただけると大変助かります。

回答No.1

maniacpa2 · ベストアンサー

1:index作成の目安について
検索の条件になる項目で、重複があまり無い項目を作成すれば良いのでは無いでしょうか。また、テーブル作成時に特定のカラムに対してPKを設定するのであれば、PKには自動的にindexが付与されるので、ベット作成の必要は無いかと思われます。
いったん、テーブルを作ってみて、使いたいSQLをexplain analyzeで実行してみて、処理が遅い部分に関係しているカラムに対してindexを設定して行く方法をお勧めします

2:複合indexのメリット
indexサイズが小さくなります。ただ、指定の順番を間違うとindexが使われなくなるときもあります。

3:indexの付与対象について
indexはテーブルのカラムに対して付与するので、同一カラム名のindexを他のテーブルに作ったからと言って、違うテーブルで作成しなくて良い訳ではありません。

4についてはご質問の意図がよくわかりませんでした。

私の回答がお役に立てるようであれば幸いです。

お礼

ありがとうございます。
４の質問に関しましては、定義書の書き方で効率のよい書き方があれば
という内容でしたが、内部でよく話し合って書き方は精査したいと思います。

1,2,3の内容に関しましては、大変参考になりました。
また質問する機会ございましたら、よろしくお願いいたします。