Re: Mechanics of Select

On Feb 11, 2008 3:56 AM, Alban Hertroys
<dalroi@solfertje.student.utwente.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.