更快的搜索记录，其中场的第一个字符不匹配[A-ZA-Z]？(Faster search for re

目前，我有以下几点：

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

我可以查询用户通过他们的名单fname初始像这样：

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开头的所有用户。我得到这个像这样的工作：

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）快有什么建议？

我使用的是Postgres 9.1与3.2的轨道

谢谢

Answer 1:

^更新答案
在此之前的问题。

我的第一个思想观念（与指数text_pattern_ops ）没有在我的测试正则表达式工作。更好地重写查询到：

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() 而尾随字符，而与单个字符是毫无意义的。

并使用此指数：

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

该COLLATE "C"部分是可选的，不仅有助于在性能上非常小的增益。它的目的是为了重新整理规则为默认POSIX整理，它只是使用字节顺序，一般要快。非常有用，其中整理规则都不相干。

如果你使用它创建索引时，只有符合排序规则查询可以使用它。所以，你可能只是跳过它简化事情，如果性能是不是你最重要的要求。

Answer 2:

作为替代@ ErwinBrandstetter的一般的解决方案，PostgreSQL支持部分索引。你可以说：

CREATE INDEX users_nonalphanumeric_not_deleted_key
ON users (id)
WHERE (users.deleted_at IS NULL) AND (lower(left(fname, 1)) ~ E'^[^a-zA-Z].*');

该指数不会帮助任何其他查找，但它会预先计算出这个特定的查询答案。这种技术往往是从一个更大的表中返回一个小的，预定义子查询是有用的，因为最终得到的指数将忽略绝大多数的表，并且只包含感兴趣的行。

文章来源: Faster search for records where 1st character of field doesn't match [A-Za-z]?