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

Question

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)"

For only two rows, an index will never be used.

user330315
– user330315

2017-01-27 14:19:16 +00:00
Commented Jan 27, 2017 at 14:19 — user330315
– user330315, Commented Jan 27, 2017 at 14:19

klin · Accepted Answer · 2017-01-27 14:16:11Z

12

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.

edited Jan 27, 2017 at 14:16

answered Jan 27, 2017 at 0:48

klin

123k15 gold badges240 silver badges262 bronze badges

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

5 Comments

Boutran Over a year ago

Thanks. I can see that the index get used on rextester.com, it still doesn't on my 9.4.x postgres instance, perhaps it's time I upgrade, although I can't find in the release notes which version does the upgrade...

klin Over a year ago

It doesn't depend on the version of the server. See the edited answer.

Boutran Over a year ago

hmm, I tried your example with 1,000,000 rows, and still the index was not used, I wonder how many rows is "sufficient" to cause index usage....

klin Over a year ago

I've tested it on 9.5 windows and 9.4 ubuntu with the results like on rextester. You might be interested in How do I force Postgres to use a particular index? and Why is my index not being used?.

user330315 Over a year ago

@MaxL.: it's not only the total number of rows but also how many rows the optimizer expects to be removed by the where condition. If the condition only removes 100 rows from a million rows, the index won't help. If the condition only leaves a 1000 rows from that million then the index should be used

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related