Kevin Grittner <kgrittn@xxxxxxxxx> writes: > Raymond C. Rodgers <sinful622@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
