我定义了以下指数:
CREATE INDEX
users_search_idx
ON
auth_user
USING
gin(
username gin_trgm_ops,
first_name gin_trgm_ops,
last_name gin_trgm_ops
);
我正在执行以下查询:
PREPARE user_search (TEXT, INT) AS
SELECT
username,
email,
first_name,
last_name,
( -- would probably do per-field weightings here
s_username + s_first_name + s_last_name
) rank
FROM
auth_user,
similarity(username, $1) s_username,
similarity(first_name, $1) s_first_name,
similarity(last_name, $1) s_last_name
WHERE
username % $1 OR
first_name % $1 OR
last_name % $1
ORDER BY
rank DESC
LIMIT $2;
auth_user
表有620万行。
查询速度看起来非常依赖于similarity
查询可能返回的结果数量。
通过使用set_limit
增加相似度阈值可以提高速度,但是通过消除部分匹配减少了结果的实用性。
某些搜索需要200毫秒,而其他搜索需要大约10秒。
我们已经使用Elasticsearch对此功能进行了现有实现,对于任何查询都可以在<200毫秒内返回,同时进行更复杂(更好)的排名。
我想知道是否有任何方法可以改进这一点,以获得更一致的性能?
据我所知,GIN索引(倒排索引)是由Elasticsearch使用的相同基本方法,因此我认为可能存在一些优化。
EXPLAIN ANALYZE EXECUTE user_search('mel', 20)
显示:
Limit (cost=54099.81..54099.86 rows=20 width=52) (actual time=10302.092..10302.104 rows=20 loops=1)
-> Sort (cost=54099.81..54146.66 rows=18739 width=52) (actual time=10302.091..10302.095 rows=20 loops=1)
Sort Key: (((s_username.s_username + s_first_name.s_first_name) + s_last_name.s_last_name)) DESC
Sort Method: top-N heapsort Memory: 26kB
-> Nested Loop (cost=382.74..53601.17 rows=18739 width=52) (actual time=118.164..10293.765 rows=8380 loops=1)
-> Nested Loop (cost=382.74..53132.69 rows=18739 width=56) (actual time=118.150..10262.804 rows=8380 loops=1)
-> Nested Loop (cost=382.74..52757.91 rows=18739 width=52) (actual time=118.142..10233.990 rows=8380 loops=1)
-> Bitmap Heap Scan on auth_user (cost=382.74..52383.13 rows=18739 width=48) (actual time=118.128..10186.816 rows=8380loops=1)"
Recheck Cond: (((username)::text % 'mel'::text) OR ((first_name)::text % 'mel'::text) OR ((last_name)::text %'mel'::text))"
Rows Removed by Index Recheck: 2434523
Heap Blocks: exact=49337 lossy=53104
-> BitmapOr (cost=382.74..382.74 rows=18757 width=0) (actual time=107.436..107.436 rows=0 loops=1)
-> Bitmap Index Scan on users_search_idx (cost=0.00..122.89 rows=6252 width=0) (actual time=40.200..40.200rows=88908 loops=1)"
Index Cond: ((username)::text % 'mel'::text)
-> Bitmap Index Scan on users_search_idx (cost=0.00..122.89 rows=6252 width=0) (actual time=43.847..43.847rows=102028 loops=1)"
Index Cond: ((first_name)::text % 'mel'::text)
-> Bitmap Index Scan on users_search_idx (cost=0.00..122.89 rows=6252 width=0) (actual time=23.387..23.387rows=58740 loops=1)"
Index Cond: ((last_name)::text % 'mel'::text)
-> Function Scan on similarity s_username (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=8380)
-> Function Scan on similarity s_first_name (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8380)
-> Function Scan on similarity s_last_name (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=8380)
Execution time: 10302.559 ms
服务器是运行在Amazon RDS上的Postgres 9.6.1。
更新
1.发布问题后不久,我找到了这些信息:https://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com
所以我尝试了一下
-> SHOW work_mem;
4MB
-> SET work_mem='12MB';
-> EXECUTE user_search('mel', 20);
(results returned in ~1.5s)
这大大改善了原来的情况(之前大于10秒)!
虽然1.5秒仍然远慢于类似查询的ES,但我仍然希望听到对查询进行优化的任何建议。
2.回应评论后,在看到这个问题(Postgresql GIN index slower than GIST for pg_trgm)后,我尝试使用GIST索引替换了GIN索引的设置。
尝试使用上面相同的搜索,它返回约3.5秒,使用默认的work_mem='4MB'
。增加work_mem
没有任何影响。
由此我得出结论,GIST索引更具有内存效率(没有像GIN一样遇到病理情况),但当GIN正常工作时,速度比GIN慢。这与文档中推荐GIN索引的描述一致。
3.我仍然不明白为什么这么多时间花在了这里:
-> Bitmap Heap Scan on auth_user (cost=382.74..52383.13 rows=18739 width=48) (actual time=118.128..10186.816 rows=8380loops=1)"
Recheck Cond: (((username)::text % 'mel'::text) OR ((first_name)::text % 'mel'::text) OR ((last_name)::text %'mel'::text))"
Rows Removed by Index Recheck: 2434523
Heap Blocks: exact=49337 lossy=53104
我不明白为什么需要这个步骤或它在做什么。
在它下面有三个Bitmap Index Scan
,分别对应每个 username % $1
子句...然后将这些结果与BitmapOr
步骤组合。所有这些部分都非常快。
但即使在我们没有用完工作内存的情况下,我们仍然花费近一秒钟在Bitmap Heap Scan
中。
BitmapOr
(在Bitmap Heap Scan
下)的需要。 - pozs