CODEWITHSUNDEEP
arrayspostgresqlindexingjsonb
Boutran
Boutran

Reputation: 10124

postgres: why does this GIN index not used for this "object in array" query

I am trying to index a JSONB column that contains array of objects :

create table tmp_t (a INTEGER PRIMARY KEY,o jsonb);

insert into tmp_t (a,o)  values(1, '[{"frame": 1, "accession": "NM_001184642.1"}]');
insert into tmp_t (a,o)  values (2, '[{"frame": 3, "accession": "NM_001178208.1"}]');

CREATE INDEX idx_tmp_t ON tmp_t USING gin (o);

EXPLAIN tells me the following query does not use the index :

EXPLAIN
SELECT * from tmp_t v where v.o @> '[{"accession": "NM_001178208.1"}]';

explain result:

QUERY PLAN
Seq Scan on tmp_t v  (cost=0.00..1.02 rows=1 width=36)
  Filter: (o @> '[{""accession"": ""NM_001178208.1""}]'::jsonb)

My setup seems identical to the one given in answer to this question :

Using indexes in json array in PostgreSQL

I have created the example table in the question, and the index does get used : 

"QUERY PLAN"
"Bitmap Heap Scan on tracks  (cost=16.01..20.02 rows=1 width=36)"
"  Recheck Cond: (artists @> '[{""z"": 2}]'::jsonb)"
"  ->  Bitmap Index Scan on tracks_artists_gin_idx  (cost=0.00..16.01 rows=1 width=0)"
"        Index Cond: (artists @> '[{""z"": 2}]'::jsonb)"

Upvotes: 7

Views: 5704

Answers (1)

klin
klin

Reputation: 121634

Actually the index is used, just use larger test data.

The planner can choose different plans depending on the data. It often happens that the planner doesn't use an index on a dataset with a small number of rows and starts using it when amount of data grows.

Upvotes: 12

Related Questions