postgresql - postgres中大型数据库的索引

Question

我在 postgres 中有一个表有大约 200 万条记录。我需要为其提供一些索引，以便它为 like %text% 查询提供良好的性能。

我在某处读到 Gin 索引适合 %text% 搜索，所以尝试了 Gin 和 Gist 索引，但不知道为什么没有这样的性能提升，Gin 索引使用顺序扫描而不是堆扫描。

这是我的 Gin 索引:

CREATE INDEX city_gin_idx_name
  ON city
  USING gin
  (to_tsvector('english'::regconfig, lower(name::text)));

查询性能:

"Sort (cost=117553.00..118496.71 rows=377482 width=50) (actual time=1719.660..1745.702 rows=35185 loops=1)" " Sort Key: (concat(name, ', ', state_name, ', ', country_name))" " Sort Method: external merge Disk: 2200kB" " -> Seq Scan on city (cost=0.00..56777.75 rows=377482 width=50) (actual time=0.392..1474.559 rows=35185 loops=1)" " Filter: ((lower((name)::text) ~~ '%ed%'::text) OR ((city_metaphone)::text = 'K'::text))" " Rows Removed by Filter: 1851806" "Total runtime: 1764.036 ms"

请告诉我任何适合此要求的索引。

Answer 1

该查询需要两个索引，并且需要在查询中使用完全相同的表达式才能使用它们:

create index … on city using GIN (to_tsvector('english', name));
create index … on city (city_metaphone);

请注意，在第一个索引中将名称小写是无用的，因为 to_tsvector 在计算向量时无论如何都会忽略大小写。

查询需要如下所示，您应该得到一个使用位图索引扫描的计划:

select *
from city
where city_metaphone = 'K'
   or to_tsvector('english', name) @@ to_tsquery('english', 'Katmandu');

话虽如此，我认为您在此处使用全文是错误的。您的 '%ed%' 尤其表明您希望全文能让您运行某种LIKE 比较。

这不是开箱即用的方式，但 trigrams 会让它以这种方式工作:

http://www.postgresql.org/docs/current/static/pgtrgm.html