- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试在 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 子句可以是表中的任何有效值。
这项技术的灵感来自于:
我们的想法是创建一个公用表,然后将您的数据与该表进行左连接,以指示存储在哪个存储桶中。这个查询真的很慢!目前需要 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
但我不确定如何以其他方式完成此任务。
附言抱歉,如果这属于 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版本:PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu, 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
不像我的referenced answer ,您加入一个时间 interval:(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 设置。相关:
关于sql - CTE 上的慢速 LEFT JOIN 具有时间间隔,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50221842/
是否可以在 CTE 内编写 CTE? 我希望它遵循这个逻辑,但解释器不喜欢这段代码。 with outertest as( with test as ( select
我有一个 with 子句,按时间间隔和天气描述对一些天气数据进行分组: With temp_table (counter, hour, current_Weather_description) as
我想要一个普通的 CTE,然后是一个递归的 CTE 如何将这两者结合起来? 我知道我可以做多个纯正常的 CTE WITH CTE1 AS( ), CTE2 AS(),... 所以我试过了 WITH C
在 SQL 中是否可以在同一查询中的另一个 C.T.E 内的公共(public)表表达式内使用引用?这里有一个例子: WITH CT1 AS (SELECT * FROM T), CT2 A
请不要将此问题标记为 CTE within a CTE 的重复项..我检查了那个问题和答案……但那个答案不能满足我的需要。 我想像这样运行嵌套 CTE 查询 Drop Table #Temp Crea
在 jOOQ 中,我在以后的 CTE 中重复使用 CTE。我正在尝试按年份和学校汇总学生的完成记录。我正在使用 jOOQ 3.11.2 和 postgres 9.4。 我有有效的 SQL 代码。但是在
我需要根据最新的时间戳更新 CTE o/p 列值之一(前 1 条记录),然后返回。 查询 WITH cte AS ( select dt_zone.zone_name, dt_mate
我的 CTE 出现错误。我在 Amazon RDS 上托管数据库。我的计算机上有另一个示例数据库主机 (localhost)。 CTE 在我的本地计算机上运行良好。我认为 Amazon RDS 有问题
我正在使用 SQL Server 进行分页,我想通过计算结果总数作为我的部分结果集的一部分来避免重复,而不是获取该结果集然后执行单独的查询以获取计数。然而,麻烦的是,它似乎增加了执行时间。例如,如果我
我想(使用 cte)以这种方式计算表中的 child 数量,以在 parent 级别所有 child 的数量包括他们的 child 。有 sample 吗? 最佳答案 CREATE TABLE t_p
鉴于以下递归 CTE(在我的示例中简化了一点): WITH myCTE (sort, parentid, myid, level, somedata) AS ( -- Anchor membe
我有一张带有自连接的表。您可以将结构视为表示组织层次结构的标准表。例如表:- MemberId MemberName RelatedMemberId 该表由 50000 条样本记录组成。我写了 CTE
我正在尝试将公用表表达式加入现有表 (table1),如下所示。 select column1, column2 from table1 left outer join ;with cte as
我可以在标量函数中使用公用表表达式(CTE) 吗? 我试图用它来获取单个浮点值,但始终为空 这是我计算每位员工总工作时间的函数代码: ALTER FUNCTION GetTotalWorkingHou
我可以在分层数据模型中找到给定记录的所有子项(请参阅下面的代码),但我不确定如何使用给定的子 ID 遍历备份父/子链。谁能指出我正确的方向来弄清楚如何做到这一点?这在 Linq to SQL 中也可能
我在这里遇到了一点困难。我的主要目标是能够在 C# 和 Entity Framework 中使用它,而我们的高层指令是远离存储过程。 我有 2 个表:一个外部参照和一个 (Celko) 树表。 /**
尝试运行此 cte 时出现以下错误 Invalid Object Name 'cte' 使用以下 CTE 语句时出错 WITH cte (LOC_ID, [Description], LOC_TYPE
我正在阅读“Murach 的 SQL Server 2016 for Developers”一书中的示例。该示例说明了如何在 SQL 中编写递归 CTS 代码。我非常了解递归函数(在 C# 中),但我
我有一个相当复杂的查询,其中包含多个 CTE,但有 1 个主 CTE,其他所有 CTE 都从中提取,这是否会导致该主 CTE 被执行多次? 最佳答案 您可以这样使用 CROSS JOIN: SELEC
我正在尝试编写一个递归 CTE,它引用另一个在它之前编写的 CTE。 第一个 cte nodes 在编写递归 Hierarchy cte 之前我用分号关闭了: WITH nodes(node, nod
我是一名优秀的程序员,十分优秀!