PostgreSQL 11.9中IS NULL左连接查询优化方案咨询

在PostgreSQL 11.9中执行含IS NULL条件的左连接查询，初始耗时1500-2000ms。已调整数据库默认统计值为1000，并对涉及表执行reindex、vacuum和analyze操作，耗时仍维持在1100-1300ms。

• 将条件改为IS NOT NULL时，查询可在150ms内完成；
• 禁用实例的merge join后，原IS NULL查询耗时降至50ms，执行计划从merge join改为hash join；
• 改写查询规避IS NULL后，执行计划仍耗时约1100ms；
• 提升work_mem至64MB后，执行计划仍采用Merge Anti Join，耗时无明显改善。

需求：在不禁用merge join的情况下强制使用hash join，并寻求进一步优化建议。

原始查询 #

select ev.id from logevent ev
left join flowtoken FT on ev.uri = 'xxx://xxx/WorkflowToken?id=''' || FT.id || ''' &xx=''Token'''
where 
ev.uri like 'xxx://xxx/WorkflowToken?id=%'
and FT.id is null

表定义 #

• flowtoken表
  • 字段：id character varying(100) not null，主键，BTREE索引
• logevent表
  • 字段：id character varying(100) not null，主键，BTREE索引
  • 字段：uri character varying(1024) not null，idx_logeventuri BTREE索引

执行计划分析 #

原始IS NULL查询执行计划 #

