每15分钟高效查询一个巨大的时间序列表一行

2023-12-01

我有两张桌子，conttagtable(t) 和contfloattable（参见）。 T 大约有 43k 行。 CF已经超过90亿了。

我在两个表上创建了索引tagindex两个表上的列。该列可以被认为是一个唯一标识符conttagtable并作为外键进入conttagtable for confloattable。我没有在与另一个表相关的任何一个表上显式创建 PK 或外键，尽管该数据在逻辑上与tagindex两个表上的列就像conttagtable.tagindex were a PRIMARY KEY and contfloattable.tagindex其中一个FOREIGN KEY (tagindex) REFERENCES conttagtable(tagindex)。数据来自微软访问转储，我不知道我是否可以相信 tagindex 是唯一的，因此不强制执行“唯一性”。

数据本身就非常大。

我需要从中获取单个任意选择的行contfloattable每 15 分钟contfloattable.dateandtime每个的间隔conttagtable.tagid。所以，如果contfloattable对于给定的tagid有 4000 个跨度为 30 分钟的样本，我需要一个 0-14 分钟范围内的样本和一个 15-30 分钟范围内的样本。 15 分钟范围内的任何一个样本都是可接受的；第一个，最后一个，随机的，等等。

简而言之，我需要每 15 分钟获取一个样本，但每个 t.tagname 只获取一个样本。现在的样本每 5 秒记录一次，数据跨度为两年。这是一个大数据问题，在 sql 方面超出了我的理解范围。我通过谷歌搜索或搜索 SO 尝试过的所有时间间隔解决方案都产生了太长的查询时间，以至于不切实际。

我的索引足以进行快速连接吗？（它们似乎是在省略时间间隔部分时）
添加任何其他索引我会受益吗？
实现上述目标的最佳/最快查询是什么？

这是一个包含架构和一些示例数据的 SQLFiddle：http://sqlfiddle.com/#!1/c7d2f/2

Schema:

        Table "public.conttagtable" (t)
   Column    |  Type   | Modifiers
-------------+---------+-----------
 tagname     | text    |
 tagindex    | integer |
 tagtype     | integer |
 tagdatatype | integer |
Indexes:
    "tagindex" btree (tagindex)


             Table "public.contfloattable" (CF)
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 dateandtime | timestamp without time zone |
 millitm     | integer                     |
 tagindex    | integer                     |
 Val         | double precision            |
 status      | text                        |
 marker      | text                        |
Indexes:
    "tagindex_contfloat" btree (tagindex)

我想看到的输出是这样的：

cf.dateandtime      |cf."Val"|cf.status|t.tagname
--------------------------------------------------
2012-11-16 00:00:02  45       S         SuperAlpha
2012-11-16 00:00:02  45       S         SuperBeta
2012-11-16 00:00:02  45       S         SuperGamma
2012-11-16 00:00:02  45       S         SuperDelta
2012-11-16 00:15:02  45       S         SuperAlpha
2012-11-16 00:15:02  45       S         SuperBeta
2012-11-16 00:15:02  45       S         SuperGamma
2012-11-16 00:15:02  45       S         SuperDelta
2012-11-16 00:30:02  45       S         SuperAlpha
2012-11-16 00:30:02  45       S         SuperBeta
2012-11-16 00:30:02  45       S         SuperGamma
2012-11-16 00:30:02  45       S         SuperDelta
2012-11-16 00:45:02  42       S         SuperAlpha

...等等等等...

正如 Clodoaldo 所建议的，这是我最新的尝试，有什么建议可以加快速度吗？

with i as (
    select cf.tagindex, min(dateandtime) dateandtime
    from contfloattable cf
    group by
        floor(extract(epoch from dateandtime) / 60 / 15),
        cf.tagindex
)
select cf.dateandtime, cf."Val", cf.status, t.tagname
from
    contfloattable cf
    inner join
    conttagtable t on cf.tagindex = t.tagindex
    inner join
    i on i.tagindex = cf.tagindex and i.dateandtime = cf.dateandtime
order by floor(extract(epoch from cf.dateandtime) / 60 / 15), cf.tagindex

由上查询计划：http://explain.depesz.com/s/loR

间隔 15 分钟：

