PostgreSQL查询性能对比：两种客户访问统计查询哪个更优？

为什么Query 2成本低但执行时间更长？ #

1. 成本估算与实际执行的偏差 #

PostgreSQL的执行计划成本基于统计数据估算，优化器认为Query 2的单条子查询开销极低，但忽略了累计效应：如果customers表有大量行（比如几万甚至几十万），每个customer都会触发一次visits表的索引查找来计算count，多次小查询的IO、CPU开销累加后，总耗时会远超Query 1的一次批量聚合+连接操作。

2. 执行模式的本质差异 #

• Query 1是批量处理：先对visits表做一次全量GROUP BY聚合，得到每个customer的访问次数，再和customers表做左连接。这种模式下，visits只被扫描一次，后续连接是批量匹配。
• Query 2是逐行嵌套查询：属于关联子查询，每处理一行customers数据，就会单独执行一次SELECT count(*) FROM visits WHERE customer_id=xxx。当customers规模较大时，这种"循环调用"的开销会被急剧放大。

另外注意：你的Query 1存在语法错误——连接条件里写的是visits.customer_id = customers.id，但子查询的别名是v，正确的连接条件应该是v.customer_id = customers.id，这个错误会导致查询报错或结果异常，先修正这个问题才能正确对比性能。

更优的查询方案 #

方案1：修正并简化Query 1的写法 #

修正连接条件，同时去掉冗余子查询，直接通过左连接后聚合实现需求：

SELECT
  customers.id AS id,
  COUNT(visits.id) AS visits
FROM
  customers
  LEFT JOIN visits ON visits.customer_id = customers.id
GROUP BY customers.id;

这个写法更简洁，优化器可以根据数据量选择更高效的执行策略（比如哈希连接、合并连接），避免子查询的额外开销。

方案2：利用覆盖索引进一步优化 #

如果visits表的customer_id索引是普通索引，查询时可能需要回表取数据。可以创建覆盖索引，让COUNT操作直接从索引完成，无需访问主表：

CREATE INDEX idx_visits_customer_id_include ON visits(customer_id) INCLUDE (id);

这个索引包含了customer_id和id字段，执行COUNT(visits.id)时可以直接从索引获取数据，大幅降低IO开销。

方案3：使用LATERAL子查询（适合特定场景） #

如果需要更灵活的关联逻辑，可以用LEFT JOIN LATERAL实现，性能和批量聚合接近：

SELECT
  customers.id AS id,
  COALESCE(v.visits, 0) AS visits
FROM
  customers
  LEFT JOIN LATERAL (
    SELECT COUNT(*) AS visits
    FROM visits
    WHERE visits.customer_id = customers.id
  ) v ON true;

优化器会将其优化为批量处理模式，避免逐行查询的开销。

内容的提问来源于stack exchange，提问作者Pablo Alejandro