在Postgres 9.1数据库中,我有一个包含~1.5M行的表table1和一个列标签(为了这个问题,简化的名称).
下层有一个功能性的三元组索引(unaccent(label))(unaccent()已经变为不可变,允许在索引中使用它).
以下查询非常快：
SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')));
count
-------
1
(1 row)
Time: 394,295 ms
但是以下查询较慢：
SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword and some more%')));
count
-------
1
(1 row)
Time: 1405,749 ms
我尝试了一个简单的技巧来为第一个单词运行子查询,然后使用完整的搜索字符串运行查询,但是(遗憾的是)查询计划程序通过我的阴谋查看：
EXPLAIN ANALYZE
SELECT * FROM (
SELECT id,title,label from table1
WHERE lower(unaccent(label)) like lower(unaccent('%someword%'))
) t1
WHERE lower(unaccent(label)) like lower(unaccent('%someword and some more%'));
Bitmap Heap Scan on table1 (cost=16216.01..16220.04 rows=1 width=212) (actual time=1824.017..1824.019 rows=1 loops=1)
Recheck Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more%'::text))
-> Bitmap Index Scan on table1_label_hun_gin_trgm (cost=0.00..16216.01 rows=1 width=0) (actual time=1823.900..1823.900 rows=1 loops=1)
Index Cond: ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword and some more%'::text))
Total runtime: 1824.064 ms
我的最终问题是搜索字符串来自一个Web界面,它可能发送很长的字符串,因此非常慢,也可能构成一个DOS向量.
所以我的问题是：
>如何加快查询速度？
>有没有办法将其分解为子查询,以便更快？
>也许后期版本的Postgres更好？ (我尝试了9.4并且它似乎没有更快：仍然是相同的效果.也许是更高版本？)
>也许需要一种不同的索引策略？
在Postgresql 9.6中将会有一个新版本的pg_trgm,1.2,这将更好.只需稍加努力,您也可以在Postgresql 9.4下使用这个新版本(您必须应用补丁,并自行编译扩展模块并安装它).
最旧版本的作用是在查询中搜索每个trigram并获取它们的并集,然后应用过滤器.新版本将做的是在查询中选择最稀有的三元组并仅搜索那个,然后在以后过滤其余部分.
9.1中没有这样做的机器.在9.4中添加了机器,但是当时pg_trgm不适合使用它.
你仍然会有潜在的DOS问题,因为恶意的人可以制作一个只有共同三元组的查询.比如’％and％’,甚至’％a％’
如果你不能升级到pg_trgm 1.2,那么欺骗规划者的另一种方法是：
WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')))
AND (lower(unaccent(label||'')) like
lower(unaccent('%someword and some more%')));
通过将空字符串连接到标签,您可以让计划程序认为它不能在where子句的那一部分上使用索引.所以它只在％someword％上使用索引,并对这些行应用过滤器.
此外,如果您总是在搜索整个单词,则可以使用函数将字符串标记为单词数组,并在该数组返回函数上使用常规内置GIN索引(不是pg_trgm).
