Re: Dumb question involving to_tsvector and a view

On 02/23/2013 05:26 AM, Tom Lane wrote:
> 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
I think the only real advantage to using something like this would be a
space savings in terms of storing the tsvector data, but I don't see
that being a significant enough reason to go ahead and use this idea in
a production situation. As mentioned [by pretty much all of us], once
the table size is sufficiently large there would be a performance
penalty by to_tsvector being executed on every record in the table. (If
I'm not mistaken, with the way I wrote that "create view", every record
in "mytable" would be subject to the function call, then any narrowing
parameters in the where clause would be applied afterwards.)

Any way, like I said originally, it was a dumb question. It might be ok
to use that in a situation where the table size is known to be small,
but there's little to no reason to do it in a production situation.

Thanks!
Raymond