Nic Lin's Blog


[Rails] 如何在 Postgres 上使用表達式索引

為了避免有許多用戶生成同樣的名稱(不區分大小寫的情況),我們在 User create 前去檢查

User.where("lower(user_name) = ?", user_name.downcase)

當 User 的資料集逐漸龐大時,為了加快這個查詢,我們可以添加一個 user_name 的 index 去加速。

但因為在 where 中使用了 lower(user_name), 所以 user_name 的 index 並不會觸發,相當時白打了。

在這種情況下,我們可以用 PostgreSQL 提供的表達式索引(Expression index)來建立

在 Rails 5 以前,如果我們要使用表達式索引,相當麻煩

def up
  execute <<-SQL
    CREATE INDEX user_lower_name_idx ON users (lower(user_name));

def down
  execute <<-SQL
    DROP INDEX user_lower_name_idx;

在 Rails 5 後,對表達式索引有完整的支援,我們可以直接如下撰寫:

def change
  add_index :users,
            name: "index_users_on_user_name_unique",
            unique: true

這樣一來,User.where("lower(user_name) = ?", user_name.downcase) 就可以吃到這組 index_users_on_user_name_unique index。

這裡要注意的是,如果使用了 LIKE %keyword%,這組 index 依然無效

User.where("lower(user_name) like ?", "%#{user_name.downcase}%")

注意:LIKE %keyword% 永遠不會選擇索引掃描,但 LIKE keyword% 有可能選擇

不過 PostgreSQL 上還是有解法的,參考原文:

'The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale.'

我們可以使用 varchar_pattern_ops 來解決這個問題, Rails 5 也同樣支援了 operator classes on expression index

def change
  remove_index :users, name: :index_users_on_user_name_unique
  add_index :users,  'lower(user_name) varchar_pattern_ops',
                        name: "index_users_on_username_unique",
                        unique: true


comments powered by Disqus