From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Alban Hertroys" <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
Cc: | "Willem Buitendyk" <willem(at)pcfish(dot)ca>, "Greg Smith" <gsmith(at)gregsmith(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Mechanics of Select |
Date: | 2008-02-11 23:21:53 |
Message-ID: | dcc563d10802111521t5a6f35d8s6b86c570289f9040@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Feb 11, 2008 3:56 AM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> On Feb 11, 2008, at 2:23 AM, Willem Buitendyk wrote:
>
> > As others have suggested my big problem with the function I wrote
> > was that I had made it Volatile instead of Immutable (it is no
> > doubt suffering from code bloat as well). That made all the
> > difference. Curiously though - I tried it just with the date_trunc
> > function and it was just as slow as my old Volatile function.
> >
> > select * from track where datetime >= '2007-04-01' and datetime <
> > date_trunc('month','2007-04-01'::timestamp)+interval '1 month'; was
> > about 55s
>
> That's probably because '2007-04-01'::timestamp can be at different
> time zones depending on client configuration and hence is volatile.
>
> If you need a timestamp you probably want to use the servers TZ,
> which you can specify using: timestamp at <your timezone>
No, straight up timestamps shouldn't have this problem, only timestamptz.
I'd suggest trying an index on the date_trunc function here and see if
that helped.
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2008-02-11 23:27:37 | Re: Conditional ordering operators |
Previous Message | Tom Lane | 2008-02-11 22:14:19 | Re: SPI_ERROR_CONNECT |