PostgreSQL 13 OHLC查询性能优化求助：15分钟周期数据查询过慢

架构老林

2026-7-9

PostgreSQL 13中1000万条数据的OHLC查询优化问题

我尝试编写查询获取OHLC数据，但查询运行速度极慢。总数据量约1000万条，表用于记录每次价格变动，结构示例如下：

id | timestamp           | priceUSD | pairAddress | ....
---------------------------------------------------------
1  | 2021-12-30 13:48:00 | 0.003    | 0x12345
2  | 2021-12-30 ........ | 0.003    | .....
3  | 2021-12-30 ........ | 0.003    | .....
4  | 2021-12-30 ........ | 0.003    | .....

当前查询语句：

WITH filtered_data AS (
    SELECT
        timestamp,
        "PairPriceHistory"."priceUSD" as PUSD,
        TIMESTAMP 'epoch' +
            floor(EXTRACT('epoch' FROM timestamp) / (15 * 60)) * (15 * 60) * interval '1 second' AS time_interval
    FROM "PairPriceHistory"
    WHERE "PairPriceHistory"."pairContractAddress" = '0x58f876857a02d6762e0101bb5c46a8c1ed44dc16' AND "PairPriceHistory"."timestamp" >= '2021-12-30' AND "PairPriceHistory"."timestamp" <= '2022-01-02'
)
SELECT
    time_interval,
    MIN(PUSD) AS low_price,
    MAX(PUSD) AS high_price,
    (SELECT PUSD FROM filtered_data fd2 WHERE fd2.time_interval = fd.time_interval AND fd2.timestamp = MIN(fd.timestamp) LIMIT 1) AS open_price,
    (SELECT PUSD FROM filtered_data fd3 WHERE fd3.time_interval = fd.time_interval AND fd3.timestamp = MAX(fd.timestamp) LIMIT 1) AS close_price
FROM filtered_data fd
GROUP BY time_interval
ORDER BY time_interval

查询目标：

获取指定日期区间内的OHLC数据
以15分钟为时间间隔
针对交易对0x58f876857a02d6762e0101bb5c46a8c1ed44dc16

但仅获取2天的数据就耗时11秒，使用PostgreSQL 13，求优化方案或指出查询问题。

更新信息

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 结果

Sort  (cost=703946.75..703947.25 rows=200 width=40) (actual time=12784.437..12784.458 rows=233 loops=1)
  Sort Key: fd.time_interval
  Sort Method: quicksort  Memory: 43kB
  Buffers: shared hit=83740 read=100317 written=4808, temp read=485919 written=1995
  CTE filtered_data
    ->  Index Scan using timestamp_pph_idx on "PairPriceHistory"  (cost=0.43..285122.57 rows=522388 width=24) (actual time=356.299..1572.435 rows=480549 loops=1)
          Index Cond: (("timestamp" >= '2021-12-30 00:00:00'::timestamp without time zone) AND ("timestamp" <= '2022-01-02 00:00:00'::timestamp without time zone))
          Filter: (("pairContractAddress")::text = '0x58f876857a02d6762e0101bb5c46a8c1ed44dc16'::text)
          Rows Removed by Filter: 3799732
          Buffers: shared hit=83740 read=100317 written=4808
  ->  HashAggregate  (cost=16977.61..418816.53 rows=200 width=40) (actual time=1883.221..12783.872 rows=233 loops=1)
        Group Key: fd.time_interval
        Batches: 1  Memory Usage: 77kB
        Buffers: shared hit=83740 read=100317 written=4808, temp read=485919 written=1995
        ->  CTE Scan on filtered_data fd  (cost=0.00..10447.76 rows=522388 width=24) (actual time=356.302..1776.790 rows=480549 loops=1)
              Buffers: shared hit=83740 read=100317 written=4808, temp written=1994
        SubPlan 2
          ->  Limit  (cost=0.00..1004.59 rows=1 width=8) (actual time=23.221..23.222 rows=1 loops=233)
                Buffers: temp read=241966 written=1
                ->  CTE Scan on filtered_data fd2  (cost=0.00..13059.70 rows=13 width=8) (actual time=23.220..23.220 rows=1 loops=233)
                      Filter: ((time_interval = fd.time_interval) AND ("timestamp" = min(fd."timestamp")))
                      Rows Removed by Filter: 250096
                      Buffers: temp read=241966 written=1
        SubPlan 3
          ->  Limit  (cost=0.00..1004.59 rows=1 width=8) (actual time=23.611..23.611 rows=1 loops=233)
                Buffers: temp read=243953
                ->  CTE Scan on filtered_data fd3  (cost=0.00..13059.70 rows=13 width=8) (actual time=23.609..23.609 rows=1 loops=233)
                      Filter: ((time_interval = fd.time_interval) AND ("timestamp" = max(fd."timestamp")))
                      Rows Removed by Filter: 252151
                      Buffers: temp read=243953
Settings: effective_cache_size = '4608MB', effective_io_concurrency = '200', max_parallel_workers = '4', random_page_cost = '1.1', search_path = 'public, "$user", public', work_mem = '7864kB'
Planning:
  Buffers: shared hit=1 read=3
