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
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general