PostgreSQL使用JDBC查询时速度缓慢，但在同一台服务器上使用PSQL运行时速度很快。

Question

我正在使用JDBC运行以下非常简单的查询，但花费了过多的时间。数据库是AWS RDS服务器。rt2表约有60万条记录，CM2表约有30万条记录。查询返回11230行。

SELECT cm2.target 
from sysmgmt.sys_root rt2 
   join cmgmt.member cm2 on cm2.cmid = rt2.cmid and cm2.version=rt2.work_version_id 
where rt2.tid=1001 
  and rt2.proj='d791194b-f2b7-42a7-aba7-f879e052e59d'::uuid 
  and rt2.deleted = false 
  and cm2.tid=1001 and cm2.proj = 'd791194b-f2b7-42a7-aba7-f879e052e59d'::uuid;

当我使用JDBC调用运行此查询时，需要40秒钟！ 但是，如果我在同一台机器上的PSQL命令行中运行完全相同的查询，则几乎瞬间完成。

运行EXPLAIN ANALYZE显示以下计划。

Nested Loop  (cost=0.85..7.77 rows=1 width=176) (actual time=0.030..36.067 rows=11230 loops=1)
->  Index Scan using m_cell_tid_proj_version_idx on member cm2  (cost=0.42..3.32 rows=1 width=197) (actual time=0.020..2.988 rows=11230 loops=1)
    Index Cond: ((tid = '1001'::numeric) AND (proj = 'ed1a7c79-a3a1-4d8e-815b-0fbbcbd7bf4b'::uuid))
->  Index Scan using sys_root_cmid_workversion_idx on sys_root rt2  (cost=0.42..4.45 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=11230)
    Index Cond: ((cmid = cm2.cmid) AND (work_version_id = cm2.version))
    Filter: ((NOT deleted) AND (tid = '1001'::numeric) AND (proj = 'ed1a7c79-a3a1-4d8e-815b-0fbbcbd7bf4b'::uuid))
Planning Time: 0.374 ms
Execution Time: 36.499 ms

我尝试了一些加速它的方法。

• 重新排列查询语句
• 添加更匹配的索引
• 更改填充因子（似乎没有任何效果）
• VACUUM

这些方法都没有任何效果。Java代码非常简单，运行查询，然后迭代结果。计时是在执行查询之前和之后进行的。

  Took :[40644.067138] Comment:found 11230 SQL Query:[SELECT cm2.target from sysmgmt.sys_root rt2 join mgmt.member cm2 on cm2.cmid = rt2.cmid and cm2.version=rt2.work_version_id where rt2.tid=1001 and rt2.proj='ed1a7c79-a3a1-4d8e-815b-0fbbcbd7bf4b'::uuid and cm2.tid=1001 and cm2.proj = 'ed1a7c79-a3a1-4d8e-815b-0fbbcbd7bf4b'::uuid and rt2.deleted = false]

在同一事务中运行了大约5到10个其他查询，这些查询可能会导致该查询在下游出现问题吗？

如果有任何人对可能存在的问题有任何想法，我将感激不尽。

- Todd Patch

---

需要下载多少数据？假设它是一个 text 列，您可以在 psql 中选择 select sum(length(cm2.target))。 - Mike Organek

使用 sum(length(cm2.target)) 返回了 1931560。 - Todd Patch

@a_horse_with_no_name，我在评论中忘记标记你了。感谢您清理计划格式。 - Todd Patch

@MikeOrganek 我忘记标记你了。这是一个文本列，大小似乎不算过大。在同一台机器上运行PSQL会立即返回结果。 - Todd Patch

发出记录消息的Java代码有多紧密地与查询相关？你能贴出那个代码块吗？如果不行，你能确保从executeQuery()之前立即到之后立即的时间测量吗？ - Mike Organek

Answer 1

结果表明，当在活动上下文中运行时，Explain Analyze计划与独立请求运行时不同。通过增加auto_explain参数并将其记录到Postgres日志文件中，显示该计划与独立请求不同时。那么问题就变成了“如何做正确的事情？”答案是将default_statistics_target从100增加到200，并对数据库运行ANALYZE。我还重新排列了连接表顺序。通过这两步操作，问题已经消失（希望是永久的）。本维基https://wiki.postgresql.org/wiki/Performance_Optimization也被证明是一个很好的资源。

- Todd Patch

---

我还重新排列了连接表的顺序。但我怀疑这是否有任何改变。 - user330315