| 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 |
| Thread: | |
| 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 | Date | Subject | |
|---|---|---|---|
| Next Message | Yasuhiro Furuse | 2006-02-26 23:26:46 | Re: Relation 0 does not exist |
| Previous Message | Florian Weimer | 2006-02-25 17:52:43 | Re: Index to support LIKE '%suffix' queries |