PostgreSQL中Left Join被转为Right Join的原因排查求助

目标查询与执行计划 #

初始查询 #

explain  SELECT * FROM markets LEFT JOIN outcomes ON markets.uuid = outcomes.market_uuid WHERE markets.event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0';

执行计划 #

Merge Right Join  (cost=63274.17..905696.60 rows=78271 width=8201)
  Merge Cond: (outcomes.market_uuid = markets.uuid)
  ->  Index Scan using outcomes_market_uuid_index on outcomes  (cost=0.43..802379.27 rows=15654269 width=2776)
  ->  Sort  (cost=63273.73..63336.34 rows=25043 width=5425)
        Sort Key: markets.uuid
        ->  Bitmap Heap Scan on markets  (cost=220.92..27250.08 rows=25043 width=5425)
              Recheck Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
              ->  Bitmap Index Scan on markets_event_uuid_index  (cost=0.00..214.66 rows=25043 width=0)
                    Index Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

带ANALYSE和BUFFERS的查询 #

EXPLAIN (ANALYSE, BUFFERS)  SELECT * FROM markets 
LEFT JOIN outcomes ON markets.uuid = outcomes.market_uuid  
WHERE markets.event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0';

执行结果 #

Merge Right Join  (cost=63274.17..905696.60 rows=78271 width=8201) (actual time=21215.656..21215.665 rows=2 loops=1)
  Merge Cond: (outcomes.market_uuid = markets.uuid)
  Buffers: shared hit=7635492 read=600026 written=2031
  ->  Index Scan using outcomes_market_uuid_index on outcomes  (cost=0.43..802379.27 rows=15654269 width=2776) (actual time=0.111..19456.586 rows=8421452 loops=1)
        Buffers: shared hit=7635488 read=600026 written=2031
  ->  Sort  (cost=63273.73..63336.34 rows=25043 width=5425) (actual time=0.047..0.049 rows=1 loops=1)
        Sort Key: markets.uuid
        Sort Method: quicksort  Memory: 26kB
        Buffers: shared hit=4
        ->  Bitmap Heap Scan on markets  (cost=220.92..27250.08 rows=25043 width=5425) (actual time=0.025..0.027 rows=1 loops=1)
              Recheck Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
              Heap Blocks: exact=1
              Buffers: shared hit=4
              ->  Bitmap Index Scan on markets_event_uuid_index  (cost=0.00..214.66 rows=25043 width=0) (actual time=0.018..0.018 rows=1 loops=1)
                    Index Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
                    Buffers: shared hit=3
Planning Time: 0.416 ms
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.989 ms, Inlining 4.691 ms, Optimization 200.586 ms, Emission 117.924 ms, Total 326.192 ms
Execution Time: 21218.828 ms

再次执行EXPLAIN ANALYZE #

EXPLAIN ANALYZE SELECT * FROM markets 
LEFT JOIN outcomes ON markets.uuid = outcomes.market_uuid 
WHERE markets.event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0';

执行结果 #

