Re: Index on wildcard text search

Leandro Fanzone <leandro@hasar.com> writes:
> Excuse me if this is a silly question. Say I have a table with a text
> field, where I perform search operations always in the following form:
> SELECT * from table where LOWER(textfield) like 'X%';
> Where "X" is one or more letters ("case insensitive begins with").
> Does help in some way if I create a binary tree index on that field?

Sure.

regression=# create table foo (f1 text);
CREATE
regression=# create index fooi on foo(lower(f1));
CREATE
regression=# explain SELECT * from foo  where LOWER(f1) like 'X%';
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using fooi on foo  (cost=0.00..17.08 rows=5 width=32)
   Index Cond: ((lower(f1) >= 'X'::text) AND (lower(f1) < 'Y'::text))
   Filter: (lower(f1) ~~ 'X%'::text)
(3 rows)

I'm using current development sources for this, which have a nicer
EXPLAIN display format so you can see what's going on more readily.
But the same plan will be generated by 7.2 and probably 7.1; don't
recall about older versions.

Note you must be running in 'C' locale or the index trick doesn't
work at all --- most non-C locales have funny sorting rules that
destroy the usefulness of an index for prefix matching.

            regards, tom lane