From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Special index for "like"-based query |
Date: | 2016-12-29 23:46:19 |
Message-ID: | CAKFQuwbkBxX8W1Ms6x65JkY9_cS6gDsrJT=5oYnKCgpdAxiMew@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Dec 29, 2016 at 4:38 PM, Tomas Vondra <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>>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
>
>
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.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2016-12-29 23:51:07 | Re: Special index for "like"-based query |
Previous Message | Tomas Vondra | 2016-12-29 23:38:57 | Re: Special index for "like"-based query |