>Sorry, I made too general a statement. Index will help when the
>expession is anchored to beginning of field. So 'A%' gets help from
>index, but '%A%' does not. Frank At 11:13 AM 1/11/02 -0500, Nick
>Fankhauser wrote:
This is of great interest to me because this is exactly what I am trying
to do: use indices to speed up anchored searches.
What you say mirrors what the faq says. However, I just can't get it to
work. I created a new database and a scratch table and inserted some
random values. After the creation of an index and turning seqscan off, I
got indexed lookups for exact searches but not for likes. What am I
doing wrong?
create table waa(a text, b text);
insert into waa values('sdf','dsafasf');
insert into waa values('sdsff','dsafasfsf');
insert into waa values('sffdsff','dsafasfssf');
insert into waa values('sfsdffdsff','dsafassdfffssf');
insert into waa values('sfsdffasfsafdsff','dsafassdfffssfaf');
insert into waa values('df','dsafasf');
insert into waa values('dsff','dsafasfsf');
insert into waa values('ffdsff','dsafasfssf');
insert into waa values('fsdffdsff','dsafassdfffssf');
insert into waa values('fsdffasfsafdsff','dsafassdfffssfaf');
insert into waa values('f','dsafas');
insert into waa values('sff','dsafsfsf');
insert into waa values('fdsff','dsfasfssf');
insert into waa values('sdffdsff','dsafassdfffssf');
insert into waa values('sdffasfsaf','dsafassdfffssfaf');
create index i_waa on waa(a);
set enable_seqscan to off;
explain select * from waa where a = 'f';
>--NOTICE: QUERY PLAN:
>--Index Scan using i_waa on waa (cost=0.00..2.01 rows=1 width=24)
explain select * from waa where a like 'f%';
>--NOTICE: QUERY PLAN:
>--Seq Scan on waa (cost=100000000.00..100000001.19 rows=1 width=24)