Index for searching nested JSONB array elements in PostgreSQL
|Added at||2016-11-28 17:11|
I'm fairly new to postgres and currently using 9.6. When trying to implement a full text search in postgres using it's jsonb documents I noticed slow search results for nested arrays. I used the 'explain' command and it didn't use any indices. For simplicity purpose I created a table to investigate:
My available indices:
And some data to fill a document:
I'm able to search for book elements using the following query, however it doesn't use any index. With my actual data of 120k products it takes around 1200ms while other searches with an index take 0.2ms.
In contrast the next query uses the book_author_name_idx but because of the array structure doesn't find anything.
How can I tweak my query to use a language index? I'm aware, that I could make a new table for authors and only reference the ids, but I'd rather keep all data in one table for performance.