CODEWITHSUNDEEP
postgresqlindexingjsonb
Sogawa-sps
Sogawa-sps

Reputation: 159

Postgresql doesn't use GIN index for "?" JSON operator

By some reason index is not used for "?" operator.

Let's take this sample https://schinckel.net/2014/05/25/querying-json-in-postgres/ :

  CREATE TABLE json_test (
  id serial primary key,
  data jsonb
  );

  INSERT INTO json_test (data) VALUES 
  ('{}'),
  ('{"a": 1}'),
  ('{"a": 2, "b": ["c", "d"]}'),
  ('{"a": 1, "b": {"c": "d", "e": true}}'),
  ('{"b": 2}');

And create an index.

create index json_test_index on public.json_test using gin (data jsonb_path_ops) tablespace pg_default;

Then take a look at plan of the following query:

SELECT * FROM json_test WHERE data ? 'a';

There will be Seq Scan while I would expect an index scan. Could please somebody advise what's wrong here?

Upvotes: 0

Views: 443

Answers (1)

jjanes
jjanes

Reputation: 44202

From the docs: "The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only." It doesn't support the ? operator.

So use the default operator for jsonb instead (called "jsonb_ops", if you wish to spell it out explicitly).

But if your table only has 5 rows, it probably won't use the index anyway, unless you force it by set enable_seqscan = off.

Upvotes: 1

Related Questions