インデックスオンリースキャンを試す：もう一度始めたい人のPostgreSQL（3）（1/2 ページ）

連載第3回目は、PostgreSQL 9.2で登場した「インデックスオンリースキャン」（Index only scan）を紹介し、その性能について考察していきます。

[正野裕大（SRA OSS, Inc. Japan），＠IT]

　第1回ではPostgreSQL 9系の主な追加機能を、第2回は新機能の中でも目玉的な位置付けである「レプリケーション機能」について紹介しました。連載第3回目は、PostgreSQL 9.2で登場した「インデックスオンリースキャン」（Index only scan）を紹介していきます。

インデックスオンリースキャンのメリット

　インデックスオンリースキャンとは、postgresプロセスがクライアントから発行されたSQLを実行する際に、内部的に実行されるテーブルデータ検索方式の1つです。インデックスオンリースキャンは、その名の通り、インデックスのみを利用してデータを読み取ります。

　インデックスオンリースキャンを使うと、インデックスが定義されているカラムのデータさえ取得すれば済む場合、つまり、ほかのカラムのデータは必要ない場合に、わざわざテーブルのデータを取得せずに結果が返せるようになります。これにより、テーブルからデータを取得する処理を省き、クエリの検索性能を向上させることができます。

　ちなみにその他の代表的な検索方式としては、「シーケンシャルスキャン」「インデックススキャン」「ビットマップスキャン」が挙げられます。

　シーケンシャルスキャンは、テーブルのデータを先頭から順番に全て読み取ります。インデックススキャンは、インデックスとテーブルに交互にアクセスして必要なデータのみを読み取ります。ビットマップスキャンは、インデックスを利用して、取得するテーブルのデータ行と対応するビットをオンに切り替えたビットマップを作成し、そのビットマップを利用してテーブルの必要な個所をシーケンシャルに読み取っていきます。

　実は、このインデックスオンリースキャンは、追記型アーキテクチャであるPostgreSQLでは実装が難しいとされてきた機能です。PostgreSQLでは、取得したいデータ行がそのトランザクションから見えてもよいかどうかという情報は、テーブルしか持っていません。したがって、インデックスオンリースキャンによってデータを取得したくても、そのデータが見えてもよいかを判断するには結局テーブルにアクセスしなければ分からない、という問題がありました。

　そこでPostgreSQL 9.2では、データ行が全てのトランザクションから可視であるか否かという情報を格納しているビジビリティマップを参照することで、この問題を解決しました。取り出したいデータが全てのトランザクションから可視であれば、インデックスからデータを取り出し、そうでなければテーブルからデータを取り出そうとします。そういう意味では、インデックスオンリースキャンは、場合によってはテーブルにアクセスすることもあります。

インデックスオンリースキャンの動作確認

　インデックスオンリースキャンの簡単な動作確認を行ってみましょう。

　前準備としてpgbenchを使ってテスト用のインデックスが付いた50万行のテーブル、pgbench_accountsを作成します。pgbenchとはPostgreSQL用の簡単なベンチマークツールです。pgbenchはデフォルトのインストールでは利用できない追加プログラムなので、PostgreSQLのソースコードディレクトリ下の“contrib/pgbench”からインストールする必要があります。

[postgres@node1 ~]$ cd /usr/local/src/postgresql-9.2.4/contrib/pgbench
[postgres@node1 pgbench]$ make
[postgres@node1 pgbench]$ make install

　pgbenchを実行します。

[postgres@node1 ~]$ pgbench -i s 5 -U postgres postgres
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...
10000 tuples done.
:
:
500000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.

　-iオプションは初期化用オプションです。ベンチマーク用のテーブルとデータを作成します。-sオプションでデータの行数を指定します。指定した値の10万倍の行数が作成されます。−Uオプションは接続ユーザーを指定します。また、第1引数でテーブルを作成するDBを指定します。

　データベースに接続して、テーブル情報一覧を確認します。

[postgres@node1 ~]$ psql -d postgres -U opstgres
psql (9.2.4)
Type "help" for help.
postgres=# \d
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

　ベンチマーク用の4つのテーブルが作成されています。

　さらに、pgbench_accountsのテーブル情報を見てみます。

postgres=# \d pgbench_accounts
   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       | 
 abalance | integer       | 
 filler   | character(84) | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

　aidカラムには主キー制約が設定されているので、自動的にインデックスも作成されています。

　これで準備が完了しました。では、このaidカラムだけを参照する単純なSELECT文を発行してみましょう。インデックスを作成したカラムだけを参照するようなSELECT文であれば、テーブルデータの検索方式はインデックスオンリースキャンが用いられるはずです。クエリにEXPLAIN句を付け加えて、postgresプロセスが受け取ったクエリを内部でどのような方略に基づいて実行するか（QUERY PLAN）を表示します。

postgres=# EXPLAIN SELECT aid FROM pgbench_accounts ;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.00..11904.29 rows=500000 width=4)
(1 row)

　ご覧の通り、インデックスオンリースキャンが使われています。

　今度は、インデックスが作成されているaidカラムに加えて、インデックス対象外のfillerカラムも含めてSELECT文を発行してみます。

postgres=# EXPLAIN SELECT aid, filler FROM pgbench_accounts ;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..12937.00 rows=500000 width=89)
(1 row)

　このように、通常のシーケンシャルスキャンが使われました。