PostgreSQL 13.1：含子查询/关联的SQL查询性能优化求助

我的数据库包含250万条person记录、300万条pet记录，每只宠物归属一位用户，其中猫（kind = 3）共有32300只。需要优化以下SQL语句：

SELECT p.name 
FROM person p
WHERE p.id IN (
    SELECT pet.person_id
    FROM pet pet
    WHERE pet.kind = 3 -- cat
)

原SQL执行计划 #

Nested Loop  (cost=898.14..66545.89 rows=32005 width=37) (actual time=22.546..12407.109 rows=32300 loops=1)
  Output: p.name
  Inner Unique: true
  Buffers: shared hit=85815 read=43622
  ->  HashAggregate  (cost=897.71..1218.20 rows=32049 width=8) (actual time=21.702..42.032 rows=32300 loops=1)
        Output: pet.person_id
        Group Key: pet.person_id
        Batches: 1  Memory Usage: 3089kB
        Buffers: shared hit=2 read=235
        ->  Index Only Scan using petx1 on pet pet  (cost=0.43..817.59 rows=32049 width=8) (actual time=1.085..10.478 rows=32300 loops=1)
              Output: pet.kind, pet.person_id
              Index Cond: (pet.kind = '3'::bigint)
              Heap Fetches: 2
              Buffers: shared hit=2 read=235
  ->  Index Scan using personxpk on person p  (cost=0.43..2.06 rows=1 width=45) (actual time=0.382..0.382 rows=1 loops=32300)
        Output: p.name, p.id
        Index Cond: (p.id = pet.person_id)
        Buffers: shared hit=85813 read=43387
Planning:
  Buffers: shared hit=444 read=64
Planning Time: 26.404 ms
Execution Time: 12413.696 ms

已创建的索引 #

CREATE UNIQUE INDEX personxpk ON person USING btree (person_id)
CREATE INDEX petx1 ON pet USING btree (kind, person_id)

改写为JOIN后的SQL及执行计划 #

尝试将语句改写为关联查询后，速度提升约一倍（推测因启用2个并行工作进程），但仍未达到预期：

SELECT p.name
FROM person p
JOIN pet pet ON pet.person_id = p.id AND pet.kind = 3 -- cat

对应的执行计划：

Gather  (cost=1000.86..32294.58 rows=32005 width=37) (actual time=2.663..4303.776 rows=32300 loops=1)
  Output: p.name
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=85819 read=43622
  ->  Nested Loop  (cost=0.86..28094.08 rows=13335 width=37) (actual time=1.850..4272.721 rows=10767 loops=3)
        Output: p.name
        Inner Unique: true
        Buffers: shared hit=85819 read=43622
        Worker 0:  actual time=1.787..4274.702 rows=10780 loops=1
          Buffers: shared hit=28696 read=14503
        Worker 1:  actual time=1.734..4271.052 rows=10780 loops=1
          Buffers: shared hit=28598 read=14602
        ->  Parallel Index Only Scan using petx1 on pet pet  (cost=0.43..630.63 rows=13354 width=8) (actual time=0.946..8.839 rows=10767 loops=3)
              Output: pet.kind, pet.person_id
              Index Cond: (pet.kind = '3'::bigint)
              Heap Fetches: 2
              Buffers: shared hit=4 read=235
              Worker 0:  actual time=0.966..8.497 rows=10780 loops=1
                Buffers: shared hit=1 read=77
              Worker 1:  actual time=0.831..9.440 rows=10780 loops=1
                Buffers: shared hit=1 read=78
        ->  Index Scan using personxpk on person p  (cost=0.43..2.06 rows=1 width=45) (actual time=0.395..0.395 rows=1 loops=32300)
              Output: p.name, p.id
              Index Cond: (p.id = pet.person_id)
              Buffers: shared hit=85815 read=43387
              Worker 0:  actual time=0.394..0.394 rows=1 loops=10780
                Buffers: shared hit=28695 read=14426
              Worker 1:  actual time=0.394..0.394 rows=1 loops=10780
                Buffers: shared hit=28597 read=14524
Planning:
  Buffers: shared hit=450 read=58
Planning Time: 27.957 ms
Execution Time: 6307.435 ms

优化建议 #

1. 创建person表的覆盖索引 #

当前personxpk仅包含person_id，查询name时需要回表读取主表数据（从执行计划的Index Scan可看出）。创建包含name的覆盖索引，避免回表开销：

CREATE INDEX person_id_name_idx ON person USING btree (id) INCLUDE (name);

这样查询时直接从索引就能获取name，无需访问主表，大幅减少IO操作。

2. 提前去重养猫用户ID #

若存在一人养多只猫的情况，JOIN会产生重复的用户记录，后续还需去重。可以先在pet查询阶段去重，减少嵌套循环的次数：

SELECT p.name
FROM person p
JOIN (
    SELECT DISTINCT person_id
    FROM pet
    WHERE kind = 3
) pet ON pet.person_id = p.id;

也可用GROUP BY person_id替代DISTINCT，效果一致，能把关联的ID数量降到实际养猫用户数，而非猫的总数。

3. 调整并行工作进程数（服务器资源充足时） #

当前启用了2个并行工作进程，可尝试调高max_parallel_workers_per_gather参数，让更多进程并行处理person表查询：

SET max_parallel_workers_per_gather = 4;

注意需根据服务器CPU核心数调整，避免资源过载。

4. 更新表统计信息 #

若表数据有更新，过时的统计信息可能导致执行计划不佳，重新分析表：

ANALYZE person;
ANALYZE pet;

让优化器基于最新统计数据生成更优的执行计划。

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