Re: temporal variants of generate_series()

On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> Jim Nasby <decibel@decibel.org> writes:
>> Also, what would be the appropriate way to put this into initdb?
> You seem to have missed a step here, which is to convince people that
> these belong in core at all.  So far I've not even seen an argument  
> that
> would justify putting them in contrib.

These are all examples of using generate series plus additional math  
to generate a series of dates/timestamps:
http://archives.postgresql.org/pgsql-general/2007-01/msg01292.php
http://archives.postgresql.org/pgsql-sql/2006-02/msg00249.php
http://archives.postgresql.org/pgsql-general/2005-06/msg01254.php
http://archives.postgresql.org/pgsql-sql/2007-03/msg00093.php
http://archives.postgresql.org/pgsql-novice/2007-01/msg00002.php
http://archives.postgresql.org/pgsql-sql/2006-03/msg00391.php
http://archives.postgresql.org/pgsql-hackers/2006-09/msg00330.php

That's from the first page of search results for 'generate_series  
timestamp'.

FWIW, I could also make use of this in some of my code.

> If they *were* of sufficiently
> wide use to justify putting them into core, a more efficient
> implementation would probably be expected.

Ok, I'll look into a C version, but why do SQL functions have such a  
high overhead? I'm seeing an SQL function taking ~2.6x longer than  
the equivalent code run directly in a query. With 100 days, the  
difference drops a bit to ~2.4x. (this is on HEAD from a few months ago)

This is on my MacBook Pro with the Jean-Pierre's version of  
generate_series:

decibel=# select count(*) from generate_series(now(),now()+'10  
days'::interval,'1'::interval);
Time: 1851.407 ms
decibel=# select count(*) from generate_series(1,86400*10);
Time: 657.894 ms
decibel=# select count(*) from (select now() + (generate_series 
(1,86400*10) * '1 second'::interval)) a;
Time: 733.592 ms
decibel=# select count(*) from (select 'epoch'::timestamptz + s.i *  
'1 second'::interval AS "generate_series" from generate_series(extract 
('epoch' from now())::bigint, extract('epoch' from now()+'10  
days'::interval)::bigint, extract('epoch' from  
'1'::interval)::bigint) s(i)) a;
Time: 699.606 ms
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)