我在时间刻度数据库中有一个表tab1，它有3列tag、time、value。time和tag组成了该表的pk：（时间、标签）。
有超过500万行（50 000 000）。我需要找到最新的时间戳或最大（时间）的每一个N标记。
有几件事，我尝试过，我会分享我的经验与每个：
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秒内给出输出。
我需要找到另一个可行的解决方案，类似于2ND一个，也许是性能更好。我尝试了一些其他的东西，如横向联接（3），窗口函数（行_数，分区）（4），但解决方案不是预期的那样。
1.使用横向给出了多个列的交叉，而不是预期的最长时间的单个值。此外，它需要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.当尝试分区时，我希望将 limit 1 设置为：（分区按标签顺序按时间描述限制1），但它是给语法错误。没有限制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中的查询有什么问题，或者是否有其他选择。
我的表的索引是：
3条答案
按热度按时间xa9qqrwz1#
有几件事可以帮助这个查询，使这个查询更容易，更有效率。第一，也可能是最重要的一个是表/超表的索引-它需要是一个多列索引标签，时间描述-时间的顺序不是很重要。但是索引中列的顺序很重要。
tag必须是这里的第一列，因为我们需要首先按标签搜索，然后获取最新时间，如果我们有单独的索引或者如果我们首先按时间排序，这将是非常低效的。您可以使用如下调用创建此索引：
接下来是查询的公式化。为每个标签获取它的最简单的方法是编写一个
DISTINCT ON查询。在Timescale中我们有optimized this sort of query。它的公式化方式有点奇怪，所以可能有点难找到。基本上你可以这样写：
这样你就能给予你想要的了。虽然有点奇怪，但肯定管用！
我不打算详细介绍其他方法，但大多数方法都将通过索引得到显著改进，但这仍然可能是性能最好的方法。
如果你想，请发表评论，说明这是如何工作的，如果它加快了你的事情！
67up9zun2#
我想在大卫的精彩回答的基础上再补充一点，那就是理解为什么列顺序对查询很重要。
主键实际上是一个多列b树索引。这意味着要使用索引，查询必须先遍历time列，然后才能检查tag列。在您的情况下，这对您没有多大帮助。您希望能够先遍历tag，然后获取最近的时间。
要做到这一点，你必须把标签放在btree列表的第一位。我不知道asc或desc是否会有很大的区别，因为PostgreSQL可以在任何方向上搜索索引，并且你的时间扫描方向并不依赖于你扫描标签的方向。然而，跳过扫描的时间刻度优化可能会，所以最好遵循这个建议。
iovurdzv3#
所以想出了另外两种方法：
1.使用横向
SELECT非重复t_外部.标记，t_顶部.时间，t_顶部.值从表1 t_外部横向连接（SELECT * 从表1 t_内部，其中t_内部.标记= t_外部.标记顺序按t_内部.时间描述限制1）t_顶部为真，其中t_外部.标记在（tag 1）中
这可以工作，但需要超过14秒的时间来处理。
1.使用窗口函数
SELECT * FROM（SELECT标签，时间，“值，”rank（）OVER（PARTITION BY标签按时间描述排序）as RN FROM标签1 WHERE标签IN（标签1））作为结果WHERE结果.RN=1;
这也是可行的，需要大约9秒的时间来处理。
当比较结果时，内部查询和横向查询是性能最差的查询，即使对于单个标签也是如此，因此它们被排除。
到目前为止，Last（）和Partition（）查询是我们的主要竞争者。如果提取的列较少，则Last（）的性能更好，否则如果提取所有列，则两者的执行时间相当。
还有一点我想补充的是，使用ORDER BY LIMIT查询比所有选项都要好（单个标签的执行时间少于1秒），但是缺点是它不适用于多个标签输入。所以如果你需要查询单个标签并且有类似的数据库配置，你可以尝试一下。