> 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 .