インデクスは特定の列(の組み合わせ)のデータを,特殊なデータ構造に格納して検索を高速に行えるようにするものである(※).インデクスは以下で作成する.

CREATE [ UNIQUE ] INDEX インデクス名
ON テーブル名 [ USING アクセスメソッド名 ]
( 列名 [ 列オペレータ ] [, 列オペレータ ]*);

UNIQUE

指定すると一意制約がかかる.

インデクス名

データベース内で一意なインデクスの名前.

アクセスメソッド名

インデクスの種類.Btree,Hash,Rtreeから選択する.デフォルトはBtree.大体において幾何データ型ならRtree,それ以外ならBtreeを指定すればよい.

smallint,bigintの場合は,クエリで値を指定する際に

値::smallint

などとしないとフィールドにインデクスを張ってもインデクスが効かない.

SELECT * FROM hoge WHERE smallint_field = 1::smallint

さらにLIKEや正規表現を使用する検索では,前方一致検索以外はインデクスが効かないので注意する.インデクスの削除はDROPである.

DROP INDEX インデクス名

インデクスはオプティマイザが使用した方が早いと判断したときに参照されるが,そのオプティマイザの判断にはテーブルの統計情報が必要である.テーブルの統計情報を収集するにはVACUUM ANALYZEを行う.

VACUUM ANALYZE [ テーブル名 ];

テーブル名を省略すると,DB内の全テーブルが対象となる.VACUUM ANALYZEは自動では実行されないので,テーブルがある程度更新されたら明示的に実行を指示する必要がある.また,コマンドラインからも実行可能である.

vacuumdb [ connection-options... ]
[ [ -d ]  dbname  ]  [ --full  |  -f  ]
[  --verbose | -v ] [ --analyze | -z ]
[ --table 'table [ ( column [,...] ) ]' ]

まずテーブルを作成する.今回はファイル名とiノード番号を属性にもつテーブルを作成した.

$ psql -h dbserver -d firstdb 
firstdb=> create table index_test (
firstdb(> filename text,
firstdb(> inode integer);

次にデータを作成する.ファイル名とそのiノード番号をフィールドをかき集め,テーブルに書き込む.

$ find /  -printf '%p\t%i\n' > ~/index_test.data 
$ psql -h dbserver -d firstdb 
firstdb=> \copy index_test from index_test.data

ここでインデクスを作成してみる.

firstdb=> CREATE INDEX inode_index
firstdb-> ON index_test (inode);

そしてVACUUMする(※).

firstdb=> VACUUM ANALYZE index_test;

当然ながらデータ数がある程度多くないと効果がわからないし,クエリによってはオプティマイザがインデクスを使用しないと判断し,その結果インデクスの効果が出ない場合がある.

$> /usr/lib/postgresql/bin/vacuumdb -h dbserver \
-d firstdb --analyze --table index_test
Password: 
VACUUM

列filenameのインデクスを作成し,以下のようなSELECT文を発行しても,インデクスは使用されない.

firstdb=> select * from index_test
firstdb-> where filename like './drivers/acpi%';

すなわちCREATE INDEXで列のインデクスを作成することができるが,列に関数を適用した結果のインデクスは別に作成しなければならない.これを関数インデクスという.作成方法は通常のインデクスとほぼ同じで,列を指定する部分に関数f(列)と記述する.列以外を引数にとる関数の関数インデクスは作成できないので注意.以下に例を示す.

firstdb=> CREATE INDEX upper_filename_index
firstdb-> ON index_test (upper(filename));
CREATE
firstdb=> VACUUM ANALYZE index_test;
VACUUM

で,実際に関数インデクスが使用されているかどうかはEXPLAINで確認できる(※).

firstdb=> explain select count(*) from index_test
firstdb-> where filename like './drivers/acpi%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=8598.00..8598.00 rows=1 width=0)
  ->  Seq Scan on index_test  (cost=0.00..8598.00 rows=1 width=0)

EXPLAIN
firstdb=> explain select count(*) from index_test
firstdb-> where upper(filename) like './DRIVERS/ACPI%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=6462.53..6462.53 rows=1 width=0)
  ->  Index Scan using upper_filename_index on index_test  (cost=0.00..6457.55 rows=1994 width=0)

EXPLAIN

上の例では二回EXPLAINしているが,一回目はインデクスを作成していないfilenameをWHERE句で使用しているので「Seq Scan」となっており,二回目はインデクスを作成したupper(filename)をWHERE句で使用しているので「Index Scan using upper_filename_index 」となっている.