Search for a string in a text column (1 million rows+)

hi,
i have a table where a store data. there is also a text column where there is an average of 1000 chars in each entry.
this table has more that 1 million rows.

what i want, is to do a search to the text column for some text.
how can i do this to speed up things?
doing it with LIKE is not a solution (too much time)
is there some way to get some results in milliseconds?


question 2: count() on this table for all rows is also time-expensive.
keeping the count of all rows in another table and updating it with every insert and delete is a solution?
what would you suggest?

thanx in advance

Did you look into full text indexing?
Also, if you keep fresh table stats you should easily have an approximate record count for that table (here) .

See the Tsearch2-functions in PostgreSQL 8.3 (latest version) or install Tsearch2 yourself in an older version of PostgreSQL (only takes a minute).

i found tsearch2.. seems pretty good , ill give it a try..

for my 2nd question,
the problem is that i need a count(*) of a subset of rows, not all rows.. from 1.000.000 the matching may be up to 50.000..
how can i count these rows fast?

SELECT COUNT(*) FROM yourtable WHERE columnname = 'your value';

You do need an index on the columnname in the WHERE, otherwise it will slow down the query.