Re: Mechanics of Select
От | Alban Hertroys |
---|---|
Тема | Re: Mechanics of Select |
Дата | |
Msg-id | 18DD2679-841B-480B-AE49-CF0DE8352CDE@solfertje.student.utwente.nl обсуждение исходный текст |
Ответ на | Re: Mechanics of Select (Willem Buitendyk <willem@pcfish.ca>) |
Ответы |
Re: Mechanics of Select
|
Список | pgsql-general |
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> Are you always entering the first day of a month for start date? In that case you can leave out the entire date_trunc as the interval already calculates the correct length internally: template1=> select '2007-04-01'::date + interval '1 month'; ?column? --------------------- 2007-05-01 00:00:00 > select * from track where datetime >= '2007-04-01' and datetime < > first_day_next_month('2007-04-01'); was about 36s Also, specifying dates like this without specifying their format is a bad habit in my book. You're in trouble as soon as the date format for your database changes (different system, for example). I suggest you use to_date('2007-04-01', 'YYYY-MM-DD') instead. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47b015f9167323996417255!
В списке pgsql-general по дате отправления: