I have a table with text fields and I need efficient full text search. I found out some technics and I wonder is it better to extend schema by creating additional ts_vector field based on text fields or i can save my schema and just create index this way:
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));
Is there any difference in the sense of efficiency?
Both strategies will work. If you just have the index, you need to make sure that queries use the exact same expression in the where clause as the one in the index, otherwise the index will not be used. E.g. where ts_vector('english', title) .... will not use that index.
The computed column with the ts_vector() will require triggers to be updated automatically because Postgres does not (yet) have persisted calculated columns. So the index "only" solution might be faster when it comes to updating the able as it removes the overhead of the trigger.
