我的桌子看起来像:
create table invoices
(
id serial not null,
data jsonb,
modified date,
search_string text not null
);
我需要用
ILIKE
打开search_string
来搜索表。同一请求中可能有许多不同的搜索查询。
我的问题是:
SELECT *
FROM invoices
WHERE (
search_string ILIKE '%1%'
OR search_string ILIKE '%2%'
OR search_string ILIKE '%3%'
)
解释不带索引的搜索
Seq Scan on invoices (cost=0.00..147139.51 rows=1004406 width=1006) (actual time=0.038..2341.489 rows=1004228 loops=1)
Filter: ((search_string ~~* '%1%'::text) OR (search_string ~~* '%2%'::text) OR (search_string ~~* '%3%'::text))
Rows Removed by Filter: 1943
Planning Time: 4.682 ms
Execution Time: 2427.400 ms
我试图通过创建GIN索引来加快速度:
CREATE EXTENSION pg_trgm;
CREATE INDEX invoices_search_string_trigram_index ON invoices USING gin (search_string gin_trgm_ops);
解释如何使用索引进行搜索
Bitmap Heap Scan on invoices_invoice (cost=414767.41..561902.40 rows=1004149 width=1006) (actual time=14878.331..17862.840 rows=1004228 loops=1)
Recheck Cond: ((search_string ~~* '%1%'::text) OR (search_string ~~* '%2%'::text) OR (search_string ~~* '%3%'::text))
Rows Removed by Index Recheck: 1943
Heap Blocks: exact=63341 lossy=66186
-> BitmapOr (cost=414767.41..414767.41 rows=1006171 width=0) (actual time=14842.199..14842.199 rows=0 loops=1)
-> Bitmap Index Scan on trgm_idx_search_string (cost=0.00..137979.36 rows=874048 width=0) (actual time=4520.466..4520.466 rows=546232 loops=1)
Index Cond: (search_string ~~* '%1%'::text)
-> Bitmap Index Scan on trgm_idx_search_string (cost=0.00..138208.03 rows=904538 width=0) (actual time=4357.453..4357.453 rows=546232 loops=1)
Index Cond: (search_string ~~* '%2%'::text)
-> Bitmap Index Scan on trgm_idx_search_string (cost=0.00..137826.91 rows=853721 width=0) (actual time=5964.276..5964.276 rows=546232 loops=1)
Index Cond: (search_string ~~* '%3%'::text)
Planning Time: 1.198 ms
Execution Time: 17971.102 ms
为什么我的索引搜索比seq scan慢?
有什么方法可以使这种搜索更快吗?
最佳答案
你的问题可能是66186块有损。增加work_mem
直到只有精确的块。
考虑到您有一百万个结果行,我认为除非您减少结果行的数量,否则这个查询永远不会很快。
关于sql - PostgreSQL位图堆扫描慢,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/55903698/