2

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?

  • 2
    What are your requirements? Are your users expecting to type "Lumberjack" and have it match any of the fields? In that case I would index one tsvector. – Colin 't Hart Oct 17 '18 at 13:26
  • @Colin'tHart Yes, those are the requirements. Couldn't I achieve that with multiple tsvector of each of the fields (title, categories, summary, description) though? If not, then in which case would such a setup be useful? – fabiomaia Oct 17 '18 at 17:26
  • 2
    Are all queries going to go against all fields, or will the user have a set of checkboxes to pick any combination of them? – jjanes Oct 18 '18 at 15:39
  • @jjanes All queries. But I'm also interested in an argument for the setup you've mentioned. – fabiomaia Oct 18 '18 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.