POSTGRESQL(PG) 性能优化之like全文检索优化-Toy模板网

这篇具有很好参考价值的文章主要介绍了POSTGRESQL(PG) 性能优化之like全文检索优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

使用like操作可以进行字符串比较，全文检索等，性能相对比较差，有些情况下可以通过建立索引来提升性能。下面我们通过使用TPCH orders表作为例子，来进行说明。但是请注意， not like是不能用任何索引的，BTREE不支持!=操作，只能进行=和范围查找。

TPCH orders表的定义如下，其中o_comment列是varchar类型的字符串：

tpch10=# \d orders
                          Table "public.orders"
     Column      |         Type          | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
 o_orderkey      | integer               |           | not null |
 o_custkey       | integer               |           | not null |
 o_orderstatus   | character(1)          |           | not null |
 o_totalprice    | numeric(15,2)         |           | not null |
 o_orderdate     | date                  |           | not null |
 o_orderpriority | character(15)         |           | not null |
 o_clerk         | character(15)         |           | not null |
 o_shippriority  | integer               |           | not null |
 o_comment       | character varying(79) |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
Foreign-key constraints:
    "orders_o_custkey_fkey" FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
Referenced by:
    TABLE "lineitem" CONSTRAINT "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)

1. 前匹配 LIKE ‘prefix%’范围查询

前匹配可以使用BTREE索引进行范围查询，在没有任何索引的情况下，PG默认使用seqscan顺序扫描：

tpch10=# explain analyze select * from orders where o_comment like 'request%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448412.00 rows=75000 width=234) (actual time=0.662..5098.904 rows=56870 loops=1)
   Filter: ((o_comment)::text ~~ 'request%'::text)
   Rows Removed by Filter: 14943130
 Planning Time: 0.268 ms
 Execution Time: 5103.280 ms
(5 rows)

我们在o_comment列加一下BTREE索引，然后再次尝试：

tpch10=# create index on orders(o_comment);
CREATE INDEX
tpch10=# analyze orders;
ANALYZE
tpch10=# explain analyze select * from orders where o_comment like 'request%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448412.00 rows=75000 width=234) (actual time=0.662..5098.904 rows=56870 loops=1)
   Filter: ((o_comment)::text ~~ 'request%'::text)
   Rows Removed by Filter: 14943130
 Planning Time: 0.268 ms
 Execution Time: 5103.280 ms
(5 rows)
tpch10=# explain analyze select * from orders where o_comment like 'request';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_o_comment_idx on orders  (cost=0.56..8.58 rows=1 width=107) (actual time=0.188..0.188 rows=0 loops=1)
   Index Cond: ((o_comment)::text = 'request'::text)
   Filter: ((o_comment)::text ~~ 'request'::text)
 Planning Time: 0.408 ms
 Execution Time: 0.250 ms
(5 rows)

发现PG在like ‘prefix%’并没有使用BTREE索引，而精确匹配可以使用索引，问题在于默认的collate是en_US.UTF-8，而不是C，将o_comment列改为C collate再进行尝试。使用alter table命令将o_comment列改成collate C，可以查看到o_comment列的Collation属性是C。

tpch10=# alter table orders alter column o_comment TYPE VARCHAR(79) collate "C";
ALTER TABLE
tpch10=# \d orders
                          Table "public.orders"
     Column      |         Type          | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+---------
 o_orderkey      | integer               |           | not null |
 o_custkey       | integer               |           | not null |
 o_orderstatus   | character(1)          |           | not null |
 o_totalprice    | numeric(15,2)         |           | not null |
 o_orderdate     | date                  |           | not null |
 o_orderpriority | character(15)         |           | not null |
 o_clerk         | character(15)         |           | not null |
 o_shippriority  | integer               |           | not null |
 o_comment       | character varying(79) | C         | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (o_orderkey)
    "orders_o_comment_idx" btree (o_comment)
Foreign-key constraints:
    "orders_o_custkey_fkey" FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
Referenced by:
    TABLE "lineitem" CONSTRAINT "lineitem_l_orderkey_fkey" FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey)

tpch10=#

再执行一次之前的语句，发现使用了创建的BTREE索引，时间从5103ms提升到232ms，可以看到性能得到极大提升。

tpch10=# explain analyze select * from orders where o_comment like 'request%';
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=3477.31..168092.77 rows=75000 width=234) (actual time=50.869..228.043 rows=56870 loops=1)
   Filter: ((o_comment)::text ~~ 'request%'::text)
   Heap Blocks: exact=51160
   ->  Bitmap Index Scan on orders_o_comment_idx  (cost=0.00..3458.56 rows=75000 width=0) (actual time=33.213..33.213 rows=56870 loops=1)
         Index Cond: (((o_comment)::text >= 'request'::text) AND ((o_comment)::text < 'requesu'::text))
 Planning Time: 0.528 ms
 Execution Time: 232.989 ms
(7 rows)

tpch10=#

2. 后匹配 LIKE ‘%prefix’范围查询

后匹配的方式不能使用BTREE索引，原因在于BTREE索引只能做大于、大于等于、等于、小于、小于等于等操作决定的。因此这时候使用reverse index来处理后匹配的文本检索。

