慢速运行的Postgres查询 [英] Slow running Postgres query

问题描述

此查询对我的数据库需要很长时间。该SQL是从应用程序内部的ORM（休眠）生成的。我没有源代码。

I have this query that takes a very long time on my database. This SQL is generated from an ORM (Hibernate) inside of an application. I don't have access to the source code.

我想知道是否有人可以看看下面的 ANALYZE EXPLAIN 输出并建议我可以做的任何Postgres调整。

I was wondering if anyone can take a look at the following ANALYZE EXPLAIN output and suggest any Postgres tweaks I can make.

我不知道从哪里开始或如何调整数据库以服务于该查询。

I don't know where to start or how to tune my database to service this query.

查询看起来像这样

select 
    resourceta0_.RES_ID as col_0_0_ 
from
    HFJ_RESOURCE resourceta0_ 
    left outer join HFJ_RES_LINK myresource1_ on resourceta0_.RES_ID = myresource1_.TARGET_RESOURCE_ID 
    left outer join HFJ_SPIDX_DATE myparamsda2_ on resourceta0_.RES_ID = myparamsda2_.RES_ID 
    left outer join HFJ_SPIDX_TOKEN myparamsto3_ on resourceta0_.RES_ID = myparamsto3_.RES_ID 
where 
    (myresource1_.SRC_RESOURCE_ID in ('4954427' ... many more))
    and myparamsda2_.HASH_IDENTITY=`5247847184787287691` and 
(myparamsda2_.SP_VALUE_LOW>='1950-07-01 11:30:00' or myparamsda2_.SP_VALUE_HIGH>='1950-07-01 11:30:00') 
    and myparamsda2_.HASH_IDENTITY='5247847184787287691' 
    and (myparamsda2_.SP_VALUE_LOW<='1960-06-30 12:29:59.999' or myparamsda2_.SP_VALUE_HIGH<='1960-06-30 12:29:59.999') 
    and (myparamsto3_.HASH_VALUE in ('-5305902187566578701')) 
limit '500'

执行计划如下： https://explain.depesz.com/s/EJgOq

编辑-已更新以添加depesz链接。
编辑2-添加了有关查询的更多信息。

Edit - updated to add the depesz link. Edit 2 - added more information about the query.

推荐答案

缓慢的原因是错误的行数估计这使得PostgreSQL选择嵌套循环联接。您几乎所有的时间都花在 hfj_res_link 的索引扫描上，重复1113次。

The cause for the slowness are the bad row count estimates which make PostgreSQL choose a nested loop join. Almost all your time is spent in the index scan on hfj_res_link, which is repeated 1113 times.

我的第一次尝试将是 ANALYZE hfj_spidx_date 并查看是否有帮助。如果是，请确保自动分析会更频繁地处理该表。

My first attempt would be to ANALYZE hfj_spidx_date and see if that helps. If yes, make sure that autoanalyze treats that table more frequently.

下一个尝试是

SET default_statistics_target = 1000;

，然后如上所述进行 ANALYZE 。如果有帮助，请使用 ALTER TABLE 增加 hash_identity STATISTICS $ c>和 sp_value_high 列。

and then ANALYZE as above. If that helps, use ALTER TABLE to increase the STATISTICS on the hash_identity and sp_value_high columns.

如果这也无济于事，并且您有最新版本的PostgreSQL，您可以尝试扩展统计信息：

If that doesn't help either, and you have a recent version of PostgreSQL, you could try extended statistics:

CREATE STATISTICS myparamsda2_stats (dependencies)
   ON hash_identity, sp_value_high FROM hfj_spidx_date;

然后再次 ANALYZE 查看表

如果所有这些都无济于事，并且您无法正确估算，则必须尝试其他角度：

If all that doesn't help, and you cannot get the estimates correct, you have to try a different angle:

CREATE INDEX ON hfj_res_link (target_resource_id, src_resource_id);

这应该大大加快索引扫描的速度，并为您提供良好的响应时间。

That should speed up the index scan considerably and give you good response times.

最后，如果以上都不起作用，则可以使用禁止查询的嵌套循环联接的严格措施：

Finally, if none of the above has any effect, you could use the cruse measure of disallowing nested loop joins for this query:

BEGIN;
SET LOCAL enable_nestloop = off;
SELECT /* your query goes here */;
COMMIT;

这篇关于慢速运行的Postgres查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！