postgresql - TIMESTAMPTZ 索引和函数不变性

Question

我们有一个类似于下面的结构:

create table company
(
    id bigint not null,
    tz text not null
);

create table company_data
(
    company_id bigint not null,
    ts_tz timestamp with time zone not null
);

表格被简化了。

在此处摆弄示例数据:SQL Fiddle

每个公司都有一个固定的 TZ。因此，当我们需要从 company_data 中提取一些信息时，我们使用类似于以下的查询:

select
       cd.company_id,
       cd.ts_tz at time zone c.tz
from company_data cd
join company c on c.id = cd.company_id;

我们还有一个获取公司tz的函数:

create or replace function tz_company(f_company_id bigint) returns text
    language plpgsql
as
$$
declare
    f_tz text;
begin
    select c.tz from company c where c.id = f_company_id into f_tz;
    return f_tz;
end;
$$;

另一个在日期中应用 tz 转换 ts:

create or replace function tz_date(timestamp with time zone, text) returns date
    language plpgsql
    immutable strict
as
$$
begin
    return ($1 at time zone $2) :: date;
end;
$$;

我们现在遇到的问题是 company_data(和其他类似的表)是一个很大且经常使用的表。该表中的大多数 SELECTs 使用 DATE 执行过滤。

例如:

select cd.company_id,
       cd.ts_tz at time zone tz_company(cd.company_id)
from company_data cd
where tz_date(cd.ts_tz, tz_company(cd.company_id)) >= '2019-08-20'
  and tz_date(cd.ts_tz, tz_company(cd.company_id)) <= '2019-08-22';

因此，为了加快查询速度，我们需要在company_data.ts_tz 列中添加一个索引。我们发现执行此操作的唯一方法如下:

create index idx_company_data_ts_tz on company_data
    (((company_data.ts_tz at time zone tz_company(company_data.company_id))::date));

为此，我们需要使 tz_company 函数不可变。

出现了一些其他问题(和想法):

1 - 使用 tz_date 函数的查询版本不使用索引。

不使用索引:

explain analyse
select cd.company_id,
       cd.ts_tz at time zone tz_company(cd.company_id)
from company_data cd
where tz_date(cd.ts_tz, tz_company(cd.company_id)) >= '2019-08-20'
  and tz_date(cd.ts_tz, tz_company(cd.company_id)) <= '2019-08-22';

使用索引:

explain analyse
select cd.company_id,
       cd.ts_tz at time zone tz_company(cd.company_id)
from company_data cd
where (cd.ts_tz at time zone tz_company(cd.company_id))::date >= '2019-08-20'
  and (cd.ts_tz at time zone tz_company(cd.company_id))::date <= '2019-08-22';

为什么会这样？

2 - 我们知道，理论上，tz_company 不应该是 immutable，至多是稳定的。但是，公司 tz 是一个永远不应更改的信息。是的，它可能会发生，但不太可能。在过去的三年里，我们从未更改过任何一家公司的tz。那么，tz_company immutable 仍然是一个问题吗？如果是，我们如何重写索引？请注意，单个 SELECT 可能会带来多个公司的信息并混合不同的时区。

3 - 由于处理 timestamptz 列中索引的复杂性，我们考虑在每个具有 ts_tz 的表中添加另一列。这个新列将是一个已经应用了 tz 的日期。这是一个好方法吗？

此外，我们需要在转换之前应用 tz，因为每个客户(公司)只选择要过滤的日期，并且此日期是区域设置感知的(tz 感知的)。

编辑 1:

所使用的查询仅用于演示。但要求是客户端看到事件发生时区的时间戳，这是一个重要的要求。我们处理巴西的物流业务，巴西本身在全国有四个不同的时区。一家控股公司可以拥有不同的公司，每家公司都可以位于不同的时区。

因此，很多查询涉及不同时区的不同公司，并应用了一些日期过滤。今天，我们的后端返回所有准备好显示的数据，并应用时区，这很难改变。

我们想要实现的是一种处理这些 timestamptz 列的简单且高效的方法:按日期应用过滤器(tz 感知)并使用索引来加速查询。

Answer 1

1 - 那是因为 tz_date 没有被标记为不可变的。如果 postgres 允许在与函数主体中相同的表达式上创建索引，则将其标记为不可变是安全的——它只允许在不可变表达式上执行此操作。一些 postgres 日期时间操作函数和类型转换是不可变的，有些则不是。顺便说一句，我不确定如果 at time zone 运算符(operator)在更改 tzdata 时违反其不变性契约(Contract)，索引会发生什么情况——这在 postgres 或操作系统升级时经常发生，具体取决于设置。

2 - 这是一种非常危险的方法，如果您更改数据，索引就会损坏。您可能会丢失数据。如果您确实需要这个伪不可变函数，我强烈建议添加一个触发器，禁止删除、截断和更新 company.tz。如果您需要更改时区数据，请先删除索引。

3 - 关键问题是您是否碰巧查询了多家公司的数据？

a) 如果你这样做，那只是数字意义上的。来自纽埃 (UTC-11) 的 2011-09-13 事件和来自新西兰 (UTC+13) 的 2019-09-13 事件永远不会同时发生。这些事件的唯一共同点是它们发生在 13 日星期五。这只是表示法，它从来没有在这两个国家/地区同时出现过 2019-09-13。因此，请确保您的查询确实有意义。在这种不太可能的情况下，将日期表示法非规范化为单独的 timestamp without time zone 列是有意义的，因为您是按时间表示法而不是按时间过滤的。我会推荐一个触发器来填充它。

b) 您的所有查询都是单一公司的。在这种情况下，我会在没有表达式的列上创建一个普通索引，然后创建一个函数并进行如下查询:

create index on company_data(company_id, ts_tz);

create function midnight_at_company(p_date date, p_company_id bigint) strict returns timestamp with time zone as $$
select p_date::timestamp at time zone tz from company where id = p_company_id;
$$ language sql;

-- put your company id instead of $1
explain analyse
select cd.company_id,
       cd.ts_tz at time zone tz_company(cd.company_id)
from company_data cd
where company_id = $1
  and cd.ts_tz >= midnight_at_company('2019-08-20', $1)
  and cd.ts_tz < midnight_at_company('2019-08-23', $1); --note exact `<`, not `<=`