Postgres EXPLAIN ANALYZE 比正常运行查询快得多
我正在尝试优化 PostgreSQL 8.4 查询。在极大地简化了原始查询之后，试图找出是什么让它选择了一个错误的查询计划，我发现在 EXPLAIN ANALYZE 下运行查询只需要 0.5 秒，而运行它通常需要 2.8 秒。那么很明显，EXPLAIN ANALYZE 向我展示的内容并不是它通常所做的，所以它向我展示的任何内容都是无用的，不是吗？这里发生了什么以及我如何了解它到底在做什么？
I'm trying to optimise a PostgreSQL 8.4 query. After greatly simplifying the original query, trying to figure out what's making it choose a bad query plan, I got to the point where running the query under EXPLAIN ANALYZE takes only 0.5s, while running it normally takes 2.8s. It seems obvious then, that what EXPLAIN ANALYZE is showing me is not what it normally does, so whatever it's showing me is useless, isn't it? What is going on here and how do I see what it's really doing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当您手动运行 EXPLAIN ANALYZE 以尝试和优化查询时，数据页很可能位于操作系统磁盘缓存中。在正常环境中运行时，页面可能不在缓存中，必须从磁盘中获取，从而增加了运行时间。
Most likely, the data pages are in the OS disk cache when you are manually running with EXPLAIN ANALYZE in order to try and optimize the query. When run in a normal environment, the pages probably aren't in the cache already and have to be fetched from disk, increasing the runtime.
它显示的时间较少，因为：
1)
EXPLAIN ANALYZE显示的总运行时间包括执行器启动和关闭时间，以及运行任何触发器的时间被解雇，但不包括解析、重写或规划时间。2）由于没有输出行传送到客户端，因此不包括网络传输成本和I/O转换成本。
警告！
EXPLAIN ANALYZE 增加的测量开销可能很大，尤其是在 gettimeofday() 操作系统调用速度较慢的机器上。因此，建议使用
EXPLAIN (ANALYZE TRUE, TIMING FALSE)。It shows less time because:
1) The Total runtime shown by
EXPLAIN ANALYZEincludes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time.2)Since no output rows are delivered to the client, network transmission costs and I/O conversion costs are not included.
Warning!
The measurement overhead added by EXPLAIN ANALYZE can be significant, especially on machines with slow gettimeofday() operating-system calls. So, it's advisable to use
EXPLAIN (ANALYZE TRUE, TIMING FALSE).