トップ 差分 一覧 Farm ソース 検索 ヘルプ RSS ログイン

PostgreSQL【インデックス】

PostgreSQL【インデックス】

[PostgreSQL]
最終更新時間:2010年10月04日 21時23分46秒

インデックス

 インデックスとは

インデックスとはレコードの検索を高速にするための仕組み。
テーブル内の特定の項目の内容を素早く検索できる形式でファイルとして保存される。

 インデックスの作成

sampleテーブル作成
 sample-# create table sample (
 sample(#   id int,
 sample(#   name varchar(20)
 sample(# );
インデックス作成

インデックスは以下のように作成できる

 CREATE INDEX [インデックス名] ON [テーブル名] (カラム1,カラム2..);

sampleテーブルのidをsample_indexとして作成する

 sample=# CREATE INDEX sample_index ON sample (id);
インデックスの確認

インデックスの確認は以下で行うことができる

 \d [インデックス名]

sampleテーブルのインデックスsample_indexの確認を行う

 sample=# \d sample_index;
 Index "public.sample_index"
  Column |  Type   
 --------+---------
  id     | integer
 btree, for table "public.sample"

また下記のようにも確認ができる

 \d [テーブル名]

sampleテーブルの確認を行う

 sample=# \d sample
            Table "public.sample"
  Column |         Type          | Modifiers 
 --------+-----------------------+-----------
  id     | integer               | 
  name   | character varying(20) | 
 Indexes:
     "sample_index" btree (id)

 インデックスのクエリ速度計測

あまり良く分かっていない状態だが、とりあえずインデックスが作ってあるテーブルと作っていないテーブルを作成し、
結果の速度計測を行ってみる。

テーブル作成

sample1(インデックスあり)とsample2(インデックスなし)のテーブルを作成する。

sample1テーブル作成

 sample-# create table sample1 (
 sample(#   id int,
 sample(#   name varchar(20)
 sample(# );
 CREATE TABLE

インデックス作成

 sample=# CREATE INDEX index1 ON sample1 (id);
 CREATE INDEX

テーブル確認

 sample=# \d sample1
     Table "public.sample1"
  Column |  Type   | Modifiers 
 --------+---------+-----------
  id     | integer | 
  name   | text    | 
 Indexes:
     "index1" btree (id)

sample2テーブル作成

 sample-# create table sample2 (
 sample(#   id int,
 sample(#   name text
 sample(# );
 CREATE TABLE

テーブル確認

 sample=# \d sample2
     Table "public.sample2"
  Column |  Type   | Modifiers 
 --------+---------+-----------
  id     | integer | 
  name   | text    | 

100万レコードを追加して計測を行う

 sample=# select count(*) from sample1;
   count  
 ---------
  1000002
 (1 row)
 sample=# select count(*) from sample2;
   count  
 ---------
  1000002
 (1 row)
計測

EXPLAIN文を使用して計測を行う

 EXPLAIN [ANALYZE][VERBOSE] query

sample1テーブルの計測

 sample=# EXPLAIN ANALYZE select * from sample1 where id = 123456;
                                                     QUERY PLAN                                                    
 ------------------------------------------------------------------------------------------------------------------
  Index Scan using index1 on sample1  (cost=0.00..8.36 rows=1 width=9) (actual time=17.714..17.718 rows=1 loops=1)
    Index Cond: (id = 123456)
  Total runtime: 17.759 ms
 (3 rows)

sample2テーブルの計測

 sample=# EXPLAIN ANALYZE select * from sample2 where id = 123456;
                                                QUERY PLAN                                               
 --------------------------------------------------------------------------------------------------------
  Seq Scan on sample2  (cost=0.00..17383.34 rows=1 width=9) (actual time=26.343..236.363 rows=1 loops=1)
    Filter: (id = 123456)
  Total runtime: 236.399 ms
 (3 rows)
インデックスが使われているかの確認

実際にインデックスが使われているかを確認するには計測のときと同様にEXPLAINコマンドを使用する。

インデックスが使われている場合

 "Index Scan using〜"となり結果に"Index Scan"が入る

インデックスが使われていない場合

 "Seq Scan on〜"となり結果に"Seq Scan"が入る

 計測結果

EXPLAIN ANALYZEとすると実際の検索にかかった時間を計測することができる。
sample1のほうは17msなのに対し、sample2のほうは236msと約13倍となった。

 インデックスのポイント

インデックスを使用した場合のメリット・デメリットをつらつら書いていく

  • インデックスを使用すると検索処理が高速になる
  • 全てに対してインデックスを作成するとディスクスペースを消費するだけなので注意
  • インデックスが使われているかの確認はEXPLAINコマンドで行える
  • EXPLAIN ANALYZEを使うと実際にクエリが返ってくるまでの時間を計測できる
  • プライマリーキーやユニークキーには自動的にインデックスが貼られる
  • インデックスが使われない例
    • 重複が多い
    • データ件数が少ない
    • 出力データ件数が多い
    • 文字列検索をしている
    • VACUUMをかけていない

マルチカラムインデックス

部分インデックス

参考

http://thinkit.co.jp/free/marugoto/2/1/14/1.html
http://www.postgresql.jp/blog/68
http://websys-memo.biz/postgres8.2.0/indexes-examine.html
http://www.stackasterisk.jp/tech/dataBase/postgresTuning02_02.jsp
http://www.stackasterisk.jp/tech/dataBase/postgresql02_04.jsp