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

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。 'date' is not a 'timestamp' .此外，虽然它在 PostgreSQL 中是允许的，但它是一个 reserved word在任何 SQL 标准中。 (不过，为了这个答案的目的，我会坚持使用 date)

除此之外，不需要 index on an expression和像 Tommy 提议的 WHERE 子句。 (日期(创建时间))。 普通 B 树索引 和不带 WHERE 子句的查询可以使工作更简单、更快速。

CREATE INDEX measures_date_idx ON measures(date);

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

---

如果您的表真的很大并且您担心索引大小，您可以使用 partial index 大幅缩小尺寸。像这样:

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;