Postgres超大表慢查询优化：2TB库三表关联查询执行效率提升问询

核心瓶颈定位 #

从执行计划可以明确耗时根源：

• 当前执行计划选择了先扫描tbl1时间范围的逻辑，共扫描出近1500万行符合client和时间条件的tbl1记录，再逐行关联tbl2做过滤
• 1400多万次tbl2索引查询中，99%的记录都被property->>'num'条件过滤掉，最终仅保留871行有效数据，无意义的索引查询消耗了99%的执行时间，累计缓冲区命中超过2.49亿次

优化方案 #

1. 新增缺失的覆盖索引 #

-- 为tbl2创建过滤+关联覆盖索引，无需回表即可拿到au查询需要的所有字段
CREATE INDEX idx_tbl2_client_num_uid ON tbl2 (client, (property->>'num'), uid);

-- 为tbl1扩展现有索引为覆盖索引，关联uid和返回字段都可直接从索引获取，无需回表
CREATE INDEX idx_tbl1_client_date_uid_cover ON tbl1 (client, date_col DESC, uid) 
INCLUDE (sid, tbl3number, name, id);

2. SQL逻辑改写 #

调整连接顺序，先过滤出小体积的tbl2结果集再关联tbl1，避免千万级的无效嵌套循环查询，同时简化冗余条件：

explain (ANALYZE, COSTS, VERBOSE, BUFFERS)
-- 强制先物化CTE的小结果集，避免PG自动展开后回到错误的连接顺序
WITH au AS MATERIALIZED (
    SELECT uid
    FROM tbl2 
    WHERE tbl2.client = '123kkjk444kjkhj3ddd'
      AND (tbl2.property->>'num') IN ('1', '2', '3', '31', '12a', '45', '78', '99')
)
SELECT 
    tbl1.id,
    COALESCE(tbl3.displayname, tbl1.name) AS name,
    tbl1.tbl3number, 
    tbl3.originalname as orgtbl3
FROM au
INNER JOIN tbl1 
    ON tbl1.client = '123kkjk444kjkhj3ddd'
    AND tbl1.uid = au.uid
    AND tbl1.date_col BETWEEN '2021-08-01T05:32:40Z' AND '2021-08-29T05:32:40Z'
LEFT JOIN tbl3 
    ON tbl3.client = '123kkjk444kjkhj3ddd' 
    AND tbl3.originalname = tbl1.name
ORDER BY tbl1.date_col DESC, tbl1.sid, tbl1.tbl3number
LIMIT 50000;

优化预期 #

调整后整体执行耗时可降低到1秒以内：

• 第一步查询tbl2的au结果集仅需一次索引扫描，返回的uid数量不超过千级
• 用千级的uid去关联tbl1，仅需要千次索引查询，远低于原来的1400万次
• 所有查询都走覆盖索引，无回表开销，最终排序的记录量仅871行，几乎无消耗

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