优化PostgreSQL查询：Bitmap堆扫描性能缓慢问题

针对你900万条记录的docket表，结合PostgreSQL 12的特性，以下是针对性优化方案：

1. 优化索引策略 #

基础过滤复合索引 #

先针对account_id和docket_type_id建立复合B-tree索引，快速缩小数据集范围：

CREATE INDEX idx_docket_account_type ON docket (account_id, docket_type_id);

JSONB条件的部分表达式索引 #

由于查询中需要检查equipment数组内的quantity值，普通索引无法生效，建议创建部分表达式索引，直接过滤出符合JSON条件的行，同时包含排序字段以避免额外排序开销：

CREATE INDEX idx_docket_equipment_quantity ON docket (account_id, docket_type_id, docket_id DESC)
WHERE EXISTS (
    SELECT 1 FROM jsonb_array_elements(equipment) eq
    WHERE (eq->>'quantity')::integer >= 150
);

如果需要支持任意quantity阈值，可创建通用的函数索引：

-- 先创建判断函数
CREATE OR REPLACE FUNCTION has_equipment_quantity_ge(jsonb, integer)
RETURNS boolean AS $$
SELECT EXISTS (
    SELECT 1 FROM jsonb_array_elements($1) eq
    WHERE (eq->>'quantity')::integer >= $2
);
$$ LANGUAGE sql IMMUTABLE;

-- 创建函数索引
CREATE INDEX idx_docket_equipment_quantity_func ON docket (account_id, docket_type_id)
USING btree ((has_equipment_quantity_ge(equipment, 0)));

2. 改写查询语句 #

将JSON字段的取值方式从eq->'quantity'改为eq->>'quantity'，前者返回JSONB类型，转整数需要额外解析开销，后者直接返回文本，转换更高效：

SELECT d.docket_id, d.docket_type_id
FROM docket d
WHERE d.account_id = 557 
  AND d.docket_type_id = ANY(array[2])
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(d.equipment) as eq
    WHERE (eq->>'quantity')::integer >= 150
)
ORDER BY docket_id desc
-- limit 500

如果启用LIMIT 500，结合上面包含docket_id DESC的部分索引，PostgreSQL可以直接通过索引扫描获取前500条数据，无需全量过滤后排序。

3. 更新统计信息 #

由于表数据量较大，可能存在统计信息过时的情况，导致执行计划选择不合理，执行以下命令更新统计：

ANALYZE docket;

4. 物化视图优化（可选） #

如果quantity阈值相对固定，或者常用几个特定阈值，可以创建物化视图提前计算符合条件的行：

CREATE MATERIALIZED VIEW mv_docket_qualified AS
SELECT docket_id, docket_type_id, account_id
FROM docket d
WHERE EXISTS (
    SELECT 1 FROM jsonb_array_elements(d.equipment) eq
    WHERE (eq->>'quantity')::integer >= 150
);

-- 给物化视图建索引
CREATE INDEX idx_mv_docket_account_type ON mv_docket_qualified (account_id, docket_type_id, docket_id DESC);

查询时直接从物化视图读取，速度会大幅提升，但需要定期刷新物化视图以保持数据最新：

REFRESH MATERIALIZED VIEW mv_docket_qualified;

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