postgresql - postgres : Index on a timestamp field

Question

我是 postgres 的新手，我有一个关于时间戳类型的问题。

为了设置场景，我有一张如下所示的表格:

CREATE TABLE IF NOT EXISTS tbl_example (
    example_id bigint not null,
    example_name text,
    example_timestamp timestamp,
    primary key (example_id)
);

现在我想运行一个查询，使用时间戳为我查找基于特定日期的示例列表。

例如，将始终运行的常见查询是:

select example_id, example_name, example_timestamp where example_timestamp = date_trunc('datepart', example_timestamp) order by example_timestamp desc;

但是，为了加快搜索过程，我想在 example_timestamp 字段中添加一个索引:

CREATE INDEX idx_example_timestamp on tbl_example(example_timestamp);

我的问题是 postgres 如何在时间戳上执行索引 - 换句话说，它会根据日期/时间索引时间戳，还是会进入秒/毫秒等？

或者，我正在考虑创建一个带有“example_date”的新列，并在该列上建立索引以简化事情。我并不热衷于同时拥有日期和时间戳字段，因为我可以从时间戳字段获取日期，但出于索引目的，我认为最好创建一个单独的字段。

如果有人对此有任何想法，将不胜感激？

谢谢。

Answer 1

别担心，开心就好

how does postgres perform the index on the timestamp - in other words will it index the timestamp based on the date/ time, or will it go into the seconds/ milliseconds, etc?

Postgres 使用的索引方案的内部结构通常对您来说应该是透明的，无需考虑。请记住，您今天学习的实现可能会在 Postgres 的 future 版本中发生变化。

你很可能掉进了 premature optimization的陷阱.相信 Postgres 及其默认行为，直到您知道您有明显的性能问题。

时刻

日期时间处理比您可能理解的要复杂。

首先，您正在使用 TIMESTAMP 这实际上是 TIMESTAMP WITHOUT TIME ZONE 的缩写名称.此类型 不能代表片刻 .这种类型只存储日期和时间。例如，2020 年 1 月 23 日中午 12:00。但这是否意味着日本东京的中午？还是几个小时后在法国巴黎的中午？或者几个小时后在美国俄亥俄州托莱多的中午？

我建议总是完全扩展类型名称，以便在您的 SQL 中非常清楚。使用 TIMESTAMP WITHOUT TIME ZONE而不是 TIMESTAMP .

但是如果你真的想代表时刻，时间线上的一个特定点，你必须使用 TIMESTAMP WITH TIME ZONE .此名称来自 SQL 标准。但是在 Postgres 和其他一些数据库中，这有点用词不当。 Postgres 实际上并不存储时区。相反，Postgres 使用随输入一起提交的任何时区或 UTC 偏移量信息来调整为 UTC。写入存储的值始终采用 UTC。如果您关心原始区域名称或偏移量(小时-分钟-秒)，则需要将其存储在第二列中。

从数据库中检索时，该值也以 UTC 格式显示。但请注意，某些中间件工具坚持在检索后应用默认时区值。虽然本意是好的，但这种反特征可能会引起很多困惑。使用如下所示的 java.time 对象时，您将不会有这样的困惑。

跨度查询

Postgres 以 UTC 格式存储片刻，可能是来自 epoch-reference 的计数日期时间，因为数据类型被记录为 64 位(8 个八位字节)的整数。根据维基百科，Postgres 使用纪元引用 2000-01-01，大概是该日期在 UTC 中的第一个时刻，2000-01-01T00:00:00.0Z。我们没有任何理由关心使用了哪些纪元引用，但是你去了。

真正的重点是 Postgres 中的日期时间值简单地存储为一个数字，计数为 microseconds .时间戳类型不是您可能认为的特定日期和时间。您的查询当然可以从时间戳列上的索引中受益，但面向日期(没有时间)的查询不会特别受益。索引不是面向日期的，也不能像我接下来要解释的那样。

从某个时刻确定日期需要时区。对于任何给定时刻，日期在全局各地因时区而异。午夜过后几分钟在巴黎法国是新的一天，而在魁北克蒙特利尔仍然是“昨天”。

要按日期查询时刻，您需要确定当天的第一个时刻和第二天的第一个时刻。然后我们使用半开方法来定义一个时间跨度，其中开始是包含的，而结束是不包含的。我们搜索等于或晚于开头同时也在结尾之前的时刻。提示:“等于或晚于开头”的另一种说法是“不早于”。

您使用的是 Java，因此您可以在那里使用行业领先的 java.time 类。

java.time 类使用的分辨率为 nanoseconds ，比 Postgres 中使用的微秒更精细。因此，您将没有问题将 Postgres 值加载到 Java 中。但是，在向另一个方向移动时要注意数据丢失，因为纳秒将被静默截断以仅存储微秒。

在确定一天的第一时刻时，不要假设一天从 00:00:00.0 开始。某些区域中的某些日期从其他时间开始，例如 01:00:00.0。始终让 java.time 确定一天中的第一个时刻。

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;                          // Or `Africa/Tunis`, `America/Montreal`, etc.
LocalDate today = LocalDate.now( z ) ;
ZonedDateTime zdtStart = today.atStartOfDay( z ) ;              // First moment of the day.
ZonedDateTime zdtStop = today.plusDays( 1 ).atStartOfDay( z ) ; // First moment of the following day.

