Tom Lane wrote, On 12/30/2003 11:06 PM:
BTW, 7.4 also has a specialized index opclass that can be used to create LIKE-compatible indexes even if you are using a non-C locale.
I do some tests: PostgreSQL 7.4.1 on a varchar(255) column: #CREATE INDEX crossref_oidx ON crossref (cr_origmark varchar_pattern_ops); #ANALYZE crossref;
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark ilike 'gie%' Seq Scan on crossref ...
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark like 'GIE%'; Index Scan using crossref_oidx on crossref ...
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark ilike 'GIE%'; Seq Scan on crossref ...
#EXPLAIN ANALYZE SELECT * from crossref where cr_origmark like upper('gie%');
Index Scan using crossref_oidx on crossref ...
In case of ilike, index is not used :(
If like used the condition is:
Index Cond: (((cr_origmark)::text ~>=~ 'GIE'::character varying) AND ((cr_origmark)::text ~<~ 'GIF'::character varying))
However ilike use the old way: Filter: ((cr_origmark)::text ~~* 'GIE%'::text)
is this bug, or this is the right way?
C.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
