sql - 具有时间间隔的 CTE 上的慢速 LEFT JOIN
问题描述
我正在尝试在 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);
该表目前有约 25m 行。我的查询以 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 子句可以是表中的任何有效值。
这项技术的灵感来自:
这个想法是创建一个公用表,然后将您的数据与该表连接起来以指示哪些存储桶中的内容。这个查询真的很慢!目前需要 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 或帮助管理硬件问题。
相关:如果我想添加其他聚合,特别是“桶中的第一个”、“桶中的最后一个”、最小值、总和,我的索引策略会改变吗?
解决方案
正确性第一:我怀疑您的查询中有错误:
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
计划展示一些非常糟糕的估计:
您使用的是当前版本的 Postgres 吗?您可能必须处理您的服务器配置 - 或者至少在相关列上设置更高的统计目标,并为大表设置更积极的 autovacuum 设置。有关的:
推荐阅读
- python - 如何找到csv文件中行之间的最小欧几里得距离并丢弃其中一个,直到我剩下特定数量的行?
- javascript - 是否可以将其编码为也可以在 DCOM 中工作?
- .net-core - 如何运行 GELF 扩展记录器?
- php - 我想在 Laravel 控制器中创建两个表嵌套对象
- dart - 未来变量和空安全:必须初始化不可空的实例字段“通知”
- php - Laravel wherenotexists 返回 null
- c++ - `clog` 被缓冲了吗?
- arrays - 如何将 JSON 转换为 Postgres 存储过程中的行
- powershell - Get-WinEvent 与匹配 TimeCreated
- javascript - React.js 中的引导程序 - 导航栏折叠不起作用