PostgreSQLのJSON型のカラムにインデックスを作成した

経緯

担当プロダクトでPostgreSqlを使用しているのですが、JSONBのカラムに検索をかけている箇所があり、全文検索となっていました。

これまでの開発経験で配列形式のカラムを使用したことがなかったため、インデックスなんて貼れないと思っていました。

DB構成

バージョン：9.4.15
AWS　RDS　インスタンスタイプ：db.m4.large

テーブル構成

JSONB型カラムが設定されているテーブル構成は次のとおりです。

mpv=> \d image_files
                                          Table "image_files"
   Column    |            Type             | Collation | Nullable |                Default
-------------+-----------------------------+-----------+----------+----------------------------------------
 id          | integer                     |           | not null | nextval('image_files_id_seq'::regclass)
 file_type   | integer                     |           |          |
 chksum      | character varying(128)      |           |          |
 savedpath   | character varying(200)      |           |          |
 created_at  | timestamp without time zone |           | not null |
 updated_at  | timestamp without time zone |           | not null |
 tiff_chksum | character varying(50)       |           |          |
 dhash       | character varying(20)       |           |          |
 meta        | jsonb                       |           |          |
 kind        | integer                     |           | not null | 1
Indexes:
    "image_files_pkey" PRIMARY KEY, btree (id)
    "index_image_files_on_chksum" btree (chksum)
    "index_image_files_on_dhash" btree (dhash)
    "index_image_files_on_file_type" btree (file_type)
    "index_image_files_on_kind" btree (kind)
    "index_image_files_on_tiff_chksum" btree (tiff_chksum)

metaというカラムがjsonbカラムに該当し、
静止画像や動画キャプチャのハッシュ値を複数アルゴリズムで解析したハッシュ値、サイズなどを格納しています。
動画と静止画像の違いで格納するデータが変わるためJSON型を採用しています。

CREATE INDEX

テーブルはrailsのActiveRecordで管理しているので、マイグレーションを作成しました。

インデックスタイプはGINを使用します。

XXXX_add_indexes_to_image_file.rb

class AddIndexesToImageFiles < ActiveRecord::Migration
  def up
    add_index :image_files, :meta, using: :gin, :name => 'index_image_files_on_meta_using_gin'
  end
  def down
    remove_index :image_files, :name => 'index_image_files_on_meta_using_gin'
  end
end

再度テーブル構成を出してみて、metaカラムにGINインデックスが作成されていることが確認できました。

SQLの修正

SQLはlambdaで定義したプログラムから発行されているのですが、
詳しく調べていないのですが、以下のSQLではインデックスが使用されませんでした。

SELECT
	id
FROM
	image_files
WHERE
	kind = 1
AND
	(meta->'avghash'->>'1p' = 'aaaa' OR meta->'phash'->>'1p' = 'bbbb OR meta->'dhash'->>'1p' = 'cccc')
	

＠>を使用する形式に書き換えました。

SELECT
	id
FROM
	image_files
WHERE
    kind = 1
    AND 
      (
        meta @> '{"avghash": {"1p": "aaaa"}}'::jsonb
      OR
        meta @> '{"avghash": {"1p": "aaaa"}}'::jsonb
      OR
        meta @> '{"avghash": {"1p": "aaaa"}}'::jsonb
      )

インデックスを使用しているか

explainしてみます。

  ->  Bitmap Index Scan on index_image_files_on_meta_using_gin  (cost=0.00..813.62 rows=749 width=0)
        Index Cond: (meta @> '{"avghash": {"1p": "aaaa"}}'::jsonb)

問題なくIndexScanが行われていました。

最後に

今プロダクトでは使用してないので情報を追ってなかったのですが、MySQLも5.7からJSON Data Typeに対応したみたいですね。

インデックスも貼れるみたいです。
機会があれば試してみたいと思います。