How can I make an index covering a ts_vector expression and also an integer? I am using the ruby pg_search gem, where code like this: Food.search(&quot;orange&quot;) produces a query like this.
SELECT &quot;foods&quot;.*
FROM &quot;foods&quot;
INNER JOIN
  (SELECT &quot;foods&quot;.&quot;id&quot; AS pg_search_id,
          (ts_rank((to_tsvector('simple', coalesce(&quot;foods&quot;.&quot;name&quot;::text, ''))), (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')), 0)) AS rank
   FROM &quot;foods&quot;
   WHERE ((to_tsvector('simple', coalesce(&quot;foods&quot;.&quot;name&quot;::text, ''))) @@ (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')))) AS pg_search_d4392ced9dff0647fed4ed ON &quot;foods&quot;.&quot;id&quot; = pg_search_d4392ced9dff0647fed4ed.pg_search_id
ORDER BY pg_search_d4392ced9dff0647fed4ed.rank DESC,
         &quot;foods&quot;.&quot;id&quot; ASC;

Ive created this index, which seems to work quite well
CREATE INDEX concurrently foods_gin ON foods USING gin
(to_tsvector('simple', coalesce(&quot;foods&quot;.&quot;name&quot;::text, '')));

I have another column, category. there are 9 categories. For a given search as shown above for name, I want to show results from category 9 first, and then all the other categories (order doesn't matter).
ideally the results could be paginated, so a perfect query/index combo would be great.
is this possible to do, and/or will it be a truly gigantic index?
p.s. suggestions for a better name for the question are welcome
With the help of the btree_gin extension, you can include an int column into your index, but doing so will not offer you an advantage.  The way gin indexes work, it can't be used for ordering.
You should write your query first, and worry about optimizing once you have something to optimize.