Thread: Index to support LIKE '%suffix' queries
Is it possible to create an index to support queries of the form "column LIKE '%suffix'" (similar to an ordinary index for LIKE 'prefix%', which I also need)? I could define a function which reverts strings (or revert them in the application) and use a normal B-tree index, but I wonder if there is a better way.
Florian Weimer <fw@deneb.enyo.de> writes:
> Is it possible to create an index to support queries of the form
> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
> 'prefix%', which I also need)?
Sounds like what you *really* need is full-text search, not half
measures ... have you looked at tsearch2?
regards, tom lane
* Tom Lane: > Florian Weimer <fw@deneb.enyo.de> writes: >> Is it possible to create an index to support queries of the form >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> 'prefix%', which I also need)? > > Sounds like what you *really* need is full-text search, not half > measures ... have you looked at tsearch2? Uh-oh, the table in question has got 50+ million rows (and is still growing). Each "document" contains about three words. Do you think tsearch2 could deal with that?
> > Florian Weimer <fw@deneb.enyo.de> writes: > >> Is it possible to create an index to support queries of the form > >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE > >> 'prefix%', which I also need)? It is possible to create a functional index on the reverse of the string. You need to also reverse the patter at query-time as well. This will make the % be at the end of the pattern, making it an indexable condition. Whether or not this beats tsearch2 is something you should investigate ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
* Alvaro Herrera: >> > Florian Weimer <fw@deneb.enyo.de> writes: >> >> Is it possible to create an index to support queries of the form >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> >> 'prefix%', which I also need)? > > It is possible to create a functional index on the reverse of the > string. Okay. Is there a predefined reverse function? I couldn't find one and I'm wondering if I just missed it. > Whether or not this beats tsearch2 is something you should investigate ... It's also possible that for this type of query, sequential scans are good enough. I forgot that they are quite fast.
am 01.03.2006, um 8:19:40 +0100 mailte Florian Weimer folgendes: > * Alvaro Herrera: > > >> > Florian Weimer <fw@deneb.enyo.de> writes: > >> >> Is it possible to create an index to support queries of the form > >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE > >> >> 'prefix%', which I also need)? > > > > It is possible to create a functional index on the reverse of the > > string. > > Okay. Is there a predefined reverse function? I couldn't find one > and I'm wondering if I just missed it. simple to write one, http://a-kretschmer.de/diverses.shtml HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===