with i as (
    select cf.tagindex, min(dateandtime) dateandtime
    from contfloattable cf
    group by
        floor(extract(epoch from dateandtime) / 60 / 15),
        cf.tagindex
)
select cf.dateandtime, cf."Val", cf.status, t.tagname
from
    contfloattable cf
    inner join
    conttagtable t on cf.tagindex = t.tagindex
    inner join
    i on i.tagindex = cf.tagindex and i.dateandtime = cf.dateandtime
order by cf.dateandtime, t.tagname

显示此查询的解释输出（如果有效），以便我们可以尝试优化。您可以将其发布在这个答案中。

解释输出

"Sort  (cost=15102462177.06..15263487805.24 rows=64410251271 width=57)"
"  Sort Key: cf.dateandtime, t.tagname"
"  CTE i"
"    ->  HashAggregate  (cost=49093252.56..49481978.32 rows=19436288 width=12)"
"          ->  Seq Scan on contfloattable cf  (cost=0.00..38528881.68 rows=1408582784 width=12)"
"  ->  Hash Join  (cost=270117658.06..1067549320.69 rows=64410251271 width=57)"
"        Hash Cond: (cf.tagindex = t.tagindex)"
"        ->  Merge Join  (cost=270117116.39..298434544.23 rows=1408582784 width=25)"
"              Merge Cond: ((i.tagindex = cf.tagindex) AND (i.dateandtime = cf.dateandtime))"
"              ->  Sort  (cost=2741707.02..2790297.74 rows=19436288 width=12)"
"                    Sort Key: i.tagindex, i.dateandtime"
"                    ->  CTE Scan on i  (cost=0.00..388725.76 rows=19436288 width=12)"
"              ->  Materialize  (cost=267375409.37..274418323.29 rows=1408582784 width=21)"
"                    ->  Sort  (cost=267375409.37..270896866.33 rows=1408582784 width=21)"
"                          Sort Key: cf.tagindex, cf.dateandtime"
"                          ->  Seq Scan on contfloattable cf  (cost=0.00..24443053.84 rows=1408582784 width=21)"
"        ->  Hash  (cost=335.74..335.74 rows=16474 width=44)"
"              ->  Seq Scan on conttagtable t  (cost=0.00..335.74 rows=16474 width=44)"

看来您需要这个索引：

create index cf_tag_datetime on contfloattable (tagindex, dateandtime)

Run analyze创建后。现在请注意，大表上的任何索引都会对数据更改（插入等）产生重大性能影响，因为每次更改都必须更新它。

Update

我添加了 cf_tag_datetime 索引（tagindex，dateandtime），这是新的解释：

"Sort  (cost=15349296514.90..15512953953.25 rows=65462975340 width=57)"
"  Sort Key: cf.dateandtime, t.tagname"
"  CTE i"
"    ->  HashAggregate  (cost=49093252.56..49490287.76 rows=19851760 width=12)"
"          ->  Seq Scan on contfloattable cf  (cost=0.00..38528881.68 rows=1408582784 width=12)"
"  ->  Hash Join  (cost=270179293.86..1078141313.22 rows=65462975340 width=57)"
"        Hash Cond: (cf.tagindex = t.tagindex)"
"        ->  Merge Join  (cost=270178752.20..298499296.08 rows=1408582784 width=25)"
"              Merge Cond: ((i.tagindex = cf.tagindex) AND (i.dateandtime = cf.dateandtime))"
"              ->  Sort  (cost=2803342.82..2852972.22 rows=19851760 width=12)"
"                    Sort Key: i.tagindex, i.dateandtime"
"                    ->  CTE Scan on i  (cost=0.00..397035.20 rows=19851760 width=12)"
"              ->  Materialize  (cost=267375409.37..274418323.29 rows=1408582784 width=21)"
"                    ->  Sort  (cost=267375409.37..270896866.33 rows=1408582784 width=21)"
"                          Sort Key: cf.tagindex, cf.dateandtime"
"                          ->  Seq Scan on contfloattable cf  (cost=0.00..24443053.84 rows=1408582784 width=21)"
"        ->  Hash  (cost=335.74..335.74 rows=16474 width=44)"
"              ->  Seq Scan on conttagtable t  (cost=0.00..335.74 rows=16474 width=44)"

