Re: Major upgrade advice - Mailing list pgsql-admin

Στις Friday 20 June 2008 16:26:19 ο/η Roberto Garcia έγραψε:
> We have an index on the time_stamp column, if the format of argument is
> different from the format the index was created it is not used, then
> performance is decreased because a sequential scan is done instead of an
> index scan.
Then create an additional index like
CREATE INDEX tablename_tscol_date on tablename (date(tscol));
where tscol is timestamp

>
> Achilleas Mantzios wrote:
> > Why not simply,
> > SELECT * FROM xxx WHERE <timestamp_column>::date = '2008-05-20'::date;
> >
> > Στις Thursday 19 June 2008 21:56:09 ο/η Roberto Garcia έγραψε:
> >
> >> We changed it because 8.3 doesn't allow the operator LIKE on timestamp
> >> columns. Your syntax works fine but we weren't used to use as u do.
> >> There weren't any specific reason, only another way to do that.
> >>
> >> I think when we read that operator LIKE and timestamp values were
> >> incompatible we assumed that timestamp values couldn't be compared to
> >> any char value, opposed as your syntax is.
> >>
> >> We've tried to do "select * from X where <timestamp column> =
> >> '2008-05-20 10:'", expecting that the result would be any minute from 10
> >> o'clock (10:15, 10:30, 10:45, etc) of the specific date, but this syntax
> >> retrieves only 10:00 from that date.
> >>
> >> Tks for the new syntax.
> >>
> >> Regards
> >> Roberto Garcia
> >>
> >> Gregory S. Youngblood wrote:
> >>
> >>> That's a pretty substantial change.  Why did you have to make this change?
> >>> Was it causing syntax errors or to get better performance on those types of
> >>> queries? Actually, now that I think about it, didn't:
> >>> select * from X where <timestamp column> between '2008-05-20 00:00:00' and
> >>> '2008-05-20 23:59:59'
> >>> work? I could have sworn I have used that syntax in 8.2 without having to
> >>> arbitrarily cast the arguments... now I'm going to have to go look. :)
> >>>
> >>> Yup, confirmed, 8.2.7 and no casting on the date arguments when I was using
> >>> between.
> >>>
> >>> I'm just curious if there was a specific reason (i.e. better performance,
> >>> better use of indexes, etc.) for your syntax.
> >>>
> >>> Thanks,
> >>> Greg
> >>>
> >>> -----Original Message-----
> >>> From: pgsql-admin-owner@postgresql.org
> >>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Roberto Garcia
> >>> Sent: Wednesday, June 18, 2008 12:01 PM
> >>> Cc: pgsql-admin@postgresql.org
> >>> Subject: Re: [ADMIN] Major upgrade advice
> >>>
> >>> Just to mention one issue we had here:
> >>>
> >>> In 8.1 we did this to retrieve all data from a specific date:
> >>> SELECT * FROM xxx
> >>> WHERE <timestamp_column> LIKE '2008-05-20%'
> >>>
> >>> In 8.3 we had to change to:
> >>> SELECT * FROM xxx
> >>> WHERE <timestamp_column> >= CAST('2008-05-20' as timestamp) AND
> >>>       <timestamp_column> < CAST('2008-05-21' as timestamp)
> >>>
> >>> Regards
> >>> Roberto Garcia
> >>>
> >>>
> >>>
> >>>
> >> Roberto Garcia
> >> Banco de Dados, MSc
> >> Fone: (12) 3186-8405
> >> --
> >> A luta contra o aquecimento global depende de cada um de nós, faça sua parte, economize recursos naturais.
> >> --
> >> http://www.cptec.inpe.br
> >> http://www.inpe.br
> >>
> >>
> >>
> >
> >
> >
> >
>
>
> Roberto Garcia
> Banco de Dados, MSc
> Fone: (12) 3186-8405
> --
> A luta contra o aquecimento global depende de cada um de nós, faça sua parte, economize recursos naturais.



--
Achilleas Mantzios