Hi
I am seeking some clarification in regard to full text search
across multiple tables and what the best approach is.
Documentation talks about two approaches when it comes to building
a document: on-the-fly concat of columns and a dedicated tsv
column approach. Let's say I want to perform a search for "txt1
& txt2 & txt3" on columns table1.col1, table1.col2 and
table2.col1. I see the following solutions:
1. Concat all three into a document and perform a FTS.
SELECT * FROM (
SELECT to_tsvector(table1.col1) ||
to_tsvector(table1.col2) ||
to_tsvector(table2.col1) as document FROM table1 LEFT JOIN table2 ON table1.table2_id=table2.id
) subquery WHERE subquery.document @@ to_tsquery(unaccent(?));
2. Create a tsv column in each table, concat tsv columns
and perform FTS on that.
SELECT * FROM table1 LEFT JOIN table2 ON table1.table2_id=table2.id WHERE table1.tsv || tale2.tsv @@ to_tsquery(unaccent(?));
3. Have a tsv column only in table1 and insert table2.col1
to the tsv via triggers. Works but seems very hacky.
It seems to me that option #2 is fast and easy to implement
but I am not sure what the concat of tsvs really means from
index usage and performance standpoint. Option #1 is the most
flexible and I'd use that all the time if it was not THAT much
slower than tsv column approacj. Documentation on TSV columns
states: "Another advantage is that searches will be
faster, since it will not be necessary to redo the to_tsvector
calls to verify index matches."
The question is, how much faster are tsv columns really? Are
there any benchmarks about this? If the performance difference is
negligible I'd advocate that using tsv columns is a waste of time
and space in most general cases. But since there is no information
on how much faster it's hard to decide.
Best regards,
Klemen
