Using a ts_vector column for a text search

Question

Hard to find out information about this ts_vector thing, but for the purposes of speeding up an address search, a simple index on the address column doesn't really give satisfying results.

To circumvent this limitation I'm trying to to use the ts_vector with the following query:

alter table mytable add tsv ts_vector;

update mytable set tsv = to_tsvector(address);

I'm very unfamiliar with this ts_vector column but would it speed things if I created a btree index (or any other index) and instead of querying the address column to query the ts_vector column instead?

Answer 1

Yes, but it has to be a GIN index:

CREATE INDEX ON mytable USING gin (tsv);

You don't need to add that extra column, you can also do:

CREATE INDEX ON mytable USING gin (to_tsvector('english', address));

Such an index can be used with the @@ operator.

Answer 2

The postgreSQL documentation seems to suggest that it is indeed faster to use a separate tsvector column:

One advantage of the separate-column approach over an expression index is that it is not necessary to explicitly specify the text search configuration in queries in order to make use of the index. As shown in the example above, the query can depend on default_text_search_config. Another advantage is that searches will be faster, since it will not be necessary to redo the to_tsvector calls to verify index matches. The expression-index approach is simpler to set up, however, and it requires less disk space since the tsvector representation is not stored explicitly.

Source: https://www.postgresql.org/docs/current/textsearch-tables.html