pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help with LIKE
6+ messages / 3 participants
[nested] [flat]

* Help with LIKE
@ 2003-03-20 21:41 David Olbersen <DOlbersen@stbernard.com>
  2003-03-20 21:55 ` Re: Help with LIKE Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: David Olbersen @ 2003-03-20 21:41 UTC (permalink / raw)
  To: pgsql-sql@postgresql.org; +Cc: pgsql-performance

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] 6+ messages in thread

* Re: Help with LIKE
  2003-03-20 21:41 Help with LIKE David Olbersen <DOlbersen@stbernard.com>
@ 2003-03-20 21:55 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2003-03-20 21:55 UTC (permalink / raw)
  To: David Olbersen <DOlbersen@stbernard.com>; pgsql-sql@postgresql.org; +Cc: pgsql-performance

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] 6+ messages in thread

* Re: Help with LIKE
@ 2003-03-20 23:19 David Olbersen <DOlbersen@stbernard.com>
  0 siblings, 0 replies; 6+ messages in thread

From: David Olbersen @ 2003-03-20 23:19 UTC (permalink / raw)
  To: pgsql-sql@postgresql.org; +Cc: pgsql-performance

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] 6+ messages in thread

* Re: Help with LIKE
@ 2003-03-20 23:35 David Olbersen <DOlbersen@stbernard.com>
  2003-03-21 01:27 ` Re: Help with LIKE Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: David Olbersen @ 2003-03-20 23:35 UTC (permalink / raw)
  To: pgsql-performance

My mistake, things don't get much better.

I'm selecting URLs out of a database like this:

  SELECT * FROM table WHERE url ~ '^http://.*something.*$';;

This still uses a sequential scan but cuts the time down to 76,351 from 212,651 using

  WHERE url LIKE '%something%';

The full text indexing doesn't look quite right as there are no spaces in this data.

Also, using something like:
  
  WHERE position( 'something', url ) > 0

is a bit worse, giving 84,259.

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


> -----Original Message-----
> From: David Olbersen 
> Sent: Thursday, March 20, 2003 3:19 PM
> To: pgsql-sql@postgresql.org
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Help with LIKE
> 
> 
> 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
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to 
> majordomo@postgresql.org
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with LIKE
  2003-03-20 23:35 Re: Help with LIKE David Olbersen <DOlbersen@stbernard.com>
@ 2003-03-21 01:27 ` Josh Berkus <josh@agliodbs.com>
  2003-03-21 02:06   ` Re: Help with LIKE Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2003-03-21 01:27 UTC (permalink / raw)
  To: David Olbersen <DOlbersen@stbernard.com>; pgsql-performance

David,

> My mistake, things don't get much better.
> 
> I'm selecting URLs out of a database like this:
> 
>   SELECT * FROM table WHERE url ~ '^http://.*something.*$';;

That search still requires a seq scan, since it has "gaps" in the seqence of 
characters.  That is,

url ~ '^http://www.something.*'; could use an index, but your search above 
cannot.

You may be right that the standard OpenFTS indexing won't help you in this 
case, since you're really searching for fragments of a continuous text 
string.

One thing I might suggest is that you look for ways that you might be able to 
break out the text you're searching for with a function.  For example, if you 
were searching strictly on the domain SLD name, then you could create an 
"immutable"  function called if_split_sld(TEXT) and index on that.

If you are really searching for "floating" text within the string, I believe 
that there are some options in tseach to help you, but they may not end up 
improving performance much.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with LIKE
  2003-03-20 23:35 Re: Help with LIKE David Olbersen <DOlbersen@stbernard.com>
  2003-03-21 01:27 ` Re: Help with LIKE Josh Berkus <josh@agliodbs.com>
@ 2003-03-21 02:06   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-03-21 02:06 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: David Olbersen <DOlbersen@stbernard.com>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> SELECT * FROM table WHERE url ~ '^http://.*something.*$';;

> That search still requires a seq scan, since it has "gaps" in the seqence of 
> characters.  That is,

> url ~ '^http://www.something.*'; could use an index, but your search above 
> cannot.

Actually, it *can* use an index ... but the index condition will only
use the characters before the ".*", ie, "http://";.  Which is just about
useless if you're searching a column of URLs :-(

I agree that tsearch or OpenFTS are the tools to be looking at.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-03-21 02:06 UTC | newest]

Thread overview: 6+ 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>
2003-03-20 23:35 Re: Help with LIKE David Olbersen <DOlbersen@stbernard.com>
2003-03-21 01:27 ` Josh Berkus <josh@agliodbs.com>
2003-03-21 02:06   ` Tom Lane <tgl@sss.pgh.pa.us>

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