Re: LIKE search and performance

From: Guido Neitzer <lists(at)event-s(dot)net>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: LIKE search and performance
Date: 2007-05-23 16:00:18
Message-ID: 532C0F0C-117B-45A1-9CA2-66CA9ECA711F@event-s.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Am 23.05.2007 um 09:08 schrieb Andy:

> I have a table with varchar and text columns, and I have to search
> through these text in the whole table.
>
> An example would be:
> SELECT * FROM table
> WHERE name like '%john%' or street
> like '%srt%'
>
> Anyway, the query planner always does seq scan on the whole table
> and that takes some time. How can this be optimized or made in
> another way to be faster?

The problem is that normal indexes cannot be used for "contains"
queries.

If you need fulltext search capabilities you have to take a look at
tsearch2 or an external search engine like Lucene.

cug

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Alexander Staubo 2007-05-23 16:05:26 Re: LIKE search and performance
Previous Message Vivek Khera 2007-05-23 15:57:15 Re: does VACUUM ANALYZE complete with this error?