Re: Dumb question involving to_tsvector and a view

Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> Raymond C. Rodgers <sinful622(at)gmail(dot)com> wrote:
>> As I went to add a tsvector column, it occurred to me that it
>> might be possible to add a dynamic tsvector column through the
>> use of a view, so I created a temporary view with a command along
>> the lines of:
>>
>> CREATE TEMPORARY VIEW ftstest AS SELECT id, field1, field2,
>> TO_TSVECTOR(COALESCE(field1,'') || ' ' ||
>> COALESCE(field2,'')) AS txtsrch FROM mytable;
>>
>> To my surprise, it worked. Now, I'm sitting here thinking about
>> the performance impact that doing this would have.

> I had a similar situation and benchmarked it both ways. For my
> situation I came out ahead writing the extra column for inserts and
> updates than generating the tsvector values on the fly each time it
> was queried. YMMV. It probably depends mostly on the ratio of
> inserts and updates to selects.

A "virtual" tsvector like that is probably going to be useless for
searching as soon as you get a meaningful amount of data, because the
only way the DB can implement a search is to compute the tsvector value
for each table row and then examine it for the target word(s).

What you want is a GIST or GIN index on the contents of the tsvector.
You can either realize the tsvector as a table column and put a regular
index on it, or you can build a functional index on the to_tsvector()
expression. The latter is kind of like your idea in that the tsvector
as a whole isn't stored anywhere --- but there's an index containing all
the words, which is what you need for searching.

I think there are examples of both ways in the "text search" chapter of
the manual. (If not, there should be ...)

regards, tom lane