I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this.

SELECT "foods".*
FROM "foods"
  (SELECT "foods"."id" AS pg_search_id,
          (ts_rank((to_tsvector('simple', coalesce("foods"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')), 0)) AS rank
   FROM "foods"
   WHERE ((to_tsvector('simple', coalesce("foods"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')))) AS pg_search_d4392ced9dff0647fed4ed ON "foods"."id" = pg_search_d4392ced9dff0647fed4ed.pg_search_id
ORDER BY pg_search_d4392ced9dff0647fed4ed.rank DESC,
         "foods"."id" ASC;

Ive created this index, which seems to work quite well

CREATE INDEX concurrently foods_gin ON foods USING gin
(to_tsvector('simple', coalesce("foods"."name"::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.

Your Answer

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

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