Re: Special index for "like"-based query

On Thu, Dec 29, 2016 at 4:51 PM, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> On 12/30/2016 12:46 AM, David G. Johnston wrote:
>
>> On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra
>> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com
>> >>wrote:
>>
>> 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>
>> <mailto: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/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
>
>
​While I may have an understanding of what operator classes and families
are when I am in my SQL thinking mode those terms don't really come to
mind. Maybe part of the problem is that SQL doesn't have indexes and so my
formal education never covered them. I learned how to use "CREATE INDEX"
to meet most common needs but the fact that I'm getting a b-tree family
index is well hidden.

While I'm all for learning the theory a more prescriptive approach (do this
to get an index that ​will allow prefix LIKEs to use it - see this section
for detail) to the topic would be welcome. Tell the user how to use an
index when they are learning about the feature that they care about - LIKE
- not require them to learn all about indexes and later realize/remember
that one particular incantation will solve the LIKE problem.

David J.