优化:亚马逊的Redshift功能可以检查夏令时是否有效

Question

我已经编写了这个连接到dateutil.tz的函数，请参阅下面的代码：

CREATE OR REPLACE FUNCTION schema_name.fnc_name(ts timestamp without time zone, timezone character varying)
RETURNS boolean STABLE AS $$
  from datetime import datetime
  from dateutil.tz import gettz
  tstz = ts.replace(tzinfo=gettz(timezone))
  is_dst = datetime.timetuple(tstz).tm_isdst
  return is_dst
$$ LANGUAGE plpythonu;

这个函数很慢，我需要在一个执行周期中调用它超过十亿行。

我对红移和时区的东西真的很陌生。有人能帮我优化一下吗？欢迎提出任何性能改进建议，如：

1. 以某种方式将时区详细信息移动到本地数据库？（告诉我怎么做）
2. 不要使用Python，使用其他东西（告诉我）

Answer 1

使用IMMUTABLE而不是STABLE，因为给定输入值，返回值将始终相同。来自文件：

STABLE:给定相同的参数，函数保证为单个语句中处理的所有行返回相同的结果函数在不同的语句中调用时可以返回不同的结果此类别允许优化器将单个语句中对函数的多次调用优化为对该语句的一次调用。

不可更改：给定相同的参数，函数始终返回相同的结果，永远。当查询使用常量参数调用IMMUTABLE函数时，优化器会对该函数进行预求值。

此外，要使Redshift能够缓存结果，传入DATE而不是TIMESTAMP。这将减少所使用的输入值的数量，以便它们更有可能使用以前计算（和缓存）的值。

Answer 2

^{注意：这个答案是关于PostgreSQL的。大多数解决方案也应该能够应用于红移，因为它基于旧版本的PostgreSQL。但是，您可能需要搜索该解决方案的某些部分的替代方案，因为我无法在红移上测试它（例如，使用CONVERT_TIMEZONE(tz, ts)函数而不是ts AT TIME ZONE tz表达式）}

首先，您需要了解时区有多种"类型"。F.ex.Europe/London是一个时区名称，数据库有关于其夏令时规则的信息。然而，时区偏移（例如UTC、UTC+2或任何时间间隔）是静态的，并且永远不会被认为是夏令时（在python中也不是）。还有一些时区缩写，它们只是时区偏移的别名，但它们的DST变体有一个备用名称（例如，夏令时中的CET是CEST），因此它们永远不会（或总是）被认为是夏令时（请注意，PostgreSQL接受（并调整）虚假的日期-时间输入，比如2016-01-12 10:00 CEST，它实际上是2016-01-12 09:00 CET）。此外，还有POSIX风格的时区，如EST5EDT，它们可以有自己的夏令时规则。

对于纯SQL检测，您需要查询pg_timezone_abbrevs和pg_timezone_names系统视图：

create or replace function tstz_isdst(ts timestamp without time zone, tz text)
  returns boolean
  immutable
  language sql
as $func$
  with tz_info as (
      select utc_offset, true fix_dst, is_dst
      from   pg_timezone_abbrevs
      where  lower(abbrev) = lower(tz)
    union all
      select utc_offset, false, is_dst
      from   pg_timezone_names
      where  lower(name) = lower(tz)
    union all
      select -coalesce(substring(tz from '([+-]?d+(:d+){1,2}(.d+)?)')::interval,
                       substring(tz from '[+-]?d+')::integer * interval '1 hour'),
             false, false
  )
  select case
           when fix_dst then is_dst
           when ts = (ts at time zone tz at time zone 'UTC' + utc_offset) then is_dst
           else not is_dst
         end
  from   tz_info
  limit  1
$func$;
select tstz_isdst('2016-01-12 10:00', 'GMT'),
       tstz_isdst('2016-04-12 10:00', 'BST'),
       tstz_isdst('2016-03-27 01:30', 'GMT0BST'), -- not exists
       tstz_isdst('2016-10-30 01:30', 'Europe/London'); -- ambiguous

请注意，对于不存在或不明确的日期-时间+时区组合（当前正在进行夏令时更改时），此函数将返回false。

但这个函数可能仍然不是你所需要的，因为在PostgreSQL中，pg_timezone_names视图的计算速度非常慢（在我的测试中是300-600毫秒），所以查询表中的每一行可能不是最佳的。但您可以使用联接：

select t.ts, t.tz, case
         when tz_abbr.is_dst is not null
           then tz_abbr.is_dst
         when tz_name.utc_offset is not null
           then case
             when t.ts = (t.ts at time zone t.tz at time zone 'UTC' + tz_name.utc_offset)
               then tz_name.is_dst
             else not tz_name.is_dst
           end
         else t.ts <> (t.ts at time zone t.tz at time zone 'UTC' -
           coalesce(substring(t.tz from '([+-]?d+(:d+){1,2}(.d+)?)')::interval,
                    substring(t.tz from '[+-]?d+')::integer * interval '1 hour'))
       end is_dst
from   (values(timestamp '2016-01-12 10:00', 'GMT'),
              (timestamp '2016-04-12 10:00', 'BST'),
              (timestamp '2016-03-27 01:30', 'GMT0BST'),
              (timestamp '2016-10-30 01:30', 'Europe/London')) t(ts, tz)
left join pg_timezone_abbrevs tz_abbr on lower(tz_abbr.abbrev) = lower(t.tz)
left join pg_timezone_names   tz_name on lower(tz_name.name)   = lower(t.tz);

Answer 3

查看CONVERT_TIMEZONE函数