Skip site navigation (1) Skip section navigation (2)

Re: Index on wildcard text search

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Leandro Fanzone <leandro(at)hasar(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Index on wildcard text search
Date: 2002-04-25 22:46:19
Message-ID: 9407.1019774779@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Leandro Fanzone <leandro(at)hasar(dot)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

In response to

pgsql-novice by date

Next:From: Aarni Ruuhimäki / Megative Tmi / KYMI.comDate: 2002-04-26 05:01:36
Subject: Fwd: Strange behaviour
Previous:From: Doug SilverDate: 2002-04-25 22:22:04
Subject: faster join access?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group