RailsでPostgreSQL使用時にインデックスを利用したcase-insensitiveな検索を行う

概要

ActiveRecordを利用し、PostgreSQLで大文字小文字を無視した検索を行うという記事で、case-insensitiveな検索方法をいくつかまとめましたが、インデックスの利用を行っていませんでした。

インデックスを利用しないと激遅のろのろ丸で、涙がちょちょぎれそうな実行速度になってしまいます。実際泣きそうなくらい実行速度が遅くなりました。

そこで、Rails で PostgreSQL を使用している際、どのようにすれば大文字小文字を無視した case-insensitive な検索をインデックスを利用して行えるかについてまとめてみました。

手順としては、

1. インデックス追加用 Migration ファイルの作成
2. ActiveRecord での実装

となります。

テーブル構造

以下のようなテーブルを用いて説明します。

Migration ファイル

create_users.rb

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :name, null: false
      t.string :email, null: false

      t.timestamps null: false
    end
  end
end

テーブル構造

users

   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 name       | character varying           | not null
 email      | character varying           | not null
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

インデックス追加用 Migration の作成

まずはインデックス追加用の Migration ファイルを生成します。

$ rails g migration add_lower_name_index_to_users

次に Migration ファイルですが、PostgreSQL 独自の機能である「式に対するインデックス」を貼る必要があるため、Migrationのメソッドを使って書くのを諦め、 SQL をベタ書きします。

add_lower_name_index_to_users.rb

class AddLowerNameIndexToUsers < ActiveRecord::Migration
  def change
    sql = 'CREATE INDEX users_lower_name_idx ON users (lower(name));'
    ActiveRecord::Base.connection.execute(sql)
  end
end

これでrake db:migrateを行えば、lower(name)に対するインデックスが以下のように貼られます。

users_indexes

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_lower_name_idx" btree (lower(name::text))

ActiveRecord での実装

基本的には、lowerを使って以下の通りに書けば良いです。

User.where('lower(name) = ?', 'hoge')

ただし、注意点が必要で、User.where('lower(name) = ?', 'hoge').firstなどのように書いた時、自動で id によるORDER BYが入り、インデックスがうまく使われない場合があります。

そのため、発行されるクエリの実行計画をexplainで確認しておくことをオススメします。

# explain SELECT users.* FROM users WHERE (lower(name) = 'hoge') ORDER BY users.id ASC LIMIT 1;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=8.31..8.32 rows=1 width=51)
   ->  Sort  (cost=8.31..8.32 rows=1 width=51)
         Sort Key: id
         ->  Index Scan using users_lower_name_idx on users  (cost=0.29..8.30 rows=1 width=51)
               Index Cond: (lower((name)::text) = 'hoge'::text)
(5 rows)

今回は問題なさそうです。レコード数とかの影響なんでしょうか。

実際、業務で使用している大規模テーブルで上記のようなfirstを使った実装を行った際、インデックスが使われず、激遅となってしまう現象に遭遇しました。

とりあえずその際は、以下のように一旦配列にすることで対処しました。

User.where('lower(name) = ?', 'hoge').to_a.first

ちなみに、発行されるクエリの実行計画はこうなります。

# explain SELECT users.* FROM users WHERE (lower(name) = 'hoge');
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Scan using users_lower_name_idx on users  (cost=0.29..8.30 rows=1 width=51)
   Index Cond: (lower((name)::text) = 'hoge'::text)
(2 rows)

せっかく貼ったインデックスが使われてなかったら悲しいですよね

まとめ

1. PostgreSQL で case-insensitive な検索をするなら lower がオススメ
2. lower(hoge) に対してインデックスを貼ると快適検索ライフが送れる
3. ActiveRecord で油断して実装をするとインデックスが使われず悲惨