PostgreSQL 不使用带有 unaccent 函数的索引

Question

我有下表:

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

我想对 name 进行不区分大小写和变音符号的 LIKE 查询。

为此我创建了以下函数:

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));

但是，当我进行查询时，查询速度非常慢(30 万行大约需要 2.5 秒)。我很确定 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%' 类型的查询，你必须找到另一种方法来索引它。

(您可能需要对几个 contrib 模块进行一些初始安装。为此，请以 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 个字符的限制，可能不会变大。

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

上面的场景所做的是索引名称的“三字母”，IE，名称中的每组“3 个字母”。因此，如果产品被称为“hello world”，它将索引 hel、ell、llo、lo、wo、wor、orl 和 rld。然后它可以以更有效的方式将该索引用于您的搜索词。如果愿意，您可以使用 gist 或 gin 索引类型。

基本上GIST 的查询速度会稍慢，但更新速度会更快。GIN则相反>