pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with LIKE
3+ messages / 2 participants
[nested] [flat]
* Help with LIKE
@ 2003-03-20 21:41 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Olbersen @ 2003-03-20 21:41 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-performance@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with LIKE
@ 2003-03-20 21:55 Josh Berkus <josh@agliodbs.com>
parent: David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2003-03-20 21:55 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; pgsql-sql; +Cc: pgsql-performance@postgresql.org
David,
> 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%';
That's what's called an "unanchored text search". That kind of query cannot
be indexed using a regular index.
What you need is called "Full Text Indexing" or "Full Text Search". Check
out two resources:
1) contrib/tsearch in your PostgreSQL source code;
2) OpenFTS (www.openfts.org).
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with LIKE
@ 2003-03-20 23:19 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Olbersen @ 2003-03-20 23:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-performance@postgresql.org
Josh,
> That's what's called an "unanchored text search". That kind
> of query cannot be indexed using a regular index.
Duh, should have tried the anchors to get what I wanted...
> What you need is called "Full Text Indexing" or "Full Text
> Search". Check
> out two resources:
This isn't actually what I was looking for, the anchor works better (only 5.87 now!)
Thanks for the reminder!
--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-20 23:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-20 21:41 Help with LIKE David Olbersen <DOlbersen@stbernard.com>
2003-03-20 21:55 ` Josh Berkus <josh@agliodbs.com>
2003-03-20 23:19 Re: Help with LIKE David Olbersen <DOlbersen@stbernard.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox