更快地搜索字段的第一个字符不匹配[A-Za-z]的记录？ [英] Faster search for records where 1st character of field doesn't match [A-Za-z]?

问题描述

我当前具有以下内容：

User (id, fname, lname, deleted_at, guest)

我可以按用户的 fname 首字母查询用户列表因此：

I can query for a list of user's by their fname initial like so:

User Load (9.6ms)  SELECT "users".* FROM "users" WHERE (users.deleted_at IS NULL) AND (lower(left(fname, 1)) = 's') ORDER BY fname ASC LIMIT 25 OFFSET 0

这要归功于以下索引：

  CREATE INDEX users_multi_idx
  ON users (lower(left(fname, 1)), fname)
  WHERE deleted_at IS NULL;

我现在想做的是能够查询所有以字母开头的用户AZ。我使它像这样工作：

What I want to do now is be able to query for all Users that do not start with the letter's A-Z. I got this to work like so:

SELECT "users".* FROM "users" WHERE (users.deleted_at IS NULL) AND (lower(left(fname, 1)) ~ E'^[^a-zA-Z].*') ORDER BY fname ASC LIMIT 25 OFFSET 0

但是问题是此查询非常慢，并且似乎没有使用索引来加快第一个查询的速度。关于如何优雅地使第二个查询（非az）更快的任何建议？

But the problem is this query is very slow and does not appear to be using the index to speed up the first query. Any suggestions on how I can elegantly make the 2nd query (non a-z) faster?

我正在将Postgres 9.1与rails 3.2一起使用

I'm using Postgres 9.1 with rails 3.2

谢谢

推荐答案

^{更新后的答案}

此处的问题。

我的第一个想法（索引号为 text_pattern_ops ）不适用于我的测试中的正则表达式。最好将查询重写为：

My first idea idea (index with text_pattern_ops) did not work with the regular expression in my tests. Better rewrite your query to:

SELECT *
FROM   users
WHERE  deleted_at IS NULL
WHERE lower(left(fname, 1)) < 'a' COLLATE "C"
OR    lower(left(fname, 1)) > 'z' COLLATE "C"
ORDER  BY fname
LIMIT  25 OFFSET 0;

除了这些表达式通常更快之外，您的正则表达式中也包含大写字母，该字母与 lower（）的索引不匹配。与单个字符相比，结尾字符毫无意义。

Besides from these expressions being faster generally, your regular expression also had capital letters in it, which did not match the index with lower(). And the trailing characters were pointless while comparing to a single char.

并使用以下索引：

CREATE INDEX users_multi_idx
ON users (lower(left(fname, 1)) COLLATE "C", fname)
WHERE deleted_at IS NULL;

COLLATE C 部分是可选的，只会对性能产生很小的影响。目的是将排序规则重置为默认的posix排序规则，该排序规则仅使用字节顺序，通常更快。很有用，如果排序规则始终不相关。

The COLLATE "C" part is optional and only contributes a very minor gain in performance. It's purpose is to reset collation rules to default posix collation, which just uses byte order and is generally faster. Useful, where collation rules are not relevant anyway.

如果用它创建索引，则只有与排序规则匹配的查询才能使用它。因此，如果性能不是您的首要要求，您可以跳过它以简化操作。

If you create the index with it, only queries that match the collation can use it. So you might just skip it to simplify things if performance is not your paramount requirement.

这篇关于更快地搜索字段的第一个字符不匹配[A-Za-z]的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！