Re: Dumb question involving to_tsvector and a view

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.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company