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

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>>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