From: | CoL <col(at)mportal(dot)hu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: 'like' refuses to use an index??? |
Date: | 2003-12-31 10:05:42 |
Message-ID: | bsu6r6$1bir$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Arjen van der Meijden | 2003-12-31 10:40:20 | Re: website doc search is extremely SLOW |
Previous Message | Bret Busby | 2003-12-31 09:24:02 | Re: Is my MySQL Gaining ? |