対象製品／バージョン

PostgreSQL 8.2 以上、PowerGres 5.0 以上

プラットフォーム

限定なし

質問

配列の要素の検索にインデックスを使うことはできますか？

回答

GINインデックスにより、配列要素に対してインデックスを利用できます。

例として、以下のようなサンプルデータを作ります。10000件のうち、10件だけがtagsカラムに要素'CC'を持ちます。

 CREATE TABLE gintest (id serial, tags text[]);
 INSERT INTO gintest (tags)
   SELECT ARRAY['AA', 'BB'] FROM generate_series(1, 10000);
 UPDATE gintest SET tags = tags || ARRAY['CC'] WHERE id % 1000 = 1;

デフォルトのBtreeインデックスでは「配列カラムtagsに'CC'という要素は含まれるか」という以下の問い合わせにはインデックスは適用されません。

 => CREATE INDEX idx_tags ON gintest (tags);
 CREATE INDEX
 => EXPLAIN ANALYZE SELECT * FROM gintest WHERE tags && ARRAY['CC'];
                                                QUERY PLAN
 --------------------------------------------------------------------------------------------------------
  Seq Scan on gintest  (cost=0.00..214.00 rows=50 width=41) (actual time=14.223..14.246 rows=10 loops=1)
    Filter: (tags && '{CC}'::text[])
  Total runtime: 14.299 ms
 (3 行)

しかし、GINインデックスを作ると、実行プランにインデックスが使われ、より高速な検索を行うことができます。配列同士に対する演算子「&&(共通要素を持つ)」「<@ (包含される)」「@> (包含する)」において適用されます。

 => CREATE INDEX gin_tags ON gintest USING gin (tags);
 CREATE INDEX
 => EXPLAIN ANALYZE SELECT * FROM gintest WHERE tags && ARRAY['CC'];
                                                    QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on gintest  (cost=4.64..84.82 rows=50 width=41) (actual time=0.042..0.054 rows=10 loops=1)
    Recheck Cond: (tags && '{CC}'::text[])
    ->  Bitmap Index Scan on gin_tags  (cost=0.00..4.63 rows=50 width=0) (actual time=0.029..0.029 rows=10 loops=1)
          Index Cond: (tags && '{CC}'::text[])
  Total runtime: 0.125 ms
 (5 行)