PostgreSQL按最大日期查询客户各item最新值的优化方案咨询

索引优化建议（400万数据量下优先执行效率提升最明显： #

• 建议创建联合覆盖索引，查询时可直接命中索引无需回表访问原表：

CREATE INDEX idx_tbl_item_cust_ts_val ON tbl (item, customer_id, timestamp DESC, value);

高效查询方案 #

无需为每个item单独写子查询做JOIN，仅需一次数据扫描即可完成最多10个item的查询，扩展性好、性能远高于多子查询JOIN的方案。
查询示例如下：

SELECT
  customer_id,
  MAX(CASE WHEN item = 'price' THEN value END) AS price,
  MAX(CASE WHEN item = 'condition' THEN value END) AS condition,
  MAX(CASE WHEN item = 'feeling' THEN value END) AS feeling,
  MAX(CASE WHEN item = 'weather' THEN value END) AS weather
  -- 新增查询item只需要新增对应CASE WHEN行即可
FROM (
  SELECT DISTINCT ON (customer_id, item)
    customer_id,
    item,
    value
  FROM tbl
  WHERE item IN ('price', 'condition', 'feeling', 'weather') -- 填入本次需要查询的所有item
  ORDER BY customer_id, item, timestamp DESC
) t
GROUP BY customer_id;

如果需要返回所有在Customer_table中存在的客户（包括没有任何上报记录的客户），调整为如下写法即可：

SELECT
  c.customer_id,
  MAX(CASE WHEN t.item = 'price' THEN t.value END) AS price,
  MAX(CASE WHEN t.item = 'condition' THEN t.value END) AS condition,
  MAX(CASE WHEN t.item = 'feeling' THEN t.value END) AS feeling,
  MAX(CASE WHEN t.item = 'weather' THEN t.value END) AS weather
FROM Customer_table c
LEFT JOIN (
  SELECT DISTINCT ON (customer_id, item)
    customer_id,
    item,
    value
  FROM tbl
  WHERE item IN ('price', 'condition', 'feeling', 'weather')
  ORDER BY customer_id, item, timestamp DESC
) t ON c.customer_id = t.customer_id
GROUP BY c.customer_id;

方案说明 #

1. 内层查询先过滤出需要的item的所有记录，通过DISTINCT ON按照「客户+item」维度取每个维度下时间最新的一条记录，直接过滤掉历史过期旧数据，大幅减少后续处理的数据量。
2. 外层通过条件聚合做行转列，需要新增查询item时，只需要在IN条件里加对应的item值，同时新增对应CASE WHEN的聚合列即可，最多10个item的扩展非常方便。
3. 配合前面的覆盖索引，整个查询可以直接走索引扫描，400万数据量下性能表现优异。

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