我正在尝试优化 PostgreSQL 15 上的慢速查询。查询非常复杂,有许多 JOIN、UNION 和子查询,但 99% 的执行时间都在最里面的节点,即 seq 使用 LIKE 条件扫描 700K 记录表。总执行时间约为 15 秒。
从查询中提取表和 WHERE 条件:
SELECT * FROM orders WHERE tag ILIKE '%EJ080EJ%';
Gather (cost=1000.00..18961.33 rows=27 width=454) (actual time=60.552..69.402 rows=0 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Parallel Seq Scan on orders (cost=0.00..17958.63 rows=9 width=454) (actual time=47.991..47.992 rows=0 loops=4)
Filter: ((tag)::text ~~* '%EJ080EJ%'::text)
Rows Removed by Filter: 74044
Planning Time: 0.732 ms
Execution Time: 69.439 ms
因此,我使用 GIN 和 pg_trgm 扩展添加了一个索引,根据我的经验,该索引对于 LIKE 条件很有用:
CREATE INDEX tmp_dba2 ON orders USING GIN (tag gin_trgm_ops);
Bitmap Heap Scan on orders(cost=18.91..48.84 rows=27 width=454) (actual time=0.061..0.062 rows=0 loops=1)
Recheck Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
-> Bitmap Index Scan on tmp_dba2 (cost=0.00..18.90 rows=27 width=0) (actual time=0.060..0.060 rows=0 loops=1)
Index Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
Planning Time: 0.947 ms
Execution Time: 0.090 ms
到目前为止,一切都很好。无论如何,执行整个查询时都会使用索引,但执行时间不会改变太多:
-> Bitmap Heap Scan on orders (cost=19.22..94.62 rows=68 width=225) (actual time=13495.733..13495.740 rows=2 loops=1)
Recheck Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
Heap Blocks: exact=2
Buffers: shared hit=21
-> Bitmap Index Scan on tmp_dba2 (cost=0.00..19.21 rows=68 width=0) (actual time=0.087..0.087 rows=2 loops=1)
Index Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
Buffers: shared hit=19
根据我对 Postgres 内部结构的(较差)理解,这意味着索引扫描检索满足条件(2 条记录)的堆页面的位图,然后堆扫描读取页面以检索数据(2 条记录)。
我不明白为什么读取2条记录需要13秒,就像seq扫描一样,所以添加索引似乎没有用。
谁能帮助我理解这种行为?
可能发生的情况是,查询的这一部分因 JIT 活动占用的所有时间而受到不公平的指责。关闭 JIT,因为无论如何它可能对您没有帮助。
如果我能看到整个计划,而不仅仅是从中提取的几行,我会对这个诊断更有信心。如果不是 JIT,那么我们无论如何都需要查看整个计划才能提出替代解释。