tsvector supports PostgreSQL full-text search by converting text into a searchable representation. Pairing it with a GIN index avoids treating long document fields like ordinary B-tree values. section 4.4
Avoid these common indexing mistakes:
- Do not use a B-tree for arbitrary substring searches or large abstracts; long values can exceed PostgreSQL's index-row limit. section 4.4
- Create a GIN expression index with a chosen text configuration, then query the matching tsvector expression with @@. section 4.4
- Choose the index for the predicate: GIN suits full-text search, while a focused B-tree is smaller and faster for exact identifiers. section 4.4
Indexed title search
CREATE INDEX ON test.arxiv USING gin (to_tsvector('english', data->>'title')); Query the same expression with plainto_tsquery('english', 'modeling of hydrogen'). The resulting plan uses a Bitmap Index Scan and completes in about 5 ms for the demonstrated dataset. section 4.4
DROP INDEX test.arxiv_lower_idx;
DROP INDEX
CREATE INDEX ON test.arxiv
USING gin (to_tsvector('english', data->>'title'));
CREATE INDEX
Inspect the executable GIN index definition for a JSON title expression. section 4.4
