sql - Postgresql 生成日期系列(性能)

Question

使用 postgresql 版本 > 10，我在使用内置 generate_series 函数生成日期系列时遇到了问题。本质上，它没有正确地对应于 一个月中的第几天。

我有许多不同的频率(由用户提供)需要在给定的开始日期和结束日期之间进行计算。开始日期可以是任何日期，因此可以是一个月中的任何一天。当频率如 monthly 与 2018-01-31 或 2018-01-30 的开始日期相结合时，这会产生问题，如中所示下面的输出。

我创建了一个解决方案并想将其发布在这里供其他人使用，因为我找不到任何其他解决方案。

但是，经过一些测试后，我发现我的解决方案与内置的 generate_series 相比在(荒谬的)大日期范围内使用时具有不同的性能。有没有人知道如何改进这一点？

TL;DR:如果可能，请避免循环，因为它们会影响性能，滚动到底部以改进实现。

内置输出

select generate_series(date '2018-01-31', 
                       date '2018-05-31', 
                       interval '1 month')::date
as frequency;

生成:

 frequency
------------
 2018-01-31
 2018-02-28
 2018-03-28
 2018-04-28
 2018-05-28

从输出中可以看出，月中的第几天未被遵守并被截断为沿途遇到的最小日期，在这种情况下:28 由于 februari 月份。

预期输出

由于这个问题，我创建了一个自定义函数:

create or replace function generate_date_series(
  starts_on date, 
  ends_on date, 
  frequency interval)
returns setof date as $$
declare
  interval_on date := starts_on;
  count int := 1;
begin
  while interval_on <= ends_on loop
    return next interval_on;
    interval_on := starts_on + (count * frequency);
    count := count + 1;
  end loop;
  return;
end;
$$ language plpgsql immutable;

select generate_date_series(date '2018-01-31', 
                            date '2018-05-31', 
                            interval '1 month')
as frequency;

生成:

 frequency
------------
 2018-01-31
 2018-02-28
 2018-03-31
 2018-04-30
 2018-05-31

性能比较

无论提供什么日期范围，内置的generate_series 的平均性能为2ms:

select generate_series(date '1900-01-01', 
                       date '10000-5-31', 
                       interval '1 month')::date 
as frequency;

而自定义函数 generate_date_series 的平均性能为 120 毫秒:

select generate_date_series(date '1900-01-01', 
                            date '10000-5-31', 
                            interval '1 month')::date 
as frequency;

问题

实际上，这样的范围永远不会出现，因此这不是问题。对于大多数查询，自定义 generate_date_series 将获得相同的性能。虽然，我确实想知道是什么导致了这种差异。

无论提供什么范围，内置函数都能达到平均 2ms 的恒定性能，这是有原因的吗？

是否有更好的方法来实现性能与内置 generate_series 一样好的 generate_date_series？

改进了无循环的实现

(源自@eurotrash 的回答)

create or replace function generate_date_series(
  starts_on date, 
  ends_on date, 
  frequency interval)
returns setof date as $$
select (starts_on + (frequency * count))::date
from (
  select (row_number() over ()) - 1 as count
  from generate_series(starts_on, ends_on, frequency)
) series
$$ language sql immutable;

通过改进实现，generate_date_series 函数的平均性能为 45 毫秒:

select generate_date_series(date '1900-01-01', 
                            date '10000-5-31', 
                            interval '1 month')::date 
as frequency;

@eurotrash 提供的实现平均给了我 80 毫秒，我认为这是由于两次调用 generate_series 函数所致。

Answer 1

为什么你的函数很慢:你使用了变量和(更重要的)循环。循环很慢。变量也意味着读取和写入这些变量。

CREATE OR REPLACE FUNCTION generate_date_series_2(starts_on DATE, ends_on DATE, frequency INTERVAL)
        RETURNS SETOF DATE AS
$BODY$
        SELECT (starts_on + (frequency * g))::DATE
        FROM generate_series(0, (SELECT COUNT(*)::INTEGER - 1 FROM generate_series(starts_on, ends_on, frequency))) g;
$BODY$
        LANGUAGE SQL IMMUTABLE;

这个概念与您的 plpgsql 函数基本相同，但通过单个查询而不是循环。唯一的问题是决定需要多少次迭代(即 generate_series 的第二个参数)。遗憾的是，除了为日期调用 generate_series 并使用它的计数之外，我想不出更好的方法来获取所需的间隔数。当然，如果您知道您的间隔只会是某些值，那么就有可能进行优化；然而这个版本处理任何间隔值。

在我的系统上，它比纯 generate_series 慢了大约 50%，比你的 plpgsql 版本快了大约 400%。