PostgreSQL 行式存储metrics表指定字段转列与业务表关联查询

你当前使用的相关子查询写法，每查询一个属性就要对metrics表做一次扫描，属性越多、数据量越大性能损耗越严重，以下是两种更优的实现方案：

方案1：条件聚合 + 单次LEFT JOIN（全SQL兼容，性能最优） #

该方案仅对metrics表做一次关联扫描，无论需要查询多少个属性都不会增加关联次数，性能比相关子查询高数倍：

SELECT 
  b.*,
  MAX(CASE WHEN m.key = 'a' THEN m.value END) AS property_a,
  MAX(CASE WHEN m.key = 'c' THEN m.value END) AS property_c
FROM business b
LEFT JOIN metrics m 
  ON b.id = m.business_id
  -- 提前过滤不需要的属性，减少扫描数据量
  AND m.key IN ('a', 'c')
GROUP BY b.id;

注：如果你的PostgreSQL版本较低不支持主键作为GROUP BY唯一字段，需要把business表所有查询字段都加到GROUP BY列表中

方案2：PostgreSQL 专属crosstab交叉表函数（行转列专用） #

如果是PostgreSQL环境，可使用专门的行转列工具crosstab，属性较多时代码更简洁：

1. 先启用tablefunc扩展（仅需执行一次）

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行查询

SELECT 
  b.*,
  ct.property_a,
  ct.property_c
FROM business b
LEFT JOIN crosstab(
  -- 第一个参数：查询需要转列的键值对数据
  'SELECT business_id, key, value FROM metrics WHERE key IN (''a'', ''c'') ORDER BY 1,2',
  -- 第二个参数：指定需要转成列的key列表
  'VALUES (''a''), (''c'')'
) AS ct(
  business_id INT, 
  property_a TEXT, -- 此处字段类型要和metrics表的value类型保持一致
  property_c TEXT
) ON b.id = ct.business_id;

通用性能优化建议 #

给metrics表创建联合覆盖索引，可实现查询无需回表，性能再提升一个层级：

CREATE INDEX idx_metrics_bid_key_value ON metrics (business_id, key, value);

另外你原来的子查询代码中存在笔误，关联条件的字段名应为business_id，不是business_is。

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