Tsearch & functional indexes
In the tsearch2 documentation I see a lot of examples where you
add a column of type tsvector to your table and then indexing
that column.
Instead of adding the extra column, would it be possible to
just make a functional index something like this:
create index foo__tsearch on foo using gist (to_tsvector('simple'::text, text))
A brief informal experiment I tried suggests that
the create index command works; but that queries
using this approach were slower than having the
column around. I would have expected it to be faster,
since the disk space used should have been smaller.
Perhaps something about table statistics that I'm
not considering?
Ron,
tsearch index is lossy, so search results needs to be verified.
If you have separate tsvector column you could use it and get all benefit
of to_tsvector already process documents (parsing, dictionaries lookup,...),
instead of reading entire document from disk and process it again.
Read tsearch2 internals for more info.
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
Oleg
On Sun, 9 Oct 2005, Ron Mayer wrote:
In the tsearch2 documentation I see a lot of examples where you
add a column of type tsvector to your table and then indexing
that column.Instead of adding the extra column, would it be possible to
just make a functional index something like this:create index foo__tsearch on foo using gist (to_tsvector('simple'::text,
text))A brief informal experiment I tried suggests that
the create index command works; but that queries
using this approach were slower than having the
column around. I would have expected it to be faster,
since the disk space used should have been smaller.
Perhaps something about table statistics that I'm
not considering?---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83