如何高效编写SQL查询每个成员的最新可用状态记录？

我有一张存储每个成员可用状态的表，仅包含4个字段，表结构如下：

CREATE TABLE availablities (
  availablity_id serial PRIMARY KEY,
  member_id serial,
  availablity_status_id serial,
  start_date timestamp
);

每个成员可在表中存在多条记录，我需要获取每个成员start_date小于等于now()的最新记录对应的当前状态。
我最初尝试了简单的Max()加Group by查询：

select 
  status_code, max(start_date) start_date,availablities.member_id 
from 
  availablities
join 
  availablity_status on availablity_status.availablity_status_id = availablities.availablity_status_id
where 
  start_date <= now() 
group by 
  status_code,availablities.member_id;

但该查询会为每个用户返回多条记录，因为是按用户和状态分组取最新记录。
之后我写出了能得到预期结果的查询：

select status_code,start_date,a2.member_id  from availablities a2 
 join availablity_status on availablity_status.availablity_status_id = a2.availablity_status_id
 where a2.availablity_id in(
     select 
        max(availablity_id)
     from availablities a 
     where 
        a.member_id = a2.member_id and 
        start_date in(
            select 
                max(start_date) start_date
            from availablities
            where 
            start_date <= now() 
            and a.member_id = availablities.member_id    
     )
);

但该查询执行耗时是普通查询的60倍，性能表现很差。我确定存在更优的解决方案但暂时无法实现。请问获取预期结果的正确方式是什么？

使用窗口函数ROW_NUMBER()可以高效实现需求，仅需一次表扫描即可完成，无需多层嵌套子查询，性能远高于当前的嵌套IN查询写法。
优化后的查询语句如下：

WITH ranked_availabilities AS (
    SELECT 
        a.member_id,
        a.availablity_status_id,
        a.start_date,
        ROW_NUMBER() OVER (
            PARTITION BY a.member_id 
            ORDER BY a.start_date DESC, a.availablity_id DESC
        ) AS rn
    FROM availablities a
    WHERE a.start_date <= NOW()
)
SELECT 
    s.status_code,
    r.start_date,
    r.member_id
FROM ranked_availabilities r
JOIN availablity_status s 
    ON s.availablity_status_id = r.availablity_status_id
WHERE r.rn = 1;

方案优势 #

• 仅扫描一次availablities表，嵌套子查询版本需要多次关联扫描表，数据量越大性能优势越明显
• 逻辑清晰，PARTITION BY member_id按成员分组，ORDER BY start_date DESC取最新的生效时间，同时间的记录按自增主键倒序取最新插入的记录，避免同时间多条记录的冲突问题

性能优化建议 #

可以创建联合索引进一步提升查询效率，该索引可以直接覆盖窗口函数的分区、排序逻辑，查询可以直接走索引无需额外排序：

CREATE INDEX idx_availabilities_member_start ON availablities (member_id, start_date DESC, availablity_id DESC);

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