pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Optimizing t1.col like '%t2.col%'
2+ messages / 2 participants
[nested] [flat]

* Optimizing t1.col like '%t2.col%'
@ 2008-02-27 19:47  Dan Kaplan <dkaplan@citizenhawk.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Dan Kaplan @ 2008-02-27 19:47 UTC (permalink / raw)
  To: pgsql-performance

I've got a lot of rows in one table and a lot of rows in another table.  I
want to do a bunch of queries on their join column.  One of these is like
this: t1.col like '%t2.col%'

 

I know that always sucks.  I'm wondering how I can make it better.  First, I
should let you know that I can likely hold both of these tables entirely in
ram.  Since that's the case, would it be better to accomplish this with my
programming language?  Also you should know that in most cases, t1.col and
t2.col is 2 words or less.  I'm not sure if that matters, I mention it
because it may make tsearch2 perform badly.

 

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

* Re: Optimizing t1.col like '%t2.col%'
@ 2008-02-27 20:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dan Kaplan <dkaplan@citizenhawk.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2008-02-27 20:26 UTC (permalink / raw)
  To: Dan Kaplan <dkaplan@citizenhawk.com>; +Cc: pgsql-performance

"Dan Kaplan" <dkaplan@citizenhawk.com> writes:
> I've got a lot of rows in one table and a lot of rows in another table.  I
> want to do a bunch of queries on their join column.  One of these is like
> this: t1.col like '%t2.col%'

> I know that always sucks.  I'm wondering how I can make it better.

tsearch or pg_trgm could probably help.  Are you really after exact
substring-match semantics, or is this actually a poor man's substitute
for full text search?  If you just want substrings then see pg_trgm,
if you want text search see tsearch.

			regards, tom lane



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

end of thread, other threads:[~2008-02-27 20:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-27 19:47 Optimizing t1.col like '%t2.col%' Dan Kaplan <dkaplan@citizenhawk.com>
2008-02-27 20:26 ` 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