GIN INDEX on the table, but PostgreSQL EXPLAIN shows me Seq. Scan

Question

I have a table:

CREATE TABLE skill (
    user_id integer NOT NULL,
    data jsonb
);

And gin index on jsonb column:

CREATE INDEX idx_user_id_knowledge ON skill USING gin (data);

INSERT INTO skill(user_id, data)
VALUES (1, '{"technology": "PHP", "knowledge": 77 }'),
       (2, '{"technology": "PHP", "knowledge": 79 }'),
       (3, '{"technology": "PHP", "knowledge": 97 }'),
       (4, '{"technology": "MySQL", "knowledge": 85 }'),
       (5, '{"technology": "MySQL", "knowledge": 89 }');

But when I run EXPLAIN query:

EXPLAIN
SELECT * FROM skill
WHERE data->>'technology' = 'PHP' AND (data->>'knowledge')::NUMERIC > 50;

The output is as follows:

Seq Scan on skill (cost=0.00..41.75 rows=2 width=36)
Filter: (((data ->> 'technology'::text) = 'PHP'::text) AND (((data ->> 'knowledge'::text))::numeric > '50'::numeric))

Why doesn't the query planner use the gin index I created instead of Seq. Scan ?

http://sqlfiddle.com/#!17/35f2c/5

rows=2, that's an almost an empty table. An index scan will never be faster than a sequential scan. — Frank Heikens

JensV JensV · Accepted Answer · 2021-01-14T15:34:26

TLDR Gin indexes don't work on this kind of operator.

Looking at the operators for GIN indexes, you can see that the only supported operators for jsonb types are: ? ?& ?| @> @? @@

See the JSON Function and Operators docs on what these mean. Based on your query, it can only optimize the text comparison if you rewrite your query as such:

SELECT * FROM skill
WHERE data @> '{"technology": "PHP"}' AND (data->>'knowledge')::NUMERIC > 50;

Which yields the following query plan:

Bitmap Heap Scan on skill (cost=16.01..20.03 rows=1 width=32)
Recheck Cond: (data @> '{"technology": "PHP"}'::jsonb)
  Filter: (((data ->> 'knowledge'::text))::numeric > '50'::numeric)
  -> Bitmap Index Scan on idx_user_id_knowledge (cost=0.00..16.01 rows=1 width=0)
     Index Cond: (data @> '{"technology": "PHP"}'::jsonb)

SQLFiddle to play around in

After some further fiddling, your could instead use a normal index which targets these json fields directly:

CREATE INDEX idx_user_id_knowledge ON skill  ((data->>'technology'), ((data->>'knowledge')::NUMERIC));

Which can be used for both conditions in your query.

Updated fiddle

GIN INDEX on the table, but PostgreSQL EXPLAIN shows me Seq. Scan

1 Answers