title: Postgresql generate date series (performance)
body: <p>Using postgresql version &gt; 10, I have come to an issue when generating date series using the built-in <code>generate_series</code> function. In essence, it does not accord for the <code>day of the month</code> correctly.</p>
  <p>I have many different frequencies (provided by the user) that need to be calculated between a given start and end date. The start date can be any date and thus any day of the month. This generates issues when having frequencies such as <code>monthly</code> combined with a start date of <code>2018-01-31</code> or <code>2018-01-30</code> as shown in the output below.</p>
  <p>I created a solution and wanted to post this here for others to use as I could not find any other solution.</p>
  <p>However, after some tests I have seen that my solution has a different  performance compared to the built-in <code>generate_series</code> when used on (absurdly) large date ranges. Does anyone have an insight as to how this can be improved?</p>
  <p><strong>TL;DR</strong>: if possible avoid loops as they are a performance hit, scroll to bottom for improved implementation.</p>
  <h2>Built-in Output</h2>
  <pre><code>select generate_series(date '2018-01-31', 
                         date '2018-05-31', 
                         interval '1 month')::date
  as frequency;
  </code></pre>
  <p>generates:</p>
  <pre><code> frequency
  ------------
   2018-01-31
   2018-02-28
   2018-03-28
   2018-04-28
   2018-05-28
  </code></pre>
  <p>As can be seen from the output, the day of the month is not respected and truncated to the minimum day encountered along the way, in this case: <code>28 due to the month of februari</code>.</p>
  <h2>Expected Output</h2>
  <p>As a result of this issue I created a custom function:</p>
  <pre><code>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 &lt;= 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;
  </code></pre>
  <p>generates:</p>
  <pre><code> frequency
  ------------
   2018-01-31
   2018-02-28
   2018-03-31
   2018-04-30
   2018-05-31
  </code></pre>
  <h2>Performance comparison</h2>
  <p>No matter what date range is provided, the built-in <code>generate_series</code> has a performance of <strong>2ms on average</strong> for:</p>
  <pre><code>select generate_series(date '1900-01-01', 
                         date '10000-5-31', 
                         interval '1 month')::date 
  as frequency;
  </code></pre>
  <p>while the custom function <code>generate_date_series</code> has a performance of <strong>120ms on average</strong> for:</p>
  <pre><code>select generate_date_series(date '1900-01-01', 
                              date '10000-5-31', 
                              interval '1 month')::date 
  as frequency;
  </code></pre>
  <h2>Question</h2>
  <p>In reality, such ranges will never occur and thus it is a non-issue. For most queries the custom <code>generate_date_series</code> will attain the same performance. Although, I do wonder what causes the difference.</p>
  <p>Is there a reason why the built-in function is able to attain a constant performance of <strong>2ms on average</strong> no matter what range is provided?</p>
  <p>Is there a better way to implement <code>generate_date_series</code> that performs as well as the built-in <code>generate_series</code>?</p>
  <h2>Improved implementation without loops</h2>
  <p>(derived from the answer of @eurotrash)</p>
  <pre><code>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;
  </code></pre>
  <p>with the improved implementation, the <code>generate_date_series</code> function has a performance of <strong>45ms on average</strong> for:</p>
  <pre><code>select generate_date_series(date '1900-01-01', 
                              date '10000-5-31', 
                              interval '1 month')::date 
  as frequency;
  </code></pre>
  <p>The implementation provided by @eurotrash gives me <strong>80ms on average</strong>, which I assume is due to calling the <code>generate_series</code> function twice.</p>