Thread: Index on wildcard text search

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? I
mean, having a btree index to search with, "begins with" sounds easier,
but the LOWER operation probably invalidates its use. If this has no
sense, do you have another suggestion for the case?
Thank you,

Leandro Fanzone
Compañía HASAR

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