Hollis Varden：五个让 PostgreSQL 做了多余工作的查询
内容提要
文章测试了五种让 PostgreSQL 做多余工作的写法：UNION 因去重耗时 13.6 秒，而 UNION ALL 仅 0.68 秒；MATERIALIZED 强制物化 CTE 耗时 1973 毫秒；ORDER BY random() 取一行需 1313 毫秒；SELECT * 比只选索引列多一倍缓冲；WHERE 中函数无表达式索引则全表扫描。修复通常只需一个关键字、一列或一个索引。
延伸解读
小改动，大收益
文章测试的五种查询中，四种的修复只需一个关键字或一列，第五种只需一个索引。例如，UNION 改为 UNION ALL 后，耗时从 13.6 秒降至 0.68 秒；去掉 MATERIALIZED 后，CTE 查询从 1973 毫秒降至 0.24 毫秒。这些改动虽小，但在大表上效果显著，提醒我们优化时优先检查 SQL 中的关键字和索引。
开发环境中的性能陷阱
这些低效查询在小表上执行仅需几毫秒，差异难以察觉，容易在开发阶段被忽略。但随着数据量增长，性能差距会急剧放大。例如，ORDER BY random() 在 200 万行表上耗时 1313 毫秒，而 TABLESAMPLE 仅需 0.3 毫秒。因此，在开发阶段就应关注查询写法，避免埋下性能隐患。
索引必须匹配表达式
当 WHERE 子句中使用函数时，如 lower(email) = '...'，普通列索引无效，必须创建表达式索引。文章显示，无索引时全表扫描耗时 623 毫秒，创建表达式索引后降至 0.5 毫秒。这强调了索引设计需与查询条件完全一致，否则无法发挥优化作用。
测试结果的适用边界
文章测试基于特定环境：PostgreSQL 18.6、默认配置、数据全在 OS 缓存中。作者指出，增大 work_mem 可能缩小 UNION 的差距，而 TABLESAMPLE BERNOULLI 的公平采样未测试。因此，实际优化时应关注性能比率而非绝对秒数，并结合自身硬件和数据量调整。
Q&A
为什么 UNION 比 UNION ALL 慢那么多？
UNION 会对结果去重，需要确保没有重复行，因此要处理所有数据；而 UNION ALL 只是简单合并结果。在测试中，UNION 耗时 13.6 秒，UNION ALL 仅 0.68 秒。
CTE 加上 MATERIALIZED 关键字会有什么影响？
MATERIALIZED 会强制 PostgreSQL 先物化整个 CTE，然后再应用外部过滤条件，导致无法将过滤条件下推到基表索引。测试中，不加 MATERIALIZED 的 CTE 被内联后耗时 0.24 毫秒，加上后耗时 1973 毫秒。
用 ORDER BY random() LIMIT 1 随机取一行有什么性能问题？
ORDER BY random() LIMIT 1 会读取所有行并为每行生成随机数进行排序，然后返回一行，效率极低。测试中 200 万行表耗时 1313 毫秒。替代方案 TABLESAMPLE SYSTEM (0.01) LIMIT 1 仅需 0.3 毫秒，但可能返回空结果且不是公平随机。
SELECT * 和只选索引列在性能上有什么区别？
如果查询只涉及索引列，PostgreSQL 可以仅通过索引扫描返回数据（Index Only Scan），减少缓冲区访问；而 SELECT * 需要回表读取每一行，增加缓冲区访问。测试中，只选 created_at 耗时 32.8 毫秒，279 个缓冲区；SELECT * 耗时 64.8 毫秒，1718 个缓冲区。
在 WHERE 子句中使用函数（如 lower(email)）会导致全表扫描吗？如何优化？
如果 WHERE 中的函数没有匹配的表达式索引，PostgreSQL 会进行全表扫描。例如 WHERE lower(email) = '...' 无索引时耗时 623 毫秒。创建表达式索引 CREATE INDEX ON u (lower(email)) 后，耗时降至 0.5 毫秒。注意索引必须与查询中的表达式完全一致。
这些慢查询在开发环境中为什么不容易被发现？
在数据量较小的开发环境中（如几千行），这些查询都能在几毫秒内完成，性能差异不明显。只有当表增长到百万行级别时，多余工作的代价才会显现出来。
