Re: looking for a faster way to do that

At 09:45 23/09/2011, hamann(dot)w(at)t-online(dot)de wrote:

>A single anchored query
>select * from items where code ~ '^ABC';
>does indeed use the index to retrieve data.
>
>
>So I wonder whether there might be a different approach to this
>problem rather than
>pattern matching.
>I recall I had a similar problem before with a "contacts" column
>possibly containing one or more
>email addresses. Here searches would also be number of people times
>number of requests
>performance. I finally ended up with a @@ match (contrib/tsquery)
>and a supporting GIST index,
>but that only supports exact match, not prefix

You can try these, i doubt they will use any index but its a
different approach:

select * from items where length(items.code)<>length(rtrim(items.code,'ABC'));

select * from items where strpos(items.code,'ABC')=0 or
strpos(items.code,'any_substring')=0;

HTH