テーブルに対してインデックスを作成すれば、ある条件のSELECT文を高速化することができる。
インデックスとは索引のようなもので、目的の行に到達する手順を提供する。
条件によってあるインデックスを使用できるかどうかが異なる。
インデックスのアルゴリズムはいろいろあるが、主にB-Treeインデックスが使用されている。
なお、インデックスが適切に動作するために、適宜ANALYZEもしくはVACUUM ANALYZEを実行する必要がある。
次のＳＱＬをファイルとして作成。（setup151.sql）
ログイン画面から実行。
oddtbs=> \i setup151.sql
...
t1テーブルの確認。
oddtbs=> select * from t1 limit 10; id1 | id2 | data -----+-----+--------------- 1 | 1 | DATA_0001_001 1 | 2 | data_0001_002 1 | 3 | DATA_0001_003 1 | 4 | data_0001_004 1 | 5 | DATA_0001_005 1 | 6 | data_0001_006 1 | 7 | DATA_0001_007 1 | 8 | data_0001_008 1 | 9 | DATA_0001_009 1 | 10 | data_0001_010 (10 rows) oddtbs=> select * from t1 order by id1 desc, id2 desc limit 10; id1 | id2 | data ------+-----+--------------- 1000 | 100 | DATA_1000_100 1000 | 99 | data_1000_099 1000 | 98 | DATA_1000_098 1000 | 97 | data_1000_097 1000 | 96 | DATA_1000_096 1000 | 95 | data_1000_095 1000 | 94 | DATA_1000_094 1000 | 93 | data_1000_093 1000 | 92 | DATA_1000_092 1000 | 91 | data_1000_091 (10 rows) oddtbs=> select count(*) from t1; count -------- 100000 (1 row)
性能を測定。
EXPLAIN ANALYZEによって性能を測定する。
oddtbs=> explain analyze select * from t1 where id1 = 500;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..1887.00 rows=99 width=22) (actual time=4.970..10.021 rows=100 loops=1)
Filter: (id1 = 500)
Total runtime: 10.061 ms
(3 rows)
※ 先頭のSeq Scanは全ての行を順番に検査したことを意味する。最後の数字は処理時間（ミリ秒）。
単一の列によるインデックスを作成する。
CREATE INDEX インデックス名 ON テーブル名(列名);
id1列に対するインデックスを作成する。
oddtbs=> create index index1 on t1(id1);
CREATE INDEX
性能を測定。
oddtbs=> explain analyze select * from t1 where id1 = 500; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using index1 on t1 (cost=0.00..9.99 rows=99 width=22) (actual time=0.043..0.070 rows=100 loops=1) Index Cond: (id1 = 500) Total runtime: 0.108 ms (3 rows) ※ index1を使用した。処理時間も大幅に高速化されている。 oddtbs=> explain analyze select * from t1 where id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on t1 (cost=0.00..1887.00 rows=962 width=22) (actual time=0.016..11.005 rows=1000 loops=1) Filter: (id2 = 50) Total runtime: 11.177 ms (3 rows) ※ 条件で使用する列が違うので、当然インデックスは使用できない。 oddtbs=> explain analyze select * from t1 where id1 = 500 and id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Index Scan using index1 on t1 (cost=0.00..10.24 rows=1 width=22) (actual time=0.030..0.040 rows=1 loops=1) Index Cond: (id1 = 500) Filter: (id2 = 50) Total runtime: 0.068 ms (4 rows) ※ 条件がANDで結ばれている場合、片方の列のインデックスを使用できる。 oddtbs=> explain analyze select * from t1 where (id1, id2) = (500, 50); 結果は上と同じ。 ※ 同じことを行コンストラクタを使用して行った。 oddtbs=> explain analyze select * from t1 where id1 = 500 or id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on t1 (cost=0.00..2137.00 rows=1060 width=22) (actual time=0.018..16.190 rows=1099 loops=1) Filter: ((id1 = 500) OR (id2 = 50)) Total runtime: 16.379 ms (3 rows) ※ 条件がORで結ばれている場合、片方の列のインデックスは使用できない。
複数の列によるインデックスを作成する。
CREATE INDEX インデックス名 ON テーブル名(列名の並び);
id1列とid2列に対するインデックスを作成する。
oddtbs=> drop index index1; DROP INDEX oddtbs=> create index index2 on t1(id1, id2); CREATE INDEX
性能を測定。
oddtbs=> explain analyze select * from t1 where id1 = 500; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Index Scan using index2 on t1 (cost=0.00..169.24 rows=99 width=22) (actual time=0.033..0.060 rows=100 loops=1) Index Cond: (id1 = 500) Total runtime: 0.100 ms (3 rows) ※ 複数列のインデックスは、最初の列による条件では使用できる。 oddtbs=> explain analyze select * from t1 where id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on t1 (cost=0.00..1887.00 rows=962 width=22) (actual time=0.016..11.009 rows=1000 loops=1) Filter: (id2 = 50) Total runtime: 11.184 ms (3 rows) ※ 最初でない列による条件では使用できない。 oddtbs=> explain analyze select * from t1 where id1 = 500 and id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Index Scan using index2 on t1 (cost=0.00..8.28 rows=1 width=22) (actual time=0.013..0.014 rows=1 loops=1) Index Cond: ((id1 = 500) AND (id2 = 50)) Total runtime: 0.039 ms (3 rows) ※ 複数列のインデックスは、ANDで結ばれた条件では使用できる。 oddtbs=> explain analyze select * from t1 where id1 = 500 or id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on t1 (cost=0.00..2137.00 rows=1060 width=22) (actual time=0.019..16.650 rows=1099 loops=1) Filter: ((id1 = 500) OR (id2 = 50)) Total runtime: 16.840 ms (3 rows) ※ 基本的にORの条件では使用できない。
条件によっては複数のインデックスを使用できる場合がある。
そのときに実際どのインデックスを使用するかはオプティマイザの判断による。
なお、その判断はPostgreSQLのバージョンやその他の状況によって異なる可能性がある。
index1を再度作成する。
oddtbs=> create index index1 on t1(id1); CREATE INDEX oddtbs=> \di List of relations Schema | Name | Type | Owner | Table --------+--------------+-------+--------+--------- ... public | index1 | index | oduser | t1 public | index2 | index | oduser | t1 ...
性能を測定。
oddtbs=> explain analyze select * from t1 where id1 = 500; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using index1 on t1 (cost=0.00..9.99 rows=99 width=22) (actual time=0.045..0.072 rows=100 loops=1) Index Cond: (id1 = 500) Total runtime: 0.115 ms (3 rows) ※ index1とindex2の両方が使用できる状況だったが、index1が使用された。 oddtbs=> explain analyze select * from t1 where id1 = 500 and id2 = 50; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Index Scan using index2 on t1 (cost=0.00..8.28 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=1) Index Cond: ((id1 = 500) AND (id2 = 50)) Total runtime: 0.026 ms (3 rows) ※ 今度はindex2が使用された。
条件が関数を使用したものだと、単純な列に対するインデックスは使用できない。
その場合は、その関数の結果に対するインデックスを作成することができる。
これを関数インデックスという。
CREATE INDEX インデックス名 ON テーブル名(関数(...));
data列は大文字と小文字が混在しているので、lower関数で小文字に変換した結果で検索する。
data列に関するインデックスを作成する。
oddtbs=> create index index3 on t1(data);
CREATE INDEX
lower関数による条件で測定。
oddtbs=> explain analyze select * from t1 where lower(data) = 'data_0500_050';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..2137.00 rows=500 width=22) (actual time=45.121..90.402 rows=1 loops=1)
Filter: (lower(data) = 'data_0500_050'::text)
Total runtime: 90.428 ms
(3 rows)
※ data列に対するインデックスはlower(data)による条件では使用できない。
関数インデックスを作成。
oddtbs=> create index index4 on t1(lower(data));
CREATE INDEX
上と同じ測定。
oddtbs=> explain analyze select * from t1 where lower(data) = 'data_0500_050';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=12.14..647.74 rows=500 width=22) (actual time=0.061..0.061 rows=1 loops=1)
Recheck Cond: (lower(data) = 'data_0500_050'::text)
-> Bitmap Index Scan on index4 (cost=0.00..12.02 rows=500 width=0) (actual time=0.057..0.057 rows=1 loops=1)
Index Cond: (lower(data) = 'data_0500_050'::text)
Total runtime: 0.088 ms
(5 rows)
※ 表示は少々複雑だがindex4を使用したことが分かる。
演算子を含んだ式に対するインデックスを作成することもできる。これを式インデックスという。
CREATE INDEX インデックス名 ON テーブル名((式));
※ 式を( )で囲む
性能を測定。
oddtbs=> explain analyze select * from t1 where id1 + id2 = 550;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..2137.00 rows=500 width=22) (actual time=6.547..14.748 rows=100 loops=1)
Filter: ((id1 + id2) = 550)
Total runtime: 14.790 ms
(3 rows)
※ 当然インデックスは使用されない。
式インデックスを作成。
oddtbs=> create index index5 on t1((id1 + id2));
CREATE INDEX
性能を測定。
oddtbs=> explain analyze select * from t1 where id1 + id2 = 550;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=12.14..647.73 rows=500 width=22) (actual time=0.053..0.128 rows=100 loops=1)
Recheck Cond: ((id1 + id2) = 550)
-> Bitmap Index Scan on index5 (cost=0.00..12.01 rows=500 width=0) (actual time=0.043..0.043 rows=100 loops=1)
Index Cond: ((id1 + id2) = 550)
Total runtime: 0.175 ms
(5 rows)
※ index5を使用した。