PostgreSQL。日志文件中的慢查询在 psql 中很快

Question

我有一个使用 Hibernate(默认 C3P0 连接池)和 PostgreSQL 数据库 (9.1) 在 Play Framework 1.2.4 上编写的应用程序。

最近我在 postgresql.conf 中打开了慢速查询日志记录(>= 100 毫秒)并发现了一些问题。

但是当我尝试分析和优化一个特定查询时，我发现它在 psql 中的速度非常快(0.5 - 1 毫秒)，而在日志中则为 200-250 毫秒。其他查询也发生了同样的事情。

应用程序和数据库服务器在同一台机器上运行，并使用本地主机接口(interface)进行通信。

JDBC 驱动程序 - postgresql-9.0-801.jdbc4

我想知道哪里出了问题，因为日志中的查询持续时间是在计算时仅考虑数据库处理时间，不包括网络周转等外部因素。

Answer 1

可能性 1:如果慢查询偶尔或突发，则可能是检查点事件。启用检查点日志记录 (log_checkpoints = on)，确保日志级别 (log_min_messages) 为“信息”或更低，然后查看出现的情况。花费很长时间或发生得太频繁的检查点表明您可能需要一些检查点/WAL 和 bgwriter 调整。如果相同的语句总是很慢而其他语句总是执行良好，则这不太可能是原因。

可能性 2:您的查询计划不同，因为您直接在 psql 中运行它们，而 Hibernate 通过 PgJDBC，至少有时会执行 PREPARE 和 EXECUTE(在协议(protocol)级别，因此您不会看到实际语句)。为此，将查询性能与 PREPARE test_query(...) AS SELECT ... 和 EXPLAIN ANALYZE EXECUTE test_query(...) 进行比较。 PREPARE 中的参数是位置参数的类型名称($1、$2 等)； EXECUTE 中的参数是值。

如果准备好的方案与一次性方案不同，可以设置PgJDBC's prepare threshold通过连接参数告诉它永远不要使用服务器端准备好的语句。

准备语句和未准备语句的计划之间的差异 should go away in PostgreSQL 9.2 .这是一个长期存在的问题，但 Tom Lane 在即将发布的版本中解决了它。