Help with LIKE

From: "David Olbersen" <DOlbersen(at)stbernard(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Help with LIKE
Date: 2003-03-20 21:41:25
Message-ID: E7E213858379814A9AE48CA6754F5ECB0D6C2D@mail01.stbernard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance pgsql-sql

I have a table with 8,628,633 rows that I'd LIKE to search (ha ha).

I have a very simple query:
SELECT * FROM tableA WHERE column1 LIKE '%something%';

tableA.column1 has an index on it and the database has been vacuumed recently. My problem is with the output of EXPLAIN:

+----------------------------------------------------------------+
| QUERY PLAN |
+----------------------------------------------------------------+
| Seq Scan on tableA (cost=0.00..212651.61 rows=13802 width=46) |
| Filter: (column1 ~~ '%something%'::text) |
+----------------------------------------------------------------+

I don't like that cost (2,12,651) at all! Is there anyway I can optimize this query? Make a different kind of index (it's currently btree)? Use substr or indexof or something instead of LIKE?

Thoughts?

--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2003-03-20 21:55:32 Re: Help with LIKE
Previous Message Guillaume Houssay 2003-03-20 21:26:40 just to get some opinion on my configuration

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2003-03-20 21:55:32 Re: Help with LIKE
Previous Message Tom Lane 2003-03-20 15:08:11 Re: SQL stored proc query (optimising)