Postgres中的Like慢查询

Question

我在表中有2000万条记录，其模式如下

FieldName   Datatype
id          bigint(Auto Inc,Primarykey)
name        varchar(255)
phone       varchar(255)
deleted_at  timestamp
created_at  timestamp
updated_at  timestamp

它有名字和电话列的索引

Column  Index type
name    GIN trgm index
phone   btree index, GIN trgm index

使用以下命令创建了索引

CREATE INDEX btree_idx ON contacts USING btree (phone);
CREATE INDEX trgm_idx ON contacts USING GIN (phone gin_trgm_ops);
CREATE INDEX trgm_idx_name ON contacts USING GIN (name gin_trgm_ops);

我正在运行以下查询

select * from contacts where phone like '%6666666%' limit 15;

我在电话上做包含查询。上面的查询需要5分钟以上的时间才能得到结果。让我提供这方面的解释语句。

explain  analyse select * from contacts where phone like '%6666666%' limit 15;

Limit  (cost=1774.88..1830.57 rows=15 width=65) (actual time=7970.553..203001.985 rows=15 loops=1)
  ->  Bitmap Heap Scan on contacts  (cost=1774.88..10819.13 rows=2436 width=65) (actual time=7970.552..203001.967 rows=15 loops=1)
        Recheck Cond: ((phone)::text ~~ '%6666666%'::text)
        Rows Removed by Index Recheck: 254869
        Heap Blocks: lossy=2819
        ->  Bitmap Index Scan on trgm_idx  (cost=0.00..1774.27 rows=2436 width=0) (actual time=6720.978..6720.978 rows=306226 loops=1)
              Index Cond: ((phone)::text ~~ '%6666666%'::text)
Planning Time: 0.139 ms
Execution Time: 203002.791 ms

下面我可以做些什么来优化我的查询呢？并将结果控制在5秒以内将是最优的

Answer 1

性能不佳的原因之一可能是

Heap Blocks: lossy=2819

您的work_mem设置太小，包含一个位图，每个表行一位，因此PostgreSQL将其降级到每8kB块一位。这导致了许多不必要的重新检查。

另外，你的测试也很糟糕。搜索字符串只包含三元组666，它将匹配许多不满足查询的行，并且在重新检查期间必须删除它们。三元组索引在这种病理情况下是无效的。使用包含更多数字的数字进行测试。