Re: Special index for "like"-based query

On Fri, Dec 30, 2016 at 11:00 AM, Job <Job(at)colliniconsulting(dot)it> wrote:
> I tried to create a GIST/GIN index on a timestamp without time zone field
> but it does not work.
> Are there alternatives index types or timezone could speed query up?

Remember a timestamp is just a real number ( a point on the time line
) with some fancy formatting for I/O ( or you will suffer ). This
menas when you have a ts column and want to query for a date it is
usually better to do [ts>='2016-12-29' and ts<'2016-12-13'] than doing
[cast(ts as date) = '2016-12-29'] ( similar to how a real number is
better queried as [r>=1.0 and r<2.0] than [int(r)=1] ). Normally you
get good results with btree indexes.

And, basically, if you need help with some queries you could try
posting them whole, even redacted, along the table defs, this way
perople can see the problem and not invent one based on a partial
description. I do not see any thing in common between 'like based
query' and timestmap columns.

Francisco Olarte.