postgres的 Planning Time和Execution Time区别

转载已于 2024-09-27 11:27:46 修改 · 2.2k 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://stackoverflow.com/questions/33549914/why-planing-time-and-execution-time-are-so-different-postgres

文章标签：

#c语言

于 2021-09-09 13:49:01 首次发布

postgres 专栏收录该内容

4 篇文章

订阅专栏

PlanningTime和ExecutionTime是衡量数据库查询性能的两个关键指标。PlanningTime指的是查询优化器寻找最佳执行计划的时间，而ExecutionTime则是实际执行查询计划并返回结果所需的时间。这两者之间的差异在于优化器的估算与实际执行的复杂性。了解这两者的区别有助于优化SQL查询性能。

Planning Time和Execution Time相差很大，其实互相比较没什么意思

因为Planning Time是指定一个执行查询的最快方式（计划）所需的时间，postgres有一个 "query planner" 来实现的

而Execution Time是运行这个最快的查询计划，并且将结果输出的时间

下面国外的文章很好的解释了两者的区别：

You write a query in SQL which is some kind of "script" that you try to tell the server what you want from him.
Most of the times there is many ways for server to collect data you ask for by writing query. There is where mechanism called "query planner" comes in to action. It tries to find the quickest way (plan) of execution of your query. It's doing so by estimates execution time of several possible ways (plans).
Server runs the query using the plan which is thought as the quickest one.
Server returns you the output.

EXPLAIN command prints you description of that process. Now:

Execution time on EXPLAIN output is time server spent on steps 3 only.
Planning time on EXPLAIN output is time server spent on step 2 only. I believe you think of it as "time planner thinks that query would take", but that can be called "planned [execution] time" or "estimated execution time".

参考：postgresql - Why Planing time and Execution time are so different Postgres? - Stack Overflow