主页 / dba / 问题 / 195202
Accepted
John Bachir
Asked: 2018-01-12 11:00:47 +0800 CST

在 tsrange 上使用 gist 索引来检查时间是否在范围之后的最佳方法是什么？

  • 772

表（简化）

                                        Table "public.events"
      Column      |            Type             |                       Modifiers                        
------------------+-----------------------------+--------------------------------------------------------
 id               | integer                     | not null default nextval('events_id_seq'::regclass)
 duration         | integer                     | not null
 start_at         | timestamp without time zone | 
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "my_idx" gist (tsrange(start_at, end_at(events.*), '[)'::text))

功能

CREATE FUNCTION end_at(rec events)
  RETURNS timestamp without time zone
  IMMUTABLE
  LANGUAGE SQL
AS $$
  SELECT $1.start_at + ($1.duration * ('00:00:01'::interval));
$$;

我已经成功地做的事情

索引用于这样的查询：

-- check if current time is within the start and end times
-- of event
where localtimestamp <@ tsrange(start_at, events.end_at, '[)')

而且效果很好。

我想做的事

我想查询当前时间在它们结束之后的事件。我知道如何做到这一点的方法：

  • where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)'). 我很确定这是我想要的语义，并explain analyze说它正在使用索引，但它有点难看，我想知道是否有更好的方式来表达这一点（而且我也模糊地不确定这是我想要的语义，因为我是范围的新手）。
  • where localtimestamp > upper(tsrange(start_at, events.end_at, '[)'))+ 上的 btree 索引upper(tsrange(start_at, events.end_at, '[)'))。这会很好用，但需要保留另一个索引。
  • where localtimestamp > events.end_at. + 上的 btree 索引events.end_at。与上述情况相同。

有没有更优雅（或更正确）的方法来实现上面的第一个要点？

关于如何解决这个问题的任何其他想法？

postgresql index

2 个回答

  1. Best Answer

    Postgres 不支持任何运算符。正如@evan-carroll 所指出的，它可能并且应该这样做。

    所以，最好的解决方案是

    where tsrange(localtimestamp, localtimestamp, '[]') >> tsrange(start_at, events.end_at, '[)')
    • 1

  2. 我想查询当前时间在它们结束之后的事件。我知道如何做到这一点的方法：

    您可以在此处查看 GiST 运算符。您可以在 中查看列表range_ops，

    && &> &< >> << <@ -|- = @> @>

    正如您所指出的，您可能想要的是<<, >>。我会用<<.

    丑吗？是的。如果您在两侧都使用范围，则范围类型很棒，但您不是。您甚至没有将范围类型存储在表格上。我会建议你这样做。

    我不确定这个索引条件是否有效，

    "my_idx" gist (tsrange(start_at, end_at(events.*), '[)'::text))

    你测试过吗？我的意思是，PostgreSQL 可以验证start_at工作，我的假设是它不够聪明，无法end_at使用events.*（如果其中使用的值（列）之一end_at从表创建更改为调用呢？即使从未发生过，PostgreSQL 将如何我知道）。我会将 存储tsrange在表上并删除所有相关的列。

    忽略下面的，看看它。

    您也可以考虑通过不构建 rhs 范围来缩短它。

    SELECT tsrange('yesterday', 'tomorrow') @> timestamp without time zone 'now';
SELECT tsrange('yesterday', 'tomorrow') @> 'now'::timestamp without time zone;

    或者，如果您更喜欢这种格式。或以下，这似乎是一种反模式。

    SELECT tsrange('yesterday', 'tomorrow') @> '[now,now]';

    或者你可以使用tstzrange（这可能是一个更好的主意），

    SELECT tstzrange('yesterday', 'tomorrow') @> now();

    • 0

相关问题