如何在Spring Data、Jpa和postgres中使用索引使原生查询更快

Question

我尝试使用JPA原生查询和PostgreSQL搜索包含数百万条记录的连接表，但结果太慢。我想让它更快使用索引搜索postgres或任何更好的选择，可以建议。我在网上查找了几篇这样的文章：https://hashrocket.com/blog/posts/exploring-postgres-gin-index

但我不太确定如何实现它，因为我没有得到提高的速度。

以下是我的JPA存储库中的内容：

 @Query(value = "SELECT DISTINCT C.APPROVED_NAME AS companyName, C.CITY AS city, C.STATE AS state, 
FROM TABLE_NAME_1 C LEFT JOIN TABLE_NAME_2 AS A ON A.COMPANY_FK = C.ID 
WHERE CONCAT(UPPER(A.FIRSTNAME), ' ', UPPER(A.SURNAME))
 LIKE UPPER(concat(:searchTerm,'%')) ", nativeQuery = true)
    List<table_1_Interface> findByName(@Param("searchTerm") String searchTerm);

下面是我尝试使用索引搜索所做的事情(我对使用索引搜索还很陌生)：

 @Query(value = "CREATE INDEX company_search_idx ON TABLE_NAME_2(firstname, surname );
SELECT DISTINCT C.APPROVED_NAME AS companyName, C.CITY AS city, C.STATE AS state, 
FROM TABLE_NAME_1 C LEFT JOIN TABLE_NAME_2 AS A ON A.COMPANY_FK = C.ID 
WHERE CONCAT(UPPER(A.FIRSTNAME), ' ', UPPER(A.SURNAME))
 LIKE UPPER(concat(:searchTerm,'%')) ", nativeQuery = true)
    List<table_1_Interface> findByName(@Param("searchTerm") String searchTerm);

请帮助我与您的建议，想法和解决方案。

Answer 1

如果您在CONCAT(UPPER(A.FIRSTNAME), ' ', UPPER(A.SURNAME))上进行搜索，那么就需要在此基础上构建索引(但不包括表别名)。但对于pg_trgm，它直接支持ILIKE，所以你可以直接使用它，而不是手动摆弄大小写。

由于只将%添加到搜索词的末尾，因此可以使用带有text_pattern_ops的btree索引。但这样你就需要用LIKE做鞋面了。