在PostgreSQL中，针对8000万个记录的正则表达式查询速度缓慢。

Question

在PostgreSQL中，针对8000万个记录的正则表达式查询速度缓慢。

4

我有一张只读表格，其中包含了8000万行：

   Column    |          Type          | Modifiers | Storage  | Stats target | Description 
-------------+------------------------+-----------+----------+--------------+-------------
 id          | character(11)          | not null  | extended |              | 
 gender      | character(1)           |           | extended |              | 
 postal_code | character varying(10)  |           | extended |              | 
 operator    | character varying(5)   |           | extended |              | 

Indexes:
    "categorised_phones_pkey" PRIMARY KEY, btree (id)
    "operator_idx" btree (operator)
    "postal_code_trgm_idx" gin (postal_code gin_trgm_ops)

id 是主键，包含唯一的手机号码。表格行看起来像这样:

      id        |     gender   |   postal_code  |   operator
----------------+--------------+----------------+------------
 09567849087    |      m       |   7414776788   |     mtn
 09565649846    |      f       |   1268398732   |     mci
 09568831245    |      f       |   7412556443   |     mtn
 09469774390    |      m       |   5488312790   |     mci

这个查询在第一次执行时需要大约65秒的时间，之后每次执行只需要大约8秒的时间。

select operator,count(*) from categorised_phones where postal_code like '1%' group by operator;

输出结果如下：

operator |  count  
----------+---------
 mci      | 4050314
 mtn      | 6235778

explain analyze 的输出结果:

HashAggregate  (cost=1364980.61..1364980.63 rows=2 width=10) (actual time=8257.026..8257.026 rows=2 loops=1)
   Group Key: operator
   ->  Bitmap Heap Scan on categorised_phones  (cost=95969.17..1312915.34 rows=10413054 width=2) (actual time=1140.803..6332.534 rows=10286092 loops=1)
         Recheck Cond: ((postal_code)::text ~~ '1%'::text)
         Rows Removed by Index Recheck: 25105697
         Heap Blocks: exact=50449 lossy=237243
         ->  Bitmap Index Scan on postal_code_trgm_idx  (cost=0.00..93365.90 rows=10413054 width=0) (actual time=1129.270..1129.270 rows=10287127 loops=1)
               Index Cond: ((postal_code)::text ~~ '1%'::text)
 Planning time: 0.540 ms
 Execution time: 8257.392 ms

如何使查询更快？

欢迎任何想法。

附：

我正在使用PostgreSQL 9.6.1

更新

我刚刚更新了问题。我禁用了Parallel Query，结果发生了变化。

- Hamed Kamrava

你在postal_code上有一个gin索引。它定义了哪些操作？你似乎没有使用任何会触发该索引的东西。 - jmelesky

我正在使用gin索引来处理 LIKE '1%'。 - Hamed Kamrava

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- joanolo · Accepted Answer

对于涉及形式为LIKE '%start'的比较查询，遵循PostgreSQL的建议，您可以使用以下索引：

CREATE INDEX postal_code_idx  ON categorised_phones (postal_code varchar_pattern_ops) ;

有了这个索引和一些模拟数据，您的执行计划很可能如下所示： | 查询计划 | | :-------------------------------------------------- | | HashAggregate (cost=2368.65..2368.67 rows=2 width=12) (actual time=18.093..18.094 rows=2 loops=1)| | Group Key: operator | | -> Bitmap Heap Scan on categorised_phones (cost=536.79..2265.83 rows=20564 width=4) (actual time=2.564..12.061 rows=22171 loops=1) | | Filter: ((postal_code)::text ~~ '1%'::text) | | Heap Blocks: exact=1455 | | -> Bitmap Index Scan on postal_code_idx (cost=0.00..531.65 rows=21923 width=0) (actual time=2.386..2.386 rows=22171 loops=1) | | Index Cond: (((postal_code)::text ~>=~ '1'::text) AND ((postal_code)::text ~<~ '2'::text)) | | 计划时间: 0.119 毫秒 | | 执行时间: 18.122 毫秒 |

您可以在 dbfiddle 这里 进行检查。

如果您同时使用 LIKE 'start%' 和 LIKE '%middle%' 进行查询，则应添加此索引，但保留已有的索引。对于第二种匹配，三元组索引可能会很有用。

为什么要这样做呢？

来自 PostgreSQL 文档中的运算符类：

文本、varchar 和 char 类型上的操作符类 text_pattern_ops、varchar_pattern_ops 和 bpchar_pattern_ops 支持 B-tree 索引。与默认运算符类不同之处在于，值是严格按字符比较而不是根据特定于语言环境的排序规则进行比较的。当数据库不使用标准的“C”语言环境时，这使得这些运算符类适用于涉及模式匹配表达式（LIKE 或 POSIX 正则表达式）的查询。

来自 PostgreSQL 索引类型文档。

优化器可以使用B树索引来处理涉及模式匹配运算符LIKE和~的查询，如果模式是一个常量并且始于字符串的开头-例如，col LIKE 'foo%'或col ~ '^foo'，但不是col LIKE '%bar'。然而，如果您的数据库不使用C语言环境，则需要使用特殊运算符类创建索引以支持模式匹配查询的索引；请参见下面的第11.9节。也可以对ILIKE和~*使用B-tree索引，但只有在模式以非字母字符开头时才行，即不受大小写转换影响的字符。

更新

如果执行的查询总是涉及一定数量（相对较小）的LIKE 'x%'表达式，请考虑使用部分索引。

例如，对于LIKE '1%'，您将拥有以下索引和以下查询计划（它显示大约3倍的提高）：

CREATE INDEX idx_1 ON categorised_phones (operator) WHERE postal_code LIKE '1%';
VACUUM categorised_phones ;

| 查询计划                                                                                                                                     |
| :-------------------------------------------------------------------------------------------------------------------------------------------- |
| 分组聚合  (成本=0.29..658.74 行=3 宽度=12) (实际时间=3.235..6.493 行=2 循环=1)                                                             |
|   分组键: 操作员                                                                                                                            |
|   ->  索引扫描 (仅索引) 使用 idx_1 在 categorised_phones 上 (成本=0.29..554.10 行=20921 宽度=4) (实际时间=0.028..3.266 行=22290 循环=1) |
|         堆获取: 0                                                                                                                            |
| 计划时间: 0.293 毫秒                                                                                                                         |
| 执行时间: 6.517 毫秒                                                                                                                         |