如何在Rails中创建LOWER（" users"。" username"）的索引（使用postgres） [英] How to create index on LOWER("users"."username") in Rails (using 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:

验证：用户名，唯一性：{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.

你实际上有几个问题：

• 验证受竞争条件的限制，因为逻辑不在其所属的数据库中。无论通常的Rails意识形态如何，数据库都应该负责所有数据完整性问题。


• 您的验证会触发表扫描，没有人喜欢表扫描。

您可以使用一个索引解决这两个问题。第一个问题是通过在数据库中使用唯一索引来解决的。第二个问题是通过索引 lower（用户名）的结果而不是用户名来解决的。

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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！