加布里埃尔·罗斯：PGSQL 星期五 #016：查询调优
内容提要
本文介绍了调优复杂查询的步骤。首先，要反思预期结果、实际结果和问题持续时间。其次，将查询放入源代码控制以跟踪更改。然后，检查查询中使用的表是否最近进行了ANALYZE。接下来，创建一些通过和失败的测试以验证结果。然后，检查查询中的明显问题，如没有ORDER BY的LIMIT，已经具有唯一约束的列上的DISTINCT，以及可以使用UNION ALL的地方使用了UNION。如果可以获取生产环境或类似生产环境的EXPLAIN输出，可以使用https://explain.depesz.com进行分析。如果查询较大，可以将其分解为组成部分，并逐个检查。最后，通过一个具体例子介绍了使用join_collapse_limit参数来优化查询性能的方法。
延伸解读
调优前的关键准备：反思与版本控制
在动手调优前，作者强调先反思三个问题：预期结果、实际结果和问题持续时间。这有助于明确问题边界。同时，将查询纳入源代码控制，以便跟踪修改。使用pg_format统一格式，提高可读性，尤其适合多人协作的查询。这些步骤虽简单，但能避免盲目调整，为后续优化奠定基础。
利用测试与EXPLAIN分析定位问题
创建通过和失败的测试（如PgTAP）能验证优化后结果是否正确。检查表是否最近ANALYZE过，确保统计信息准确。快速扫描明显问题：LIMIT无ORDER BY、唯一约束列上的DISTINCT、UNION可用UNION ALL替代。获取生产环境EXPLAIN (ANALYZE, BUFFERS, TIMING)输出，用explain.depesz.com分析，关注估计与实际行数差异大、大数据集上循环次数多等红色区域。
分解复杂查询与检查FROM子句
对于较大查询，将其分解为CTE、子查询、UNION等组成部分，逐个检查。首先审查FROM子句，因为它是SQL执行顺序的第一步。检查是否有表在SELECT中未使用任何列，如果不需要作为连接表，尝试移除。这能减少不必要的数据处理。作者强调，可靠的测试是这一步的关键，确保移除后结果仍正确。
join_collapse_limit：控制连接顺序的利器
作者通过一个迁移案例发现join_collapse_limit参数。当连接表数量超过该值（默认8）时，规划器不会尝试所有连接顺序。在会话中将其设为1（不要全局设置），可强制Postgres遵循查询中写的连接顺序。这显著提升了查询速度。但需注意，如果数据分布发生显著变化，可能需要重新调整连接顺序。
Q&A
如何开始调优复杂的SQL查询？
首先反思预期结果、实际结果和问题持续时间。
在调优查询时，如何确保查询的可读性？
可以使用pg_format工具来提高查询的可读性。
调优查询时，如何验证查询的结果？
创建一些通过和失败的测试以验证结果。
在查询中常见的明显问题有哪些？
例如LIMIT没有ORDER BY、DISTINCT在唯一约束列上、使用UNION而非UNION ALL。
如何使用EXPLAIN分析查询性能？
获取EXPLAIN输出并使用https://explain.depesz.com进行分析。
join_collapse_limit参数如何影响查询性能？
设置join_collapse_limit可以强制Postgres尊重JOIN的书写顺序，从而提高查询速度。
