PostgreSQL的不同疑问更快

今天发布的时间尺寸为2.2.1包括“时间尺寸跳过扫描”，我们实现了一种技术，以实现不同查询的查询时间。跳过扫描在PostgreSQL表，时间尺度的超级赌注和时间尺度的分布式超级赌注（在多节点集群中的计算机上缩放），在我们的测试中实现了26x-8000x的速度。

向工作的工程师喊叫：Sven Klemm，Joshua Lockerman，Nikhil Sontakke，以及整个审稿人和测试人员！

上周有多少次您使用了应用程序或查询数据寻找最近的东西的值？也许您最近审查了每项投资的最新库存或加密货价。或者，也许您检查了所有幻想棒球队参与者的最新百分比。

或者您也许您使用IoT设备，或者使用Prometheus等工具监控您的基础架构，并具有图形和警报，重复查询每个设备或服务的最新值。

无论你＆＃39; re看着股票或试图找到一块网络设备，＆＃39;＆＃39;你有一个很好的机会与过去几天这样的数据询问这样的数据：

每个项目的最后一次阅读（库存，物联网设备等）是什么？它发生了什么时间？

一小时前报告的设备是什么，但在过去的10分钟内发送了数据＆＃39;

检索所有客户端x的最新读数的分页列表

当我们收集和分析比以往任何时候都要分析更多的数据 - 即将监控我们的应用程序，物联网设备，金融市场或其他情景 - 我们能够快速计算我们问题的答案至关重要，特别是在推送时应用程序或仪表板。

PostgreSQL是一个惊人的数据库，但它可以与某些类型的查询斗争，尤其是表方法，数十万行（或更多）。截然不同的查询是此示例。

当他们似乎问一个＆＃34时，为什么在PostgreSQL上慢慢慢慢查询; Easy＆＃34;问题？事实证明，PostgreSQL目前缺乏有效地从有序索引中提取唯一值列表的能力。即使您有索引匹配这些＆＃34的确切顺序和列;最后一点＆＃34;查询，PostgreSQL仍然被迫扫描整个索引以查找所有唯一值。随着表格增长（并且它们随时间序列数据快速增长），此操作不断变慢。

像MySQL，Oracle和DB2等其他数据库，实现一个名为＆＃34;松散索引＆＃34;或＆＃34;索引跳过扫描＆＃34;或“跳过扫描”以加快如下查询的性能。

当数据库有一个特征和＃34;跳过扫描＆＃34;，它可以从一个有序的值逐步跳转到下一个没有读取所有行。如果没有支持此功能，数据库引擎必须扫描整个有序索引，然后在最后重复使用 - 这是一个更慢的过程。

自2018年以来，有计划在PostgreSQL上支持类似的东西。 （注意：由于Postgres扩展框架中可能的一些局限性，我们无法直接使用此实现。）

不幸的是，这个补丁不是在PostgreSQL 14的CommitFest中包含的，这意味着它在最早的PostgreSQL 15之前赢得了＆＃39;否。

今天，通过时间尺寸的2.2.1，我们正在发布时间尺寸跳过扫描，这是一个自定义查询策划节点，使得PostgreSQL🔥中的有序不同查询燃烧。

如您，LL在下面的基准中看到，一些查询比之前的8000倍以下，而且许多SQL查询您的应用程序和分析工具使用也可以看到此新功能的戏剧性改进。

这意味着使用时间尺度，不仅您的时间序列不同的查询速度更快，而且您可能在正常Postgres表上可能拥有的任何其他相关查询也将更快。

这是因为时间尺寸不仅仅是一个时间序列数据库。它是一个关系数据库：特别是，时间序列的关系数据库。使用时间尺度的开发人员获得了一个目的 - 内置的时间序列数据库的好处，以及一个经典关系（Postgres）数据库，全部有一个完整的SQL支持。

