PostgreSQL全文搜索与模式匹配：性能比较

我们将分析PostgreSQL全文搜索（FTS）与模式和正则表达式搜索的性能，重点比较各种方法的执行效率和权衡取舍。

在软件开发领域，我们经常需要接受权衡取舍。实际上，在架构和设计产品时，我们做出的每个决定都是一种妥协。根据开发产品的目的、要求和具体情况来平衡这些权衡，以确保解决方案和价值交付，并帮助客户完成他们的需求。

基于这一观点，为了取得成功，你需要做出妥协。本文旨在比较两种方法（FTS和模式匹配）的执行性能，当然是在一组明确的先决条件（权衡）下进行的。目的显然是提高这些方法适用性的客观性，并帮助程序员在需要时轻松选择。

先决条件

通过查找多个表的列，对三个不同的实体执行模式搜索
感兴趣的实体是：电信发票、开具发票的库存项以及此类库存项的订单
操作结果中组成的实体将一起显示
针对目标列的"以…开头"匹配是可接受的
感兴趣的字段是：发票编号和评论、库存编号和评论、订单编号和评论

性能分析

假设PostgreSQL数据库服务器已启动并运行，可以连接到它并探索三个感兴趣的实体。

1
2
3


select count(*) from inventories;   -- 2,821,800条记录
select count(*) from invoice;       -- 55,911条记录  
select count(*) from orders;        -- 30,564条记录

可以观察到搜索实体的总数约为300万，这对于客观分析来说是一个相当不错的样本。

为了能够在不需要修改现有表的情况下进行分析，创建了以下物化视图：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


CREATE MATERIALIZED VIEW IF NOT EXISTS mv_fts_entities AS
SELECT contractid AS id,
       'INVENTORY' AS type,
       extrainfo AS number,
        (
            setweight(to_tsvector('simple', coalesce(extrainfo, '')), 'A') ||
            setweight(to_tsvector('simple', coalesce(itemcomments, '')), 'B')
        ) AS search_vector
FROM inventories
UNION ALL
SELECT i.invoiceid AS id,
       'INVOICE' AS type,
       i.invoicenum AS number,
        (
            setweight(to_tsvector('simple', i.invoicenum), 'A') ||
            setweight(to_tsvector('simple', coalesce(i.comments, '')), 'B') ||
            setweight(to_tsvector('simple', a.account), 'A')
        ) AS search_vector
FROM invoice i
LEFT JOIN account a on a.id = i.accountid
UNION ALL
SELECT orderid AS id,
       'ORDER' AS type,
       ordernumber AS numnber,
        (
            setweight(to_tsvector('simple', ordernumber), 'A') ||
            setweight(to_tsvector('simple', coalesce(comments, '')), 'B')
        ) AS search_vector
FROM orders;

几点说明：

物化视图汇集了三个感兴趣表中的所有记录
id代表每个实体的唯一标识符
type允许识别实体—INVENTORY、INVOICE或ORDER
number是我们主要执行模式搜索的字段—分别是库存、发票和订单编号
search_vector包含感兴趣列的tsvector表示，它代表PostgreSQL的文本搜索向量类型，表示可搜索内容
setweight()—根据考虑的列，在计算词位时设置不同的权重
coalesce()优雅地处理空值
对于发票记录，还尝试在发票账户上进行匹配，尽管该列指定了不同实体的属性

物化视图的创建大约需要三秒钟。

如果希望刷新内容以使数据保持最新，可以执行以下命令：

1

REFRESH MATERIALIZED VIEW mv_fts_entities;

物化视图刷新大约需要10秒钟。

此外，创建了以下索引以提高两种情况的性能：

在search_vector列上创建GIN索引以改进全文搜索

1

CREATE INDEX mv_fts_entities_search_idx ON mv_fts_entities USING GIN (search_vector);

在number列上创建B-Tree索引以改进"以…开头"模式搜索

1

CREATE INDEX mv_fts_entities_number_idx ON mv_fts_entities(number);

两个操作都需要大约五秒钟。

有了上述设置，让我们检查以下每种场景下的性能。

1. 全文搜索

1
2
3
4
5
6
7


EXPLAIN ANALYZE
    SELECT id, type, number, search_vector, ts_rank(search_vector, query) as rank
    FROM mv_fts_entities,
         to_tsquery('simple', '101:*') query
    WHERE search_vector @@ query
    ORDER BY rank DESC
    LIMIT 10;

关键点：

使用位图索引扫描进行匹配
使用Top-N堆排序对结果进行排序（排名）
搜索时间约为1毫秒

2. 区分大小写的通配符模式搜索

1
2
3
4
5


EXPLAIN ANALYZE
    SELECT id, type, number
    FROM mv_fts_entities
    WHERE number LIKE '101%'
    LIMIT 10;

关键点：

使用顺序扫描
结果集明显不同于第1点
搜索时间稍长，约为2.2毫秒

3. 不区分大小写的通配符模式搜索

1
2
3
4
5


EXPLAIN ANALYZE
    SELECT id, type, number
    FROM mv_fts_entities
    WHERE number ILIKE '101%'
    LIMIT 10;

关键点：

使用顺序扫描
结果集明显不同于第1点，也不同于第2点
搜索时间比之前的都长，约为11.5毫秒

4. 不区分大小写的正则表达式搜索

1
2
3
4
5


EXPLAIN ANALYZE
    SELECT id, type, number
    FROM mv_fts_entities
    WHERE number ~* '^101'
    LIMIT 10;

关键点：

使用顺序扫描
结果集明显不同于第1点，但与上一个相同
搜索时间比使用全文搜索的时间长，但比模式搜索短，约为6.2毫秒

在执行时间方面获得的结果可以总结如下：

FTS « 区分大小写模式搜索 < 不区分大小写正则搜索 < 不区分大小写模式搜索

本文中的实验和获得的结果使得FTS成为即使在可接受其已知限制的情况下也值得考虑的模式搜索候选方案。此外，其在tsvector计算方面的配置灵活性及其执行速度使其在其他解决方案中具有优势，当然，是在所呈现的情况下。

PostgreSQL全文搜索与模式匹配性能对比分析

本文通过实验对比PostgreSQL全文搜索与模式匹配在300万条记录下的性能表现，详细分析四种搜索策略的执行效率、索引使用情况和查询计划，为数据库搜索方案选择提供实践参考。

PostgreSQL全文搜索与模式匹配：性能比较

先决条件

性能分析

1. 全文搜索

2. 区分大小写的通配符模式搜索

3. 不区分大小写的通配符模式搜索

4. 不区分大小写的正则表达式搜索