在Timescaledb中，用于查找与N个id对应的表中最新或最大时间戳值的SQL查询。

8 浏览
0 Comments

在Timescaledb中，用于查找与N个id对应的表中最新或最大时间戳值的SQL查询。

我在timescale db中有一个名为tab1的表，它有3个列tag，time，value。time和tag组成了表的主键：(time, tag)。

有超过500万（50 000 000）行数据。我需要找到每个N个标签的最新时间戳或最大值(time)。

我尝试了一些方法，我将与每个方法的经验分享：

  1. 内部查询

SELECT "time", "tag", "value"
FROM tab1 
WHERE ("tag","time") IN 
(SELECT "tag", MAX("time") FROM tab1 WHERE "tag" IN(tag1,tag2) GROUP BY "tag" );

这个查询能够给出结果，但执行时间约为19秒，超出了可接受的限制

  1. 使用timescale db中的last函数

SELECT tag, last(time, time), last(value,time)
FROM tab1
WHERE "tag" IN (tag1,tag2) GROUP BY "tag" ;

这个查询可以在10秒内得出结果。

我需要找到另一个类似于第二个方法的可行解决方案，性能可能更好。我尝试了一些其他方法，如LATERAL JOIN（3），WINDOW FUNCTIONS（ROW_NUMBER，PARTITION）（4），但结果并不如预期。

  1. 使用LATERAL会导致多个列的交叉，而不是得到一个带有最大时间的单个值。此外，它执行时间为15秒，但可能是由于查询错误。

SELECT table1."tag", table1."time",table1."value" from tab1 as table1
join lateral (
    SELECT table2 ."tag",table2 ."time" from tab1 as table2   
    where table2."tag" = table1."tag"
    order by table2."time" desc limit 1
) p on true
where table1."tag" in (tag1,tag2)

  1. 当尝试使用PARTITION时，我想像这样加上limit 1：(partition by tag order by time desc limit 1)，但是它会导致语法错误。没有limit 1，我无法得到最新的时间。

SELECT * from 
( SELECT *, row_number() over (partition by tag order by time desc) as rownum
from  tab1) a
where tag in (tag1,tag2)

有人可以指出3、4中的查询有什么问题，或者是否有其他替代方案。

0

3 答案

0 Comments

在这篇文章中，我们将讨论一个关于在Timescaledb中从表中找到对应于N个id的最新或最大时间戳的SQL查询的问题。问题的出现原因是由于查询中列的顺序对于索引的使用非常重要。

首先，需要了解的是，主键实际上是一个多列B树索引。这意味着查询必须首先遍历时间列，然后才能检查标签列。在这种情况下，这对你来说并没有太大的帮助。相反，你想要的是首先遍历标签，然后获取最新的时间。

为了实现这一点，你必须在B树列表中将标签放在首位。不确定升序还是降序在这里是否会有很大的区别，因为PostgreSQL可以在任何方向上搜索索引，而你在时间上的扫描方向并不依赖于标签扫描的方向。但是，Timescale对于跳过扫描的优化可能会有影响，所以最好遵循这个建议。

我认为他们之前有两个单独的索引，一个是在标签上，一个是在时间上，现在他们可能有了正确顺序的多列索引，但不确定是否百分之百正确。

但是这是关于B树排序的一个重要观点！

是的，有些情况下它会有很大的影响。然而，由于PostgreSQL可以在任何方向上扫描B树，所以影响较大的情况可能较少。例如，你提到的跳过扫描意味着如果你必须在不同方向上扫描两个不同的键，则如果这些键在索引排序中未指定，将会出现问题。

另一方面，如果标签和时间都是降序的，在查询中，索引要求它们都是相同的方向（因为如果它们都是升序，你可以同时进行向前扫描，如果它们都是降序，你可以同时进行向后扫描）。我实际上建议如果在索引中指定了一个列的方向，最好同时指定另一个列的方向，这样它们之间的关系就更清晰明确。

至于原始索引的问题，主键指定了两个列，表明它是一个多列B树索引。

简而言之，索引和主键都是多列的（标签，时间）。

0
0 Comments

原因：这个问题的出现可能是因为在Timescaledb中查询具有最新或最大时间戳的表的N个id时，性能不佳。可能的原因是没有正确的索引和查询语句的不合理。

解决方法：为了提高查询性能，首先需要在表/超表上创建一个多列索引，其中包括tag和time列。索引的顺序很重要，tag列必须是第一列，以便先按照tag搜索，然后获取最新的时间戳。可以使用以下代码创建索引：

CREATE INDEX ON tab1 (tag, "time" DESC);

其次，需要使用DISTINCT ON查询来获取每个tag的最新时间戳。在Timescale中，已经针对这种查询进行了优化。可以使用以下查询语句：

SELECT DISTINCT ON (tag) tag, "time" FROM tab1 ORDER BY tag, "time" DESC;

这样就可以得到想要的结果。

其他方法也可以通过创建索引来显著改进性能，但使用DISTINCT ON查询可能仍然是最高效的方法。

如果有需要，请在评论中分享您的结果和性能提升情况。

感谢您的回答。我会尝试并与您分享更新。关于索引的问题，我们曾尝试将其从升序改为降序，但并没有看到很大的改进。但是，根据您的建议，我们将尝试优先考虑tag列。

这个方法非常有效。即使有15个以上的标签，响应时间也在2秒内。我们还更新了索引。对于其他需要了解此查询如何工作的人，请查看答案中的链接，然后查看描述distinct和distinct on之间区别的这个答案。

0
0 Comments

这篇文章将讨论在Timescaledb数据库中，如何通过SQL查询找到与N个id对应的表中最新或最大时间戳的值，并提供解决方法。

在这段代码中，首先介绍了两种解决方法：

1. 使用lateral关键字：

SELECT distinct t_outer.tag, t_top.time, t_top.value from  tab1 t_outer
join lateral (
SELECT * from  tab1 t_inner
where t_inner.tag = t_outer.tag
order by t_inner.time desc
limit 1
) t_top on true
where t_outer.tag in (tag1)

这种方法能够正常工作，但处理时间超过14秒。

2. 使用Window函数：

SELECT * FROM (SELECT tag,time,"value",
rank() OVER (PARTITION BY tag order by time desc ) as RN
FROM tab1
WHERE tag IN(tag1) ) as results WHERE results.RN=1;

这种方法也能够正常工作，但处理时间约为9秒。

通过比较结果，内部查询和lateral方法在性能方面表现最差，即使只有一个标签。因此，它们被排除在外。

现在，Last()和Partition()查询是我们的最佳选择。

如果只获取少数列，Last()方法的性能更好；如果获取所有列，两种方法的执行时间相当。

另外，需要注意的是，使用ORDER BY LIMIT查询在所有选项中的性能都要好得多（对于单个标签，执行时间小于1秒）。但它的缺点是无法用于多个标签的输入。

总结起来，对于在Timescaledb数据库中查找与N个id对应的表中最新或最大时间戳的值，可以考虑使用Last()和Partition()方法，或者根据具体情况使用ORDER BY LIMIT查询。

0