Re: two questions about fulltext searchign / tsvector indexes

On Mon, Jun 9, 2014 at 8:55 PM, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:
> I can't figure out which one to use. This is on a steadily growing table of around 20MM rows that gets 20-80k new records a day, but existing records are rarely updated.

The question as always is a time-space trade-off. How frequently do
you make the full text search? If you do it frequently, then with a
pre-computed tsv column you save all that time per row of computing
the tsvector on every search. If you do it infrequently, the space
savings (and not needing to maintain that column) may benefit you.

Personally in these days of cheap disks I'd go with the dedicated
column. Given that, you want to just have a GIN index on that one
column, and the query you want, given some plain text string like
"fluffy dog" is this:

select plainto_tsquery('fluffy dog') @@ my_tsv_column;

I always use a trigger on insert and update to maintain the ts_vector
column, so there is no doubt of how it was computed by various
programs.