How to use jsonb index in postgres

Question

My pg is 9.5+. I have a jsonb data in column 'body':

{
   "id":"58cf96481ebf47eba351db3b",
   "JobName":"test",
   "JobDomain":"SAW",
   "JobStatus":"TRIGGERED",
   "JobActivity":"ACTIVE"
}

And I create index for body and key:

CREATE INDEX scheduledjob_request_id_idx ON "ScheduledJob" USING gin ((body -> 'JobName'));
CREATE INDEX test_index ON "ScheduledJob" USING gin (body jsonb_path_ops)

This are my queries:

SELECT body FROM "ScheduledJob" WHERE body @> '{"JobName": "analytics_import_transaction_job"}';
SELECT body FROM "ScheduledJob" WHERE (body#>'{JobName}' = '"analytics_import_transaction_job"')  LIMIT 10;

Those are return correct data, but no one use index. I saw the explain:

->  Seq Scan on public."ScheduledJob"  (cost=0.00..4.55 rows=1 width=532)

So, I don't know why didn't use the index, and how to use the index for jsonb correctly.

Update:

I create index before insert data, the query can use index.
But I create index after insert the first data, the query will be scan all records.

This is so strange, and how can I make the index useful when I insert data first.

Queries with body#>'{JobName}' will never use an index of (body -> 'JobName'). Use the same expression (i.e. the same operator) to have at least a chance. -- Please post EXPLAIN ANALYZE of your queries. Your table may not contain enough sample rows to use an index. — pozs
– pozs, Commented Mar 21, 2017 at 10:02
In addition to that, for the first index you might be better off using an INDEX ON "ScheduledJob" USING btree ((body ->> 'JobName')). — Laurenz Albe
– Laurenz Albe, Commented Mar 21, 2017 at 13:25
I add the data to 10000 rows, and the query use the index!!! THX!!! — Louie Liu
– Louie Liu, Commented Mar 22, 2017 at 6:27

Louie Liu · Accepted Answer · 2017-03-22 06:42:09Z

1

So, I do some research and test that:

SELECT body FROM "ScheduledJob" WHERE (body#>'{JobName}' = '"analytics_import_transaction_job"')  LIMIT 10;

This kind of query will never use the index.

And only the table have enough data, index can be available anytime.

answered Mar 22, 2017 at 6:42

Louie Liu

1091 silver badge7 bronze badges

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

Collectives™ on Stack Overflow

How to use jsonb index in postgres

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