CODEWITHSUNDEEP
sqlpostgresqljsonb
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

PostgreSQL index for jsonb @> search

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.

Upvotes: 0

Views: 62

Answers (1)

Kristo Mägi
Kristo Mägi

Reputation: 1684

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:

Upvotes: 2

Related Questions