优化/索引时区查询 [英] Optimize/Index Timezone Query

问题描述

我想知道是否有人对如何优化此查询以使其可以使用索引有任何建议？我们有与数据库中的属性关联的保留。每个属性都有一个时区设置。我们要查询的是所有在给定日期（例如今天）开始的预订。

I'm wondering if anyone has any recommendations on how to optimize this query so it can use an index? We have reservations that are associated to properties in our database. Each property has a timezone set. What we want to query for is "all reservations that start on a given date (e.g. today)".

相关的查询片段是

reservations.start_on::Date = COALESCE((current_timestamp at time zone properties.time_zone), current_timestamp)::Date

属性表在查询中的连接早于您的预期。

The properties table is joined earlier in the query as you would expect.

问题您可能猜到了，是时区。我们不能简单地要求 start_on = {date} ，因为当我们说 {date} 时，我们的意思是{date该属性的时区}中，根据当前时间实际上可能不是 {date} 。 {date} 将以例如 4/7/2014 基于我们应用服务器的时区，但实际上我们想包括从 4/8/2014 如果预订的财产在澳大利亚的悉尼。

The problem is, as you probably guessed, timezones. We can't simply ask for start_on = {date} because when we say {date} we really mean {date in that property's timezone} which may not actually be {date} based on the current time. {date} will come in as, e.g. 4/7/2014 based on the timezone of our App server but we actually want to include reservations that start on 4/8/2014 if the reservation's property is in Sydney Australia.

----编辑----

---- EDIT ----

一些其他信息。

reservations.start_on存储为 date 类型。

reservations.start_on is stored as type date.

properties.time_zone存储为字符变化（255），默认为'America / Los_Angeles'::字符变化。

properties.time_zone is stored as character varying(255), default 'America/Los_Angeles'::character varying.

预订的开始日期存储为您在属性时区中的日期。在大多数情况下，需要解释的部分是我们查询的日期，以将其转换为各个属性时区中的适当日期。

The reservation's start_on date is stored as the date were you in the timezone of the property. The part that needs to be interpreted is the date we are querying for to shift it to the appropriate date in the various property timezones in most cases.

我肯定是开放的到其他方法，但这是到目前为止我能想到的一切...基本上，我希望能够问数据库的几个问题：

I'm certainly open to other approaches but this was all I could think to do so far... Basically I want to be able to ask a few questions of the DB:

1. 哪些保留在今天开始？


2. 哪些保留在将来有开始日期？


3. 哪些保留在今天开始

开始日期虽然是一个日期，但实际上实际上是一个时间范围，因此您不能只说 start_on>例如：now（），因为时区。

Start date, being a date though, is obviously actually a time range so you can't just say start_on > now() for example because timezones.

表定义： https://gist.github.com/anonymous/10295380

----编辑2 ----

---- EDIT 2 ----

我尝试切换到使用tsrange作为start_on。时间范围从酒店的时区更改为UTC。这意味着2014年4月11日在悉尼的start_on存储为 ['4/10/2014 14:00:00'，'4/11/2014 13:59:59'] 。我在start_on列的tsrange版本上有一个重点索引。

I tried switching over to using tsrange for start_on. The range is time shifted from the property's timezone to UTC. That means a start_on of 4/11/2014 in Sydney is stored as ['4/10/2014 14:00:00', '4/11/2014 13:59:59']. I have a gist index on the tsrange version of the start_on column.

这似乎可以正常工作并返回正确的结果。它对特定时间的@>查询使用要点索引，并且速度很快。不幸的是，它没有对所有范围操作都使用gist索引...

This seems to work perfectly and return the right results. It uses the gist index for @> queries with a specific time and is blazing fast. Unfortunately it is NOT using the gist index for all range operations...

以下是要点（haha），其中一些示例查询示例： https://gist.github.com/bdmac/10496601

Here is a gist (haha) with example EXPLAINs on some of our queries: https://gist.github.com/bdmac/10496601

