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@xxxxxxxxxxxxxxx>>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@xxxxxxxxxxxxxxxxxxxx> <mailto:Job@xxxxxxxxxxxxxxxxxxxx <mailto:Job@xxxxxxxxxxxxxxxxxxxx>>>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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
