Re: t1.col like '%t2.col%'

Joshua Drake spake thusly:

> On Fri, 29 Feb 2008 15:52:31 -0800
> "Dan Kaplan" <dkaplan(at)citizenhawk(dot)com> wrote:
>
> > I learned a little about pg_trgm here:
> > http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm
> >
> > But this seems like it's for finding similarities, not substrings.
> > How can I use it to speed up t1.col like '%t2.col%'?
>
> Faster disks.
>
> No matter what, that will seqscan. So if you want it to go faster, you
> need faster hardware.

Word!

That said ...

Once upon a time we had a requirement to allow users to search within US counties for property owner name or street names by text fragment.

We used the now deprecated Full Text Indexing (FTI) with some handwaving. But that was in PostgreSQL 7.4 and FTI is not in the contrib package for some time now. See <http://pgfoundry.org/projects/simplefti/> ... I looked at using it in 8.1 but my "C" chops weren't up to it, and it depended heavily on OIDs which we didn't want to use. Anyway, our business requirement evaporated so it doesn't matter to us now.

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)