PostgreSQL不使用带取消重音函数的索引

Question

我有下表：

CREATE TABLE products (
  id   bigserial NOT NULL PRIMARY KEY,
  name varchar(2048)
  -- Many other rows
);

我想在LIKE上做一个案例和不敏感的name查询。

为此，我创建了以下函数：

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE OR REPLACE FUNCTION immutable_unaccent(varchar)
  RETURNS text AS $$
    SELECT unaccent($1)
  $$ LANGUAGE sql IMMUTABLE;

然后使用以下函数在name上创建一个索引：

CREATE INDEX products_search_name_key ON products(immutable_unaccent(name));

但是，当我进行查询时，查询非常慢(300 k行大约2.5s )。我很确定PostgreSQL没有使用索引

-- Slow (~2.5s for 300k rows)
SELECT products.* FROM products
    WHERE immutable_unaccent(products.name) LIKE immutable_unaccent('%Hello world%')

-- Fast (~60ms for 300k rows), and there is no index
SELECT products.* FROM products
    WHERE products.name LIKE '%Hello world%'

我尝试创建一个单独的列，其中包含一个大小写和名称的不敏感副本，在这种情况下，查询是快速的：

ALTER TABLE products ADD search_name varchar(2048);
UPDATE products
    SET search_name = immutable_unaccent(name);

-- Fast (~60ms for 300k rows), and there is no index
SELECT products.* FROM products
    WHERE products.search_name LIKE immutable_unaccent('%Hello world%')

我做错什么了？为什么我的索引方法不能工作?

编辑:慢速查询的执行计划

explain analyze SELECT products.* FROM products
    WHERE immutable_unaccent(products.name) LIKE immutable_unaccent('%Hello world%')

Seq Scan on products  (cost=0.00..79568.32 rows=28 width=2020) (actual time=1896.131..1896.131 rows=0 loops=1)
  Filter: (immutable_unaccent(name) ~~ '%Hello world%'::text)
  Rows Removed by Filter: 277986
Planning time: 1.014 ms
Execution time: 1896.220 ms

Answer 1

如果您想要执行类似的'%hello world%‘类型查询，则必须找到另一种方法对其进行索引。

(您可能需要对一些控制模块进行初步安装。为此，以postgres admin/root用户身份登录并发出以下命令)

先决条件：

CREATE EXTENSION pg_trgm;
CREATE EXTENSION fuzzystrmatch;

尝试以下几点：

create index on products using gist (immutable_unaccent(name) gist_trgm_ops);

在这一点上，应该在查询中使用索引。

select * from product 
where immutable_unaccent(name) like '%Hello world%';

注意:这个索引可能会变大，但有240个字符的限制，可能不会有那么大。

您也可以使用全文搜索，但这要复杂得多。

上面的场景所做的是索引“trigram”的名称，IE，每套“3个字母”内的名称。所以这个产品叫做“你好世界”，它会索引hel，ell，llo，lo，wo，wor，orl和rld。然后，它可以使用该索引对您的搜索词，以更有效的方式。如果愿意，可以使用gist或gin索引类型。

基本上，GIST的查询速度稍慢，但更新速度更快。杜松子酒是opposite>