PostgreSQL LIKE通配符查询未使用Trigram索引问题咨询

我遇到PostgreSQL查询的异常行为：执行LIKE 'NIKE'时会使用索引，而执行LIKE '%NIKE%'时却未使用已创建的Trigram索引。以下是两种情况的执行计划：

1. 使用索引的查询计划 #

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "companies" WHERE (name LIKE 'NIKE');
                                                                QUERY PLAN                                                                 
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_companies_on_name_hash on companies  (cost=0.00..8.02 rows=1 width=483) (actual time=2.598..2.599 rows=1 loops=1)
   Index Cond: ((name)::text = 'NIKE'::text)
   Filter: ((name)::text ~~ 'NIKE'::text)
   Buffers: shared read=3
   I/O Timings: read=1.543
 Planning:
   Buffers: shared hit=20
 Planning Time: 0.222 ms
 Execution Time: 2.636 ms
(9 rows)

2. 未使用索引的查询计划 #

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "companies" WHERE (name LIKE '%NIKE%');
                                                           QUERY PLAN                                                            
--------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..161713.42 rows=248 width=483) (actual time=24.409..6687.109 rows=253 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=1580 read=146187
   I/O Timings: read=18801.002
   ->  Parallel Seq Scan on companies  (cost=0.00..160688.62 rows=103 width=483) (actual time=80.824..6626.085 rows=84 loops=3)
         Filter: ((name)::text ~~ '%NIKE%'::text)
         Rows Removed by Filter: 826900
         Buffers: shared hit=1580 read=146187
         I/O Timings: read=18801.002
 Planning:
   Buffers: shared hit=47
 Planning Time: 3.536 ms
 Execution Time: 6687.635 ms
(14 rows)

表结构定义（schema.rb） #

create_table "companies", force: :cascade do |t|
  ...
  t.index ["name"], name: "index_companies_on_name", opclass: :gin_trgm_ops, using: :gin
  t.index ["name"], name: "index_companies_on_name_hash", using: :hash     # For other use cases
end

我原本认为Trigram索引可以处理通配符查询，请问：

1. 为何会出现这种现象？
2. 如何创建能支持LIKE '%NIKE%'这类通配符查询的索引？

补充信息 #

数据库成本参数设置 #

postgres=> SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%\_cost';
          name           | setting | unit 
-------------------------+---------+------
 cpu_index_tuple_cost    | 0.005   | 
 cpu_operator_cost       | 0.0025  | 
 cpu_tuple_cost          | 0.01    | 
 jit_above_cost          | 100000  | 
 jit_inline_above_cost   | 500000  | 
 jit_optimize_above_cost | 500000  | 
 parallel_setup_cost     | 1000    | 
 parallel_tuple_cost     | 0.1     | 
 random_page_cost        | 4       | 
 seq_page_cost           | 1       | 
(10 rows)

执行SET enable_seqscan = off;后的查询计划 #

postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM "companies" WHERE (name LIKE '%NIKE%');
                                                           QUERY PLAN                                                             
---------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on companies  (cost=10000000000.00..10000178746.81 rows=248 width=454) (actual time=46.214..3637.287 rows=253 loops=1)
   Filter: ((name)::text ~~ '%NIKE%'::text)
   Rows Removed by Filter: 2480699
   Buffers: shared hit=3295 read=144476
   I/O Timings: read=3013.466
 Planning Time: 0.119 ms
 Execution Time: 3637.461 ms
(7 rows)

问题解答 #

1. 为何Trigram索引未被使用？ #

你确实定义了GIN类型的Trigram索引，但数据库未选用它，核心原因可能有以下几点：

• pg_trgm扩展未安装：Trigram索引完全依赖pg_trgm扩展，若未安装，即使索引存在也无法被优化器识别。执行SELECT * FROM pg_extension WHERE extname='pg_trgm';可确认是否安装。
• 统计信息过时：PostgreSQL优化器依赖表的统计信息判断成本。如果统计信息未更新，优化器可能错误认为全表扫描比索引扫描更划算。执行ANALYZE companies;可更新统计信息。
• 索引未正确创建：schema.rb中的定义可能未同步到实际数据库，或创建索引时出错。执行SELECT * FROM pg_indexes WHERE tablename='companies';检查索引是否存在，且操作符类为gin_trgm_ops。
• 成本参数不合理：你的random_page_cost=4是默认值，若数据库运行在SSD上，该值过高会让优化器倾向于全表扫描。可尝试将其降低到1.1左右，提升索引扫描的优先级。

2. 如何创建支持%NIKE%查询的索引？ #

首先确保pg_trgm扩展已安装：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

然后根据业务场景选择合适的索引类型：

方式1：GIN索引（适合高并发查询场景）

CREATE INDEX index_companies_on_name_trgm ON companies USING GIN (name gin_trgm_ops);

GIN索引查询速度更快，但写入时开销较高。

方式2：GIST索引（适合写入频繁场景）

CREATE INDEX index_companies_on_name_trgm ON companies USING GIST (name gist_trgm_ops);

GIST索引体积更小，写入开销低，但查询速度略慢于GIN。

验证索引有效性

创建索引后执行ANALYZE companies;更新统计信息，再重新运行查询的EXPLAIN命令。若执行计划中出现Index Scan using index_companies_on_name_trgm on companies，说明索引已生效。

内容的提问来源于stack exchange，提问作者sscirrus