获取表的最后一个值的时间越来越长

Question

我的 Postgres 数据库中有一个表，每 10 分钟存储一次值。我现在在该表中有很多记录（我不想删除旧的记录）。基本上，该行包含一个值和一个时间戳。我的 SQL 请求没有优化，因为它似乎循环遍历整个表...

SELECT value, date FROM measures order by date desc limit 1

我还尝试在日期上添加过滤器，例如：（

SELECT value, date FROM measures WHERE date > date '2011-10-20' order by date desc limit 1

当然日期是动态的）。但同样的事情......

是否需要索引或者可以优化查询吗？

Answer 1

首先，您不应将时间戳列命名为 date。 “日期”不是“时间戳”。另外，虽然 PostgreSQL 允许这样做，但它是一个保留字 在任何 SQL 标准中。 （不过，出于本答案的目的，我将坚持使用日期）

除此之外，不需要 表达式上的索引 和像 Tommy 提议的 WHERE 子句。 （日期（创建时间））。 普通 B 树索引和不带 WHERE 子句的查询使工作更简单、更快。

CREATE INDEX measures_date_idx ON measures(date);

然后您的查询将按原样运行，速度快如闪电。 B 树索引可用于升序和降序排序，同样有效。阅读有关索引和 ORDER BY - 涵盖了您需要了解的针对您的案例的大部分内容。

---

如果您的表确实很大并且您担心索引大小，您可以使用部分索引可大幅减小大小。像这样：

CREATE INDEX measures_date_idx ON measures(date)
WHERE date > '2011-10-20 00:00:00'::timestamp;

然后您的查询必须包含与部分索引完全相同的 WHERE 子句。像这样：

SELECT value, date
FROM   measures
WHERE  date > '2011-10-20 00:00:00'::timestamp
ORDER  BY date DESC
LIMIT  1;

Answer 2

首先 - 动态+1。你是对的，你的查询变得越来越慢，因为没有索引，你每次查询一个值时都会进行全表扫描。我不是一个强大的 postgres DB 用户，但看起来您确实可以在日期字段上放置索引（因为这是您主要查询的内容）。

摘自本文：

另一个常见的示例是查找给定日期的行，其中
我们已将时间戳存储在日期时间字段中，但希望通过
日期铸造值。像

这样的索引

CREATE INDEX articles_day ON articles ( date(created_at) )  

可以由包含

的查询使用

WHERE date(articles.created_at) = date('2011-03-07').

。根据您的具体情况，您可能会发现该文章中的一些其他信息也很有趣。