title: PostgreSQL full text search performance not acceptable when ordering by ts_rank_cd
body: <p>In my PostgreSQL 9.3 database, I have a table called <code>articles</code>. It looks kind of like this:</p>
  
  <pre><code>+------------+--------------------------------------------------------------+
  |    Name    |                        Information                           |
  +------------+--------------------------------------------------------------+
  | id         | Auto incrememnt integer ID                                   |
  | title      | text                                                         |
  | category   | character varying(255) with index                            |
  | keywords   | String with title and extra words used for indexing          |
  | tsv        | Trigger updates w/ tsvector_update_trigger based on keywords |
  +------------+--------------------------------------------------------------+
  </code></pre>
  
  <p>There's more columns in the table but I don't think they aren crucial to the question. The total size of the table is 94GB and about 29M rows.</p>
  
  <p>I'm trying to run a query on a keyword search on a subset of 23M of the <code>article</code> rows. to do this I use the following query:</p>
  
  <pre><code>SELECT title, id FROM articles, plainto_tsquery('dog') AS q 
  WHERE (tsv @@ q) AND category = 'animal' 
  ORDER BY ts_rank_cd(tsv, q) DESC LIMIT 5
  </code></pre>
  
  <p>The problem with this is that it appears by running <code>ts_rank_cd</code> on each of the results first before it can sort them and therefore this query is very slow, about 2-3 minutes. I've read around a lot to try and find a solution and it was suggested that I wrap the search query in another query so that the ranking is only applied to the found results like so: </p>
  
  <pre><code>SELECT * FROM (
    SELECT title, id, tsv FROM articles, plainto_tsquery('dog') AS q 
    WHERE (tsv @@ q) AND category = 'animal'
  ) AS t1
  ORDER BY ts_rank_cd(t1.tsv, plainto_tsquery('dog')) DESC LIMIT 5;
  </code></pre>
  
  <p>However, because the query is so short, there are 450K results in the subset. So it still takes a long time, it might be a bit quicker but I need this to be essentially instant.</p>
  
  <p>The question: Is there <em>anything</em> I can do to keep this searching functionality within PostgreSQL? </p>
  
  <p>It's nice having this logic kept in the database and means I don't require any extra servers or configuration for something like Solr or Elasticsearch. For example, would increasing the database instance capacity help things? Or would the cost efficiency not make sense when compared to shifting this logic over to a dedicated Elasticsearch instance.</p>
  
  <hr>
  
  <p>The EXPLAIN response from the first query is as follows:</p>
  
  <pre><code>Limit  (cost=567539.41..567539.42 rows=5 width=465)
    -&gt;  Sort  (cost=567539.41..567853.33 rows=125568 width=465)
          Sort Key: (ts_rank_cd(articles.tsv, q.q))
          -&gt;  Nested Loop  (cost=1769.27..565453.77 rows=125568 width=465)
                -&gt;  Function Scan on plainto_tsquery q  (cost=0.00..0.01 rows=1 width=32)
                -&gt;  Bitmap Heap Scan on articles  (cost=1769.27..563884.17 rows=125567 width=433)
                      Recheck Cond: (tsv @@ q.q)
                      Filter: ((category)::text = 'animal'::text)
                      -&gt;  Bitmap Index Scan on article_search_idx  (cost=0.00..1737.87 rows=163983 width=0)
                            Index Cond: (tsv @@ q.q)
  </code></pre>
  
  <p>And for the second query:</p>
  
  <pre><code>Aggregate  (cost=565453.77..565453.78 rows=1 width=0)
    -&gt;  Nested Loop  (cost=1769.27..565139.85 rows=125568 width=0)
          -&gt;  Function Scan on plainto_tsquery q  (cost=0.00..0.01 rows=1 width=32)
          -&gt;  Bitmap Heap Scan on articles  (cost=1769.27..563884.17 rows=125567 width=351)
                Recheck Cond: (tsv @@ q.q)
                Filter: ((category)::text = 'animal'::text)
                -&gt;  Bitmap Index Scan on article_search_idx  (cost=0.00..1737.87 rows=163983 width=0)
                      Index Cond: (tsv @@ q.q)
  </code></pre>