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

问题描述

我的 UsersController#create 操作中发生了顺序扫描.

I have a sequential scan occurring in my 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: ?

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

I am fairly positive this arises from the following model validation:

validates :username, uniqueness: { case_sensitive: false }

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

Should I be creating an index against this express? And if so, what's the proper way to do this in Rails 4?

推荐答案

是的，验证会执行那种查询，而那种查询会执行表扫描.

Yes, that validation would do that sort of query and that sort of query is going to do a table scan.

您实际上在这里遇到了一些问题:

You actually have a couple problems here:

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

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

You can solve both of those problems with one index. The first problem is solved by using a unique index inside the database. The second problem is solved by indexing the result of lower(username) rather than username.

AFAIK Rails 仍然不理解表达式的索引，因此您必须做两件事:

AFAIK Rails still doesn't understand indexes on expressions so you'll have to do two things:

1. 从 schema.rb 切换到 structure.sql 以防止 Rails 忘记您的索引.在您的 config/application.rb 中，您需要设置:

1. Switch from schema.rb to structure.sql to keep Rails from forgetting about your index. In your config/application.rb you'll want to set:

config.active_record.schema_format = :sql

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

You'll also have to start using the db:structure:* rake tasks instead of the db:schema:* tasks. Once you've switched to structure.sql, you can delete db/schema.rb since it won't be updated or used anymore; you'll also want to start tracking db/structure.sql in revision control.

通过在迁移中编写一些 SQL 手动创建索引.这很简单:

Create the index by hand by writing a bit of SQL in a migration. This is easy:

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 无论如何都不会给您任何有用的可移植性.

Of course this will leave you with PostgreSQL-specific things but that's nothing to worry about since ActiveRecord doesn't give you any useful portability anyway.

这篇关于如何在 Rails 中的 LOWER("users"."username") 上创建索引(使用 postgres)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2