Thread: full text index

Hi,

first I want to apologize, because I belive this to
be a FAQ. But I didn't find anything on www.postgresql.org
and I couldn't search the list archives (the search
never returned anything, not even "nothing found").

I have this table:

create table keywords (
kw     varchar(128) not null,
hits   integer not null,
primary key(kw)
)

I want to do this kind of select:

SELECT kw,hits from keywords where kw like'%xyz%';

What is the most efficient way to do it? The table
will have around 3 to 4 million rows. So a full
table scan is out of question. I need the answer in
around one second. (It's a web application)

Any ideas? Where could I read more about that?
Please advise me on finding my way around if I missed
some RTFM.

Thanks

Ulrich

> SELECT kw,hits from keywords where kw like'%xyz%';

Using regular expressions in this case is much faster than LIKE, see

http://www.postgresql.org/idocs/index.php?functions-matching.html


> What is the most efficient way to do it? The table
> will have around 3 to 4 million rows. So a full
> table scan is out of question. I need the answer in
> around one second. (It's a web application)

This will still need to do that, if they're keywords you should be doing an
exact match on them, else don't maintain an index that won't be used.

You may be interested in full text searching which can be found in
/contrib/fulltextindex (IIRC) or http://openfts.sourceforge.net .