Planning Time: 0.441 ms
JIT:
  Functions: 29
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 5.542 ms, Inlining 54.131 ms, Optimization 187.571 ms, Emission 112.228 ms, Total 359.473 ms
Execution Time: 12795.266 ms

表结构与索引

字段：
索引：

简单查询测试

执行以下仅筛选数据的查询，返回480549条数据，耗时11.235秒：

SELECT timestamp,
        "PairPriceHistory"."priceUSD" as PUSD
    FROM "PairPriceHistory"
    WHERE "PairPriceHistory"."pairContractAddress" = '0x58f876857a02d6762e0101bb5c46a8c1ed44dc16' AND "PairPriceHistory"."timestamp" >= '2021-12-30' AND "PairPriceHistory"."timestamp" <= '2022-01-02'

优化方案

1. 优化索引，解决数据过滤慢的核心问题

从EXPLAIN结果看，当前使用timestamp_pph_idx（仅timestamp的索引）扫描时，过滤掉了379万条非目标交易对的数据，这是性能瓶颈的根源。需要创建复合索引，同时包含pairContractAddress和timestamp，让数据库直接定位到目标交易对的时间区间数据，避免大量过滤：

CREATE INDEX idx_pair_timestamp ON "PairPriceHistory" ("pairContractAddress", "timestamp");

创建后，查询会直接通过这个索引获取符合条件的48万条数据，无需扫描额外的379万条记录，能大幅降低IO和过滤耗时。

2. 重构OHLC查询，避免子查询的重复扫描

原查询中open_price和close_price使用了子查询，每个时间区间都要全量扫描CTE数据，导致233个区间就扫描了近50万*2次数据，这是第二个性能瓶颈。可以使用FIRST_VALUE()和LAST_VALUE()窗口函数，在一次扫描中获取开盘价和收盘价：

WITH filtered_data AS (
    SELECT
        timestamp,
        "priceUSD" as PUSD,
        TIMESTAMP 'epoch' + floor(EXTRACT('epoch' FROM timestamp) / (15 * 60)) * (15 * 60) * interval '1 second' AS time_interval
    FROM "PairPriceHistory"
    WHERE "pairContractAddress" = '0x58f876857a02d6762e0101bb5c46a8c1ed44dc16' 
      AND "timestamp" >= '2021-12-30' 
      AND "timestamp" <= '2022-01-02'
),
windowed_data AS (
    SELECT
        time_interval,
        PUSD,
        FIRST_VALUE(PUSD) OVER (PARTITION BY time_interval ORDER BY timestamp) AS open_price,
        LAST_VALUE(PUSD) OVER (PARTITION BY time_interval ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close_price,
        MIN(PUSD) OVER (PARTITION BY time_interval) AS low_price,
        MAX(PUSD) OVER (PARTITION BY time_interval) AS high_price
    FROM filtered_data
)
SELECT DISTINCT
    time_interval,
    open_price,
    high_price,
    low_price,
    close_price
FROM windowed_data
ORDER BY time_interval;

或者更高效的方式，先分组聚合MIN/MAX，再通过JOIN获取开盘和收盘：

WITH filtered_data AS (
    SELECT
        timestamp,
        "priceUSD" as PUSD,
        TIMESTAMP 'epoch' + floor(EXTRACT('epoch' FROM timestamp) / (15 * 60)) * (15 * 60) * interval '1 second' AS time_interval
    FROM "PairPriceHistory"
    WHERE "pairContractAddress" = '0x58f876857a02d6762e0101bb5c46a8c1ed44dc16' 
      AND "timestamp" >= '2021-12-30' 
      AND "timestamp" <= '2022-01-02'
),
aggregated AS (
    SELECT
        time_interval,
        MIN(PUSD) AS low_price,
        MAX(PUSD) AS high_price,
        MIN(timestamp) AS min_ts,
        MAX(timestamp) AS max_ts
    FROM filtered_data
    GROUP BY time_interval
)
SELECT
    a.time_interval,
    fd_open.PUSD AS open_price,
    a.high_price,
    a.low_price,
    fd_close.PUSD AS close_price
FROM aggregated a
JOIN filtered_data fd_open ON fd_open.time_interval = a.time_interval AND fd_open.timestamp = a.min_ts
JOIN filtered_data fd_close ON fd_close.time_interval = a.time_interval AND fd_close.timestamp = a.max_ts
ORDER BY a.time_interval;

这两种方式都能避免子查询的重复扫描，将数据扫描次数从O(n*m)降到O(n)。

3. 其他辅助优化

验证pairContractAddress字段类型：如果是TEXT类型，确保索引也是基于TEXT；如果是BYTEA或其他类型，避免查询中的类型转换（EXPLAIN中出现::text转换会导致索引失效）。
调整work_mem：当前设置为7864kB，如果内存充足，可以适当提高（比如16MB），减少临时文件的IO操作。
考虑分区表：如果数据按时间持续增长，可以将表按timestamp分区，进一步提升时间区间查询的性能。

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

PostgreSQL 13 OHLC查询性能优化求助：15分钟周期数据查询过慢

更新信息 #

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 结果 #

表结构与索引 #

简单查询测试 #

1. 优化索引，解决数据过滤慢的核心问题 #

2. 重构OHLC查询，避免子查询的重复扫描 #

3. 其他辅助优化 #