如何提高 PostgreSQL LIKE %text% 查询性能

Question

我有3个表: requests, steps, 和 payloads。每个请求都有N个步骤（因此是一对多的关系），每个步骤都有一个负载（一对一的关系）。

现在，每当我想要按负载正文进行过滤时，执行时间都非常长。

这是简化后的请求:

select rh.id
from request_history_step_payload rhsp
join request_history_step rhs on rhs.id =  rhsp.step_id
join request_history rh on rhs.request_id = rh.id
where rh.id> 35000 and rhs.step_type = 'CONSUMER_REQUEST' and rhsp.payload like '%09141%'

这里是关于 EXPLAIN ANALYZE 的内容（在执行完 VACUUM ANALYZE 后立即运行）:

Nested Loop  (cost=0.71..50234.28 rows=1 width=8) (actual time=120.093..2494.929 rows=12 loops=1)
  ->  Nested Loop  (cost=0.42..50233.32 rows=3 width=8) (actual time=120.083..2494.900 rows=14 loops=1)
        ->  Seq Scan on request_history_step_payload rhsp  (cost=0.00..50098.28 rows=16 width=8) (actual time=120.063..2494.800 rows=25 loops=1)
              Filter: ((payload)::text ~~ '%09141%'::text)
              Rows Removed by Filter: 164512
        ->  Index Scan using request_history_step_pkey on request_history_step rhs  (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=25)
              Index Cond: (id = rhsp.step_id)
              Filter: ((step_type)::text = 'CONSUMER_REQUEST'::text)
              Rows Removed by Filter: 0
  ->  Index Only Scan using request_history_pkey on request_history rh  (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=14)
        Index Cond: ((id = rhs.request_id) AND (id > 35000))
        Heap Fetches: 0
Planning Time: 0.711 ms
Execution Time: 2494.964 ms

现在，我想要建议计划器将LIKE操作作为最后一步应用，但是我无法想出任何方法来实现。我尝试了各种类型的连接，并对它们进行重新排序，将条件从ON子句移动到WHERE子句中，反之亦然。所有这些都无济于事！它首先查找payload表中的所有行，这显然是最糟糕的想法，因为有其他条件可以大大减少需要应用的LIKE操作的数量。因此，我希望它首先应用id条件，这已经解决了大约90％的记录; 然后它会应用step_type条件，这将解决剩下85％的问题; 因此，只需对不到5％的所有有效载荷应用LIKE条件。

我应该怎么做？ 我正在使用Postgres 11。

更新：有人建议为这些表添加索引信息，因此：

- request_history - 在id字段上有2个唯一索引（我不知道为什么有2个） - request_history_step - 在id字段上有2个唯一索引 - request_history_step_payload - 在id字段上有1个唯一索引

更新2：还为step和payload表定义了外键（在payload.step_id->step.id和step.request_id -> request_id上，分别）。

我还尝试了几个简化的带有子查询的查询：

explain analyze select rhs.id from request_history_step rhs
join (select step_id from request_history_step_payload rhsp where rhsp.payload like '%09141%') rhsp on rhs.id = rhsp.step_id 
where rhs.step_type = 'CONSUMER_REQUEST';

explain analyze select rhsp.step_id from request_history_step_payload rhsp
join (select id from request_history_step rhs where rhs.step_type = 'CONSUMER_REQUEST') rhs on rhs.id = rhsp.step_id 
where rhsp.payload like '%09141%';


explain analyze select rhsp.step_id from request_history_step_payload rhsp
where rhsp.step_id  in (select id from request_history_step rhs where rhs.step_type = 'CONSUMER_REQUEST')  
and rhsp.payload like '%09141%';

（使用JOIN LATERAL而不是仅使用JOIN）-每个查询都会得到完全相同的计划，即嵌套循环，在其中嵌套循环的“外部”（第一条）路径是SeqScan。这让我非常困惑。为什么它希望对最广泛的行集执行最昂贵的操作？？

更新3：受原始问题下的评论启发，我进行了进一步的实验。我选择了更简单的查询：

select rhs.request_id 
from request_history_step_payload rhsp
join request_history_step rhs on rhs.id =  rhsp.step_id
where rhs.step_type = 'CONSUMER_REQUEST' and rhsp.payload like '%09141%';

