Index for ranking JSONB search results in PostgreSQL
|Added at||2016-12-08 13:12|
I'm currently optimizing my search results on jsonb fields of PostgreSQL. I'm using Postgres 9.6. My ultimate goal is to search on multiple fields within my jsonb document and rank the results according to their total hits in all fields. But I'm stuck because the ts_rank function doesn't use my index and slowing down the search immensely. Here is a minimal example:
When trying to search on the title field I'm using this query:
Without ranking the search on my real data takes < 1ms, with the ranking it's ~1800ms. It gets worse the more fields I search on. I need the ranking only to make hits in multiple fields more valueable.