英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论