AFAIK, wildcards at both ends are not optimized at all, unless you use some sort of specialized index (may be Gist or FullText).
Until 9.1 there is no such "Index Scan" feature, that would help (very little).
Other databases (like MS SQL Server) solve this kind of query by executing an Index Scan, then merge join with rest of the query.
This is all I know about LIKE optimization in PostgreSQL:
LIKE 'str%' -> optimized by normal indexes
LIKE '%str%' -> not optimized. You can use FullText, but then your wildcards will have to change to something not SQL-standard compatible solution...
LIKE '%str' -> can be optimized if you create index with column content reversed then query reversed as well. See code below for details.
How did I optimized "%str" queries (code implemented with help from the PgSql community):
CREATE OR REPLACE FUNCTION reverse(input character varying)
RETURNS character varying AS
result character varying = '';
FOR i IN 1..length(input) BY 2 LOOP
result = substr(input,i+1,1) || substr(input,i,1) || result;
LANGUAGE plpgsql IMMUTABLE STRICT
create index idx on tb1 (reverse(nome));
select * from tb1 where reverse(nome) like reverse('%RICHTER');
Em 20/06/2012 14:10, Sam Z J escreveu:
[Postgresql Jobs] [Postgresql Admin] [Postgresql Performance] [Linux Clusters] [PHP Home] [PHP on Windows] [Programming PHP] [Kernel Newbies] [PHP Classes] [Find Someone Nice] [PHP Books] [PHP Databases] [Postgresql & PHP] [Yosemite]