如何在 Rails 中的 LOWER("users"."username") 上创建索引（使用 postgres）

我的系统中发生了顺序扫描UsersController#create action.

SELECT ? AS one FROM "users" WHERE (LOWER("users"."username") = LOWER(?) AND "users"."id" != ?) LIMIT ?

Explain plan
1 Query planLimit (cost=0.00..3.35 rows=1 width=0)
2 Query plan-> Seq Scan on users (cost=0.00..3.35 rows=1 width=0)
3 Query planFilter: ?

我相当肯定这是由以下模型验证引起的：

validates :username, uniqueness: { case_sensitive: false }

我应该针对这个快递创建索引吗？如果是这样，在 Rails 4 中执行此操作的正确方法是什么？

是的，该验证将执行此类查询，并且此类查询将执行表扫描。

您实际上这里有几个问题：

• 验证受到竞争条件的影响，因为逻辑不在其所属的数据库中。无论通常的 Rails 思想如何，数据库都应该负责所有数据完整性问题。
• 您的验证会触发表扫描，但没有人喜欢表扫描。

您可以使用一个索引解决这两个问题。第一个问题是通过使用数据库内的唯一索引来解决的。第二个问题通过对结果建立索引来解决lower(username)而不是username.

AFAIK Rails 仍然不理解表达式上的索引，所以你必须做两件事：

1. 切换自schema.rb to structure.sql防止 Rails 忘记你的索引。在你的config/application.rb你需要设置：

   config.active_record.schema_format = :sql
   

   您还必须开始使用db:structure:*耙任务而不是db:schema:*任务。一旦你切换到structure.sql，你可以删除db/schema.rb因为它不会再更新或使用；您还需要开始跟踪db/structure.sql在修订控制中。

2. 通过在迁移中编写一些 SQL 来手动创建索引。这很容易：

   def up
     connection.execute(%q{
       create index idx_users_lower_username on users(lower(username))
     })
   end
   
   def down
     connection.execute(%q{
       drop index idx_users_lower_username
     })
   end
   

当然，这会给您留下 PostgreSQL 特定的东西，但这没什么好担心的，因为 ActiveRecord 无论如何也不给您任何有用的可移植性。