PostgreSQL匹配开始和结束时间之间的时间段与时间戳

Question

我正在设计一个系统，用于存储包含开始时间和结束时间的记录。例如：

CREATE TABLE test (
  id bigserial PRIMARY KEY,
  ts_start timestamp NOT NULL,
  ts_end timestamp NOT NULL,
  foo bar NOT NULL,
  ...
);

现在我想在这个时间序列上运行查询，以查找与特定时间戳重叠的所有行。这将导致一个类似于以下的where子句：

WHERE ts_start <= '2006-4-6 12:34:56' AND ts_end > '2006-4-6 12:34:56'

我已经使用大量生成的测试数据进行了测试，性能非常差。我在ts_start上测试了索引，也在ts_end上测试了索引，还在ts_start和ts_end上测试了多列索引。最后一个给出了最佳结果，但仍远非最优。

问题是postgresql不知道ts_end保证大于ts_start的事实，因此它使用的计划可以找到ts_end小于ts_start的行。

有什么建议解决这个问题吗？

编辑： 对于遇到这个问题的人们，如果你可以再等一段时间，那么PostgreSQL 9.2有完美的解决方案：range types。9.2现在正处于beta版本，最终版本很可能在2012年底发布。

- Eelke

---

非常感谢您添加了关于范围类型的信息！ - paul

Answer 1

有一个名为“temporal postgres”的项目（在谷歌上搜索），但我不知道它是否仍在维护……我相信曾经讨论过将这种类型的搜索功能纳入postgres，但我不记得最终结果如何。无论如何：

以下是使用box和gist的示例：

CREATE TABLE segments( start INTEGER NOT NULL, stop INTEGER NOT NULL, range_box BOX NOT NULL );
INSERT INTO segments SELECT n,n+1,BOX(POINT(n,-1),POINT(n+1,1)) FROM generate_series( 1, 1000000 ) n;
CREATE INDEX segments_box ON segments USING gist( range_box );
CREATE INDEX segments_start ON segments(start);
CREATE INDEX segments_stop ON segments(stop);

EXPLAIN ANALYZE SELECT * FROM segments WHERE 300000 BETWEEN start AND stop;
 Index Scan using segments_start on segments  (cost=0.00..12959.24 rows=209597 width=72) (actual time=91.990..91.990 rows=2 loops=1)
   Index Cond: (300000 >= start)
   Filter: (300000 <= stop)
 Total runtime: 92.023 ms

EXPLAIN ANALYZE SELECT * FROM segments WHERE range_box && '(300000,0,300000,0)'::BOX;
 Bitmap Heap Scan on segments  (cost=283.49..9740.27 rows=5000 width=72) (actual time=0.036..0.037 rows=2 loops=1)
   Recheck Cond: (range_box && '(300000,0),(300000,0)'::box)
   ->  Bitmap Index Scan on segments_box  (cost=0.00..282.24 rows=5000 width=0) (actual time=0.032..0.032 rows=2 loops=1)
         Index Cond: (range_box && '(300000,0),(300000,0)'::box)
 Total runtime: 0.064 ms

正如您所看到的，这里的gist索引非常快（1500倍！哈哈），并且您可以使用许多运算符，例如重叠、包含、被包含等等。

http://www.postgresql.org/docs/8.2/static/functions-geometry.html

- bobflux

Answer 2

您遇到了与试图索引线段并查询点是否在该段中的人相同的问题。您无法通过分别索引每个维度来做到这一点，需要构建某种BSP结构进行索引。

我不确定PG是否有任何内置数据类型来支持日期范围，但我确定如果您使用PostGIS将时间范围表示为2D空间中的点，然后告诉PG对其进行地理索引，您将获得此查询的最佳性能。

也许在pg中有一个特定于日期的等效项，但正如我所说，我不熟悉它。我熟悉pg的几何索引功能，我认为您应该认真考虑它作为优化。

以下是一个天真的示例（尽管我确定查询速度非常快）：

1. 将每个时间范围表示为从原点（0,0）到点（from，to）的矩形。 2. 打开地理索引。 3. 给定时间段P，您可以通过检查点（P，P）是否在具有类似ST_Contains函数的矩形内来查询它是否在时间内。此查询将为O（log（范围数））。

说明：

               |
               |
               |
               |
        to     |
  (timestamp)  |
               |
               |
               |_________________  (from,to)
               |__               |
               |  |(p,p)         |
               |__|______________|_______________________

                                from (timestamp)

- Assaf Lavie

---

我刚刚建立了一个简单的测试表格，其中包含开始和结束时间戳，全部随机生成，所有结束时间都比开始时间晚一些随机的时间，并且在我的笔记本电脑上有100万行数据。当我使用类似于上面的范围进行count(*)查询时，查询结果在30到300毫秒之间。通过更改random_page_cost（降低它）来优化索引，并获得更好的运行时间。你所查询的表有多大？ - Scott Marlowe

@Scott：目前我正在测试1900万行数据，使用多列索引大约需要6秒钟（并且CPU负荷很高）。我有另一个类似的用例，增加了一个限制条件，可以进行更有针对性的查询，只需要1毫秒左右的时间，针对一个类似大小的表格和结果。 - Eelke

你的 explain analyze 对查询计划有何评价？降低 random_page_cost 直到使用索引扫描是否有帮助？ - Scott Marlowe

Answer 3

问题在于PostgreSQL不知道ts_end保证大于ts_start，因此它使用了一种能够查找ts_end小于ts_start的行的计划。

在这种情况下，您需要重新表达查询，以便告诉Postgres。

就像在嵌套集中针对lft/rgt进行查询时一样：如果您使用lft/rgt索引树，使得子项具有parent_lft < lft和lft < rgt和parent_lft < parent_rgt，则最佳查询将依赖于parent_lft < lft和lft < parent_rgt（在lft上查找小范围的索引），而不是parent_lft < lft和rgt < parent_rgt（从一个点开始在lft上查找索引）。

当您添加索引时，您会遇到类似的情况。除非您限制ts_start和ts_end中的一个或两个，否则您将查看大量行。

现在我想在此上运行查询，以查找与某个时间戳重叠的所有行。这将导致像这样的where子句：

WHERE ts_start <= '2006-4-6 12:34:56' AND ts_end > '2006-4-6 12:34:56'

对于特定的查询，您可能需要研究几何类型并使用GIST索引。

具体而言，如果您将ts_start和ts_end分别向下取整和向上取整到午夜，则可以获得整数表示（例如自纪元以来的天数）。然后将后者存储为可索引类型，并使用重叠条件查询它。

顺便说一下，在pg-hackers列表中最近几个月有关于添加某种时间戳段/事件类型的讨论，但是我通过谷歌搜索仍未能找到相关参考资料。因此...在这里提及一下，以防您比我更幸运。

- Denis de Bernardy