From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Special index for "like"-based query |
Date: | 2016-12-29 23:38:57 |
Message-ID: | f8e87c32-ca0c-0d60-0972-7c1a72e6069a@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/30/2016 12:33 AM, David G. Johnston wrote:
> On Thu, Dec 29, 2016 at 4:21 PM, Job <Job(at)colliniconsulting(dot)it
> <mailto:Job(at)colliniconsulting(dot)it>>wrote:
>
> Hello,
>
> in Postgresql 9.6 we have a query running on a very large table
> based, in some cases, on a like statement:
>
> ... WHERE FIELD='CODE' OR FIELD LIKE 'CODE_%'
>
> Which type of index can i create to speed to the search when the
> "like" case happens?
>
>
> GIST
>
> https://www.postgresql.org/docs/9.6/static/pgtrgm.html
>
> https://www.postgresql.org/docs/9.6/static/btree-gist.html
>
For prefix queries, it's also possible to use simple btree index with
varchar_pattern_ops.
https://www.postgresql.org/docs/9.6/static/indexes-opclass.html
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-12-29 23:46:19 | Re: Special index for "like"-based query |
Previous Message | David G. Johnston | 2016-12-29 23:33:59 | Re: Special index for "like"-based query |