At 09:45 23/09/2011, hamann.w@xxxxxxxxxxx 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
