如何优化AWS RDS（兼容PostgreSQL）上的聚合SQL查询以提升性能？

原始查询与场景说明 #

你当前使用AWS Aurora PostgreSQL兼容版执行高频查询，将每分钟生成的K线合并为3分钟K线，核心SQL与Python解析代码如下：

原始SQL代码 #

SELECT
    t,
    opening,
    MIN(low) as low,
    MAX(high) as high,
    SUM(low * volume) / NULLIF(SUM(volume), 0) AS aggr_low,
    SUM(high * volume) / NULLIF(SUM(volume), 0) AS aggr_high,
    closing,
    SUM(volume) as volume
FROM (
    SELECT 
        FIRST_VALUE(open) OVER (
            PARTITION BY timestamp - (timestamp % 180) ORDER BY timestamp, volume DESC
        ) AS opening,
        FIRST_VALUE(close) OVER (
             PARTITION BY timestamp - (timestamp % 180) ORDER BY timestamp DESC, volume DESC
        ) AS closing,
        low,
        high,
        volume,
        timestamp - (timestamp % 180) as t
    FROM candle
    WHERE pool_id IN (SELECT id FROM pool WHERE asset_1_id = 123 AND asset_2_id IS NULL)
    and type = "1M"
    and timestamp >= 0
    and timestamp < 99999999999999
) AS aggr
GROUP BY t, opening, closing
ORDER BY t;

原始Python解析代码 #

for row in rows:
    (timestamp, open, low, high, aggr_low, aggr_high, close, volume) = row
    candles[timestamp] = {
        "open": open,
        "low": min(low if aggr_low is None else aggr_low, open, close),
        "high": max(high if aggr_high is None else aggr_high, open, close),
        "close": close,
        "volume": volume,
    }

具体优化建议 #

1. 替换IN子查询为JOIN，降低嵌套开销 #

IN子查询在数据量大时易引发性能瓶颈，改为JOIN可让优化器生成更高效的执行计划：

SELECT
    t,
    opening,
    MIN(low) as low,
    MAX(high) as high,
    SUM(low * volume) / NULLIF(SUM(volume), 0) AS aggr_low,
    SUM(high * volume) / NULLIF(SUM(volume), 0) AS aggr_high,
    closing,
    SUM(volume) as volume
FROM (
    SELECT 
        FIRST_VALUE(c.open) OVER (
            PARTITION BY c.timestamp - (c.timestamp % 180) ORDER BY c.timestamp, c.volume DESC
        ) AS opening,
        FIRST_VALUE(c.close) OVER (
             PARTITION BY c.timestamp - (c.timestamp % 180) ORDER BY c.timestamp DESC, c.volume DESC
        ) AS closing,
        c.low,
        c.high,
        c.volume,
        c.timestamp - (c.timestamp % 180) as t
    FROM candle c
    JOIN pool p ON c.pool_id = p.id
    WHERE p.asset_1_id = 123 AND p.asset_2_id IS NULL
      AND c.type = '1M'
      AND c.timestamp >= 0
      AND c.timestamp < 99999999999999
) AS aggr
GROUP BY t, opening, closing
ORDER BY t;

2. 优化分区键计算逻辑 #

原分区键timestamp - (timestamp % 180)可改为更清晰的整数除法形式，逻辑等价且PostgreSQL优化更稳定：

-- 若timestamp为bigint类型，直接用整数除法
(c.timestamp / 180) * 180 AS t

3. 简化GROUP BY子句 #

由于opening和closing是每个分区t内的唯一值（通过FIRST_VALUE窗口函数获取），GROUP BY只需保留t即可，减少分组计算开销：

GROUP BY t

注：需提前验证同一t内所有行的opening、closing值完全一致。

4. 添加针对性覆盖索引，消除全表扫描 #

这是提升查询速度的核心步骤：

• 给candle表创建联合覆盖索引，包含过滤条件与所需字段，避免回表：

  CREATE INDEX idx_candle_type_poolid_timestamp_inc ON candle (type, pool_id, timestamp)
  INCLUDE (open, close, low, high, volume);
  

• 给pool表创建索引，加速JOIN匹配：

  CREATE INDEX idx_pool_asset1_asset2_inc ON pool (asset_1_id, asset_2_id)
  INCLUDE (id);
  

5. 合并客户端计算到SQL，减少数据传输 #

将Python中最终low、high的计算逻辑移到SQL中，减少返回数据量与客户端运算：

SELECT
    t,
    opening,
    -- 直接计算最终low：优先用aggr_low，否则取MIN(low)，再与open、close取最小
    LEAST(
        COALESCE(SUM(low * volume) / NULLIF(SUM(volume), 0), MIN(low)),
        opening,
        closing
    ) AS final_low,
    -- 直接计算最终high：优先用aggr_high，否则取MAX(high)，再与open、close取最大
    GREATEST(
        COALESCE(SUM(high * volume) / NULLIF(SUM(volume), 0), MAX(high)),
        opening,
        closing
    ) AS final_high,
    closing,
    SUM(volume) as volume
FROM (
    SELECT 
        FIRST_VALUE(c.open) OVER (
            PARTITION BY (c.timestamp / 180) * 180 ORDER BY c.timestamp, c.volume DESC
        ) AS opening,
        FIRST_VALUE(c.close) OVER (
             PARTITION BY (c.timestamp / 180) * 180 ORDER BY c.timestamp DESC, c.volume DESC
        ) AS closing,
        c.low,
        c.high,
        c.volume,
        (c.timestamp / 180) * 180 as t
    FROM candle c
    JOIN pool p ON c.pool_id = p.id
    WHERE p.asset_1_id = 123 AND p.asset_2_id IS NULL
      AND c.type = '1M'
      AND c.timestamp >= 0
      AND c.timestamp < 99999999999999
) AS aggr
GROUP BY t, opening, closing
ORDER BY t;

对应的Python代码可简化为：

for row in rows:
    (timestamp, open, low, high, close, volume) = row
    candles[timestamp] = {
        "open": open,
        "low": low,
        "high": high,
        "close": close,
        "volume": volume,
    }

6. Aurora专属优化策略 #

• 分流到只读副本：将该高频聚合查询路由到Aurora只读副本，减轻主库CPU与IO压力。
• 调整内存参数：增大work_mem（如设置为64MB），让窗口函数、聚合操作在内存中完成，避免磁盘排序；合理配置shared_buffers利用Aurora内存优势。
• 预加载缓存：使用pg_prewarm将常用的candle、pool数据加载到内存，减少磁盘读取。

7. 数据类型优化 #

若type字段为固定枚举值（如'1M'、'3M'），改为ENUM类型替代VARCHAR，减少存储空间并提升索引效率：

CREATE TYPE candle_type AS ENUM ('1M', '3M', '5M');
ALTER TABLE candle ALTER COLUMN type TYPE candle_type USING type::candle_type;

验证优化效果 #

每次优化后，执行EXPLAIN ANALYZE查看执行计划：

• 确认是否使用了创建的索引，避免全表扫描
• 检查排序操作是否在内存中完成（Sort Method: QuickSort Memory: XXXkB）
• 对比执行时间与CPU使用率的变化

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