22.2. Building Substring Indexes
Indexes are the key to performance. If data is selected and retrieved from a table, indexes will help you to speed up your queries significantly. However, in some operations indexes cannot be used. One of these operations is substring searching. If you are working with regular expressions, PostgreSQL is not capable of using indexes. Internally an index is a tree built on a sorted list. This way data can be retrieved efficiently. In substrings it is not possible to benefit from the advantages of a sorted list. Therefore it is necessary to build a workaround. This can easily be done by using the fulltextindex package, which can be found in PostgreSQL's contributed section ($PATH_TO_POSTGRES/contrib/fulltextindex ...
Get PHP and PostgreSQL: Advanced Web Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
