PostgreSQL查询jsonb列同邮箱不同externalId数据过慢优化

现有notifications表，存储2742691行数据，核心字段如下：

• id：主键
• payload：jsonb类型，已创建GIN索引，存储业务数据，其中嵌套customer对象，包含email、externalId两个属性
• created_at：记录创建时间

表结构示例数据：

需要返回同时满足以下所有条件的邮箱地址列表：

1. 同一email地址对应存在多条记录
2. 同email的多条记录中，存在至少两条记录的externalId取值不同
3. 所有关联记录的created_at时间在最近一个月范围内

按照示例数据，仅需返回foo@example.com，排除其他邮箱的原因：

• bar@example.com仅出现1次，不满足多行条件
• baz@example.com没有最近一个月内的记录，不满足时间条件
• gna@example.com虽有多条近一个月内的记录，但所有记录的externalId取值完全一致，不满足多ID条件

当前使用LEFT JOIN LATERAL编写的查询语句如下：

select
  n.payload -> 'customer' -> 'email'
from
  notifications n
  left join lateral (
    select
      n2.payload -> 'customer' ->> 'externalId' tid
    from
      notifications n2
    where
      n2.payload @> jsonb_build_object(
        'customer',
        jsonb_build_object('email', n.payload -> 'customer' -> 'email')
      )
      and not (n2.payload @> jsonb_build_object(
        'customer',
        jsonb_build_object('externalId', n.payload -> 'customer' -> 'externalId')
      ))
      and n2.created_at > NOW() - INTERVAL '1 month' 
    limit
      1
  ) sub on true
where
  n.created_at > NOW() - INTERVAL '1 month'
  and sub.tid is not null;

该查询执行耗时极长，执行计划如下：

QUERY PLAN
Nested Loop  (cost=0.17..53386349.38 rows=259398 width=32)
  ->  Index Scan using index_notifications_created_at on notifications n  (cost=0.09..51931.08 rows=259398 width=514)
        Index Cond: (created_at > (now() - '1 mon'::interval))
  ->  Subquery Scan on sub  (cost=0.09..205.60 rows=1 width=0)
        Filter: (sub.tid IS NOT NULL)
        ->  Limit  (cost=0.09..205.60 rows=1 width=32)
              ->  Index Scan using index_notifications_created_at on notifications n2  (cost=0.09..53228.33 rows=259 width=32)
                    Index Cond: (created_at > (now() - '1 mon'::interval))
                    Filter: ((payload @> jsonb_build_object('customer', jsonb_build_object('email', ((n.payload -> 'customer'::text) -> 'email'::text)))) AND (NOT (payload @> jsonb_build_object('customer', jsonb_build_object('externalId', ((n.payload -> 'customer'::text) -> 'externalId'::text))))))
JIT:
  Functions: 13
  Options: Inlining true, Optimization true, Expressions true, Deforming true

• 嵌套循环导致复杂度飙升：执行计划走Nested Loop，外层先扫描近一个月约26万条记录，对每一条外层记录都要再执行一次内层表查询，整体时间复杂度接近O(N²)，总执行代价超过5000万，是性能差的核心原因
• GIN索引未被利用：内层查询优先命中created_at普通索引，返回近一个月所有数据后再逐行做JSON条件匹配，payload列的GIN索引完全没生效，JSON包含判断的计算开销极大
• 存在重复结果：同一个符合条件的email会被重复返回多次，比如一个email对应3个不同externalId，会至少返回2次重复结果，额外浪费计算资源

直接使用聚合逻辑替代LATERAL嵌套查询，仅需单次扫描近一个月数据集即可完成计算，优化后SQL如下：

SELECT
  payload -> 'customer' ->> 'email' AS email
FROM
  notifications
WHERE
  created_at > NOW() - INTERVAL '1 month'
GROUP BY
  email
HAVING
  COUNT(DISTINCT payload -> 'customer' ->> 'externalId') >= 2;

该写法的优势：

• 时间复杂度降到O(N)，仅需扫描一次近一个月的数据集，完全避免嵌套循环
• 逻辑贴合需求：按email分组后，直接统计每个email关联的不同externalId数量，大于等于2即满足条件，天然去重，不会返回重复结果

额外索引优化 #

如果近一个月数据量仍然较大，可以创建针对性的覆盖索引，避免回表，进一步提速：

CREATE INDEX idx_notifications_created_at_cust ON notifications (created_at)
INCLUDE ((payload -> 'customer' ->> 'email'), (payload -> 'customer' ->> 'externalId'));

如果坚持使用@>操作符做JSON匹配，可将现有GIN索引替换为jsonb_path_ops类型，对包含查询的速度比默认jsonb_ops快约3倍，但注意该类型索引不支持键存在判断、范围查询等其他JSON操作。

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