0

I need to optimize below query on jsonb field :

example :

create table sample (id char(10) pk, doc1 jsonb);

sample data:

 ('abcd',{"_id": "__default:5cd3e-f49f", "status": "updated"}

  select * from sample where doc1->>'_id'='__default:5cd3e-f49f';

I have tried to create index :

 create index idx1 on doc1 using gin (doc1) -- not picked

 create index idx2 on doc2 using gin ((doc1->>'id') - no luck

my query plan remains same, its not picking any of above index. and the query execution time is very high :

 Gather  (cost=1000.00..90780.97 rows=2938 width=1108) (actual time=0.277..124.337 rows=1 loops=1)
   Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Seq Scan on sample  (cost=0.00..89487.17 rows=1224 width=1108) (actual time=79.340..120.226 rows=0 loops=3)
     Filter: ((doc1 ->> '_id'::text) = '__default:5cd3e-f49f'::text)
     Rows Removed by Filter: 195527
    Planning Time: 0.054 ms
    Execution Time: 124.359 ms
naveen_sfx
  • 727
  • 2
  • 9
  • 18

1 Answers1

0

The below index worked , query execution time improved .

 create index idx3 on doc2 ((doc1->>'_id'));
naveen_sfx
  • 727
  • 2
  • 9
  • 18