> I don't beleive indexes will improve SELECT using LIKE.
I wondered about that too, so I did a test using a database I'm working
with. The results indicate that it helps substantially- here is my terminal
log file:
staging=#
staging=#
staging=# explain select count(*) from actor where actor_full_name like
'A%';
NOTICE: QUERY PLAN:
Aggregate (cost=1067.72..1067.72 rows=1 width=0) -> Seq Scan on actor (cost=0.00..1066.29 rows=572 width=0)
EXPLAIN
staging=# create index test on actor(actor_full_name);
CREATE
staging=# explain select count(*) from actor where actor_full_name like
'A%';
NOTICE: QUERY PLAN:
Aggregate (cost=577.46..577.46 rows=1 width=0) -> Index Scan using test on actor (cost=0.00..576.03 rows=572
width=0)
EXPLAIN
staging=#
-Nick
--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/