| 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: | Whole Thread | Raw Message | 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
| 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 |
| 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) |