| Lists: | pgsql-sql |
|---|
| From: | Florian Weimer <fw(at)deneb(dot)enyo(dot)de> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Index to support LIKE '%suffix' queries |
| Date: | 2006-02-25 14:17:41 |
| Message-ID: | 87mzgfh69m.fsf@mid.deneb.enyo.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
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.
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Florian Weimer <fw(at)deneb(dot)enyo(dot)de> |
| Cc: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Index to support LIKE '%suffix' queries |
| Date: | 2006-02-25 17:22:27 |
| Message-ID: | 11673.1140888147@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
Florian Weimer <fw(at)deneb(dot)enyo(dot)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
| From: | Florian Weimer <fw(at)deneb(dot)enyo(dot)de> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Index to support LIKE '%suffix' queries |
| Date: | 2006-02-25 17:52:43 |
| Message-ID: | 877j7jfhqs.fsf@mid.deneb.enyo.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
* Tom Lane:
> Florian Weimer <fw(at)deneb(dot)enyo(dot)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?
| From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
|---|---|
| To: | Florian Weimer <fw(at)deneb(dot)enyo(dot)de> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Index to support LIKE '%suffix' queries |
| Date: | 2006-02-25 18:13:46 |
| Message-ID: | 20060225181346.GA6092@surnet.cl |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
> > Florian Weimer <fw(at)deneb(dot)enyo(dot)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.
| From: | Florian Weimer <fw(at)deneb(dot)enyo(dot)de> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Index to support LIKE '%suffix' queries |
| Date: | 2006-03-01 07:19:40 |
| Message-ID: | 874q2ik4xf.fsf@mid.deneb.enyo.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
* Alvaro Herrera:
>> > Florian Weimer <fw(at)deneb(dot)enyo(dot)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.
| From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Index to support LIKE '%suffix' queries |
| Date: | 2006-03-01 11:33:01 |
| Message-ID: | 20060301113301.GD9939@webserv.wug-glas.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-sql |
am 01.03.2006, um 8:19:40 +0100 mailte Florian Weimer folgendes:
> * Alvaro Herrera:
>
> >> > Florian Weimer <fw(at)deneb(dot)enyo(dot)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 ===