Is it better to store one tsvector column for all fields or one tsvector column per field?

by fabiomaia   Last Updated October 17, 2018 12:06 PM

Consider a simple movies table like this:

CREATE TABLE movies(
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title character varying(255),
    categories character varying(255),
    summary text,
    description text
);

Suppose I am interested in doing full-text search on all the fields like this:

SELECT *
FROM movies
WHERE to_tsvector(title) @@ to_tsquery('Lumberjack')
OR to_tsvector(categories) @@ to_tsquery('Lumberjack')
OR to_tsvector(summary) @@ to_tsquery('Lumberjack')
OR to_tsvector(description) @@ to_tsquery('Lumberjack')

Clearly I should not be using to_tsvector at runtime like that and should cache the tsvector representation of the fields instead. But I see two ways of doing that:

  1. Store one tsvector-representation column for all of the fields
  2. Store one tsvector-representation column per each field

Which is more performant and why?



Related Questions


Performing search on a column by query

Updated May 22, 2015 22:02 PM

Fuzzy Matching with Postgresql 9.3

Updated April 06, 2017 14:06 PM