（我们也开放到向PostgreSQL上游升级，但实际上更喜欢将其实施在较低级别的其他补丁中，使其成为PostgreSQL 15.因此，如果我们可以提供帮助，请告诉我们。）

并清楚，我们喜欢PostgreSQL。我们聘请为PostgreSQL提供贡献的工程师。我们为PostgreSQL周围的生态系统做出了贡献。 PostgreSQL是世界上增长最快的数据库，我们很高兴能够与数千个其他用户和贡献者一起支持它。

我们不断寻求使用数据库推进现有技术，并且跳过扫描等功能只是我们对行业的最新贡献。跳过扫描使时间尺寸更好，但也使PostgreSQL更好，更具竞争力的数据库，特别是相对于MySQL，Oracle，DB2等。

什么是跳过扫描，它如何使这么巨大？请继续阅读，了解它是如何工作的，我们如何在不同基数和模拟场景中基准测试性能，以及如何开始。

此时，经验丰富的PostgreSQL用户可能指出，已经可以通过递归CTE获得合理的不同查询。

从Postgresql Wiki，使用递归CTE可以让您获得良好的结果，但写下这些类型的查询通常可以感到繁琐和不行，特别是对于PostgreSQL新的开发人员来说：

使用递归CTE为（（从CPU顺序从TAGS_ID选择TAGS_ID，TAGS_ID限制1）UNION所有选择（从CPU选择TAGS_ID＆gt; t.tags_id命令由tags_id，time desc限制1）从其中t.tags_id的cte t没有空值）从CTE限制50中选择*;

但即使在日常询问中撰写像这样的递归CTE，对您来说，＆＃39;是一个更大的问题。大多数应用程序开发人员，ORM和图表工具，如Grafana或Tableau仍然使用更简单，直接的形式：

从cpuwhere tags_id＆gt中选择distinct on（tags_id）*＆gt; = 1 outdy by tags_id，时间desclimit 50;

在PostgreSQL中，没有A＆＃34;跳过扫描＆＃34;节点，此查询将执行较慢的索引仅扫描，导致您的应用程序和图形工具感到笨拙和慢速。

跳过扫描是选择DISTINCT ON（列）的选择形式的查询优化。概念上，跳过扫描是一个常规indexScan，但横跨索引的“跳过”寻找大于当前值的下一个值：

通过跳过扫描扫描扫描，在时间尺寸下，查询规划和执行现在可以使用新节点（在解释输出中显示（SKIPSCAN）），以便从正确有序的索引中快速返回不同的项目。不是必须使用索引扫描扫描的整个索引，而不是扫描扫描，递增扫描在有序索引中的每个连续项。当它找到一个项目时，（skipscan）节点会快速重新启动搜索下一个项目。这证明是在有序指数中找到不同项目的更有效方式。 （有关详细信息，请参阅github。）

我们的跳过扫描的实现与任何有序的索引一起工作，在普通的PostgreSQL表，时间尺度为期快递和时间尺寸分布式超级赌注上。 🎉

在每个示例查询中，跳过扫描的时间尺寸扫描将查询响应时间提高至少26倍。

但是，真正的惊喜是它在较低的数据中产生了多少差异 - 几乎8500倍速度更快地检索每个设备的最新读数。那个＆＃39; s快！

在我们的测试中，在我们的4,000个设备基准中，跳过扫描也始终如一地更快 - 到80倍或更多 - 在我们的4,000个设备基准中。 （这种级别的基数是少校的时间表。）

要执行我们的基准测试，我们使用以下规范将时间尺寸安装在DigitaloCean Droplet上。 PostgreSQL和TimeScaledB从包装中安装，我们从时间尺寸的时间应用了推荐的调音。

时间尺寸为2.2（第一次发布跳过扫描。时间尺度为2.2.1主要添加分布式的超级载支持和一些错误修复。）

