大数据场景下关联JSONB列的Count查询优化求助

问题场景 #

需基于JSONB列内嵌套对象过滤，同时关联另一张表进行去重计数。原查询语句如下：

SELECT COUNT(*) AS total
FROM (
    SELECT DISTINCT s.mid
    FROM "whatsapp-cloud".statuses s
    LEFT JOIN "whatsapp-cloud".outbounds o ON s.mid = o.mid 
    WHERE s."data"->'entry'->0->'changes'->0->'value'->'statuses'->0->'conversation'->'origin'->>'type' = 'utility'
        AND s."type" = 'delivered'
        AND s.created_at::date = '2023-02-28'::date
        and s.account_id  = {account_id}
        AND o."type" = 'template'
) AS counter;

涉及表信息 #

核心表whatsapp-cloud.statuses存储空间达77GB，约43492221行数据。两张表的DDL结构如下：

statuses表DDL #

CREATE TABLE "whatsapp-cloud".statuses (
id varchar(50) NOT NULL DEFAULT uuid_generate_v1()::character varying(50),
client_id varchar(50) NOT NULL,
channel_id varchar(50) NOT NULL,
account_id varchar(50) NOT NULL,
application_id varchar(50) NOT NULL,
"oid" varchar(150) NULL,
xid varchar(100) NULL,
mid varchar(200) NOT NULL,
"type" varchar(50) NULL,
error varchar(50) NULL,
"data" jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT statuses_pkey PRIMARY KEY (id));
CREATE INDEX idx_statuses_account_id ON "whatsapp-cloud".statuses USING btree (account_id);
CREATE INDEX idx_statuses_channel_id ON "whatsapp-cloud".statuses USING btree (channel_id);
CREATE INDEX idx_statuses_cid ON "whatsapp-cloud".statuses USING btree (oid);
CREATE INDEX idx_statuses_client_id ON "whatsapp-cloud".statuses USING btree (client_id);
CREATE INDEX idx_statuses_mid ON "whatsapp-cloud".statuses USING btree (mid);
CREATE INDEX idx_statuses_xid ON "whatsapp-cloud".statuses USING btree (xid);
CREATE INDEX idx_whatsapp_cloud_type ON "whatsapp-cloud".statuses USING btree (type);
CREATE INDEX statuses_date_of_tz_idx ON "whatsapp-cloud".statuses USING btree (date_of_tz(created_at));

outbounds表DDL #

CREATE TABLE "whatsapp-cloud".outbounds (
id varchar(50) NOT NULL DEFAULT uuid_generate_v1()::character varying(50),
xid varchar(100) NULL,
mid varchar(200) NULL,
client_id varchar(50) NOT NULL,
channel_id varchar(50) NOT NULL,
account_id varchar(50) NOT NULL,
application_id varchar(50) NULL,
"type" varchar(50) NULL,
recipient varchar(350) NOT NULL,
error varchar(50) NULL,
"data" jsonb NOT NULL,
"result" jsonb NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamp NULL,
CONSTRAINT outbounds_pkey PRIMARY KEY (id));
CREATE INDEX idx_outbound_created_at ON "whatsapp-cloud".outbounds USING btree (((timezone('UTC'::text, created_at))::date));
CREATE INDEX idx_outbounds_account_id ON "whatsapp-cloud".outbounds USING btree (account_id);
CREATE INDEX idx_outbounds_channel_id ON "whatsapp-cloud".outbounds USING btree (channel_id);
CREATE INDEX idx_outbounds_client_id ON "whatsapp-cloud".outbounds USING btree (client_id);
CREATE INDEX idx_outbounds_mid ON "whatsapp-cloud".outbounds USING btree (mid);
CREATE INDEX idx_outbounds_xid ON "whatsapp-cloud".outbounds USING btree (xid);
CREATE INDEX outbounds_date_of_tz_idx ON "whatsapp-cloud".outbounds USING btree (date_of_tz(created_at));
CREATE INDEX outbounds_month_of_tz_idx ON "whatsapp-cloud".outbounds USING btree (month_of_tz(created_at));
CREATE INDEX outbounds_type_idx ON "whatsapp-cloud".outbounds USING btree (type);
CREATE INDEX outbounds_year_of_tz_idx ON "whatsapp-cloud".outbounds USING btree (year_of_tz(created_at));
CREATE UNIQUE INDEX uidx_outbounds_xid ON "whatsapp-cloud".outbounds USING btree (xid, client_id);

性能问题 #

因JSONB嵌套字段过滤+表关联，查询执行耗时差异极大：结果为0时约1秒，结果为1万/10万条时耗时剧增。每日需针对数百个account_id执行该查询，CRON任务从00:01开始，通常耗时超13小时，最长近17小时。

Count=0时的EXPLAIN ANALYZE结果：

