Postgres 13查询拥有多个不同邮编地址的Person ID（支持分页）

我正在使用Postgres 13，现有以下表格（为简洁起见截断了部分列）：本质上是Person与Address之间的关联表Location，该表包含额外字段且有自身主键（此结构无法修改）。

表结构 #

Person表 #

person_id
-----------
         1
         2

Location表 #

location_id | status | address_id | person_id
-------------+--------+------------+-----------
           1 |      1 |          1 |         1
           2 |      1 |          2 |         1
           3 |      1 |          3 |         2
           4 |      1 |          4 |         2

Address表 #

address_id | postcode
------------+----------
          1 |       NR1
          2 |       NR2
          3 |       NR3
          4 |       NR3

查询需求 #

需要编写查询语句，获取所有满足以下条件的person_id：

• 关联的Location状态为1
• 拥有多个不同的邮编地址

由于Person表有超过1000万条记录，且每人可拥有多个地址，因此需要使用limit和offset实现分页查询。

示例数据的查询结果应仅返回person_id 1（其拥有两个不同邮编NR1和NR2），而person_id 2的两个地址邮编均为NR3，不符合条件。

初始查询语句 #

我编写了无分页的初始查询语句，似乎可行，但想确认是否有更优实现：

SELECT sq.person_id
FROM
(
    SELECT
        p.person_id,
        COUNT(DISTINCT a.postcode) AS count_postcode
    FROM person p 
        INNER JOIN location l
             ON p.person_id = l.person_id
        INNER JOIN address a
            ON a.address_id = l.address_id
    WHERE l.status = 1
    GROUP BY p.person_id
) sq
WHERE sq.count_postcode > 1;

优化方案 #

1. 简化查询逻辑 #

可以直接从Location表出发关联Address，不需要额外关联Person表——因为Location中已包含person_id，且关联关系保证了person_id的合法性，减少一次JOIN操作能显著提升大表查询效率：

SELECT l.person_id
FROM location l
JOIN address a ON l.address_id = a.address_id
WHERE l.status = 1
GROUP BY l.person_id
HAVING COUNT(DISTINCT a.postcode) > 1;

2. 分页实现 #

针对千万级数据的分页，直接用LIMIT+OFFSET会存在性能瓶颈（OFFSET越大，数据库需要扫描的前置行数越多），这里提供两种实现方式：

方式1：传统LIMIT+OFFSET（适合小偏移量场景）

SELECT l.person_id
FROM location l
JOIN address a ON l.address_id = a.address_id
WHERE l.status = 1
GROUP BY l.person_id
HAVING COUNT(DISTINCT a.postcode) > 1
ORDER BY l.person_id
LIMIT 100 OFFSET 0; -- 第一页，每页100条

方式2：范围分页（大偏移量场景更高效）

利用person_id的有序性，以上一页的最后一个person_id作为过滤条件，避免扫描大量前置数据：

-- 第一页
SELECT l.person_id
FROM location l
JOIN address a ON l.address_id = a.address_id
WHERE l.status = 1
GROUP BY l.person_id
HAVING COUNT(DISTINCT a.postcode) > 1
AND l.person_id > 0
ORDER BY l.person_id
LIMIT 100;

-- 第二页（替换[上一页最后一个person_id]为实际值）
SELECT l.person_id
FROM location l
JOIN address a ON l.address_id = a.address_id
WHERE l.status = 1
GROUP BY l.person_id
HAVING COUNT(DISTINCT a.postcode) > 1
AND l.person_id > [上一页最后一个person_id]
ORDER BY l.person_id
LIMIT 100;

3. 索引优化 #

为进一步提升查询速度，建议创建以下覆盖索引：

• CREATE INDEX idx_location_status_person_address ON location(status, person_id, address_id);：覆盖WHERE过滤、分组字段和JOIN字段，避免回表查询
• CREATE INDEX idx_address_id_postcode ON address(address_id, postcode);：覆盖JOIN字段和统计字段，加速关联过程

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