PostgreSQL筛选子项分布在多个地点的父盒子的实现方案

你的现有实现逻辑是正确的，在给出的测试场景中可以正确筛选出符合要求的父盒子（仅返回id为2的记录），不存在逻辑错误。但在两百万级数据量的场景下，两层聚合（DISTINCT ON + 外层GROUP BY）会产生额外的计算开销，存在明显的性能优化空间。

你可以直接通过一次GROUP BY配合COUNT(DISTINCT)完成统计，无需CTE中间层，语法更简洁，执行效率更高，且兼容所有当前官方支持的PostgreSQL版本：

SELECT b.box_id
FROM boxes b
INNER JOIN things t ON b.id = t.box_id
WHERE t.quantity > 0
GROUP BY b.box_id
HAVING COUNT(DISTINCT t.place_id) > 1;

该方案和你的原查询返回结果完全一致，减少了一次中间结果集的处理步骤，大数据量下性能优势更为明显。

为了进一步提升两百万级数据表的查询速度，建议添加以下复合索引，实现覆盖索引扫描，避免回表查询：

• boxes表添加复合索引：CREATE INDEX idx_boxes_parent_child ON boxes(box_id, id);
• things表添加复合索引：CREATE INDEX idx_things_box_quantity_place ON things(box_id, quantity, place_id);

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