在f_unaccent()上使用GIN索引的查询看起来很慢吗？

Question

在一个包含产品的表中，我有近2000万行，包括它们的名称。

我希望以名称快速搜索全文，所以我创建了这个索引：

CREATE INDEX uprice_item_occurrence_unaccent_name_trgm_idx ON price_item_occurrence USING gin (f_unaccent(name) gin_trgm_ops);;

我跳过下面的查询将花费更少的时间(比方说)500 was

select * from price_item_occurrence as oo
where f_unaccent(oo.name) % f_unaccent('iphone');

但这需要近2秒：

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone');
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=1956.42..63674.14 rows=16570 width=287) (actual time=247.918..1880.759 rows=94 loops=1)
   Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
   Rows Removed by Index Recheck: 87838
   Heap Blocks: exact=76663
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..1952.28 rows=16570 width=0) (actual time=195.418..195.418 rows=88962 loops=1)
         Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.444 ms
 Execution time: 1880.833 ms

数据库可能很忙，但我不确定。

我试着使用select set_limit(0.9); (增加了)，它有一点帮助，但没有多大帮助。

我正在使用Postgres 10，可以改变Postgres配置，我是开放的建议。

我在ilike上试用了它，并对其进行了改进：

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) ilike ('%' || f_unaccent('iphone') || '%');
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=3135.08..416823.45 rows=166075 width=286) (actual time=50.258..670.085 rows=65917 loops=1)
   Recheck Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
   Rows Removed by Index Recheck: 10
   Heap Blocks: exact=59750
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..3093.56 rows=166075 width=0) (actual time=37.385..37.385 rows=67700 loops=1)
         Index Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
 Planning time: 0.545 ms
 Execution time: 675.776 ms
(8 rows)

大约快2倍。

我试过limit 10：

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone') limit 10;
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=373.27..410.51 rows=10 width=287) (actual time=268.718..589.131 rows=10 loops=1)
   ->  Bitmap Heap Scan on price_item_occurrence oo  (cost=373.27..62493.45 rows=16680 width=287) (actual time=268.715..589.123 rows=10 loops=1)
         Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
         Rows Removed by Index Recheck: 18917
         Heap Blocks: exact=17100
         ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..369.10 rows=16680 width=0) (actual time=165.958..165.958 rows=69268 loops=1)
               Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.397 ms
 Execution time: 589.187 ms
(9 rows)

这也更快，也许已经足够好了

Answer 1

对于这些查询，我看到GIN索引的执行速度通常比GiST快得多。尝试使用此索引：

CREATE INDEX price_item_occurrence_name_trgm_gin idx ON price_item_occurrence
USING GIN (f_unaccent(name) gin_trgm_ops);

相关信息：

• PostgreSQL是否支持“重音不敏感”排序规则？
• 使用带非重音的ILIKE和只使用右通配符的ILIKE
• 如何实现？
• 在PostgreSQL中使用类似、类似或正则表达式的模式匹配
• 不按预期工作的ILIKE模式的Trigram索引

所有关于性能优化的基本建议都适用。首先，您的表需要VACUUM编辑，ANALYZEd足够快。

• 配置PostgreSQL以提高读取性能