postgresql - 优化 PostgreSQL 中串联名字和姓氏的搜索

Question

我在 Postgres 中编写了一个 SQL 查询，它通过名字和姓氏搜索用户。我的问题很简单，它是否可以优化，因为它会被大量使用。

CREATE INDEX users_firstname_special_idx ON users(firstname text_pattern_ops);
CREATE INDEX users_lastname_special_idx ON users(lastname text_pattern_ops);

SELECT id, firstname, lastname FROM users WHERE firstname || ' ' || lastname ILIKE ('%' || 'sen' || '%') LIMIT 25;

如果我运行解释，我会得到以下输出:

Limit  (cost=0.00..1.05 rows=1 width=68)
  ->  Seq Scan on users  (cost=0.00..1.05 rows=1 width=68)
        Filter: (((firstname || ' '::text) || lastname) ~~* '%sen%'::text)

据我所知，我应该尝试让 postgrep 跳过“过滤器:”-thing。对吗？

希望大家多多指教。

干杯。

Answer 1

如果字符串中有超过 1 个 % 通配符，则需要使用 trigram index .

但是，在您的情况下，您正在做一些奇怪的事情。您连接 firstname 和 lastname，中间有一个空格。因此，搜索字符串“%sen%”仅出现在firstname 或lastname 中，从不包含空格。因此，更好的解决方案是:

CREATE INDEX users_firstname_special_idx ON users USING gist (firstname gist_trgm_ops);
CREATE INDEX users_lastname_special_idx ON users USING gist (lastname gist_trgm_ops);

SELECT id, firstname || ' ' || lastname AS fullname
FROM users
WHERE firstname ILIKE ('%sen%') OR lastname ILIKE ('%sen%')
LIMIT 25;