PostgreSQL中如何高效单查询获取同一cust_id的所有记录？

我原本可以通过两次SELECT实现需求：先查表首行的cust_id，再查所有匹配该cust_id的记录。现在想改成单条查询，已经试了子查询写法：

SELECT * FROM customers
WHERE cust_id=(SELECT cust_id FROM customers LIMIT 1)

想知道这个写法的效率如何，有没有更优方案。

实际需求是：用Python编写的AWS Lambda函数定期归档某一随机cust_id或最早cust_id的所有关联记录，操作需在单事务内完成。目前使用PostgreSQL 10.5，表结构如下：

id BIGINT PRIMARY KEY,
cust_id VARCHAR(100) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
cust_data JSONB

注意：created字段是复制数据时生成的，并非记录实际创建时间，无法用它判断最早的cust_id。

示例输入数据：

单次查询需要返回某一cust_id的所有关联记录，比如下面任意一组结果：

预期输出1

预期输出2

预期输出3

子查询写法的效率分析 #

你的子查询写法逻辑可行，但效率核心取决于cust_id是否有索引：

• 无索引时：外层查询需要全表扫描匹配子查询返回的cust_id，子查询本身LIMIT 1会快速返回表中第一行，但外层全表扫在数据量大时性能较差。
• 有索引时：外层查询可通过索引快速定位所有匹配记录，整体效率会大幅提升。

更优替代方案 #

1. 窗口函数方案（适配"最早出现"的cust_id） #

如果把"最早的cust_id"定义为主键id最小的记录对应的cust_id，可以用窗口函数一次性完成筛选，避免多次扫描：

SELECT *
FROM (
    SELECT *,
           FIRST_VALUE(cust_id) OVER () AS target_cust
    FROM customers
) t
WHERE cust_id = target_cust;

该方案仅需一次全表扫描（若有cust_id索引，后续过滤会更快）。

2. 关联查询方案 #

用JOIN替代子查询，逻辑更直观，PostgreSQL优化器通常会将其与子查询优化为相同执行计划，可读性更好：

SELECT c.*
FROM customers c
JOIN (SELECT cust_id FROM customers LIMIT 1) t
ON c.cust_id = t.cust_id;

3. 随机选择cust_id的方案 #

默认LIMIT 1返回的是存储顺序（通常按id排序），若需要真正随机选择cust_id，可改用：

SELECT *
FROM customers
WHERE cust_id = (
    SELECT cust_id
    FROM customers
    ORDER BY RANDOM()
    LIMIT 1
);

注意：大表上ORDER BY RANDOM()会因全表扫描+排序导致性能下降，可优化为：

SELECT *
FROM customers
WHERE cust_id = (
    SELECT cust_id
    FROM customers
    OFFSET FLOOR(RANDOM() * (SELECT COUNT(DISTINCT cust_id) FROM customers))
    LIMIT 1
);

先获取不同cust_id的数量，再随机偏移，减少排序开销。

关键优化建议 #

• 给cust_id建立索引：CREATE INDEX idx_customers_cust_id ON customers(cust_id);，这会让所有基于cust_id的过滤查询性能提升数个量级，尤其在数据量大时。
• Lambda执行SQL时需开启事务（比如用psycopg2设置connection.autocommit = False，完成归档操作后再提交），确保操作原子性。

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