ts_rank massively slows my query, how can I improve performance?

Question

Here's the table definition

Table "public.kb_article_contents"
   Column   |   Type   | Modifiers 
------------+----------+-----------
article_id | smallint | not null
contents   | text     | not null
keywords   | text     | not null
Indexes:
   "contents_idx" gin (to_tsvector('english'::regconfig, contents))
Foreign-key constraints:
   "kb_article_contents_article_id_fkey" FOREIGN KEY (article_id) REFERENCES kb_articles(id)

and the query

support=> EXPLAIN ANALYSE
SELECT
    id,
-- if we remove the next line runtimes speeds up (and of course the order by)
    ts_rank(to_tsvector( 'english', contents ), plainto_tsquery('string')) AS rank
FROM
    kb_article_contents
    INNER JOIN kb_articles
            ON ( kb_article_contents.article_id = kb_articles.id )

WHERE
    published = 'true'
    AND
    to_tsvector( 'english', contents ) @@ plainto_tsquery('string')
ORDER BY rank DESC
LIMIT 25
;

Here's the query plan for the slow query

                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=57.90..57.92 rows=5 width=830) (actual time=452.003..452.028 rows=25 loops=1)
   ->  Sort  (cost=57.90..57.92 rows=5 width=830) (actual time=452.001..452.010 rows=25 loops=1)
         Sort Key: (ts_rank(to_tsvector('english'::regconfig, kb_article_contents.contents), plainto_tsquery('string'::text)))
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Hash Join  (cost=21.36..57.85 rows=5 width=830) (actual time=17.688..451.334 rows=299 loops=1)
               Hash Cond: (kb_articles.id = kb_article_contents.article_id)
               ->  Seq Scan on kb_articles  (cost=0.00..32.06 rows=1156 width=4) (actual time=0.008..1.059 rows=1156 loops=1)
                 Filter: published
               ->  Hash  (cost=21.30..21.30 rows=5 width=828) (actual time=1.175..1.175 rows=302 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 279kB
                     ->  Bitmap Heap Scan on kb_article_contents  (cost=4.30..21.30 rows=5 width=828) (actual time=0.318..0.700 rows=302 loops=1)
                           Recheck Cond: (to_tsvector('english'::regconfig, contents) @@ plainto_tsquery('string'::text))
                           ->  Bitmap Index Scan on contents_idx  (cost=0.00..4.30 rows=5 width=0) (actual time=0.284..0.284 rows=302 loops=1)
                                 Index Cond: (to_tsvector('english'::regconfig, contents) @@ plainto_tsquery('string'::text))
Total runtime: 452.109 ms
(15 rows)

Here's the query plan without the ts_rank

                                                            QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=21.36..57.81 rows=5 width=4) (actual time=0.812..0.938 rows=25 loops=1)
  ->  Hash Join  (cost=21.36..57.81 rows=5 width=4) (actual time=0.810..0.920 rows=25 loops=1)
        Hash Cond: (kb_articles.id = kb_article_contents.article_id)
        ->  Seq Scan on kb_articles  (cost=0.00..32.06 rows=1156 width=4) (actual time=0.009..0.064 rows=89 loops=1)
           Filter: published
        ->  Hash  (cost=21.30..21.30 rows=5 width=2) (actual time=0.782..0.782 rows=302 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 7kB
              ->  Bitmap Heap Scan on kb_article_contents  (cost=4.30..21.30 rows=5 width=2) (actual time=0.203..0.589 rows=302 loops=1)
                    Recheck Cond: (to_tsvector('english'::regconfig, contents) @@ plainto_tsquery('string'::text))
                    ->  Bitmap Index Scan on contents_idx  (cost=0.00..4.30 rows=5 width=0) (actual time=0.171..0.171 rows=302 loops=1)
                          Index Cond: (to_tsvector('english'::regconfig, contents) @@ plainto_tsquery('string'::text))
Total runtime: 1.002 ms
(12 rows)

I'm not sure why adding ts_rank to the query is causing it to balloon in time like this. Is there anything I can do to optimize the query? please note that removing the ORDER BY does not increase the speed of the query, so it doesn't appear to be that.

Answer 1

You have three function calls which are likely fairly CPU intensive. to_tsvector( 'english', contents ) will need to be run for each row and is likely where your time is spent. plainto_tsquery('string') should be run only once per query so it shouldn't cost that much. ts_rank will also need to be run for each row no matter how you do your data.

Rather than generate the tsvector each query you should create a text search index. See the Postgress documentation for details.

Answer 2

It doesn't seem you even need the ts_rank call. By default, a query that actually uses your gin index will be ordered by ts_rank desc. If you're only interested in the top results, try stripping out the explicit ts_rank call and the order by clause. Unless you've very few published rows (or huge numbers of qualifying rows) it'll be sorted as you expect automatically, without any need for extra work.