PostgreSQL 15千万级账本表按数组标签关联分组求和性能优化

问题背景 #

使用PostgreSQL 15，账本表myschema.ledger数据量超1000万行，简化结构如下：

需求：按分类汇总指定用户的金额，分类由tags数组中的一个条目映射而来（其余标签忽略），通过myschema.tag_category表实现标签到自定义分类的1对1映射，预期结果：

现有实现与瓶颈 #

现有查询语句：

SELECT SUM(amount) as total, tc.tag as category
FROM myschema.tag_category tc
JOIN myschema.ledger l 
ON tc.category = ANY(l.tags)
WHERE l.user_id = '112233' 
GROUP BY tc.tag

已创建索引：

CREATE INDEX ON myschema.ledger(user_id);
CREATE INDEX ON myschema.points_ledger USING GIN (tags);

当前查询耗时约400毫秒，需要进一步优化性能。

优化方案及建议 #

1. 调整查询逻辑：先过滤用户再关联映射表 #

原查询从tag_category出发关联大表，改为先筛选指定用户的账本数据，再拆分数组标签关联映射表，减少关联的数据量：

SELECT SUM(l.amount) AS total, tc.tag AS category
FROM myschema.ledger l
-- 拆分数组中的每个标签
CROSS JOIN UNNEST(l.tags) AS tag_item
JOIN myschema.tag_category tc ON tc.category = tag_item
WHERE l.user_id = '112233'
GROUP BY tc.tag;

2. 创建复合索引提升过滤+拆标签效率 #

针对user_id和tags创建复合GIN索引，同时覆盖amount字段，避免回表：

CREATE INDEX idx_ledger_user_tags_amount ON myschema.ledger USING GIN (user_id, tags) INCLUDE (amount);

如果PostgreSQL版本支持，可先安装btree_gin扩展，让user_id（B-tree类型）和tags（GIN类型）更好结合：

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS btree_gin;
-- 创建复合索引
CREATE INDEX idx_ledger_user_tags_amount ON myschema.ledger USING GIN (user_id, tags) INCLUDE (amount);

3. 优化分组逻辑：提前聚合减少计算量 #

如果tag_category表数据量小，可以先预存标签到分类的映射，再对用户账本数据的标签进行映射后聚合：

WITH tag_map AS (
    SELECT category, tag FROM myschema.tag_category
)
SELECT SUM(l.amount) AS total, tm.tag AS category
FROM myschema.ledger l
CROSS JOIN UNNEST(l.tags) AS tag_item
JOIN tag_map tm ON tm.category = tag_item
WHERE l.user_id = '112233'
GROUP BY tm.tag;

4. 检查执行计划，确认索引使用 #

执行EXPLAIN ANALYZE查看查询执行计划，确认是否用到了预期的索引：

EXPLAIN ANALYZE
SELECT SUM(l.amount) AS total, tc.tag AS category
FROM myschema.ledger l
CROSS JOIN UNNEST(l.tags) AS tag_item
JOIN myschema.tag_category tc ON tc.category = tag_item
WHERE l.user_id = '112233'
GROUP BY tc.tag;

如果发现索引未被使用，可临时关闭全表扫描强制数据库选择索引（仅用于测试，生产环境不建议长期设置）：

SET enable_seqscan = off;

5. 数据结构优化（可选） #

如果业务允许，考虑将tags数组拆分为单独的关联表，避免数组操作的开销：

-- 创建关联表
CREATE TABLE myschema.ledger_tags (
    ledger_id INT REFERENCES myschema.ledger(id),
    tag TEXT,
    PRIMARY KEY (ledger_id, tag)
);
-- 导入数据
INSERT INTO myschema.ledger_tags (ledger_id, tag)
SELECT id, unnest(tags) FROM myschema.ledger;
-- 创建索引
CREATE INDEX idx_ledger_tags_tag ON myschema.ledger_tags(tag);
CREATE INDEX idx_ledger_tags_ledger_id ON myschema.ledger_tags(ledger_id);

之后的查询可改为：

SELECT SUM(l.amount) AS total, tc.tag AS category
FROM myschema.ledger l
JOIN myschema.ledger_tags lt ON l.id = lt.ledger_id
JOIN myschema.tag_category tc ON tc.category = lt.tag
WHERE l.user_id = '112233'
GROUP BY tc.tag;

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