PostgreSQL index for jsonb @> search

Question

I have the following query:

 SELECT "survey_results".* FROM "survey_results" WHERE (raw @> '{"client":{"token":"test_token"}}');

EXPLAIN ANALYZE returns following results:

 Seq Scan on survey_results  (cost=0.00..352.68 rows=2 width=2039) (actual time=132.942..132.943 rows=1 loops=1)
   Filter: (raw @> '{"client": {"token": "test_token"}}'::jsonb)
   Rows Removed by Filter: 2133
 Planning time: 0.157 ms
 Execution time: 132.991 ms
(5 rows)

I want to add index on client key inside raw field so search will be faster. I don't know how to do it. When I add index for whole raw column like this:

CREATE INDEX test_index on survey_results USING GIN (raw);

then everything works as expected. I don't want to add index for whole raw because I have a lot of records in database and I do not want to increase its size.

Kristo Mägi · Accepted Answer · 2017-07-11 08:23:19Z

2

If you are using JSON objects as atm in the example then you can specify index only client like that:

CREATE INDEX test_client_index ON survey_results USING GIN (( raw->'client ));

But since you are using @> operator in your query then in your case it might make sense to create index only for that operator like that:

CREATE INDEX test_client_index ON survey_results USING GIN (raw jsonb_path_ops);

See more from documentation about Postgres JSONB indexing:

answered Jul 11, 2017 at 8:23

Kristo Mägi

1,70413 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL index for jsonb @> search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related