Teodor Sigaev wrote:
CREATE INDEX idxname ON tblname USING gin (textcolumn fulltext_ops);
Fulltext_ops opclass parses the document similarly to_tsvector nad
stores lexemes in gin index. It's a full equalent of
CREATE INDEX ... ( to_tsvector( textcolumn ) )
And, let we define operation text @ text, which is equivalent of text @@
plainto_tsquery(text), so, queries will look like
SELECT * FROM tblname WHERE textcolumn @ textquery;
Fulltext_ops can speedup both operation, text @@ tsquery and text @
text. Because gin API has extractQuery method which calls once per index
scan and it can parse query to lexemes.
Some disadvantage: with that way it isn't possible make fast ranking -
there is no stored parsed text. And, fulltext_ops may be done for GiST
index too, but fulltext opclass will be lossy which means slow search
due to reparse texts for each index match.
Just a thought:
If the patch that implements the "GENERATED ALWAYS" syntax is accepted,
than creating a seperate field that hold the parsed text and an index
on that column becomes as easy as:
alter table t1 add column text_parsed generated always as to_tsvector(text);
create index idx on t1 using gin (text_parsed fulltext_ops);
I know that there is a trigger function in tsearch that support something
similar, but I really like the simplicity of the statements above.
One a related note - will to_tsvector and to_tsquery be renamed to
something like ft_parse_text() and ft_parse_query() if tsearch2 goes
into core? It seems like the "ts" part of those names would be the only
referenced left to the name "tsearch" if they are not, which could be
somewhat confusing for users.
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
