在CTE上以时间间隔缓慢左联接

小编典典

在CTE上以时间间隔缓慢左联接

sql

我正在尝试在PostgreSQL中调试一个查询，该查询是为了在 任意时间间隔的时间段中 存储市场数据而构建的。这是我的表定义：

CREATE TABLE historical_ohlcv (
  exchange_symbol TEXT                     NOT NULL,
  symbol_id       TEXT                     NOT NULL,
  kafka_key       TEXT                     NOT NULL,
  open            NUMERIC,
  high            NUMERIC,
  low             NUMERIC,
  close           NUMERIC,
  volume          NUMERIC,
  time_open       TIMESTAMP WITH TIME ZONE NOT NULL,
  time_close      TIMESTAMP WITH TIME ZONE,
  CONSTRAINT historical_ohlcv_pkey
  PRIMARY KEY (exchange_symbol, symbol_id, time_open)
);

CREATE INDEX symbol_id_idx
  ON historical_ohlcv (symbol_id);

CREATE INDEX open_close_symbol_id
  ON historical_ohlcv (time_open, time_close, exchange_symbol, symbol_id);

CREATE INDEX time_open_idx
  ON historical_ohlcv (time_open);

CREATE INDEX time_close_idx
  ON historical_ohlcv (time_close);

该表目前有约2500万行。以我的查询为例，时间为1小时，但可能是5分钟，10分钟，2天等。

EXPLAIN ANALYZE WITH vals AS (
    SELECT
      NOW() - '5 months' :: INTERVAL AS frame_start,
      NOW() AS frame_end,
      INTERVAL '1 hour'        AS t_interval
)
  , grid AS (
      SELECT
        start_time,
        lead(start_time, 1)
        OVER (
          ORDER BY start_time ) AS end_time
      FROM (
             SELECT
               generate_series(frame_start, frame_end,
                               t_interval) AS start_time,
               frame_end
             FROM vals
           ) AS x
  )
SELECT max(high)
FROM grid g
  LEFT JOIN historical_ohlcv ohlcv ON ohlcv.time_open >= g.start_time
WHERE exchange_symbol = 'BINANCE'
AND symbol_id = 'ETHBTC'
GROUP BY start_time;

WHERE子句可以是表中的任何有效值。

该技术的灵感来自：

在Rails + Postgres中按任意时间间隔对记录进行计数的最佳方法。

这样做的目的是创建一个公用表，并与该表保持连接，以表明其中包含哪些存储桶中的东西。此查询的速度确实很慢！目前耗时15秒。基于查询计划器，我们有一个非常昂贵的嵌套循环：

QUERY PLAN
HashAggregate  (cost=2758432.05..2758434.05 rows=200 width=40) (actual time=16023.713..16023.817 rows=542 loops=1)
  Group Key: g.start_time
  CTE vals
    ->  Result  (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)
  CTE grid
    ->  WindowAgg  (cost=64.86..82.36 rows=1000 width=16) (actual time=2.986..9.594 rows=3625 loops=1)
          ->  Sort  (cost=64.86..67.36 rows=1000 width=8) (actual time=2.981..4.014 rows=3625 loops=1)
                Sort Key: x.start_time
                Sort Method: quicksort  Memory: 266kB
                ->  Subquery Scan on x  (cost=0.00..15.03 rows=1000 width=8) (actual time=0.014..1.991 rows=3625 loops=1)
                      ->  ProjectSet  (cost=0.00..5.03 rows=1000 width=16) (actual time=0.013..1.048 rows=3625 loops=1)
                            ->  CTE Scan on vals  (cost=0.00..0.02 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=1)
  ->  Nested Loop  (cost=0.56..2694021.34 rows=12865667 width=14) (actual time=7051.730..16015.873 rows=31978 loops=1)
        ->  CTE Scan on grid g  (cost=0.00..20.00 rows=1000 width=16) (actual time=2.988..11.635 rows=3625 loops=1)
        ->  Index Scan using historical_ohlcv_pkey on historical_ohlcv ohlcv  (cost=0.56..2565.34 rows=12866 width=22) (actual time=3.712..4.413 rows=9 loops=3625)
              Index Cond: ((exchange_symbol = 'BINANCE'::text) AND (symbol_id = 'ETHBTC'::text) AND (time_open >= g.start_time))
              Filter: (time_close < g.end_time)
              Rows Removed by Filter: 15502
