Search code examples
sqlpostgresqlindexingtsvector

Using a ts_vector column for a text search

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?

Solution

  • 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.