看起来你已经基于TimescaleDB搭好了审计日志超表，这个选型本身就很贴合审计数据的时间序列增长特性。针对「过滤非分区列时高效查询近期行」的需求，我结合实际优化经验给你梳理几个核心方案和避坑要点：

一、必做基础：用分区时间列做前置过滤 #

Timescale超表的性能核心就是分区裁剪——只扫描符合时间范围的分区，而非全表。不管你要过滤什么非分区列（比如table_schema、table_name、id），第一步必须先限定分区时间列的范围（比如近7天、近24小时），这是所有优化的前提。

举个最基础的高效查询写法（假设你的超表分区键是modified，如果是dt就替换成dt）：

-- 示例：查询近7天内public schema下user_info表的所有操作
SELECT *
FROM log.data
WHERE modified > now() - interval '7 days'  -- 这行是关键，触发分区裁剪
  AND table_schema = 'public'
  AND table_name = 'user_info'
ORDER BY modified DESC;

⚠️ 这里提个容易踩的坑：别在时间列上用函数，比如DATE(modified) = '2024-05-20'，这种写法会导致分区裁剪失效，数据库还是会扫全部分区。要写成modified >= '2024-05-20' AND modified < '2024-05-21'。

二、针对性优化索引策略 #

你已经建了(modified)和(dt, id)的索引，但可以根据高频查询场景补建复合索引，务必把时间列放在复合索引的最前面：

• 如果经常按「某个业务表+近期时间」查询：

  CREATE INDEX ON log.data (modified, table_schema, table_name);
  

• 如果经常按「某条数据ID+近期时间」查询：

  CREATE INDEX ON log.data (modified, id);
  

• 要是需要对cols数组或row_dataJSONB做查询，可以补建部分GIN索引（只针对近期数据，避免索引体积过大）：

  CREATE INDEX ON log.data USING GIN (row_data) WHERE modified > now() - interval '1 month';
  

三、用Timescale专属特性提效 #

1. 自动清理旧数据 #

审计数据通常不需要永久保存，用add_retention_policy自动删除旧分区，减少后续扫描的数据量：

-- 保留最近3个月的审计数据，自动删除更早的分区
SELECT add_retention_policy('log.data', INTERVAL '3 months');

2. 连续聚合（针对统计类查询） #

如果你的需求是统计近期审计数据（比如「每天某个表的增删改次数」），用连续聚合预计算结果，查询直接取聚合好的数据，速度能提升几个数量级：

-- 创建连续聚合视图，按天统计每个表的操作次数
CREATE MATERIALIZED VIEW log.data_daily_stats
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', modified) AS bucket,
  table_schema,
  table_name,
  operation,
  COUNT(*) AS op_count
FROM log.data
GROUP BY bucket, table_schema, table_name, operation
WITH DATA;

-- 设置刷新策略，每小时刷新一次最新数据
SELECT add_continuous_aggregate_policy('log.data_daily_stats',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

之后查统计数据直接查log.data_daily_stats就行，不用扫原始数据。

四、高频固定查询的终极优化：物化视图 #

如果有固定的高频查询（比如「核心业务库public的所有近期审计日志」），可以建一个只包含该范围数据的物化视图，定期刷新：

CREATE MATERIALIZED VIEW log.public_schema_recent_ops AS
SELECT *
FROM log.data
WHERE modified > now() - interval '7 days'
  AND table_schema = 'public';

-- 每小时刷新一次（可以用Timescale的作业调度或系统crontab实现定时刷新）
REFRESH MATERIALIZED VIEW log.public_schema_recent_ops;

查询的时候直接查这个物化视图，速度比扫超表快很多，因为数据量小了不止一个量级。

最后再强调一次：所有优化的前提都是先通过分区时间列限定范围，没有这一步，其他索引或特性都发挥不了最大作用。如果你的超表分区键是dt而不是modified，那所有时间过滤都要换成dt，不然分区裁剪不生效，性能会大打折扣！