postgresql - 优化Postgres相似性查询(pg_trgm + Gin 指数)

我定义了以下索引:

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对该功能进行了现有的实现，对于任何查询，它都会在
我想知道是否有什么方法可以改进此方法以获得更一致的性能？

据我了解，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/[email protected]

所以我尝试了

-> SHOW work_mem;
4MB
-> SET work_mem='12MB';
-> EXECUTE user_search('mel', 20);
(results returned in ~1.5s)

这有了很大的改进(以前> 10s)!

对于类似的查询，1.5s仍然比ES慢得多，因此我仍然想听听有关优化查询的任何建议。

2。

为了回应评论，并在看到了这个问题(Postgresql GIN index slower than GIST for pg_trgm)之后，我尝试了使用GIST索引代替GIN索引的完全相同的设置。

尝试以上相同的搜索，它使用默认的work_mem='4MB'在〜3.5s内返回。增加work_mem没什么区别。

由此得出的结论是，GIST索引具有更高的内存效率(没有像GIN那样遇到病理情况)，但是在GIN正常工作时，它比GIN慢。这与docs建议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上花费了将近一整秒的时间。

最佳答案

我希望通过这种方法可以使更快:
1。
创建一个Gist索引，其中1列包含连接的值:

CREATE INDEX users_search_idx ON auth_user
USING gist((username || ' ' || first_name || ' ' || last_name) gist_trgm_ops);

假定所有3列都定义为NOT NULL(您未指定)。否则，您需要做更多的事情。
为什么不使用concat_ws()简化呢？

Combine two columns and add into one new column

Faster query with pattern-matching on multiple text fields

Combine two columns and add into one new column

2。
使用正确的nearest-neighbor查询，匹配上面的索引:

SELECT username, email, first_name, last_name
     , similarity(username  , $1) AS s_username
     , similarity(first_name, $1) AS s_first_name
     , similarity(last_name , $1) AS s_last_name
     , row_number() OVER () AS rank  -- greatest similarity first
FROM   auth_user
WHERE     (username || ' ' || first_name || ' ' || last_name) %   $1  -- !!
ORDER  BY (username || ' ' || first_name || ' ' || last_name) <-> $1  -- !!
LIMIT  $2;

WHERE和ORDER BY中的表达式必须与索引表达式匹配!
特别是ORDER BY rank(就像您曾经使用过的那样)对于从更大的合格行池中选择较小的LIMIT总是表现不佳，因为它不能直接使用索引:rank背后的复杂表达式必须为每个合格行计算，然后所有这些都必须先进行排序，然后才能返回一小部分最佳匹配项。这比要多得多，而则比真正的最近邻居查询要昂贵得多，后者可以直接从索引中选择最佳结果，而无需查看其余部分。
窗口定义为空的row_number()仅反射(reflect)相同ORDER BY的SELECT产生的顺序。
相关答案:

Best index for similarity function

Search in 300 million addresses with pg_trgm

至于您的项目3.，我为您引用的问题添加了答案，应该可以对其进行解释:

PostgreSQL GIN index slower than GIST for pg_trgm?

关于postgresql - 优化Postgres相似性查询(pg_trgm + Gin 指数)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43867449/