Postgres：为什么添加索引会减慢正则表达式查询的速度？

debugcn 发表于 Dev

用户124114

我keyvalues在 Postgres 中有一个 TEXT列：

select * from test5 limit 5;

 id |                      keyvalues
----+------------------------------------------------------
  1 | ^ first 1 | second 3
  2 | ^ first 1 | second 2 ^ first 2 | second 3
  3 | ^ first 1 | second 2 | second 3
  4 | ^ first 2 | second 3 ^ first 1 | second 2 | second 2
  5 | ^ first 2 | second 3 ^ first 1 | second 3

我的查询必须^从匹配的中间排除字符，所以我使用正则表达式：

explain analyze select count(*) from test5 where keyvalues ~* '\^ first 1[^\^]+second 0';

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=78383.31..78383.32 rows=1 width=8) (actual time=7332.030..7332.030 rows=1 loops=1)
   ->  Gather  (cost=78383.10..78383.30 rows=2 width=8) (actual time=7332.021..7337.138 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=77383.10..77383.10 rows=1 width=8) (actual time=7328.155..7328.156 rows=1 loops=3)
               ->  Parallel Seq Scan on test5  (cost=0.00..77382.50 rows=238 width=0) (actual time=7328.146..7328.146 rows=0 loops=3)
                     Filter: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
                     Rows Removed by Filter: 1666668
 Planning Time: 0.068 ms
 Execution Time: 7337.184 ms

查询有效（零行匹配），但在 > 7 秒时速度太慢。

我认为用三元组索引会有所帮助，但没有运气：

create extension if not exists pg_trgm;
create index on test5 using gin (keyvalues gin_trgm_ops);

explain analyze select count(*) from test5 where keyvalues ~* '\^ first 1[^\^]+second 0';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1484.02..1484.03 rows=1 width=8) (actual time=23734.646..23734.646 rows=1 loops=1)
   ->  Bitmap Heap Scan on test5  (cost=1480.00..1484.01 rows=1 width=0) (actual time=23734.641..23734.641 rows=0 loops=1)
         Recheck Cond: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
         Rows Removed by Index Recheck: 5000005
         Heap Blocks: exact=47620
         ->  Bitmap Index Scan on test5_keyvalues_idx  (cost=0.00..1480.00 rows=1 width=0) (actual time=1756.158..1756.158 rows=5000005 loops=1)
               Index Cond: (keyvalues ~* '\^ first 1[^\^]+second 0'::text)
 Planning Time: 0.412 ms
 Execution Time: 23734.722 ms

使用 trigram 索引的查询慢了 3 倍！它仍然返回正确的结果（零行）。我希望三元组索引立即找出second 0任何地方都没有字符串，并且速度非常快。

（动机：我想避免将规范化keyvalues到另一个表中，所以我希望TEXT使用文本索引和正则表达式在单个字段中对匹配逻辑进行编码。该逻辑有效，但速度太慢，JSONB 也是如此。）

格洛芬德尔

按照OP，正确的答案被赋予这里的DBA.SE用户@jjanes：

我希望三元组索引立即找出second 0任何地方都没有字符串

'second' 和 '0' 是单独的词，因此它无法检测到它们的联合缺失。似乎它可以检测到“0”的缺失，但是来自“contrib/pg_trgm/trgm_regexp.c”的这条评论似乎是相关的：
     * Note: Using again the example "foo bar", we will not consider the
     * trigram "  b", though this trigram would be found by the trigram
     * extraction code.  Since we will find " ba", it doesn't seem worth
     * trying to hack the algorithm to generate the additional trigram.
由于 0 是模式字符串中的最后一个字符，因此也不会有“0a”形式的三元组，因此它只是错过了这个机会。

即使不是因为这个限制，你的方法似乎也非常脆弱。