Planning time: 0.568 ms
Execution time: 16023.979 ms

我的猜测是这条线在做很多事情：

LEFT JOIN historical_ohlcv ohlcv ON ohlcv.time_open >= g.start_time
                                AND ohlcv.time_close < g.end_time

但是我不确定如何以另一种方式来实现这一目标。

如果PS属于dba.SE，则表示歉意。我阅读了常见问题解答，这对于该网站来说似乎太基础了，所以我在这里发布了。

根据要求进行编辑：

SELECT avg(pg_column_size(t)) FROM historical_ohlcv t TABLESAMPLE SYSTEM (0.1); 返回107.632

对于exchange_symbol，有3个唯一值，对于symbol_id〜400

PostgreSQL版本：x86_64-pc-linux-gnu上的PostgreSQL 10.3（Ubuntu 10.3-1.pgdg16.04 +
1），由gcc（Ubuntu 5.4.0-6ubuntu1〜16.04.9）编译5.4.0 20160609，64位。

该表每天将增长约100万条记录，因此不完全是只读的。所有这些工作都是在本地完成的，我将尝试移至RDS或帮助管理硬件问题。

相关：如果我想添加其他聚合，特别是“存储桶中的第一个”，“存储桶中的最后一个”，最小值和总计，我的索引编制策略是否会更改？

阅读 216

2021-03-17

共1个答案

小编典典

正确性优先 ：我怀疑您的查询中存在错误：

 LEFT JOIN historical_ohlcv ohlcv ON ohlcv.time_open >= g.start_time
                                 AND ohlcv.time_close < g.end_time

与我参考的答案不同，您在一个时间 间隔内
加入：(time_open, time_close]。执行此操作的方式会排除间隔跨越存储桶边界的表中的行。只有间隔完全包含在单个存储桶计数中。我不认为这是故意的？

一个简单的解决方法是仅基于time_open（或time_close）确定存储桶成员身份。如果要同时使用两者，则必须确切
定义如何处理与多个存储桶重叠的时间间隔。

另外，您正在寻找max(high)每个存储桶，这与count(*)我引用的答案本质上有所不同。

而您的存储桶是每小时简单的时间间隔？

然后，我们可以从根本上简化。与公正工作time_open：

SELECT date_trunc('hour', time_open) AS hour, max(high) AS max_high
FROM   historical_ohlcv
WHERE  exchange_symbol = 'BINANCE'
AND    symbol_id = 'ETHBTC'
AND    time_open >= now() - interval '5 months'  -- frame_start
AND    time_open <  now()                        -- frame_end
GROUP  BY 1
ORDER  BY 1;

有关的：

重新采样时间序列数据

在基础尚不清楚的情况下，很难谈论进一步的性能优化。而且，我们需要更多信息。

是WHERE条件变量？
有多少不同的价值观exchange_symbol和symbol_id？
平均行大小？你得到什么：

SELECT avg(pg_column_size(t)) FROM historical_ohlcv t TABLESAMPLE SYSTEM (0.1);

该表是只读的吗？

假设您始终进行过滤exchange_symbol，symbol_id并且值是可变的，则表是只读的，或者autovacuum可以满足写负载的要求，因此我们希望仅进行索引扫描，最好启用
多列索引(exchange_symbol, symbol_id, time_open, high DESC)以支持此查询。按此顺序索引列。有关的：

多列索引和性能

根据数据分布和其他详细信息，LEFT JOIN LATERAL解决方案可能是另一种选择。有关的：

除此之外，您的EXPLAIN计划还显示出一些 非常差的估计值：

https://explain.depesz.com/s/E5yI

您使用的是最新版本的Postgres吗？您可能需要进行服务器配置-或至少在相关列上设置更高的统计信息目标，并为大表设置更积极的自动清理设置。有关的：

2021-03-17