A PostgreSQL full text search
PostgreSQL provides a full text search capability, which is used to overcome SQL pattern matching operators, including LIKE
and ILIKE
, boosting the performance of the text search. For example, even though an index on text using the text_pattern_op
class is supported, this index cannot be used to match a nonanchored text search. To explain this limitation, let's create the following table:
CREATE TABLE document( document_id serial primary key, document_body text ); CREATE INDEX on document (document_body text_pattern_ops); INSERT INTO document VALUES (default, 'Can not use text_pattern_op class to search for non-anchored text');
To test the index with anchored and nonanchored text search, let's disable sequential scan and generate execution plans as shown in the following example:
car_portal=# EXPLAIN SELECT * FROM document WHERE document_body like 'Can%text_pattern'; QUERY PLAN ---------------------------------...