现在，它的执行计划与原始计划基本相同，只是少了一个“嵌套循环”。

现在，我为 payload.step_id 添加了索引：

create index request_history_step_payload_step_id on request_history_step_payload(step_id);

运行 VACUUM ANALYZE 命令；使用 explain analyze 查看查询结果 - 没有任何变化。嗯。

现在我运行了set enable_seqscan to off。然后现在我们可以开始：

Gather  (cost=1000.84..88333.90 rows=3 width=8) (actual time=530.273..589.650 rows=14 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  ->  Nested Loop  (cost=0.84..87333.60 rows=2 width=8) (actual time=544.639..580.608 rows=7 loops=2)
        ->  Parallel Index Scan using request_history_step_pkey on request_history_step rhs  (cost=0.42..15913.04 rows=20867 width=16) (actual time=0.029..28.667 rows=17620 loops=2)
              Filter: ((step_type)::text = 'CONSUMER_REQUEST'::text)
              Rows Removed by Filter: 64686
        ->  Index Scan using request_history_step_payload_step_id on request_history_step_payload rhsp  (cost=0.42..3.41 rows=1 width=8) (actual time=0.031..0.031 rows=0 loops=35239)
              Index Cond: (step_id = rhs.id)
              Filter: ((payload)::text ~~ '%09141%'::text)
              Rows Removed by Filter: 1
Planning Time: 0.655 ms
Execution Time: 589.688 ms

现在，由于SeqScan的成本飙升，我认为我们可以看出问题的要点：尽管执行时间实际上要短得多（590ms vs 2700ms），但是这个执行计划的成本被认为比原始计划高（88k vs 50k）。显然，这就是Postgre规划器坚持选择“SeqScan first”的原因，尽管我已经尽了最大的努力说服它不要这样做。

我还尝试为step.step_type字段添加哈希和基于btree的索引。每一个都会生成成本远高于50k的计划，所以在enable_seqscan设置为on（默认值）的情况下，规划器总是会忽略它们。

有人知道如何缓解这个问题吗？我担心适当的解决方案可能需要改变规划器变量权重，这当然不是我想做的。但我很乐意听取任何建议！

更新4：现在我玩了一些更多，报告进一步的结果（enable_seqscan设置为on）：

这个很慢，应用seqscan，即使在step.step_type上有索引：

explain analyze 
select rhsp.step_id 
from (select request_id, id from request_history_step rhs2 where rhs2.step_type = 'CONSUMER_REQUEST') rhs
join request_history_step_payload rhsp on rhs.id =  rhsp.step_id
where rhsp.payload like '%09141%';

这个方案基于O. Jones的建议，仍然很慢：

explain analyze
with rhs as (select request_id, id from request_history_step rhs2 where rhs2.step_type = 'CONSUMER_REQUEST') 
select rhsp.step_id from request_history_step_payload rhsp 
join rhs on rhs.id = rhsp.step_id 
where rhsp.payload like '%09141%';

但是这一个稍加修改，就变得快速：

explain analyze
with rhs as (select request_id, id from request_history_step rhs2 where rhs2.step_type = 'CONSUMER_REQUEST') 
select rhsp.step_id   
from request_history_step_payload rhsp 
join rhs on rhs.id = rhsp.step_id 
where rhsp.step_id  in (select id from rhs) and rhsp.payload like '%09141%';

它的执行计划是：

Hash Join  (cost=9259.55..10097.04 rows=2 width=8) (actual time=1157.984..1162.199 rows=14 loops=1)
  Hash Cond: (rhs.id = rhsp.step_id)
  CTE rhs
    ->  Bitmap Heap Scan on request_history_step rhs2  (cost=1169.28..6918.06 rows=35262 width=16) (actual time=3.899..19.093 rows=35241 loops=1)
          Recheck Cond: ((step_type)::text = 'CONSUMER_REQUEST'::text)
          Heap Blocks: exact=3120
          ->  Bitmap Index Scan on request_history_step_step_type_hash  (cost=0.00..1160.46 rows=35262 width=0) (actual time=3.047..3.047 rows=35241 loops=1)
                Index Cond: ((step_type)::text = 'CONSUMER_REQUEST'::text)
  ->  CTE Scan on rhs  (cost=0.00..705.24 rows=35262 width=8) (actual time=3.903..5.976 rows=35241 loops=1)
  ->  Hash  (cost=2341.39..2341.39 rows=8 width=16) (actual time=1153.976..1153.976 rows=14 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Nested Loop  (cost=793.81..2341.39 rows=8 width=16) (actual time=104.170..1153.919 rows=14 loops=1)
              ->  HashAggregate  (cost=793.39..795.39 rows=200 width=8) (actual time=33.315..44.875 rows=35241 loops=1)
                    Group Key: rhs_1.id
                    ->  CTE Scan on rhs rhs_1  (cost=0.00..705.24 rows=35262 width=8) (actual time=0.001..23.590 rows=35241 loops=1)
              ->  Index Scan using request_history_step_payload_step_id on request_history_step_payload rhsp  (cost=0.42..7.72 rows=1 width=8) (actual time=0.031..0.031 rows=0 loops=35241)
                    Index Cond: (step_id = rhs_1.id)
                    Filter: ((payload)::text ~~ '%09141%'::text)
                    Rows Removed by Filter: 1
Planning Time: 1.318 ms
Execution Time: 1162.618 ms

成本大幅降低，但执行时间并没有减少太多。

- 62mkv

---

为了让条件like '%09141%'使用索引，您需要创建一个三元组索引。 - user330315

@jjanes 同感。嵌套循环（cost=10000000000.71..10000050244.15 rows=1 width=8）（实际时间=114.443..2715.924行=12个循环=1） -> 嵌套循环（cost=10000000000.42..10000050243.19 rows=3 width=8）（实际时间=114.432..2715.895行=14个循环=1） -> 请求历史步骤有效负载rhsp的Seq扫描（cost=10000000000.00..10000050108.15 rows=16 width=8）（实际时间=114.414..2715.775行=25个循环=1） 过滤器：（（payload）::text~~'%09141%'::text） 过滤器删除的行数：164544 - 62mkv

你应该在 request_history_step_payload (step_id) 和 request_history_step(request_id) 上建立索引（可能是外键）。 - user330315

你尝试过在外部查询中使用LIKE谓词的嵌套查询吗？或者在HAVING子句中使用它？ - symcbean

我是指在那些列上创建索引。外键约束不会自动创建索引。 - user330315

Answer 1

根据您的计划，看起来您在 request_history_step_payload 操作上的 step_type 谓词并没有起到太大的帮助。因此，让我们尝试使用包含列的文本（三元组）索引来加速该搜索步骤。

 CREATE INDEX CONCURRENTLY rhsp_type_payload 
     ON request_history_step_payload
  USING GIN (step_type gin_trgm_ops)
INCLUDE (rhs_step_type, rhs_step_id);

我希望这可能会有所帮助，请试一试。

当您拥有该索引时，您也可以尝试像这样重新编写查询：

select rh.id
from (  select step_id
          from request_history_step_payload
         where  rhs.step_type = 'CONSUMER_REQUEST'
           and rhsp.payload like '%09141%'
     ) rhsp
join request_history_step rhs on rhs.id =  rhsp.step_id
join request_history rh on rhs.request_id = rh.id
where rh.id> 35000

这将把你对request_history_step_payload表的搜索移动到一个子查询中。您可以单独优化子查询，同时尝试使整个应用程序运行速度足够快。

此外，请删除任何重复的索引。它们没有好处，只会减慢INSERT和UPDATE操作的速度。

- O. Jones

---

谢谢，@o-jones！我已经在我的答案中添加了UPD3部分，你能为它添加任何内容吗？目前我无法使用三元组（这需要数据库更改，必须等到我们的DBA从度假回来），但是仍然感觉有可能在这里进行改进。你认为呢？ - 62mkv

当然，“WHERE col LIKE '%constant%'”是一个臭名昭著的性能反模式。PostgreSQL有三元索引来帮助减轻它的影响。但你以后最好的选择始终是避免它。如果你认为你的系统会扩大规模，重组你的数据很明智。 - O. Jones

不得不承认，现在这个程序运行速度_非常快_。很酷的是，我甚至认识写这个扩展的人——Oleg Bartunov。如果他是喜欢啤酒的人（他不是），我会给他几瓶啤酒作为奖励。 - 62mkv