PostgreSQL单查询中如何聚合每组的部分成员？

问题说明 #

现有people表结构：

gid int, name varchar

表内数据如下：

gid    name                 
1      Bill
2      Will
2      Musk
2      Jack
1      Martin
1      Jorge

需求为：按gid分组，每组取2个成员拼接成字符串作为代表，顺序无关只需数量达标。当前使用的CTE方案需全表扫描生成行号，在大数据量表上性能较差，希望找到无需全表扫描的优化方案。

当前使用的SQL：

with indexed as(
    select gid,name,row_number() over (partition by gid) as index
    from people
),filtered as(
    select gid, name 
    from indexed where index<3
)
select gid,string_agg(name,',')
from filtered
group by gid;

优化方案 #

方案一：使用LATERAL子查询（兼容全版本PostgreSQL） #

通过LATERAL关联子查询，针对每个唯一gid直接查询前2条记录，配合gid上的索引可避免全表扫描：

select 
    p.gid,
    string_agg(p_sub.name, ',') as representative
from (select distinct gid from people) p
left join lateral (
    select name 
    from people 
    where gid = p.gid 
    limit 2
) p_sub on true
group by p.gid;

关键前提：必须在gid字段上创建B-tree索引，否则数据库仍会执行全表扫描。索引创建语句：

create index idx_people_gid on people(gid);

方案二：利用array_agg的LIMIT特性（PostgreSQL 14+） #

PostgreSQL 14及以上版本中，array_agg支持LIMIT参数，可直接在聚合时限制每个分组的元素数量，写法更简洁高效：

select 
    gid,
    array_to_string(array_agg(name limit 2), ',') as representative
from people
group by gid;

该方案无需额外子查询，数据库会在分组聚合过程中直接截断每个分组到前2条数据，配合gid索引能最大化性能。

核心优化逻辑 #

• 避免全表扫描的关键是让数据库快速定位每个分组的数据，因此gid字段的索引是基础。
• 两种方案均减少了不必要的数据处理：LATERAL子查询针对每个分组只取2条；array_agg的LIMIT特性在聚合阶段直接截断，无需先生成全表行号再过滤。

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