Re: [GENERAL] Special index for "like"-based query

On 12/30/2016 12:46 AM, David G. Johnston wrote:
> On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com <mailto:tomas.vondra@2ndquadrant.com>>wrote:
>
>     On 12/30/2016 12:33 AM, David G. Johnston wrote:
>
>         On Thu, Dec 29, 2016 at 4:21 PM, Job <Job@colliniconsulting.it
>         <mailto:Job@colliniconsulting.it>
>         <mailto:Job@colliniconsulting.it
>         <mailto:Job@colliniconsulting.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/pgtrgm.html>
>
>         ​https://www.postgresql.org/docs/9.6/static/btree-gist.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
>     <https://www.postgresql.org/docs/9.6/static/indexes-opclass.html>
>
>
> Even knowing that this feature exists I don't know that I could have
> found it within a reasonable amount of time in its present location.  A
> few cross-references from elsewhere (probably at least the functions
> part of the documentation) would make learning about the capability a
> lot easier.
>

Well, it's referenced right from the "Indexes" part of the documentation
(right at the beginning of "Index Types"):

https://www.postgresql.org/docs/9.6/static/indexes.html

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services