From: | Jim Nasby <decibel(at)decibel(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | JEAN-PIERRE PELLETIER <pelletier_32(at)sympatico(dot)ca>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-05-07 13:05:33 |
Message-ID: | 8C069465-D4C1-43EE-9E30-A688B79537AD@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> Jim Nasby <decibel(at)decibel(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2007-05-07 13:08:24 | Re: plperl vs. bytea |
Previous Message | Andrew Dunstan | 2007-05-07 13:02:55 | Re: plperl vs. bytea |