PostgreSQLチューニング実践テクニック第5回

部分インデックス

ここまでに紹介したインデックスは、あるテーブルのある列（もしくは列の組み合わせ）に関するすべての行のデータをインデックス化するものでした。しかし、頻繁に検索される値の範囲が決まっている場合は、その範囲を指定することによって、一部の行のみをインデックス化した部分インデックス（partial index）を作ることができます。部分インデックスはデータ量が少ないため、ディスクスペースを浪費しないだけでなく、検索効率も優れています。

たとえば、売上データを管理するテーブルがあり、売上高の高いものを頻繁に検索する場合には、次のようにCREATE INDEX文の最後にWHERE句で条件を指定して、条件にマッチするデータだけがインデックスに格納されるようにします。

 CREATE INDEX salesindex ON sales (amount) WHERE amount > 10000;

この部分インデックスは下記には適用されます。

 SELECT * FROM sales WHERE amount > 10000;

 SELECT * FROM sales WHERE amount > 20000;

しかし、下記には適用されません。

 SELECT * FROM sales WHERE amount > 5000;

検索値に部分インデックスの範囲外の値が含まれるからです。

インデックスが使われているかどうかを判断する

   実際にインデックスが使われているかどうかを判断する一番簡単な方法はEXPLAINを使うことです。EXPLAINはもともとPostgreSQLの問い合わせオプティマイザが生成した問い合わせプランを検証するためのコマンドです。

   通常、PostgreSQLは最も適切な（言い換えると最も高速な）問い合わせプランを作成してくれるのですが、この問い合わせプランは常に完璧というわけではありません。何らかの理由で不適切な問い合わせプランを作成してしまうこともあります。インデックスを作成したのに効率が向上しない場合は、 EXPLAINを使って問い合わせプランを確認してみるとよいでしょう。

   EXPLAINの使い方は簡単です。通常のSQL文の前に「EXPLAIN」と入れるだけです。図6を参照してください。

 test=# EXPLAIN SELECT * FROM accounts WHERE aid = 1;

	

	QUERY PLAN

	---------------------------------------------------------------------

	 Index Scan using accounts_pkey on accounts (cost=0.00..3.01 rows=2 width=100)

	   Index Cond: (aid = 1)

	(2 rows)

図6：EXPLAINの使用例1

「Index Scan using〜」とあるので、実際にインデックスが使われていることがわかります。使われているのはaccounts_pkeyです。これは、図7を見るとわかるようにaid列に自動的に作られた主キー制約用のインデックスです。

test=# \d accounts Table "public.accounts"
Column	\|	Type	\|	Modifiers
−−−−−−−−−	+	−−−−−−−−−−−−−−	+	−−−−−−−−−−
aid	\|	integer	\|	not null
bid	\|	integer	\|
abalance	\|	integer	\|
filler	\|	character(84)	\|
Indexes: "accounts_pkey" primary key, B-tree (aid)

図7：accountsテーブルのインデックス

逆にインデックスが使われないケースも見てみましょう（図8）。

 test=# EXPLAIN SELECT count(*) FROM accounts;

	QUERY PLAN

	---------------------------------------------------------------------

	Aggregate (cost=2890.00..2890.00 rows=1 width=0)

	 ->Seq Scan on accounts (cost=0.00..2640.00 rows=100000 width=0)

	(2 rows)

図8：EXPLAINの使用例2

「Seq Scan」とはsequential scanの略で、順スキャン、すなわちテーブルを頭から順番に見ていく処理を表しています。当然ながら順スキャンは高速とはいえませんが、count() を行うには順スキャン以外に方法がないので、処理を改善する余地はありません。