如何使用SpringDataJpa和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的鞋面。