in Postgresql how to speed up substring queries

Question

I have a simple logs table with about 500,000 rows, table structure is

 TABLE logs
(
  id serial NOT NULL,
  username character varying(32),
  user_id integer,
  description text NOT NULL,
  "time" timestamp with time zone DEFAULT now(),
  referrer character varying(128),
  "type" character varying(25)
)

The most common operation in terms of querying this table for contents of the description column. Typical query is...

SELECT username , time , description FROM logs WHERE description ~* 'some text'  ORDER by time DESC

This takes anywhere from 8 ~ 20 seconds, to bring back results. Is there any other method to optimize the table or query that would produce faster results. I'm on 8.2 Portgresql .

Answer 1

You could create a column (boolean) which gets set on insert and update via. a trigger, this would then be searchable, but the search terms would always be the same.

Have you considered using Full-text search? You'll have to install the FTS module manually in your version of postgres.

Answer 2

First of all, you should upgrade to 8.4 to get all new performance benefits. 8.4 has integrated tsearch for full text searching. If you can't upgrade, install Tsearch2 from contrib.

For further optimization, I'd create an index for time and use it to limit results. E.g.

SELECT username , time , description FROM logs WHERE 
    to_tsvector('english', description) @@ to_tsquery('english', 'some text')
    AND time > current_timestamp - INTERVAL '1 day'
    ORDER by time DESC

See the linked documentation for further information. You'll need to create an index for to_tsvector or the full text search is quite useless.

Edit: If your table contains millions of tuples of data, consider recreating the whole table using partitioning available in 8.2. For raw speed improvement, switch to SSD disks. See this article for demonstration of speed increase.