0

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%';

Results :

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.

UPDATE :

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);

and :

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?

  • 2
    A performance test on 8 rows is utterly useless. "I tested this with more data, but the results were consistent." Then you will have to show that. I can't reproduce it. Indexes seem to work just fine. – jjanes Oct 17 at 14:07
  • Ok, I'll update soon with the results with a lot more data. How much data needs to be so that the performance of tsvector will surpass that of text? And can you please share your view on whether I should be using text/tsvector /with/without index ? In my case the documents can be of any language, up to 2000 words. Also, there would be also the columns like "title" and list of hashtags that I would like to search in combination with the document itself – BinaryVeil Oct 17 at 14:33
  • 1
    It is not surprising that at 8 rows, it would not use the trgm index. The planner pays attention to the size of the tables. It doesn't just use an index whenever that would be theoretically possible. For experimental purposes, you can force it to use the index by set enable_seqscan=off; – jjanes Oct 17 at 14:59
  • 2
    If I double your data 5 times (so 8 rows * 2^^5, or 256 rows) the indexes start to win. But there is no reason to try to cut it close, just go with 100,000 rows as a minimum test size, unless your real data is smaller than that. And if your real data has 2000 words per row, you should try to test with something similar to that. The index performance is sensitive to that factor. – jjanes Oct 17 at 15:04
  • 3
    Besides the dataset being too small, the queries you compare are not equivalent. If you have blackjack in the contents, content_text ~~ '%black%' will find it whereas content_vector @@ to_tsquery('black') will not, by design. – Daniel Vérité Oct 17 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.