为了展示跳过扫描对不同程度的基数的性能影响，我们基准于3个不同尺寸的单独数据集。要生成我们的数据集，我们使用了＆＃39;仅限CPU和＃39;在时间序列基准套件（TSB）中使用案例，每个设备创建10个度量，每10秒，每个设备（由我们的基准查询中的标签标识）。

在现实生活中，并非所有设备数据都是最新的，因为设备脱机，Internet连接中断。因此，为了模拟更现实的场景（即，某些设备已停止报告一段时间），我们将随机设备删除了以下每个时段的行。

要删除数据，我们使用Postgres的Tablesample函数。此选择功能允许您根据总行的百分比返回从表中的随机行示例。在下面的示例中，我们随机采样10％的行（Bernoulli（10）），然后采取前10（限制10）。

从cpuwhere tags_id中删除（从标签中选择ID tableample bernoulli（10）限制10）和时间＆gt; =现在（） - 间隔＆＃39; 30分钟＆＃39 ;;

从那里，我们多次运行每个基准测试查询，以适应缓存，在没有跳过扫描的情况下启用。

如前所述，对于所有查询的高密度，存在以下两个索引。 场景＃1：分页列表中每个设备的上次报告时间是什么？ 选择DISTINCT ON（TAGS_ID）TAGS_ID，从CPUORDERBY TAGS_ID的时间，时间DESCLIMIT 10偏移量50; 场景＃2：分页列表中每个设备的时间和最近报告的一组值是什么？ 场景＃3：过去5分钟内所有报告设备的最新点是什么？ 注意：此查询类似于Prometheus＆＃34;即时查询＆＃34; 并且可以显着加快不同系列的查询。 在一个PromScale实例的一些初始测试中，此查询通过启用跳过扫描来提升9倍。

从CPU中选择不同的（标签）*，其中时间＆gt; =现在（） - 间隔＆＃39; 5分钟＆＃39;按Tags_ID，Time DESC订购;

场景＃4：在今天的某个时间报告的设备，但在最后一小时内没有？

较旧的（在CPU中选择DIMETINCT ON（TAGS_ID）TAGS_ID中的时间＆gt;现在（） - 间隔＆＃39; 24小时＆＃39;）选择*从旧的O不存在（从CPU中选择1，其中CPU.tags_id = o.tags_id和time＆gt;现在（） - 间隔＆＃39; 1小时＆＃39;）;

较旧的（在CPU中选择DISTINCT ON（TAGS_ID）TAGS_ID中的时间＆gt;现在（） - 间隔＆＃39; 48小时＆＃39;和时间＆lt;现在（） - 间隔＆＃39; 24小时＆＃39; 24小时＆＃39;）选择*从旧的O不存在（从CPU中选择1，其中CPU.tags_id = O.TAGS_ID和TIME＆GT; Now（） - 间隔＆＃39; 24小时＆＃39;）;

但是，跳过扫描不是对基准博客帖子保留的理论改进 - 它具有真实的含义，以及许多我们使用的应用程序尽可能快地获取此数据。

考虑每天使用（或开发）的应用程序。他们是否从数据库表中检索唯一项目的名单以填充下拉选项（或数据网格）？

在几千项，查询延迟可能不是很明显。但是，随着您的数据增长，您有数百万行的数据和成千上万的不同项目，即下拉菜单可能需要几秒钟 - 或分钟 - 填充。

甚至更好地，跳过扫描还提供了一种快速，有效的方式回答了这么多与时间序列数据的人每天询问的问题：

＆＃34;我的每一个[设备/用户/服务/加密和股票投资/等等]的最后一次和值是什么？＆＃34;

只要有一个索引＆＃34; device_id＆＃34;和＃34;时间＆＃34;降序，跳过扫描将使用像这样的查询更有效地检索数据。

通过跳过扫描，依赖这些类型查询的应用程序和仪表板现在将加载一批更快的🚀（见下文）。

你是如何开始的？升级到时间尺寸的2.2.1并如下所述设置您的架构和索引。您应该开始看到许多不同查询的立即速度改进。

