I tested the performance of searches on two columns : text, tsvector. Somehow it appears that tsvector is slower than text. Also, there's some incosistency with planning time and execution time. It seems that the gin index is not really helpful. (text planning time for example with the gin index is slower a bit than without the index. And also the tsvector's execution time is better without the index. how come?)
CREATE TABLE test1 ( content_id smallserial, content_text text, content_vector tsvector, PRIMARY KEY (content_id) ); CREATE INDEX content_text_idx ON test1 USING gin (content_text gin_trgm_ops); CREATE INDEX content_vector_idx ON test1 USING gin (content_vector); insert into test1 (content_text) values ('Pack my box with five dozen liquor jugs.'), ('Jackdaws love my big sphinx of quartz.'), ('The five boxing wizards jump quickly.'), ('How vexingly quick daft zebras jump!'), ('Bright vixens jump; dozy fowl quack.'), ('Sphinx of black quartz, judge my vow.'), ('Jackdaws love my big sphinx of quartz.'), ('Bright vixens jump; dozy fowl quack.'); UPDATE test1 d1 SET content_vector = to_tsvector(d1.content_text) FROM test1 d2;
Then I ran the following :
EXPLAIN ANALYZE SELECT * FROM test1 WHERE content_vector @@ to_tsquery('black') ; EXPLAIN ANALYZE SELECT * FROM test1 WHERE content_text LIKE '%black%';
tsvector + gin index :
Bitmap Heap Scan on test1 (cost=12.28..21.74 rows=4 width=66) (actual time=0.033..0.033 rows=1 loops=1) Recheck Cond: (content_vector @@ to_tsquery('black'::text)) Heap Blocks: exact=1 -> Bitmap Index Scan on content_vector_idx (cost=0.00..12.28 rows=4 width=0) (actual time=0.028..0.028 rows=1 loops=1) Index Cond: (content_vector @@ to_tsquery('black'::text)) Planning Time: 0.150 ms Execution Time: 0.062 ms
text + gin index :
Seq Scan on test1 (cost=0.00..20.75 rows=1 width=66) (actual time=0.015..0.016 rows=1 loops=1) Filter: (content_text ~~ '%black%'::text) Rows Removed by Filter: 7 Planning Time: 0.127 ms Execution Time: 0.034 ms
Now, even when I dropped the indexes, the results still were better for text :
tsvector (no index)
Seq Scan on test1 (cost=0.00..235.75 rows=4 width=66) (actual time=0.032..0.039 rows=1 loops=1) Filter: (content_vector @@ to_tsquery('black'::text)) Rows Removed by Filter: 7 Planning Time: 0.196 ms Execution Time: 0.053 ms
text (no index)
Seq Scan on test1 (cost=0.00..20.75 rows=1 width=66) (actual time=0.016..0.018 rows=1 loops=1) Filter: (content_text ~~ '%black%'::text) Rows Removed by Filter: 7 Planning Time: 0.062 ms Execution Time: 0.034 ms
I tested this with more data, but the results were consistent. In my real project, the text can be of any language. So I cannot define a specific dictionary (e.g 'english', etc.). But even when I tried to_tsquery('english','black'), and the results were a bit better, it still was worse than 'text'.
Did I do something wrong, or should I incorporate 'text' columns with gin indexes + gin_trgm_ops ? In my case, the documents can be up to 2000 words in length. Though the majority will be much less. Would appreciate any advices.
I've retested the cases with a table of 100000 records, containing 2500 words each. the index does seem to work and I get results in 0.4-0.5 ms. (7-12 seconds without index).
Also, instead of having a dedicated tsvector column, I think I might do with text, and use the tsvector that's already in the gin index.
My question is what is essentially the difference between the two indexes :
CREATE INDEX content_text_idx ON test USING gin (content_text gin_trgm_ops);
CREATE INDEX content_vector_idx ON test USING gin (to_tsvector('simple',content_text));
They both result in 600mb of space, and similar performance of query (looked for the word "black" as above.)
** note : in some cases I would need to index 3 columns together, cause I will be searching for keywords that could potentially be contained in any of them. With the first approach it's rather simple :
CREATE INDEX search_index ON test using gin ((content_text || ' ' || title) gin_trgm_ops);
not sure about the second approach.
Also, both don't seem to working for all languages. With Hebrew for example the first method took 7 seconds, and eventually didn't return anything. The second one took 0.026 ms but also didn't return anything. Russian however works only with the second method (tsvector + 'simple').
How can I make either of them support all languages?