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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