编写您的半开放 SQL 语句。不要使用 SQL 命令 BETWEEN因为它不是半开。

String sql = "SELECT * FROM tbl WHERE event !< ? && event < ? ;" ;  // Half-Open query in SQL.

将您的开始和结束值传递给准备好的语句。

您的 JDBC driver配套J DBC 4.2和更高版本可以通过使用 PreparedStatement::setObject 与大多数 java.time 一起使用& ResultSet::getObject .奇怪的是，JDBC 规范不需要支持两种最常用的类型: Instant (总是在 UTC)和 ZonedDateTime .这些可能会也可能不会适用于您的特定驱动程序。该标准确实需要支持 OffsetDateTime ，所以让我们转换成那个。

preparedStatement.setObject( 1 , zdtStart.toOffsetDateTime() ) ;
preparedStatement.setObject( 2 , zdtStop.toOffsetDateTime() ) ;

由此产生的 OffsetDateTime传递给 PreparedStatement 的对象将携带该时区在该日期时间使用的偏移量。为了调试或好奇，您可能希望以 UTC 格式查看这些值。因此，让我们通过提取 Instant 来调整到 UTC ，然后应用零时分秒的偏移量来获得 OffsetDateTime携带UTC本身的偏移量。

OffsetDateTime start = zdtStart.toInstant().atOffset( ZoneOffset.UTC ) ;
OffsetDateTime stop = zdtStop.toInstant().atOffset( ZoneOffset.UTC ) ;

传递给准备好的语句。

preparedStatement.setObject( 1 , start ) ;
preparedStatement.setObject( 2 , stop ) ;

一旦这些 start和 stop值到达数据库服务器后，它们将被转换为一个数字，表示一个从纪元开始的计数，一个简单的整数。然后 Postgres 执行一个简单的数字比较。如果这些整数上存在索引，则该索引可能会或可能不会被利用，因为 Postgres 查询规划器认为合适。

如果您的行数相对较少，并且有大量 RAM 来缓存它们，则可能不需要索引。执行测试，并使用 EXPLAIN/ANALYZE 查看真实世界的性能。

通过 Java 的日期列

如果您已经完成了证明面向日期查询的性能问题的工作，您可以添加类型为 DATE 的第二列。 .然后索引该列，并在面向日期的查询中明确引用它。

在插入您的时刻时，还包括在对您的应用程序有意义的任何时区中感知的日期的计算值。请务必清楚地记录您的意图，以及用于确定日期的时区的细节。提示:Postgres 提供了一项功能，可以在列名称及其数据类型旁边包含文本简介作为列定义的一部分。

作为第二个 DATE列是从另一列派生的，根据定义，它是多余的，并且是非规范化的。通常，您应该将非规范化仅作为最后的手段。

插入值时的 Java 代码。

String sql = "INSERT INTO tbl ( event , date_tokyo ) VALUES ( ? , ? ) ;" ;

确定当前时刻，以及在时区中感知的当前时刻的日期 Asia/Tokyo .

Instant now = Instant.now() ;  // Always in UTC, no need to specify a time zone here.
OffsetDateTime odt = now.atOffset( ZoneOffset.UTC ) ;  // Convert from `Instant` to `OffsetDateTime` if your JDBC driver does not support `Instant`.
ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = now.atZone( z ) ;
LocalDate localDate = zdt.toLocalDate() ; // Extract the date as seen at this moment by people in the Tokyo time zone.

传递给你准备好的声明。

preparedStatement.setObject( 1 , odt ) ;
preparedStatement.setObject( 2 , localDate ) ;

现在您可以在 date_tokyo 上进行面向日期的查询柱子。如果需要，索引。

通过 SQL 的日期列

或者，您可以填充 date_tokyo列自动在 Postgres 中。

扳机

您可以编写一个触发器，它使用 Postgres 中内置的日期时间函数来确定在时区 Asia/Tokyo 中看到的那个时刻的日期。 .然后触发器可以将结果日期值写入第二列。

生成值列

或者，使用 Postgres 12，您可以更简单地使用新的生成列功能。这个新功能执行相同的工作，但无需定义和附加触发器。有关此新功能的讨论，请参阅:

New In PostgreSQL 12: Generated Columns

Generated columns in PostgreSQL 12作者:柯克·罗伊巴尔

PostgreSQL 12: generated columns作者:丹尼尔·韦斯特曼

在 Postgres 12 中，一列带有 GENERATED ALWAYS AS (…) STORED物理上存储了它的值，并且可以被索引。

警告

此类日期时间工作的关键是有关当前时区定义的正确信息。通常此信息来自 tz data维护者 ICANN/IANA。

Java 和 Postgres 都包含自己的 tz 数据副本。

世界各地的政界人士都表现出重新定义时区的倾向，通常几乎没有警告。所以一定要跟踪你关心的时区的变化。当您更新 Java 或 Postgres 时，您可能会获得 tz 数据的新副本。但在某些情况下，您可能需要手动更新其中一个或两个环境(Java 和 Postgres)。还有你的房东 OS也有 tz 数据副本，仅供引用。