类似 'abc%' 搜索的 PostgreSQL 索引

Question

我有一个包含城市列表 (>1M) 的 postgreSQL 表，我需要按“abc%”等模式搜索此表。我在 city.name 列上创建了 B 树索引，这是我得到的:

EXPLAIN SELECT * FROM city WHERE NAME ~~* 'Мос%' 
Seq Scan on city  (cost=0.00..44562.62 rows=117 width=131)

以及确切的选择:

EXPLAIN SELECT * FROM city WHERE NAME = 'Москва' 
Index Scan using city_name_idx on city  (cost=0.43..12.33 rows=2 width=131)

有什么方法可以使用标准索引在首次选择时获得良好的性能？

我正在使用 Symfony2/Doctrine2，所以在这里实现特定于数据库的东西不是很容易(而且我不想)。

Answer 1

• 要加快 LIKE(区分大小写)的速度，请创建如下索引:

  create index indexname on city (name text_pattern_ops);
  

• 为了加速 ILIKE 或 ~~* ，除了 LIKE 之外，假设 PostgreSQL 9.1 或更新版本，创建一个索引像这样:

  create index indexname on city  using gin(name gin_trgm_ops);
  

  gin_trgm_ops 由 pg_trgm 提供如果数据库中不存在，则应添加扩展名。