高写入量分区表的查询性能优化方案咨询

我们每秒向以下表中插入3万行数据：

CREATE UNLOGGED TABLE some_data
(
    ts           TIMESTAMP NOT NULL DEFAULT NOW(),
    a_column     VARCHAR,
    b_column     VARCHAR,
    c_column     boolean
)  PARTITION BY RANGE (ts);

CREATE INDEX ON some_data (ts);
CREATE INDEX ON some_data (a_column);
CREATE INDEX ON some_data (b_column);
CREATE INDEX ON some_data (c_column);

写入进程会在无法插入行时自动创建分区，分区时长为15分钟。数据属于临时数据，会定期删除；采用分区方案是因为删除分区可确保数据被物理移除，且由于插入速率极高，从未运行过VACUUM。此外，我们不要求所有数据都必须写入成功，数据库挂载在tmpfs（内存文件系统）中，当前插入性能符合预期。

另有一个进程持续轮询上述表，通过以下查询获取过去30秒内的Top b_column：

SELECT b_column, count(*) as c FROM some_data
 WHERE ts >= NOW() - INTERVAL '30 SECONDS'
 AND a_column = 'a_value1'
 AND c_column IS NOT True
 AND b_column != 'b_value4' AND b_column != ''
 AND b_column != 'b_value1' AND b_column != 'b_value2'
 AND b_column != 'b_value3'
GROUP BY b_column
HAVING count(*) > 15000
 ORDER BY c DESC
LIMIT 100;

该查询的WHERE子句参数固定，但因数据量较大，查询耗时在1.5-2秒之间。请给出重构表结构或优化查询的具体措施以提升性能。

执行计划（中文翻译） #

Limit  (代价=26488.73..26488.90 行数=67 宽度=30) (实际时间=1210.177..1216.418 行数=4 循环次数=1)
  输出: some_data.b_column, (count(*))
  ->  Sort  (代价=26488.73..26488.90 行数=67 宽度=30) (实际时间=1210.175..1216.415 行数=4 循环次数=1)
        输出: some_data.b_column, (count(*))
        排序键: (count(*)) DESC
        排序方式: quicksort  内存: 25kB
        ->  Finalize GroupAggregate  (代价=26435.53..26486.70 行数=67 宽度=30) (实际时间=1040.285..1216.384 行数=4 循环次数=1)
              输出: some_data.b_column, count(*)
              分组键: some_data.b_column
              过滤条件: (count(*) > 15000)
              被过滤的行数: 180403
              ->  Gather Merge  (代价=26435.53..26482.20 行数=400 宽度=30) (实际时间=1003.876..1136.791 行数=236999 循环次数=1)
                    输出: some_data.b_column, (PARTIAL count(*))
                    计划的工作进程数: 2
                    启动的工作进程数: 1
                    ->  Sort  (代价=25435.51..25436.01 行数=200 宽度=30) (实际时间=977.874..1037.332 行数=118500 循环次数=2)
                          输出: some_data.b_column, (PARTIAL count(*))
                          排序键: some_data.b_column
                          排序方式: 外部合并  磁盘: 5144kB
                          工作进程0: 实际时间=994.460..1056.444 行数=115752 循环次数=1
                            排序方式: 外部合并  磁盘: 4912kB
                          ->  Partial HashAggregate  (代价=25425.86..25427.86 行数=200 宽度=30) (实际时间=641.936..727.838 行数=118500 循环次数=2)
                                输出: some_data.b_column, PARTIAL count(*)
                                分组键: some_data.b_column
                                批处理数: 5  内存使用: 8257kB  磁盘使用: 7184kB
                                工作进程0: 实际时间=637.586..726.780 行数=115752 循环次数=1
                                  批处理数: 5  内存使用: 8257kB  磁盘使用: 7064kB
                                ->  Parallel Index Scan using some_data_2024_02_13_14_15_ts_idx on my_db.some_data_2024_02_13_14_15 some_data  (代价=0.43..24392.95 行数=206582 宽度=22) (实际时间=0.074..463.918 行数=382730 循环次数=2)
                                      输出: some_data.b_column
                                      索引条件: (some_data.ts >= (now() - '00:00:30'::interval))
                                      过滤条件: ((some_data.c_column IS NOT TRUE) AND ((some_data.b_column)::text <> 'b_value4'::text) AND ((some_data.b_column)::text <> ''::text) AND ((some_data.b_column)::text <> 'b_value1'::text) AND ((some_data.b_column)::text <> 'b_value2'::text) AND ((some_data.b_column)::text <> 'b_value3'::text) AND (some_data.a_column = 'a_value1'::a_column))
                                      被过滤的行数: 8091
                                      工作进程0: 实际时间=0.097..466.999 行数=369674 循环次数=1
