Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
I created an index, it's using it, that's what the "contents_idx". If I didn't have even without using ts_rank it would take like 10s to run. Perhaps it's not using it as much as it could be? any idea whether I can get more out of it? –  xenoterracide Jul 23 '11 at 17:52
    
ts_rank is documented to be I/O intensive so more memory may help caching. On Linux/Unix you could try running sar while the query is running to determine where the bottleneck is. –  BillThor Jul 23 '11 at 20:07
add comment

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.

share|improve this answer
add comment

ts_rank will rank all rows, most of which will probably have a ranking of 0. So it's faster to do a search without ts_rank and then ts_rank the results of that search using the same query. So you'd probably want to do something like

SELECT score.id, ts_rank(score.contents::tsvector, plainto_tsquery('string')) as rank from
(SELECT
    id, contents
FROM
    kb_article_contents
    INNER JOIN kb_articles
            ON ( kb_article_contents.article_id = kb_articles.id )

WHERE
    published = 'true'
    AND
    contents::tsvector @@ plainto_tsquery('string')) 
    as score
order by rank desc;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.