PostgreSQL 9.4 – Creating Index for JSONB Key/Value

indexperformancepostgresqlpostgresql-9.4postgresql-performance

Can we create an index for a key/value of a JSONB data type?

For example, for these schema:

CREATE TABLE x (
  id BIGSERIAL,
  data JSONB
);
CREATE TABLE y (
  id BIGSERIAL,
  data JSONB
);

The slow query:

SELECT *
FROM x
  LEFT JOIN y
    ON (y.data->>'x_id')::BIGINT = x.id

How to create an index for y.data->>'x_id' that can be used for that kind of query?

Best Answer

I suggest an expression index on the value of the key 'x_id', cast to bigint - plain (default) btree, not GIN. A specific difficulty here: the short notation for type casts would need multiple layers of parentheses to make the syntax work for index creation. 

CREATE INDEX y_data_xid_idx ON y (((y.data->>'x_id')::bigint));

Rather use the explicit form (to the same effect):

CREATE INDEX y_data_xid_idx ON y (cast(y.data->>'x_id' AS bigint));
Related Question