Gather (cost=23655.83..33636.20 rows=72532 width=33) (actual time=783.987..1117.367 rows=703 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Merge Anti Join    (cost=22644.83..25383.00 rows=30222 width=33) (actual time=706.542..972.300 rows=234 loops=3)
          Merge Cond: ((ev.uri)::text = ((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
          ->    Sort (cost=17003.08..17154.19 rows=60443 width=140) (actual time=626.520..739.990 rows=48237 loops=3)
                    sort key:ev.uri
                    sort method: external merge disk: 8136kb
                    worker 0:   sort method: external merge disk: 6184kb
                    worker 1:   sort method: external merge disk: 6184kb
                    ->  Parallel Seq Scan on logevent ev (cost=0.00..7862.91 rows=60443 width=140) (actual time=0.022..19.463 rows=48237 loops=3)
                            Filter: ((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                            Rows Removed by filter: 142
          ->    Sort (cost=5641.08..5757.19 rows=46369 width=37) (actual time=77.520..164.990 rows=46368 loops=3)
                    sort key:((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
                    sort method: external merge disk: 6992kb
                    worker 0:   sort method: external merge disk: 6992kb
                    worker 1:   sort method: external merge disk: 6992kb
                    ->  Index Only Scan using pk_flowtoken on flowtoken  FT (cost=0.41..2047.91 rows=46369 width=37) (actual time=0.049..7.539 rows=46369 loops=3)
                        
                                Heap Fetches: 0

 Planning Time: 1.381 ms
 Execution Time: 1120.433 ms

IS NOT NULL条件执行计划 #

Hash Join   (cost=13709.89..39695.69 rows=400812 width=33) (actual time=86.005..149.488 rows=144007 loops=1)
    Hash Cond: (('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text) = (ev.uri)::text)
    ->  Index Only Scan using pk_flowtoken on flowtoken FT (cost=0.41..2163.87 rows=46369 width=37) (actual time=0.020..4.256 rows=46369 loops=1)
        Index Cond: (id is not null)
        Heap fetches: 0
    ->  Hash (cost=8921.19..8921.19 rows=145063 width=140) (actual time=85.828..85.829 rows=144710 loops=1)    
            Buckets: 32768 Batches:8 Memory Usage: 3228kb
            ->  seq scan on logevent ev (cost=0.00..8921.19 rows=145063 width=140) (actual time=0.013..46.118 rows=144710 loops=1)
                    Filter:((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                    Rows Removed by filter: 425

 Planning Time: 0.417 ms
 Execution Time: 153.211 ms

禁用Merge Join后的执行计划 #

Gather (cost=3018.96..85692.57 rows=72532 width=33) (actual time=15.420..50.290 rows=703 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Hash Anti Join (cost=2018.96..77439.37 rows=30222 width=33) (actual time=8.375..40.668 rows=234 loops=3)
            Hash Cond: ((ev.uri)::text = ((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
            
            -> Parallel seq scan on logevent ev (cost=0.00..7862.91 rows=60443 width=140) (actual time=0.012..20.100 rows=48237 loops=3)
                Filter: ((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                Rows Removed by filter:142
            ->  Parallel Index Only Scan using pk_flowtoken on flowtoken FT (cost=0.41..1777.46 rows=19320 width=37) (actual time=0.031..1.829 rows=15456 loops=3)
                    Heap Fetches: 0

Planning Time: 0.240 ms
Execution Time: 50.363 ms

提升work_mem至64MB后的执行计划 #

Gather  (cost=19304.83..29296.20 rows=72532 width=33) (actual time=1029.283..1114.902 rows=703 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Merge Anti Join    (cost=18304.83..21043.00 rows=30222 width=33) (actual time=821.942..901.624 rows=234 loops=3)
          Merge Cond: ((ev.uri)::text = ((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
          ->    Sort (cost=12663.08..12814.19 rows=60443 width=140) (actual time=746.900..753.702 rows=48237 loops=3)
                    sort key:ev.uri
                    sort method: quicksort memory: 17703kb
                    worker 0:   sort method: quicksort memory: 12731kb
                    worker 1:   sort method: quicksort memory: 12614kb
                    ->  Parallel Seq Scan on logevent ev (cost=0.00..7862.91 rows=60443 width=140) (actual time=0.011..23.60 rows=48237 loops=3)
                            Filter: ((uri)::text ~~ 'xxx://xxx/WorkflowToken?id=%'::text)
                            Rows Removed by filter: 142
          ->    Sort (cost=5641.08..5757.19 rows=46369 width=37) (actual time=74.520..77.532 rows=46368 loops=3)
                    sort key:((('xxx://xxx/WorkflowToken?id='''::text || (FT.id)::text) || ''' &xx=''Token'''::text)))
                    sort method: quicksort memory:13853kb
                    worker 0:   sort method: quicksort memory: 13853kb
                    worker 1:   sort method: quicksort memory: 13853kb
                    ->  Index Only Scan using pk_flowtoken on flowtoken  FT (cost=0.41..2047.91 rows=46369 width=37) (actual time=0.049..7.539 rows=46369 loops=3)
                        
                                Heap Fetches: 0

 Planning Time: 0.445 ms
 Execution Time: 1118.389 ms

改写后的查询 #

select ev.id 
from logevent ev 
where not exists ( select * 
                   from flowtoken FT 
                   where ev.uri = 'xxx://xxx/WorkflowToken?id=''' || FT.id || ''' &xx=''Token''') 
 and ev.uri like 'xxx://xxx/WorkflowToken?id=%'

解决方案与优化建议 #

1. 强制使用Hash Join（不禁用全局Merge Join） #

• 会话级临时调整参数
  仅对当前查询禁用Merge Join，不影响全局：

  SET enable_mergejoin = off;
  -- 执行目标查询
  select ev.id from logevent ev ...;
  -- 恢复默认设置（可选）
  SET enable_mergejoin = on;
  

• 使用扩展实现查询提示
  PostgreSQL 11本身不支持原生查询提示，可安装pg_hint_plan扩展强制指定Hash Join：

  /*+ HashJoin(ev FT) */
  select ev.id from logevent ev ...;
  

2. 优化查询逻辑与索引 #

• 提取URI中的Token ID
  在logevent表新增计算列存储提取出的Token ID，将字符串拼接匹配转为字段等值匹配：

  -- 添加计算列
  ALTER TABLE logevent ADD COLUMN workflow_token_id varchar(100) GENERATED ALWAYS AS (
    substring(uri from 'WorkflowToken?id=''([^'']+)''')
  ) STORED;
  -- 建立索引
  CREATE INDEX idx_logevent_workflow_token_id ON logevent(workflow_token_id);
  

  改写查询：

  select ev.id
  from logevent ev
  left join flowtoken FT on ev.workflow_token_id = FT.id
  where ev.uri like 'xxx://xxx/WorkflowToken?id=%'
    and FT.id is null;
  

• 创建部分索引
  针对logevent表中符合过滤条件的行创建索引，提升扫描效率：

  CREATE INDEX idx_logeventuri_workflow_token ON logevent(uri)
  WHERE uri like 'xxx://xxx/WorkflowToken?id=%';
  

3. 调整计划器参数与统计信息 #

• 调整存储成本参数
  若数据库使用SSD存储，降低random_page_cost（默认4）至1.1-2，引导计划器优先选择索引扫描：

  SET random_page_cost = 1.1;
  

• 更新统计信息
  重新收集表的统计信息，确保计划器获取准确的行数估算：

  ANALYZE VERBOSE logevent;
  ANALYZE VERBOSE flowtoken;
  

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