规划时间: 1.563 ms
执行时间: 1219.166 ms

1. 创建精准的复合/部分索引 #

从执行计划看，当前仅使用了ts单字段索引，之后需要回表过滤a_column、c_column和b_column，导致大量数据被过滤。建议创建覆盖过滤条件的部分索引，直接包含查询所需的b_column，避免回表：

CREATE INDEX ON some_data (ts) INCLUDE (b_column)
WHERE a_column = 'a_value1' AND c_column IS NOT True;

该索引仅包含符合a_column='a_value1'且c_column IS NOT True的行，体积远小于全表索引，查询时可直接通过索引获取ts和b_column，无需访问表数据，大幅减少IO开销。

2. 缩小分区粒度 #

当前分区为15分钟，但查询仅需要最近30秒的数据，单个分区内大部分数据不在查询范围内。建议将分区粒度缩小到1分钟或30秒，这样查询时仅需扫描当前的1-2个分区，而非整个15分钟的分区，减少扫描的数据量。由于数据是临时的，定期删除旧分区即可，不会出现分区过多的维护问题。

3. 预聚合优化（实时统计或物化视图） #

由于查询条件固定，且需要聚合结果，可通过预聚合避免每次查询都全量扫描数据：

• 实时聚合表：创建一个专门的聚合表，通过触发器或应用层逻辑，在插入some_data时更新对应b_column的计数（使用Upsert方式）。查询时直接从聚合表读取结果，几乎无延迟：

  CREATE TABLE b_column_stats (
      b_column VARCHAR PRIMARY KEY,
      count BIGINT NOT NULL DEFAULT 0
  );
  
  -- 插入时更新统计（示例触发器逻辑）
  CREATE OR REPLACE FUNCTION update_b_stats()
  RETURNS TRIGGER AS $$
  BEGIN
      IF NEW.a_column = 'a_value1' AND NEW.c_column IS NOT True 
         AND NEW.b_column NOT IN ('b_value4', '', 'b_value1', 'b_value2', 'b_value3') THEN
          INSERT INTO b_column_stats (b_column, count)
          VALUES (NEW.b_column, 1)
          ON CONFLICT (b_column) DO UPDATE SET count = b_column_stats.count + 1;
      END IF;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trg_update_b_stats AFTER INSERT ON some_data
  FOR EACH ROW EXECUTE FUNCTION update_b_stats();
  

  同时需要定期清理超过30秒的统计数据，可结合ts字段维护有效期。

• 物化视图：创建物化视图定期刷新，适合对延迟容忍度稍高的场景：

  CREATE MATERIALIZED VIEW mv_top_b AS
  SELECT b_column, count(*) as c 
  FROM some_data
  WHERE ts >= NOW() - INTERVAL '30 SECONDS'
    AND a_column = 'a_value1'
    AND c_column IS NOT True
    AND b_column NOT IN ('b_value4', '', 'b_value1', 'b_value2', 'b_value3')
  GROUP BY b_column
  HAVING count(*) > 15000;
  

  用REFRESH MATERIALIZED VIEW mv_top_b;每5-10秒刷新一次，查询直接从物化视图读取。

4. 调整内存参数避免磁盘操作 #

执行计划显示HashAggregate和Sort都使用了磁盘（外部合并、批处理），可临时调大会话级work_mem参数，让这些操作在内存中完成：

SET work_mem = '64MB'; -- 根据实际内存情况调整，比如128MB

该参数仅对当前会话生效，不会影响全局，可在查询进程启动时设置。

5. 查询改写简化过滤逻辑 #

将多个b_column !=条件合并为NOT IN，简化语法且可能帮助优化器生成更高效的执行计划：

SELECT b_column, count(*) as c FROM some_data
 WHERE ts >= NOW() - INTERVAL '30 SECONDS'
 AND a_column = 'a_value1'
 AND c_column IS NOT True
 AND b_column NOT IN ('b_value4', '', 'b_value1', 'b_value2', 'b_value3')
GROUP BY b_column
HAVING count(*) > 15000
 ORDER BY c DESC
LIMIT 100;

6. 表结构优化 #

• 将a_column改为枚举类型：如果a_column的可选值有限，枚举类型比VARCHAR占用更少存储，比较速度更快。
• 明确c_column的默认值：如果c_column大部分场景是False，可设置默认值为False，避免Null值的额外判断开销。

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