PostgreSQL高效存储数据创建分面桶的方案探讨

方案一：预计算物化视图（最优推荐） #

针对6万条数据的规模，若数据更新不频繁，用物化视图预计算所有分面的统计值，查询时直接读取预存结果，性能最优。

创建物化视图 #

假设你的分面字段为category、brand、price_range等15+个字段，执行以下SQL创建物化视图：

CREATE MATERIALIZED VIEW product_facets AS
-- 分面1：商品分类
SELECT 
  'category' AS attr, 
  category AS value, 
  COUNT(*) AS count
FROM product
GROUP BY category

UNION ALL
-- 分面2：品牌
SELECT 
  'brand' AS attr, 
  brand AS value, 
  COUNT(*) AS count
FROM product
GROUP BY brand

UNION ALL
-- 分面3：价格区间（示例）
SELECT 
  'price_range' AS attr, 
  CASE 
    WHEN price < 100 THEN '0-99'
    WHEN price < 500 THEN '100-499'
    ELSE '500+' 
  END AS value, 
  COUNT(*) AS count
FROM product
GROUP BY price_range

-- 依次添加剩余分面字段的统计逻辑;

添加索引加速查询 #

CREATE INDEX idx_product_facets_attr_value ON product_facets(attr, value);

查询分面结果 #

SELECT attr, value, count FROM product_facets ORDER BY attr, value;

数据更新时刷新物化视图 #

-- 普通刷新（锁表，适合低峰期）
REFRESH MATERIALIZED VIEW product_facets;

-- 并发刷新（需物化视图有唯一索引，适合业务高峰期）
REFRESH MATERIALIZED VIEW CONCURRENTLY product_facets;

方案二：实时复合索引+分组查询 #

如果需要实时统计（数据更新频繁），直接针对每个分面字段创建FTS+字段的复合索引，通过UNION ALL一次性获取所有分面结果。

创建复合索引 #

为每个分面字段与FTS向量字段创建复合索引：

-- 分类+FTS向量索引
CREATE INDEX idx_product_tsv_category ON product(tsv, category);

-- 品牌+FTS向量索引
CREATE INDEX idx_product_tsv_brand ON product(tsv, brand);

-- 其他分面字段同理创建索引;

实时查询分面结果 #

替换your_search_query为实际的全文检索条件：

SELECT 
  'category' AS attr, 
  category AS value, 
  COUNT(*) AS count 
FROM product 
WHERE to_tsquery('your_search_query') @@ tsv 
GROUP BY category

UNION ALL
SELECT 
  'brand' AS attr, 
  brand AS value, 
  COUNT(*) AS count 
FROM product 
WHERE to_tsquery('your_search_query') @@ tsv 
GROUP BY brand

-- 添加剩余分面字段的查询逻辑
ORDER BY attr, value;

方案三：优化现有ts_stat方案 #

如果坚持使用ts_stat，可通过以下方式优化：

1. 移除不必要排序：若业务无需按word排序，直接删除ORDER BY word，可节省近2秒的排序耗时；
2. 临时表预存+索引排序：

-- 预存ts_stat结果到临时表
CREATE TEMP TABLE temp_ts_stat AS
SELECT 
  split_part(word, ':', 1) AS attr,
  split_part(word, ':', 2) AS value,
  ndoc AS count
FROM ts_stat($$ SELECT tsvagg FROM product $$);

-- 临时表加索引加速排序
CREATE INDEX idx_temp_ts_stat_attr ON temp_ts_stat(attr, value);

-- 查询排序结果
SELECT attr, value, count FROM temp_ts_stat ORDER BY attr, value;

方案对比 #

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