Re: optimizing LIKE '%2345' queries - Mailing list pgsql-performance

From Tarhon-Onu Victor
Subject Re: optimizing LIKE '%2345' queries
Date
Msg-id Pine.LNX.4.64.0607041408450.20387@oynpxoyhr.vnfv.npgvirzrqvngrpu.pbz
Whole thread Raw
In response to optimizing LIKE '%2345' queries  (Gene <genekhart@gmail.com>)
Responses Re: optimizing LIKE '%2345' queries  (Gene <genekhart@gmail.com>)
List pgsql-performance
On Sun, 2 Jul 2006, Gene wrote:

> can use an index and perform as fast as searching with like '2345%'?
>
> Is the only way to create a reverse function and create an index using
> the reverse function and modify queries to use:
>
> where reverse(column) like reverse('%2345') ?

     Hmm.. interesting.
     If (and only if) the records stored in "column" column have fixed
length (say, all are 50 characters in length) you could create and index
on, say, substring(column,45,50), and use this in the WHERE clauses in
your queries.
     Or if the length of those records is not the same maybe it is
feasible to create an ondex on substring(column, length(column)-5,
length(column)).

--
Any views or opinions presented within this e-mail are solely those of
the author and do not necessarily represent those of any company, unless
otherwise expressly stated.

pgsql-performance by date:

Previous
From: "Mikael Carneholm"
Date:
Subject: Re: Is postgresql ca do the job for software deployed in ASP ou SaaS mode?
Next
From: David Gagnon
Date:
Subject: Re: Is postgresql ca do the job for software deployed in ASP