Hash Right Join  (cost=98.69..681948.87 rows=269 width=4521) (actual time=31871.835..31871.846 rows=2 loops=1)
  Hash Cond: (outcomes.market_uuid = markets.uuid)
  ->  Seq Scan on outcomes  (cost=0.00..640757.69 rows=15654269 width=2776) (actual time=0.034..29402.201 rows=15654279 loops=1)
  ->  Hash  (cost=97.62..97.62 rows=86 width=1745) (actual time=364.287..364.290 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 10kB
        ->  Index Scan using markets_event_uuid_index on markets  (cost=0.43..97.62 rows=86 width=1745) (actual time=0.027..0.032 rows=1 loops=1)
              Index Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
Planning Time: 0.326 ms
JIT:
  Functions: 12
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 3.773 ms, Inlining 3.798 ms, Optimization 226.614 ms, Emission 133.456 ms, Total 367.641 ms
Execution Time: 31875.826 ms

环境信息 #

• 数据库已从备份恢复
• 服务器上Postgres已重启
• 其他Left Join查询运行正常
• 仅上述查询（Join+Where）出现问题
• 查询耗时20-30秒，关联的右侧表有1500万条记录
• 表均有主键和索引
• PostgreSQL版本为15.2
• 本地PostgreSQL 14.3版本中该查询运行正常

正常执行的类似查询案例 #

案例1：LEFT JOIN events表 #

EXPLAIN (ANALYSE, BUFFERS)  SELECT * FROM markets 
LEFT JOIN events ON markets.event_uuid = events.uuid 
WHERE markets.event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0';

执行结果 #

Buffers: shared hit=8
  ->  Bitmap Heap Scan on markets  (cost=220.92..27250.08 rows=25043 width=5425) (actual time=0.030..0.031 rows=1 loops=1)
        Recheck Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
        Heap Blocks: exact=1
        Buffers: shared hit=4
        ->  Bitmap Index Scan on markets_event_uuid_index  (cost=0.00..214.66 rows=25043 width=0) (actual time=0.019..0.019 rows=1 loops=1)
              Index Cond: (event_uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
              Buffers: shared hit=3
  ->  Materialize  (cost=0.42..2.65 rows=1 width=5491) (actual time=0.045..0.045 rows=1 loops=1)
        Buffers: shared hit=4
        ->  Index Scan using events_pkey on events  (cost=0.42..2.64 rows=1 width=5491) (actual time=0.024..0.024 rows=1 loops=1)
              Index Cond: (uuid = 'f286f78b-9e9c-4052-9785-7c908a8ab2c0'::uuid)
              Buffers: shared hit=4
Planning Time: 0.384 ms
Execution Time: 0.253 ms

案例2：LEFT JOIN outcomes表，WHERE条件为markets.uuid #

EXPLAIN (ANALYSE, BUFFERS)  SELECT * FROM markets 
LEFT OUTER JOIN outcomes ON markets.uuid = outcomes.market_uuid 
WHERE markets.uuid = '6dc00c42-9b80-43a5-a263-5010648a38fd';

执行结果 #

Buffers: shared hit=3 read=6
  ->  Index Scan using markets_pkey on markets  (cost=0.43..2.65 rows=1 width=5425) (actual time=3.183..3.185 rows=1 loops=1)
        Index Cond: (uuid = '6dc00c42-9b80-43a5-a263-5010648a38fd'::uuid)
        Buffers: shared hit=1 read=3
  ->  Bitmap Heap Scan on outcomes  (cost=781.94..78619.53 rows=78271 width=2776) (actual time=1.713..1.723 rows=2 loops=1)
        Recheck Cond: (market_uuid = '6dc00c42-9b80-43a5-a263-5010648a38fd'::uuid)
        Heap Blocks: exact=2
        Buffers: shared hit=2 read=3
        ->  Bitmap Index Scan on outcomes_market_uuid_index  (cost=0.00..762.37 rows=78271 width=0) (actual time=1.682..1.682 rows=2 loops=1)
              Index Cond: (market_uuid = '6dc00c42-9b80-43a5-a263-5010648a38fd'::uuid)
              Buffers: shared hit=1 read=2
Planning Time: 0.360 ms
Execution Time: 4.975 ms

问题分析与解决 #

1. LEFT JOIN转为RIGHT JOIN的本质 #

这不是Bug，是PostgreSQL查询优化器的正常逻辑转换。A LEFT JOIN B与B RIGHT JOIN A在逻辑上完全等价，优化器会根据成本估算选择更高效的执行顺序，转换操作不会改变最终返回的结果集。

2. 查询性能极差的核心原因 #

优化器对markets表的行数预估严重失真：

• 多次执行计划中，预估markets返回行数为25043或86，但实际仅返回1行
• 错误的统计信息导致优化器选择了低效的连接策略：要么扫描800多万条outcomes记录做Merge Join，要么全表扫描1500万条outcomes记录做Hash Join

正常最优路径应为：先获取1条符合条件的markets记录，再通过outcomes_market_uuid_index索引精准匹配对应的outcomes记录（如案例2所示，仅需几毫秒）。

3. 统计信息失真的原因 #

数据库从备份恢复后，pg_statistic中的统计信息可能未被正确更新，导致优化器无法准确估算表的行数和分布。

4. 解决方法 #

手动更新表的统计信息，让优化器获取准确的数据分布：

-- 更新单表统计
ANALYZE markets;
ANALYZE outcomes;

-- 或更新所有表的统计
ANALYZE;

执行完成后重新运行目标查询，优化器将基于正确的统计信息选择最优执行路径。

5. 版本差异的原因 #

本地PostgreSQL 14.3运行正常，大概率是因为本地数据库的统计信息准确，或者14.x版本的优化器在处理统计偏差时的策略更稳健。

内容的提问来源于stack exchange，提问作者Piotr Kazuś