主页 / dba / 问题 / 340021
Accepted
birgersp
Asked: 2024-06-05 19:48:03 +0800 CST

时间索引应该按升序还是降序排列？

  • 772

规划一些新表，我试图决定索引应该是“升序”还是“降序”。

该表将非常大（我想象每分钟大约有 2000 次插入，最初从大约有 10 亿行的不同表迁移）。

我将为此使用 timescaledb 扩展（按时间进行分区）。

这是创建表的方式：

create table "Sample"(
    "id" bigserial,
    "deviceId" int not null,
    "timestamp" timestamptz not null,
    "value" float8 not null
);
select create_hypertable('"Sample"', 'timestamp'); -- creates a desc index on "timestamp"

create index on "Sample"("deviceId", "timestamp"); -- should this be "desc"?

这是我们将运行的两个最常见的查询（当然，deviceId 和时间戳可能会有所不同）：

select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp"<'2024-01-01Z'
order by "timestamp" desc limit 1;

和

select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp" between '2024-01-01Z' and '2024-02-01Z'
order by "timestamp" asc;

所以我想了解的是“时间戳”应该按什么顺序排列？为什么？

我的（可能错误的）直觉告诉我带有“时间戳”的索引应该按升序排列，因为我需要按时间戳升序对数据进行排序。

但是，时间刻度文档中的示例总是按降序对时间列进行索引。我不太明白为什么。

这里理想的指数选择是什么？

postgresql

1 个回答

  1. Best Answer

    您绝对应该按升序创建索引，除非您按降序插入行。

    对于索引扫描，向前还是向后扫描都没有区别，因此对于您的查询来说顺序并不重要。

    但是PostgreSQL的B树索引针对在上端插入值进行了优化，而对于下端则没有这样的优化。因此，如果您要插入时间戳通常不断增加的时间序列数据，则升序索引更好。

    除了这个考虑之外，创建降序索引的唯一原因是如果您需要支持ORDER BY混合列顺序的条件，例如ORDER BY a DESC, b。

    • 1

相关问题