On Thu, Dec 29, 2016 at 4:51 PM, Tomas Vondra <tomas.von...@2ndquadrant.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.von...@2ndquadrant.com <mailto:tomas.von...@2ndquadrant.com >> >>wrote: >> >> On 12/30/2016 12:33 AM, David G. Johnston wrote: >> >> On Thu, Dec 29, 2016 at 4:21 PM, Job <j...@colliniconsulting.it >> <mailto:j...@colliniconsulting.it> >> <mailto:j...@colliniconsulting.it >> <mailto:j...@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 > > 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.