示例中显示的列 arrival_day 等效于 start_on ，但范围是tsrange。我还没有删除旧的 start_on 列。

The column shown in the examples, arrival_day is equivalent to start_on but is a tsrange. I have not removed the old start_on column yet.

一些现在很慢的查询即将来临或过去保留，我必须构造一个tsrange，没有上限或下限。我似乎无法弄清楚如何使用<<或>>接受元素而不是范围，例如@@

Some of the queries that are now slow are "upcoming" or "past" reservations where I had to construct a tsrange with no upper or lower bound depending. I can't seem to figure out how to do a query with << or >> that accepts an element instead of a range like you can do with @>

推荐答案

如果您实际上有一列键入 timestamp 并根据当前时区（部分）对其进行解释，并且该时区会有所不同，因此索引通常不可能。您只能在 IMMUTABLE 数据上建立索引...

If you actually have a column of type timestamp and interpret it (in parts) depending on the current time zone, and this time zone can vary, then an index is generally impossible. You can only build an index on IMMUTABLE data ...

要回答以下问题：

1. 今天开始进行哪些预订？


2. 哪些预订的开始日期在将来？


3. 哪些预订的开始日期在过去？

...最好存储一个 带有时区的时间戳记 。只是 不够精确。

... you best store a timestamp with time zone. Just a date is not precise enough.

只要我们只对本地的今天感兴趣（ （由当前时区定义），我们不需要明确保存时区。我们不在乎它发生在哪里，我们只需要一个绝对的时间就可以进行比较。

As long as we are only interested in the local "today" (as defined by the current time zone), we do not need to save the time zone explicitly. We do not care where in the world it happens, we only need an absolute time to compare to.

然后，从今天开始就开始预订：

Then, to get reservations starting "today" simply:

SELECT *
FROM   reservations
WHERE  start_on::date = current_date;

但这是不可修改，因为 start_on :: date 是派生表达式，我们也无法为此建立函数索引（（肮脏的把戏），因为该表达式取决于当前时区，而不是 IMMUTABLE 。

But this is not sargable because start_on::date is a derived expression and we cannot build a functional index for this, either, (without dirty tricks) because the expression depends on the current time zone and is not IMMUTABLE.

代替，比较UTC时间中我们一天的开始和结束：

Instead, compare to the start and end of "our" day in UTC time:

SELECT *
FROM   reservations
WHERE  start_on >= current_date::timestamptz
AND    start_on < (current_date + 1)::timestamptz; -- exclude upper border

现在，此简单索引可以支持查询：

Now, this simple index can support the query:

CREATE INDEX ON reservations (start_on);

Demo

SQL Fiddle关闭了ATM 。以下是一个演示示例，有助于您理解：

Demo

SQL Fiddle is down ATM. Here is a little demo to help understanding:

CREATE TEMP TABLE reservations (
   reservation_id serial
 , start_on timestamptz NOT NULL
 , time_zone text);    -- we don't need this

INSERT INTO reservations (start_on, time_zone) VALUES
  ('2014-04-09 01:00+02', 'Europe/Vienna')
, ('2014-04-09 23:00+02', 'Europe/Vienna')
, ('2014-04-09 01:00+00', 'UTC')    -- the value is independent of the time zone
, ('2014-04-09 23:00+00', 'UTC')    -- only display depends on current time zone
, ('2014-04-09 01:00-07', 'America/Los_Angeles')
, ('2014-04-09 23:00-07', 'America/Los_Angeles');

SELECT start_on, time_zone 
     , start_on::timestamp             AS local_ts
     , start_on AT TIME ZONE time_zone AS ts_at_tz
     , current_date::timestamptz       AS lower_bound
     , (current_date + 1)::timestamptz AS upper_bound
FROM   reservations
WHERE  start_on >= current_date::timestamptz
AND    start_on < (current_date + 1)::timestamptz;

更多说明和链接在这里：

完全忽略Rails和PostgreSQL中的时区

More explanation and links here:
Ignoring timezones altogether in Rails and PostgreSQL

这篇关于优化/索引时区查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！