它似乎已经及时增加了:(但是，如果我删除 order by 子句（不完全是我需要的，但会起作用），这就是发生的情况，大幅减少：

"Hash Join  (cost=319669581.62..1127631600.98 rows=65462975340 width=57)"
"  Hash Cond: (cf.tagindex = t.tagindex)"
"  CTE i"
"    ->  HashAggregate  (cost=49093252.56..49490287.76 rows=19851760 width=12)"
"          ->  Seq Scan on contfloattable cf  (cost=0.00..38528881.68 rows=1408582784 width=12)"
"  ->  Merge Join  (cost=270178752.20..298499296.08 rows=1408582784 width=25)"
"        Merge Cond: ((i.tagindex = cf.tagindex) AND (i.dateandtime = cf.dateandtime))"
"        ->  Sort  (cost=2803342.82..2852972.22 rows=19851760 width=12)"
"              Sort Key: i.tagindex, i.dateandtime"
"              ->  CTE Scan on i  (cost=0.00..397035.20 rows=19851760 width=12)"
"        ->  Materialize  (cost=267375409.37..274418323.29 rows=1408582784 width=21)"
"              ->  Sort  (cost=267375409.37..270896866.33 rows=1408582784 width=21)"
"                    Sort Key: cf.tagindex, cf.dateandtime"
"                    ->  Seq Scan on contfloattable cf  (cost=0.00..24443053.84 rows=1408582784 width=21)"
"  ->  Hash  (cost=335.74..335.74 rows=16474 width=44)"
"        ->  Seq Scan on conttagtable t  (cost=0.00..335.74 rows=16474 width=44)"

我还没有尝试过这个索引......不过会这样做。支持。

现在再看一遍，我认为逆索引可能会更好，因为它不仅可以用于Merge Join但也在决赛中Sort:

create index cf_tag_datetime on contfloattable (dateandtime, tagindex)

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

每15分钟高效查询一个巨大的时间序列表一行的相关文章

在 Doctrine DQL 中选择 count() ，并使用左连接多对多单向关系，其中用户没有关系特定组

情况我尝试在 DQL 中为不属于特定组的用户选择 count 标准ManyToMany之间的单向关系User and Group实体来自FOSUserBundle and SonataUserBundle 系统 Symfony 2 5 D
多级排序

我有一个表其中包含一些记录其中包含名称评级等字段我首先想要根据评级将结果限制为 20 进行排序然后在此结果集上想要进一步应用基于名称的排序我知道要排序我们需要使用像这样的查询 Select from table order by
如何在SqlAlchemy中执行“左外连接”

我需要执行这个查询 select field11 field12 from Table 1 t1 left outer join Table 2 t2 ON t2 tbl1 id t1 tbl1 id where t2 tbl2 id is
尝试使用 sql 获取单行结果？ [复制]

这个问题在这里已经有答案了我正在尝试显示所有员工 ID 我需要这样的结果 emp id 10 11 12 13 14 15 当尝试时 SELECT LISTAGG emp id WITHIN GROUP ORDER BY emp id A
将 5 gig 文件导入表时出错

我正在尝试批量插入表 use SalesDWH go BULK INSERT dbo npi FROM S tmp npi csv WITH FIELDTERMINATOR ROWTERMINATOR n lastrow 200 first
如何在没有聚合函数的情况下在sql server中创建枢轴查询

我正在使用 MS SQL SERVER 2008 并且有以下数据 select from account PERIOD ACCOUNT VALUE 2000 Asset 205 2000 Equity 365 2000 Profit 524
Heroku 上的“PG::错误 - 数字字段溢出”

我构建了一个应用程序来查询 Google Analytics 的过去 7 天的数据一切都在本地进行在 Heroku 上该过程运行顺利直到它尝试获取今天日期的数据然后我收到以下错误 2012 10 29T02 32 02 00 00
如何在此查询中获取以 KM 为单位的距离

salons Salon select salons gt selectRaw 6371 acos cos radians cos radians lat cos radians lng radians sin radians sin ra
Postgres 使用 TypeORM SET 运行时变量，如何在调用之间的连接生命周期中保留变量

我有使用 GraphQL 的 NodeJS Web 服务器使用 2 个连接一个具有管理员访问权限另一个具有 CRUD 访问权限底层 Postgres DB 有行级安全策略即 ALTER TABLE main user ENABLE
从 URL 生成报告 - SQL Server Reporting Services 2008

我有 SQL Server Reporting Services 2008 当我打开以下 URL 时 http localhost Reports Pages Report aspx someReport 我正在进入报告屏幕在其中填写参数
使用输出在合并语句中设置变量

我有一个合并语句应该始终更新或插入一条记录我想记住变量中该语句的 ID 它看起来像这样 DECLARE int int MERGE dbo table AS A USING SELECT stringtomatch AS string A
Entity Framework Core 多对多更改导航属性名称

我有一个名为 LogBookSystemUsers 的表我想在 EF Core 5 中设置多对多功能我几乎可以正常工作但问题是我的 ID 列被命名为SystemUserId and LogBookId但是当 EF 进行连接时它会尝试使
如何使用索引更改表的列？

我想将带有某些索引的表中 a 列的列大小从 varchar 200 更改为 varchar 8000 我应该如何进行既然是VARCHAR你正在增加尺寸然后简单地ALTER TABLE ALTER COLUMN https learn m
插入MYSQL时自动初始化GETDATE()

类似问题 https stackoverflow com questions 17700239 mysql column automaticly current time of insert w3schools 也许有用的链接 http w
SQL最近的命令？微软SQL

我只是编写一个查询来查看我的客户数据库并列出他们下了多少订单等我正在努力添加到此查询中的是只显示该电子邮件的最新 OrderID 有任何想法吗这是我的查询 select top 1000 BuyerEMail COUNT HowMany
新分配的序列不起作用

在 PostgreSQL 中我创建了一个新表并为其分配了一个新序列id柱子如果我从 PostgreSQL 控制台插入记录它可以工作但是当我尝试从 Rails 导入记录时它会引发异常无法找到关联的序列这是表格 d user me
SQL 用随机数据填充表

我有一个包含两个字段的表 id UUID 是主键并且描述 var255 我想用SQL语句插入随机数据我希望这个描述是随机的 PS 我正在使用 PostgreSQL 我不确定这是否符合随机描述的要求也不清楚您是否想要生成完整的数据
SQL Server 为什么索引不与 OR 一起使用

我一直在研究索引并试图了解它们是如何工作的以及如何使用它们来提高性能但我错过了一些东西我有下表 Person Id Name Email Phone 1 John E1 P1 2 Max E2 P2 我正在尝试找到对列进行索引的最佳方法
如何使用默认约束为mysql中的列创建随机数？

DEFAULT 约束在接受字符串或当前日期值方面没有问题我需要的是一个约束每次创建实体时都会创建一个随机的 4 位数字我尝试了以下代码但它返回语法错误 ALTER TABLE client number ADD 代码 INT 4 D
SQL查询中的Python列表作为参数[重复]

这个问题在这里已经有答案了我有一个 Python 列表比如说 l 1 5 8 我想编写一个 SQL 查询来获取列表中所有元素的数据例如 select name from students where id IN THE LIST l

随机推荐

尽管已设置标头，但仍不允许 CORS 请求

我在尝试执行 XHR 请求时收到以下错误我已经设置了 CORS 响应标头但显然出现了问题任何人都可以发现错误吗 XMLHttpRequest 无法加载http domain dev 路径起源http mobile devAccess
谷歌地图：事件监听器仅记住变量的最终值

我正在整理一张 Google 地图其中包含我国各地各个考试中心的位置它在每个县上绘制一个标记当您单击县标记时它会放大并提供该县考试中心的概述我也在使用 jQuery 来实现这个问题是这样的当我绘制县标记并单击它们时它总是缩放
正则表达式反向引用来匹配不同的值

我有一个正则表达式用于匹配以下形式的表达式 val1 operator val2 这个正则表达式看起来像 s a zA Z 0 9 0 9 s ni in gt gt lt lt s a zA Z 0 9 0 9 s 正如你所看到的这实
如何使用 javascript 更改 JQuery Mobile 按钮图标

我想要一个根据用户选择更改数据图标类别的按钮示例按钮为 a href language2 span English span a 我想知道我需要实现什么 JavaScript 代码才能更改自定义定义data icon english fl
Castle.Windsor：来自类型化工厂的组件的可选解析

我有一个带有 Castle Windsor 的 WCF 服务设置消息到达调度程序调度程序将它们发送到正确的组件基本上是一个IHandler
Chrome 的 Javascript 控制台：它以对象形式输出什么？

从 Chrome 中的 javascript 控制台 gt function Person name this name name undefined 此时根据 Javascript 规范 Person prototype 应该是一个空对
如何并行迭代两个数组？ [复制]

这个问题在这里已经有答案了我看起来能够并行迭代两个数组或仅使用一个 for 循环这是我尝试过的脚本 usr bin env python list1 one two three list2 I II III IV V for word
SpriteKit - 创建计时器

如何创建一个每两秒触发一次的计时器以便在屏幕上的 HUD 上将分数加一这是我的 HUD 代码 implementation MyScene int counter BOOL updateLabel SKLabelNode counter
如何在 R 中的直方图上叠加频率多边形？

以下是我在 R 中使用的代码使用 RGui 64 位 R 版本 3 3 1 用于绘制数据直方图以及频率多边形我没有使用ggplot2 如何将频率多边形叠加在直方图之上这样我就不必制作两个单独的图表也就是说我想要绘制直方图并将频率
Firefox 中的 NS_ERROR_INVALID_POINTER 错误是什么？

在 Firefox 3 5 中测试 JavaScript 代码时有时会出现以下错误 Component returned failure code 0x80004003 NS ERROR INVALID POINTER 我尝试过谷歌搜索
在自定义策略中获取 Azure AD B2C 应用程序客户端 ID

您好使用 B2C 应用程序的客户端 ID 调用自定义策略我如何在策略中访问它我认为这将被硬编码到 client id 声明中但我不认为它是它仅作为 aud 声明的默认值返回但我再次在自定义策略中没有看到这一点 Thanks 好吧
选择不同计数 cloudant/couchdb

我正在使用 Cloudant 启动一个项目这是一个简单的日志记录系统因此我可以跟踪应用程序的使用情况我的文档如下所示 app 应用程序名称类型页面视图登录 ETC 所有者用户的电子邮件设备 iphone 安卓 ETC 日期
通过 PHP 导出为 CSV

假设我有一个数据库有没有一种方法可以通过 PHP 将数据库中的内容导出到 CSV 文件和文本文件如果可能我个人使用此函数从任何数组创建 CSV 内容 function array2csv array array if count a
javascript：如何在弹出窗口内编写

我创建了一个 javascript 程序创建了电影对象创建了名为 myWin 的新窗口并在 myWin 内创建了另一个名为 actorWin 的窗口现在当我单击单击此处访问演员窗口时我想在 actorWin 中传递 url
使用 svg 圆的 url 填充模式时图像模糊

我正在尝试使用d3 js 圆形包装示例使用 SVG 的图案填充用图像填充一堆 svg 圆圈我的源图像是 800x600 但圆圈的大小会有所不同我设置如下 var patterns defs selectAll pattern data
在 VBA 中处理一系列逗号分隔值

我有这个函数它以字符串形式返回以逗号分隔的字符串中的值该值按给定整数值的顺序排列 Private Sub TestGetNthNumber Debug Print GetNthNumber NUMBERS 5088 5089 5090
查找数组的最大值，如果数组为空则为 0

我需要一种干净的方式来寻找max对于 JavaScript 中的数组说它是arrayMax then arrayMax gt 0 arrayMax 1 2 gt 2 arrayMax 1 gt 1 我尝试过的 Math max apply
适用于 azure 1.50 的 PDFSharp 私有字体

我已经下载并安装了 PDFSharp 1 5 但在使用私有字体时遇到问题我在测试中创建了一个 pdf 创建器效果很好当我将其加载到 Azure 时出现错误无法加载字体研究发现他们没有任何加载的字体所以我必须使用私有字体我只能
C++ 交互式命令行提示符，无需等待换行符

有没有办法在看到换行符之前处理命令行输入我正在考虑制作一个支持自动完成的程序如搜索引擎或 iOS 键盘中的程序更具体地说我的程序在启动时会提示用户输入并且对于每个字符输入我想输出一些内容BEFORE输入换行符或 EOF 以下代
每15分钟高效查询一个巨大的时间序列表一行

我有两张桌子 conttagtable t 和contfloattable 参见 T 大约有 43k 行 CF已经超过90亿了我在两个表上创建了索引tagindex两个表上的列该列可以被认为是一个唯一标识符conttagtable并作为

每15分钟高效查询一个巨大的时间序列表一行

每15分钟高效查询一个巨大的时间序列表一行 的相关文章

随机推荐

热门标签

每15分钟高效查询一个巨大的时间序列表一行的相关文章