psql执行计划解释,时间突增与成本比较(无突增)

huangapple go评论67阅读模式
英文:

psql execution plan interpretation, Time spike compare to Cost (no spike)

问题

能帮助我解释一下一个psql执行计划吗?我是一名Java开发者,而不是数据库工程师,无法理解下面的内容。

我有一个大型SQL查询(实际的SQL内容可能不太重要),但它的形式如下:

SELECT DISTINCT someFields
FROM (巨大的内部查询)
ORDER BY someField
LIMIT 1000 OFFSET 0

我已经为这个请求创建了一个执行计划,并收到了以下计划:

     节点类型: "Limit"
     启动成本: 1281955.01 - 与Unique步骤中的成本相同
     总成本: 1281960.45
     实际启动时间: 218620.633 - 这里
     实际总时间: 218630.771 - 这与之前的步骤相比发生了巨大变化,而成本几乎没有变化,为什么会这样?
     实际行数: 93
     实际循环次数: 1
       - 节点类型: "Unique"
         启动成本: 1281955.01
         总成本: 1281960.45
         实际启动时间: 4000.387 - 与此相比
         实际总时间: 4010.517 - 以及这个
         实际行数: 93
         实际循环次数: 1
           - 节点类型: "Sort"
             启动成本: 1281955.01
             总成本: 1281955.26
             实际启动时间: 4000.380
             实际总时间: 4010.420
             实际行数: 93
             实际循环次数: 1
...
然后成本和时间都朝着相同的方向变化,没有出现峰值和大的差异
...
   JIT:
     工作线程号: -1
     函数数: 10479
     选项:
       内联: true
       优化: true
       表达式: true
       变形: true
     时间:
       生成: 2737.565
       内联: 339.475
       优化: 124066.840
       发出: 91849.576
       总计: 218993.455
   执行时间: 221597.690

我感到困惑的是“时间”(从4000.380到218620.633)在“节点类型: Limit”中出现了巨大的峰值,但成本没有出现相同的峰值(我猜想,也许我错了),它们是不是表示不同单位的相同值?

这是Postgres的正常行为吗,还是显示了一些奇怪的情况?

我假设我的问题出现在“Limit”步骤上,它增加了214秒的处理时间。
因此,我尝试从查询中删除LIMIT,但没有帮助,所以我有点困惑这个峰值代表什么以及如何解释它。

谢谢。

英文:

could you help me to interpret a psql execution plan, i'm a java developer, rather then DB engineer and can't get the below.

I have a big sql (the actual sql is not that important i guess), but it form is

SELECT DISTINCT someFields
FROM (huge inner select)
ORDER BY someField
LIMIT 1000 OFFSET 0

I've made an execution plan to the request and have received the following plan:

     Node Type: "Limit"
     Startup Cost: 1281955.01 - here the same Cost as in Unique step
     Total Cost: 1281960.45
     Actual Startup Time: 218620.633 - this 
     Actual Total Time: 218630.771.  - and this changed dramatically compared to previous step, while the Costs, almost not changed, why so?
     Actual Rows: 93
     Actual Loops: 1
       - Node Type: "Unique"
         Startup Cost: 1281955.01
         Total Cost: 1281960.45
         Actual Startup Time: 4000.387 - compared to this
         Actual Total Time: 4010.517.  - and this
         Actual Rows: 93
         Actual Loops: 1
           - Node Type: "Sort"
             Startup Cost: 1281955.01
             Total Cost: 1281955.26
             Actual Startup Time: 4000.380
             Actual Total Time: 4010.420
             Actual Rows: 93
             Actual Loops: 1
...
then the costs and Time are moving in the same direction without spikes and big differences
...
   JIT:
     Worker Number: -1
     Functions: 10479
     Options:
       Inlining: true
       Optimization: true
       Expressions: true
       Deforming: true
     Timing:
       Generation: 2737.565
       Inlining: 339.475
       Optimization: 124066.840
       Emission: 91849.576
       Total: 218993.455
   Execution Time: 221597.690

And what i'm confused is the huge spike in 'Time' (from 4000.380 to 218620.633) for the Node Type: Limit, but the absence of the same spike for the Cost (i supposed, maybe i'm wrong) that they are sort of the same value, but shown in different units

Is this a normal behaviour for postgres, or it shows me that smth strange happening?

I've assumed that my issue is the Limit step, which increases processing time on 214 seconds
Therefore i've tried to remove the LIMIT from the query, but it didn't help, so i'm bit confused what this spike means and how to interpet it.

Thank you.

答案1

得分: 1

JIT占据了几乎所有的查询时间,而它所花费的时间完全解释了“唯一”和“限制”之间实际启动时间差距出现在不直观的计划部分的原因,但是当涉及到JIT时,奇怪的事情是正常的。只需关闭JIT。如果您无法让数据库管理员在整个服务器上关闭它,至少可以为您自己的用户关闭它:

alter user your_name_here set jit = off;

(这将在您下次注销并重新登录时生效)

英文:

JIT is taking pretty much all the time for that query, and the time it takes fully explains the gap in actual startup times between the "Unique" and the "Limit". I don't know why the gap shows up in that unintuitive spot in the plan, but weirdness is normal when JIT is involved. Just turn jit off. If you can't get your DBA to turn it off server-wide, you can at least turn it off just for your own user:

alter user your_name_here set jit = off;

(Which will take affect next time you log out and back in)

huangapple
  • 本文由 发表于 2023年6月27日 20:02:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76564666.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定