首先，查询必须在单个列上使用Distinct关键字。上面的基准查询将为您提供一些示例来绘制。

其次，必须有一个索引，其中包含Distinct列，以及列的任何其他订单。具体来说：

独特的列必须是索引的第一列，或者任何前导列必须用作查询中的约束

在实践中，这意味着，如果我们从本博客文章的开头使用问题（＆＃34;检索顺序＆＃34的唯一ID列表;和＆＃34;检索每个id＆＃34的最后读取; ），我们需要至少一个这样的索引（但是如果您使用时间尺寸高度过度，则可能已经存在）：

通过该索引到位，如果您的查询类似于下面的基准示例，您应该开始立即受益。为查询选择跳过扫描时，解释分析输出将显示类似于此的一个或多个自定义扫描（Skipscan）节点：

- ＆gt;独特 - ＆gt; merge附加排序键：_hyper_8_79_chunk.tags_id，_hyper_8_79_chunk。＆＃34;时间＆＃34; desc - ＆gt; _hyper_8_79_chunk - ＆gt上的自定义扫描（skipscan）;索引仅使用_hyper_8_79_chunk_cpu_tags_id_time_idx扫描_hyper_8_79_chunk索引cond :( tags_id＆gt; null ::整数） - ＆gt;在_hyper_8_80_chunk - ＆gt上的自定义扫描（skipscan）;索引仅在_hyper_8_80_chunk_cpu_tags_id_time_idx上扫描_hyper_8_80_chunk索引cond :( tags_id＆gt; null ::整数）...

我们越来越多地看到应用程序和社区驱动的PostgreSQL的示例用于存储和查询大容量，时间序列订购数据。

无论是＆＃39; SIT设备监控还是高频交易，数据正在快速摄取 - 并且用户需要他们的＆＃34;最后一件事发生了什么？＆＃34;查询也快速返回。在这个领域，内置PostgreSQL计划者ISN＆＃39;目前针对这种查询进行了优化。这就是为什么我们建立了跳过扫描。

在使用TSB生成＆gt; 100万行数据并模拟不同范围的＆＃34;上次报道的时间＆＃34;对于所有设备，我们看到跳过扫描可能对有序的不同查询性能有巨大影响（在某些情况下最多8500倍！）。值得注意的是，当应用程序需要向分页接口中显示所有设备的最新值时，我们的查询基准显示跳过扫描在这些方案中提高了应用程序的响应性。

除查询速度改进外，在时间尺寸的跳过扫描中还突出了PostgreSQL可扩展架构的功率。

随着用户尝试跳过扫描，我们希望他们经历的绩效结果解决了他们现在拥有的查询问题，并且可用于支持额外的跳过扫描开发和实现。

以及我们 - 和任何社区成员 - 可以使用PostgreSQL扩展提供的工具解决查询疼痛点来证明生态系统的多功能性，这在我们看来是PostgreSQL的最佳部分之一。

如果您是TimesCaledB的新手，请创建一个免费帐户以开始使用完全管理的时间尺寸实例（30％免费）。

Timescale Forge：TimescaledB 2.2.1现在是Timescale Forge上的所有新服务的默认值，并且您的任何现有服务将在下一个维护窗口中自动升级。

TimeScale Cloud：超级赌注的跳过扫描，并且PostgreSQL表今天可通过时间尺寸为时获得2.2。对分布式超级赌注的支持即将到来，通过TimescaledB 2.2.1即将到来。

加入我们的Slack社区以分享您的结果，提出问题，获取建议，并与其他开发人员联系（我以及我们的联合创始人，工程师和充满激情的社区成员在所有渠道上都是活跃的）。

您还可以访问我们的GitHub来了解更多信息（以及一如既往地，⭐️是值得赞赏的！）以及您希望帮助解决问题的挑战类型：我们正在招聘！