Search Postgresql Archives

Re: Special index for "like"-based query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Dec 29, 2016 at 4:51 PM, Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> wrote:
On 12/30/2016 12:46 AM, David G. Johnston wrote:
On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra
<tomas.vondra@xxxxxxxxxxxxxxx <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@xxxxxxxxxxxxxxxxxxxx
        <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


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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux