6

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)"
klin
  • 112,967
  • 15
  • 204
  • 232
Max L.
  • 9,774
  • 15
  • 56
  • 86

1 Answers1

11

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • 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... – Max L. Jan 27 '17 at 14:04
  • It doesn't depend on the version of the server. See the edited answer. – klin Jan 27 '17 at 14:14
  • 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.... – Max L. Jan 27 '17 at 14:37
  • 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?](http://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index) and [Why is my index not being used?](https://www.depesz.com/2010/09/09/why-is-my-index-not-being-used/). – klin Jan 27 '17 at 14:53
  • @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 –  Jan 27 '17 at 15:00