比如我们使用相同的例子，只是like匹配的是‘%request’，PG依旧使用顺序扫描，而没有使用之前建的索引。

tpch10=# explain analyze select * from orders where o_comment like '%request';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448412.00 rows=151515 width=107) (actual time=0.043..8640.280 rows=57305 loops=1)
   Filter: ((o_comment)::text ~~ '%request'::text)
   Rows Removed by Filter: 14942695
 Planning Time: 1.065 ms
 Execution Time: 8645.223 ms
(5 rows)

我们给o_comment列建一个reverse的索引，会看到执行语句会选用reverse索引，时间由8645ms降到162ms，提升效率非常高。因此在这种情况下可以考虑使用reverse索引来提高后匹配的检索效率。

tpch10=# create index on orders(reverse(o_comment));
CREATE INDEX
tpch10=# analyze orders;
ANALYZE

tpch10=# explain analyze select * from orders where reverse(o_comment) like reverse('%request');
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_reverse_idx on orders  (cost=0.56..8.59 rows=1500 width=107) (actual time=0.142..157.412 rows=57305 loops=1)
   Index Cond: ((reverse((o_comment)::text) >= 'tseuqer'::text) AND (reverse((o_comment)::text) < 'tseuqes'::text))
   Filter: (reverse((o_comment)::text) ~~ 'tseuqer%'::text)
 Planning Time: 0.620 ms
 Execution Time: 162.048 ms
(5 rows)

tpch10=#

3. 中间匹配'%prefix%'范围查询

对于中间匹配的全文检索，以上两种索引都不能解决，PG提供了GIN索引，可以使用PG提供的pg_trgm插件来加速查询。GIN索引可以应用于前匹配、后匹配和中间匹配，但是因为GIN本身比较昂贵，对于前匹配和后匹配，可以使用上面提到的方式，中间匹配采用GIN索引，但是也需要考虑索引维护本身的代价。

在没有使用GIN索引时候，PG依旧使用顺序扫描：

tpch10=# explain analyze select * from orders where o_comment like '%request%';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..448442.18 rows=2273093 width=107) (actual time=0.108..8773.358 rows=2318296 loops=1)
   Filter: ((o_comment)::text ~~ '%request%'::text)
   Rows Removed by Filter: 12681704
 Planning Time: 0.836 ms
 Execution Time: 8901.078 ms
(5 rows)

tpch10=# explain analyze select * from orders where reverse(o_comment) like reverse('%request%');
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..485948.21 rows=3030791 width=107) (actual time=0.069..18095.022 rows=2318296 loops=1)
   Filter: (reverse((o_comment)::text) ~~ '%tseuqer%'::text)
   Rows Removed by Filter: 12681704
 Planning Time: 4.783 ms
 Execution Time: 18218.893 ms
(5 rows)

但是GIN索引的创建和维护非常昂贵，因为GIN索引对每组三个连续字符（三元组）进行索引，以便能够查找任何匹配模式。而且GIN索引也可以应用于前后匹配的组合使用，这种情况下效率也很高。

加载pg_trgm插件后，在o_comment列创建GIN索引，重新进行查询，时间由8901ms提升到3939ms，效率得到了提升，同时我们也可以看到GIN索引也可以进行‘%prefix%prefix%’的查询，效率也是很明显的提升。文章来源地址https://www.toymoban.com/news/detail-755320.html

tpch10=# create extension if not exists pg_trgm;
CREATE EXTENSION
tpch10=# create index on orders using gin (o_comment gin_trgm_ops);
CREATE INDEX
tpch10=# analyze orders;
ANALYZE

tpch10=# explain analyze select * from orders where o_comment like '%request%';
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=29361.97..330044.89 rows=3181674 width=107) (actual time=1727.012..3828.371 rows=2318296 loops=1)
   Recheck Cond: ((o_comment)::text ~~ '%request%'::text)
   Rows Removed by Index Recheck: 2088
   Heap Blocks: exact=260892
   ->  Bitmap Index Scan on orders_o_comment_idx1  (cost=0.00..28566.55 rows=3181674 width=0) (actual time=1604.355..1604.355 rows=2320384 loops=1)
         Index Cond: ((o_comment)::text ~~ '%request%'::text)
 Planning Time: 0.473 ms
 Execution Time: 3939.557 ms
(8 rows)


tpch10=# explain analyze select * from orders where o_comment like '%special%request%';
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=1478.19..236736.24 rows=151508 width=107) (actual time=1794.711..2266.371 rows=168338 loops=1)
   Recheck Cond: ((o_comment)::text ~~ '%special%request%'::text)
   Rows Removed by Index Recheck: 80603
   Heap Blocks: exact=161167
   ->  Bitmap Index Scan on orders_o_comment_idx1  (cost=0.00..1440.31 rows=151508 width=0) (actual time=1731.915..1731.915 rows=248941 loops=1)
         Index Cond: ((o_comment)::text ~~ '%special%request%'::text)
 Planning Time: 0.501 ms
 Execution Time: 2278.128 ms
(8 rows)

到了这里，关于POSTGRESQL(PG) 性能优化之like全文检索优化的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！