在PostgreSQL中对80M记录进行缓慢的正则表达式查询

Question

我有一个包含 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 alanyze的输出：

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，结果发生了变化。

Answer 1

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

CREATE INDEX postal_code_idx  ON categorised_phones (postal_code varchar_pattern_ops) ;

有了该索引和一些模拟数据，您的执行计划很可能如下所示：

|查询计划 | |:------------------------------------------------------------------------------------------------------------------------------------- | |哈希聚合(成本=2368.65..2368.67 行=2 宽度=12)(实际时间=18.093..18.094 行=2 个循环=1)| |  组键：运算符 | |  -> categorised_phones上的位图堆扫描(成本=536.79..2265.83 行=20564 宽度=4)(实际时间=2.564..12.061 行=22171 循环=1)| |        过滤器： ((postal_code)：：文本 ~~ '1%'：：文本) | |        堆块：精确=1455 ||        -> 位图索引扫描postal_code_idx(成本=0.00..531.65 行=21923 宽度=0)(实际时间=2.386..2.386 行=22171 循环=1)| |              索引 Cond： (((postal_code)：：文本 ~>=~ '1'：：文本) 和 (postal_code)：：文本 ~<~ '2'：：文本)) | |规划时间：0.119 ms | |执行时间：18.122 ms |

你可以在这里的dbfiddle上检查它

如果您同时具有LIKE 'start%'和LIKE '%middle%'的查询，则应添加此索引，但保留已存在的索引。三元组索引可能被证明对第二种匹配很有用。

---

为什么？

来自PostgreSQL文档中关于运算符类：

运算符类text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops分别支持 text 、varchar 和 char 类型的 B 树索引。与默认运算符类的区别在于，这些值是严格逐字符比较的，而不是根据特定于区域设置的排序规则进行比较。这使得当数据库不使用标准"C"区域设置时，这些运算符类适合由涉及模式匹配表达式(LIKE 或 POSIX 正则表达式)的查询使用。

来自PostgreSQL文档关于索引类型

优化器还可以将 B 树索引用于涉及模式匹配运算符的查询，LIKE和~模式是否是常量并锚定到字符串的开头 - 例如，col LIKE 'foo%'或col ~ '^foo'，但不是 colLIKE '%bar'。但是，如果您的数据库不使用 C 语言环境，则需要使用特殊的运算符类创建索引，以支持模式匹配查询的索引;请参阅下文第 11.9 节。也可以将B树索引用于ILIKE和~*，但前提是模式以非字母字符开头，即不受大写/小写转换影响的字符。

---

更新

如果执行的查询始终涉及固定(且相对较少)数量的LIKE 'x%'表达式，请考虑使用partial indexes。

例如，对于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) | |  组键：运算符 | |  -> 仅索引 在categorised_phones上使用idx_1扫描(成本=0.29..554.10 行=20921 宽度=4)(实际时间=0.028..3.266 行=22290 循环=1)| |        堆提取： 0 ||规划时间： 0.293 ms | |执行时间：6.517 ms |