WHERE子句更换关联字段导致PostgreSQL查询异常变慢

原查询与修改操作 #

原查询执行耗时约1秒：

SELECT id, dt
FROM table1 t1
WHERE status is not null
    AND (
        (NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.big_int_id = t1.big_int_id and t2.count is not null)
            AND t1.dt > NOW() - INTERVAL '15 minutes'
        ) OR
        (NOT EXISTS
            (SELECT 1 FROM table3 t3
                JOIN table2 t2 ON t2.another_id = t3.another_id AND
                    t2.big_int_id = t3.big_int_id
            )
            AND t1.dt BETWEEN NOW() - INTERVAL '15 minutes' AND NOW()
        )
    )

将子查询中的关联字段从big_int_id改为text_id后，查询耗时超过10分钟：
修改前的子查询：

(SELECT 1 FROM table2 t2 WHERE t2.big_int_id = t1.big_int_id and t2.count is not null)

修改后的子查询：

(SELECT 1 FROM table2 t2 WHERE t2.text_id = t1.text_id and t2.count is not null)

关键差异点 #

修改后存在两个核心变化：

• 关联字段类型从bigint大整数变为text文本（格式为"XYZ-#####"，#####是1到10亿的整数）
• 数据分布差异：
  • table1中每个不同的big_int_id对应2行数据
  • table2中每个不同的text_id对应1行数据，但98%的行text_id为null（table1的text_id从不为null）

执行计划对比 #

原查询执行计划 #

Seq Scan on table t1  (cost=0.00..7362242.49 rows=1 width=24)
  Filter: ((status is null) AND (((NOT (SubPlan 1)) AND (dt > (now() - '00:15:00'::interval))) OR ((NOT (SubPlan 2)) AND (dt >= (now() - '00:15:00'::interval)) AND (dt <= now()))))
  SubPlan 1
    ->  Index Scan using idx_t2_QRID on table2 t2  (cost=0.56..80.66 rows=17 width=0)
          Index Cond: (big_int_id = t1.big_int_id)
          Filter: (count IS NOT NULL)
  SubPlan 2
    ->  Nested Loop  (cost=0.99..239.84 rows=2 width=0)
          ->  Index Only Scan using idx_t2_QRID on table2 q_1  (cost=0.56..22.64 rows=38 width=8)
                Index Cond: (big_int_id = q_1.big_int_id)
          ->  Index Only Scan using idx_table3_qid_rt on table3 t3  (cost=0.42..5.71 rows=1 width=8)
                Index Cond: (big_int_id = q_1.big_int_id)

修改后查询执行计划 #

Seq Scan on table t1  (cost=0.00..2318157244.23 rows=1 width=24)
  Filter: ((status is null) AND (((NOT (SubPlan 1)) AND (dt > (now() - '00:15:00'::interval))) OR ((NOT (SubPlan 2)) AND (dt >= (now() - '00:15:00'::interval)) AND (dt <= now()))))
  SubPlan 1
    ->  Seq Scan on table2 t2  (cost=0.00..671602.70 rows=17 width=0)
          Filter: ((count IS NOT NULL) AND (text_id = t1.text_id))
  SubPlan 2
    ->  Nested Loop  (cost=0.99..239.84 rows=2 width=0)
          ->  Index Only Scan using idx_t2_QRID on table2 q_1  (cost=0.56..22.64 rows=38 width=8)
                Index Cond: (big_int_id = t1.big_int_id)
          ->  Index Only Scan using idx_table3_qid_rt on table3 t3  (cost=0.42..5.71 rows=1 width=8)
                Index Cond: (big_int_id = q_1.big_int_id)

各表索引情况 #

table1索引 #

CREATE INDEX t1_text_id     ON t1 (text_id)
CREATE INDEX t1_id          ON t1 (id)
CREATE UNIQUE INDEX t1_pkey ON t1 (id10)

table2索引 #

CREATE UNIQUE INDEX idx_t2_text_id ON t2 (text_id)
CREATE INDEX idx_t2_QRID           ON t2 (big_int_id, id5)
CREATE UNIQUE INDEX t2_pkey        ON t2 (id5)

table3索引 #

CREATE INDEX idx_table3_qid_rt  ON t3 (big_int_id, response_type)
CREATE UNIQUE INDEX table3_pkey ON t3 (big_int_id)

性能骤降原因分析 #

1. 索引未被利用：修改后的子查询1放弃了原有的索引扫描，改为全表扫描（Seq Scan）。虽然table2上有idx_t2_text_id索引，但PostgreSQL没有选择使用它——原因可能是table2中98%的text_id为null，统计信息让数据库认为全表扫描的成本更低，但实际每次子查询都要遍历整个table2，而原查询是利用idx_t2_QRID索引快速定位匹配行。
2. 数据类型对比开销：text类型的字符串对比比bigint整数对比的CPU开销高很多，尤其是当需要对比的字符串长度较长时，每次匹配都要逐字符比较，进一步放大了全表扫描的性能损耗。
3. 子查询执行次数：原查询中table1的每一行都会触发一次子查询1，原查询用索引可以快速返回结果；修改后每次子查询都要全量扫描table2，当table1数据量较大时，累计的执行成本会呈指数级上升，直接导致总耗时从秒级变为分钟级。

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