PostgreSQL带CASE条件的表连接性能问题及优化咨询

为什么CASE条件导致查询极慢？ #

1. 索引失效：CASE语句里用到了LENGTH()和RIGHT()函数，这类函数会破坏product_id字段上常规索引的可用性。数据库无法通过索引快速定位匹配行，只能对两张表执行全表扫描。
2. 连接优化失效：数据库处理JOIN时，复杂的CASE条件会让优化器无法选择高效的连接策略（比如嵌套循环、哈希连接），只能逐行计算CASE结果来判断是否匹配，本质上是做笛卡尔积式的比对，直接导致查询耗时暴增。

更优的表关联方法 #

方法1：预处理数据，统一格式（推荐长期方案） #

从根源解决格式不统一问题，给两张表新增标准化字段：

1. 给table_a和table_b新增字段normalized_product_id（建议设为12位字符串类型）。
2. 批量更新数据：
   • 若原product_id是12位，直接赋值给normalized_product_id；
   • 若原product_id是6位，根据业务规则补全前6位（比如从关联分类表获取前缀，或按固定规则拼接）。
3. 给标准化字段创建索引：

CREATE INDEX idx_table_a_normalized_pid ON table_a(normalized_product_id);
CREATE INDEX idx_table_b_normalized_pid ON table_b(normalized_product_id);

4. 后续查询直接用标准化字段关联，速度和方法1一致：

SELECT * FROM table_a ta JOIN table_b tb ON ta.normalized_product_id = tb.normalized_product_id;

方法2：改写关联条件，拆分逻辑并创建函数索引 #

不用CASE，把条件拆成精确匹配+异长后6位匹配的OR逻辑，同时给后6位计算结果建索引：

1. 创建函数索引：

-- MySQL 版本
CREATE INDEX idx_table_a_right6_pid ON table_a(RIGHT(product_id, 6));
CREATE INDEX idx_table_b_right6_pid ON table_b(RIGHT(product_id, 6));

-- PostgreSQL 版本（表达式索引）
CREATE INDEX idx_table_a_right6_pid ON table_a(substring(product_id from length(product_id)-5 for 6));
CREATE INDEX idx_table_b_right6_pid ON table_b(substring(product_id from length(product_id)-5 for 6));

2. 改写查询语句：

SELECT * FROM table_a ta
JOIN table_b tb ON
    -- 优先精确匹配，走原索引
    ta.product_id = tb.product_id
    OR
    -- 异长时匹配后6位，走函数索引
    (LENGTH(ta.product_id) != LENGTH(tb.product_id) 
     AND RIGHT(ta.product_id, 6) = RIGHT(tb.product_id, 6));

这个方法比CASE快很多，两个分支都能利用对应的索引。

方法3：分两次查询合并结果 #

把精确匹配和异长匹配拆成两个独立查询，用UNION ALL合并，避免OR逻辑的潜在性能问题：

-- 第一部分：精确匹配，走原索引
SELECT * FROM table_a ta
JOIN table_b tb ON ta.product_id = tb.product_id

UNION ALL

-- 第二部分：仅异长且后6位匹配的记录，走函数索引
SELECT * FROM table_a ta
JOIN table_b tb ON
    LENGTH(ta.product_id) != LENGTH(tb.product_id)
    AND RIGHT(ta.product_id, 6) = RIGHT(tb.product_id, 6)
-- 排除已在精确匹配中的重复数据
WHERE NOT EXISTS (
    SELECT 1 FROM table_b tb2 WHERE ta.product_id = tb2.product_id
);

两次查询都能高效利用索引，结果准确且速度接近方法1。

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