Aggregate  (cost=23344.51..23344.52 rows=1 width=8) (actual time=0.089..0.090 rows=1 loops=1)
  Output: count(*)
  ->  Unique  (cost=23344.49..23344.50 rows=1 width=62) (actual time=0.085..0.086 rows=0 loops=1)
        Output: s.mid
        ->  Sort  (cost=23344.49..23344.49 rows=1 width=62) (actual time=0.084..0.085 rows=0 loops=1)
              Output: s.mid
              Sort Key: s.mid
              Sort Method: quicksort  Memory: 25kB
              ->  Nested Loop  (cost=1.13..23344.48 rows=1 width=62) (actual time=0.048..0.049 rows=0 loops=1)
                    Output: s.mid
                    ->  Index Scan using idx_statuses_account_id on "whatsapp-cloud".statuses s  (cost=0.56..23335.89 rows=1 width=62) (actual time=0.047..0.048 rows=0 loops=1)
                          Output: s.id, s.client_id, s.channel_id, s.account_id, s.application_id, s.oid, s.xid, s.mid, s.type, s.error, s.data, s.created_at
                          Index Cond: ((s.account_id)::text = '{account_id}'::text)
                          Filter: (((s.type)::text = 'delivered'::text) AND ((s.created_at)::date = '2023-02-28'::date) AND (((((((((((s.data -> 'entry'::text) -> 0) -> 'changes'::text) -> 0) -> 'value'::text) -> 'statuses'::text) -> 0) -> 'conversation'::text) -> 'origin'::text) ->> 'type'::text) = 'utility'::text))
                    ->  Index Scan using idx_outbounds_mid on "whatsapp-cloud".outbounds o  (cost=0.56..8.58 rows=1 width=62) (never executed)
                          Output: o.id, o.xid, o.mid, o.client_id, o.channel_id, o.account_id, o.application_id, o.type, o.recipient, o.error, o.data, o.result, o.created_at, o.updated_at
                          Index Cond: ((o.mid)::text = (s.mid)::text)
                          Filter: ((o.type)::text = 'template'::text)
Planning Time: 316.699 ms
Execution Time: 0.258 ms

优化方案 #

1. 重构查询逻辑，替换LEFT JOIN为EXISTS子查询 #

原查询中LEFT JOIN后添加o.type = 'template'过滤，实际等价于INNER JOIN，使用EXISTS可以避免DISTINCT的排序开销（原计划中出现了Sort和Unique步骤），且EXISTS找到匹配项后立即停止扫描，性能更优。修改后的查询：

SELECT COUNT(DISTINCT s.mid) AS total
FROM "whatsapp-cloud".statuses s
WHERE s.account_id = {account_id}
  AND s.type = 'delivered'
  AND s.created_at::date = '2023-02-28'::date
  AND s.data->'entry'->0->'changes'->0->'value'->'statuses'->0->'conversation'->'origin'->>'type' = 'utility'
  AND EXISTS (
    SELECT 1
    FROM "whatsapp-cloud".outbounds o
    WHERE o.mid = s.mid
      AND o.type = 'template'
  );

2. 创建JSONB字段的复合表达式索引 #

原查询中JSONB的嵌套路径固定，创建包含所有过滤条件的复合表达式索引，避免每次查询解析JSONB并回表：

CREATE INDEX idx_statuses_filter_full 
ON "whatsapp-cloud".statuses 
USING btree (
  account_id,
  type,
  (created_at::date),
  ((data->'entry'->0->'changes'->0->'value'->'statuses'->0->'conversation'->'origin'->>'type'))
);

该索引覆盖了account_id、type、created_at日期、JSONB嵌套的type字段，查询可直接走索引扫描，无需访问表数据。

3. 利用已有的时区日期索引 #

原查询使用s.created_at::date，如果date_of_tz是业务中统一的时区处理函数，建议将过滤条件改为：

date_of_tz(s.created_at) = '2023-02-28'::date

这样可以直接利用已创建的statuses_date_of_tz_idx索引，避免类型转换的额外开销。

4. 优化outbounds表的查询索引 #

针对EXISTS子查询的条件mid = s.mid AND type = 'template'，创建复合索引：

CREATE INDEX idx_outbounds_mid_type 
ON "whatsapp-cloud".outbounds 
USING btree (mid, type);

该索引可以直接定位到符合条件的记录，无需先扫描mid索引再过滤type。

5. 批量处理多个account_id #

避免循环逐个查询数百个account_id，改为批量查询减少规划时间（原单查询规划时间达316ms）：

SELECT s.account_id, COUNT(DISTINCT s.mid) AS total
FROM "whatsapp-cloud".statuses s
WHERE date_of_tz(s.created_at) = '2023-02-28'::date
  AND s.type = 'delivered'
  AND s.data->'entry'->0->'changes'->0->'value'->'statuses'->0->'conversation'->'origin'->>'type' = 'utility'
  AND EXISTS (
    SELECT 1
    FROM "whatsapp-cloud".outbounds o
    WHERE o.mid = s.mid
      AND o.type = 'template'
  )
  AND s.account_id IN ('acc_1', 'acc_2', ...) -- 批量传入需要统计的account_id列表
GROUP BY s.account_id;

6. 可选：去除不必要的DISTINCT #

如果业务上statuses表中同一个mid不会重复满足所有过滤条件，可以去掉COUNT(DISTINCT)，直接使用COUNT(*)进一步提升性能，但需先确认业务逻辑。

内容的提问来源于stack exchange，提问作者Ibnu Agiels Althur