Hi,
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.
