PostgreSQL多次请求后数据库查询变慢问题排查求助

以下是结合你的技术栈（Spring Boot+Hibernate+Hikari+PostgreSQL），针对问题场景的可能原因：

1. 连接池复用的“脏连接”残留状态 #

Hikari默认连接池大小通常为10（maximumPoolSize默认值），刚好对应你遇到的“9次正常后第10次变慢”的规律。当某个连接执行带like 'text%'的查询后，可能残留未关闭的游标、未提交/回滚的事务，或是PostgreSQL端的连接状态异常（比如索引扫描后的页锁残留）。当连接被放回池子里复用，第10次请求刚好拿到这个“脏连接”，导致查询阻塞。手动重连相当于丢弃所有旧连接，新建的连接无状态残留，问题自然消失。

验证方向：

• 查看Hikari配置的maximumPoolSize是否为10；
• 启用PostgreSQL的pg_stat_activity视图，慢请求时查看对应连接状态，是否存在idle in transaction或等待锁的情况；
• 调整Hikari的connectionTestQuery为SELECT 1，确保连接返回池时做严格有效性验证，过滤状态异常的连接。

2. PostgreSQL查询计划缓存与执行计划退化 #

带like 'text%'的前缀查询本可利用B-tree索引，但如果Hibernate未正确参数化查询（比如直接拼接SQL而非使用绑定参数），或是PostgreSQL的统计信息在循环请求中发生变化，可能导致查询计划缓存失效。第10次请求时，PostgreSQL选择了低效的执行计划（比如全表扫描），导致耗时剧增。而去掉like条件后，查询逻辑简单，执行计划稳定，不会出现退化。

验证方向：

• 开启PostgreSQL慢查询日志，对比前9次和第10次查询的执行计划，看是否出现索引扫描到全表扫描的切换；
• 检查Hibernate的查询语句，确保like条件使用绑定参数（比如like :prefix || '%'）而非硬编码字符串，避免每次生成新SQL导致计划无法缓存。

3. Hibernate会话/事务管理不规范 #

如果REST服务中的数据库操作未正确管理Hibernate会话和事务，比如会话未关闭、事务未提交/回滚，连接回到Hikari池时仍处于活跃事务状态。后续复用该连接的查询会被事务状态影响，比如需要等待锁或触发额外事务处理逻辑，导致延迟。手动重连会重置所有连接的事务状态，解决问题。

验证方向：

• 检查数据库操作方法是否正确添加@Transactional注解，确保事务边界清晰；
• 确认Hibernate会话在查询结束后被正确关闭（比如通过EntityManager.close()或Spring自动管理）。

4. PostgreSQL索引资源争用 #

like 'text%'查询会触发B-tree索引的范围扫描，如果循环请求的查询条件集中在索引的某个热点区间，可能导致PostgreSQL的索引页锁竞争。当某个连接持有锁未释放，后续复用该连接的请求需要等待锁，出现延迟。重连后新连接不会持有旧锁，恢复正常。

验证方向：

• 查看PostgreSQL的pg_locks视图，慢请求时是否存在等待索引页锁的情况；
• 尝试对查询字段添加更合适的索引（比如部分索引、表达式索引），减少锁竞争概率。

内容的提问来源于stack exchange，提问作者PoweR