Thanks for the great resources on this site!

I am using the new JSONB type in PostgreSQL 9.4 and have a question about indexing. Here is my scenario. Assume I have a table named "user" with two columns: (1) "id" that is of type TEXT and is also the primary key, and (2) "setting" that is of type JSONB and contains the bulk of the payload for each row (as a JSON object). Here is my query pattern:

SELECT setting
FROM user
WHERE id IN [...]
ORDER BY setting->>'name',

Notes about the query:

  1. The WHERE clause is in the form of "id IN [...]" where the list of IDs can range from very short to very long.
  2. The ORDER BY clause may contain an arbitrary list of root-level fields in the JSONB object.

In this case, how should I index the "user" table to get good query performance?

Thanks, Pai-Hung

asked Apr 19 '15 at 20:10

Lamplighter's gravatar image


What have you tried already? How did it perform? What would you consider "good query performance"?

(Apr 20 '15 at 14:07) Markus Winand ♦♦

By "good performance," I actually meant the optimal indexing strategy given the specific query pattern I showed, so the query can be executed as fast as it can be.

(Apr 21 '15 at 09:39) Lamplighter