postgresql - EXPLAIN ANALYZE QUERY 如何用于测量查询性能？

Question

今天我尝试使用 explain analyze 来提取查询的执行时间。我希望在不受查询延迟影响的情况下正确计算它们的执行时间，但是在 documentation 中它指出存在分析开销:

In order to measure the run-time cost of each node in the execution plan, the current implementation of EXPLAIN ANALYZE adds profiling overhead to query execution. As a result, running EXPLAIN ANALYZE on a query can sometimes take significantly longer than executing the query normally. The amount of overhead depends on the nature of the query, as well as the platform being used. The worst case occurs for plan nodes that in themselves require very little time per execution, and on machines that have relatively slow operating system calls for obtaining the time of day.

因此，我质疑 explain analyze 的有用性，因为您似乎只是用分析开销来交换延迟。更令人惊讶的是，在代码中计时我的数据库查询(在我的例子中是 Python，使用 psycopg2 库)导致查询执行时间更短。

Answer 1

EXPLAIN (ANALYZE) 很有用，不是因为它测量执行时间的准确性，而是因为它告诉您大部分执行时间花在了哪里以及 PostgreSQL 的估计在哪里。