PostgreSQL中两张大表关联查询的性能优化问询

表结构信息 #

Location表 #

- id: uuid（主键）
- name: string（已建索引）
- country: string（已建索引）
- number: string

Product表 #

- id: uuid（主键）
- name: string
- score: number（已建索引）
- rate: number（已建索引）
- report: number（已建索引）
- lock: boolean（已建索引）
- location_id: uuid（非空，已建索引）

关联关系：Location与Product为1对1唯一关联，Location可关联或不关联Product

原查询语句 #

select 
    l.id, l.name, l.number, 
    p.id as pId, p.name, p.score, p.rate
from
    location l
left join 
    product p on p.location_id = l.id
where 
    l.country = 'US'
    and l.id < 'xxx-yyy-zzz'
    and (p.name is not null or l.name is not null)
    and p.score > 1 and p.rate > 4 and p.lock = false
order by 
    id desc
limit 100

执行计划（EXPLAIN ANALYZE） #

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit  (cost=0.86..176.73 rows=100 width=81) (actual time=234.211..1765.086 rows=100 loops=1)                                                                                                    |
|  ->  Merge Join  (cost=0.86..975290.35 rows=554575 width=81) (actual time=234.210..1764.985 rows=100 loops=1)                                                                                   |
|        Merge Cond: (l.id = p.location_id)                                                                                                                                                             |
|        Join Filter: ((l.name IS NOT NULL) OR (p.name IS NOT NULL))                                                                                                                           |
|        Rows Removed by Join Filter: 3492                                                                                                                                                        |
|        ->  Index Scan Backward using "PK_c16f58426537a660b3f2a26e983" on location l  (cost=0.43..504099.86 rows=2444771 width=43) (actual time=1.035..1282.591 rows=3668 loops=1)          |
|              Index Cond: (id < 'ffbe90da-429a-4e79-99c8-b3ef9ac64b2d'::uuid)                                                                                                                    |
|              Filter: ((country)::text = 'US'::text)                                                                                                                                             |
|              Rows Removed by Filter: 3920                                                                                                                                                       |
|        ->  Index Scan Backward using "PK_fa791fa9c903bb99bbcebde4878" on product p  (cost=0.43..455544.85 rows=1653751 width=38) (actual time=0.586..473.972 rows=12362 loops=1)|
|              Filter: ((NOT lock) AND (COALESCE(score, 0) > 1) AND (COALESCE(rate, 0) > 4))                                                                             |
|              Rows Removed by Filter: 167                                                                                                                                                        |
|Planning Time: 30.386 ms                                                                                                                                                                         |
|Execution Time: 1765.250 ms                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

问题诊断 #

从执行计划可定位核心性能瓶颈：

• Location表通过主键倒序扫描后再过滤country='US'，无效扫描行数达3920，占总扫描行数的51%，严重浪费IO资源
• Product表依赖主键扫描后过滤条件，未利用联合索引缩小扫描范围，虽过滤行数不多但仍有优化空间
• Merge Join后通过(p.name is not null or l.name is not null)过滤3492行，属于事后过滤，增加CPU开销
• 原查询中left join后添加了Product字段的非空过滤条件，实际逻辑等价于inner join，误导优化器选择执行计划

优化方案 #

1. 重构Location表联合索引，精准过滤目标数据 #

当前Location表仅单独对country和id建索引，无法同时满足country='US'+id < 'xxx-yyy-zzz'+order by id desc的组合条件，创建覆盖索引：

CREATE INDEX idx_location_country_id_desc ON location (country, id DESC) INCLUDE (name, number);

作用：直接按country分组，再按id倒序排列，无需事后过滤country；INCLUDE子句包含查询所需字段，避免回表查询

2. 优化Product表联合索引，覆盖过滤与关联条件 #

针对Product表的过滤条件lock=false、score>1、rate>4及关联字段location_id，创建覆盖索引：

CREATE INDEX idx_product_lock_score_rate_location_id ON product (lock, score, rate, location_id) INCLUDE (id, name);

作用：先通过lock快速筛选未锁定数据，再通过score和rate过滤符合条件的记录，最后关联location_id；INCLUDE子句包含查询所需字段，避免回表

3. 修正查询语句，消除误导性语法 #

原查询中left join后添加了Product字段的非空过滤条件，实际逻辑等价于inner join，修改后让优化器能选择更高效的执行计划：

select 
    l.id, l.name, l.number, 
    p.id as pId, p.name, p.score, p.rate
from
    location l
inner join 
    product p on p.location_id = l.id
where 
    l.country = 'US'
    and l.id < 'xxx-yyy-zzz'
    and (p.name is not null or l.name is not null)
    and p.score > 1 and p.rate > 4 and p.lock = false
order by 
    l.id desc -- 明确指定排序字段，避免歧义
limit 100;

4. 提前过滤无效数据，减少Join范围 #

将(p.name is not null or l.name is not null)的条件尽可能拆分到单表过滤：

• 若业务允许，可在Location表的where条件中添加l.name is not null（如果大部分US地区的Location都有name）
• 或在Product表的索引中包含name字段，让优化器在扫描Product时提前过滤p.name is not null的记录

5. 更新统计信息，确保优化器生成准确计划 #

PostgreSQL的统计信息过时会导致优化器选择低效执行计划，执行以下命令更新：

ANALYZE location;
ANALYZE product;

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