Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A jsonb query is slow and the GIN index is never used

ErrorSeq Scan on a table with a GIN index on the jsonb column

Short answer

A GIN index on a jsonb column only helps queries whose operator it supports and whose left side is exactly the indexed expression. The json type cannot be indexed this way at all. Check the column type, then match operator, operator class and expression, and confirm with EXPLAIN.

  • Applies to PostgreSQL 16 to 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the column type, the indexes on the table and the plan of the slow query. The plan shows a sequential scan when the index is not usable.

    SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'api';
    SELECT indexdef FROM pg_indexes WHERE tablename = 'api';
    EXPLAIN SELECT * FROM api WHERE jdoc @> '{"company": "Magnafone"}';
  2. 2Match what you see, then jump to its fix

    1. 1data_type says json in the first queryThe column is json, not jsonbFix
    2. 2The index is on (jdoc) but the query is WHERE jdoc -> 'tags' ? 'qui'The query applies the operator to a sub-expression the index does not coverFix
    3. 3The index uses jsonb_path_ops and the query uses ?, ?| or ?&The operator class does not support the operator in the queryFix

What does the error look like?

  • Seq Scan on a table with a GIN index on the jsonb column
  • WHERE doc -> 'tags' ? 'qui' takes seconds on a large table

Cause 1 of 3

The column is json, not jsonb

1Check

The jsonb indexing features described in the manual apply only to jsonb. The json type stores an exact copy of the text and has no equivalent containment or indexing support.

SELECT data_type FROM information_schema.columns WHERE table_name = 'api' AND column_name = 'jdoc';

2Fix

Convert the column to jsonb, then create the GIN index.

ALTER TABLE api ALTER COLUMN jdoc TYPE jsonb USING jdoc::jsonb;
CREATE INDEX idxgin ON api USING GIN (jdoc);

3Verify

The plan now mentions the index.

EXPLAIN SELECT * FROM api WHERE jdoc @> '{"company": "Magnafone"}';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The query applies the operator to a sub-expression the index does not cover

1Check

A GIN index on the whole column cannot serve a query that applies ? to jdoc -> 'tags', because the operator is not applied directly to the indexed column.

EXPLAIN SELECT * FROM api WHERE jdoc -> 'tags' ? 'qui';

2Fix

Either rewrite the query as a containment check on the whole column, or add an index on exactly that expression. Expression indexes are smaller and faster, the simple index is more flexible.

SELECT * FROM api WHERE jdoc @> '{"tags": ["qui"]}';
-- or
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

3Verify

Re-run EXPLAIN and look for a bitmap scan on the index.

EXPLAIN SELECT * FROM api WHERE jdoc @> '{"tags": ["qui"]}';

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The operator class does not support the operator in the query

1Check

jsonb_path_ops supports only @>, @? and @@. The key-exists operators ?, ?| and ?& need the default jsonb_ops.

SELECT indexdef FROM pg_indexes WHERE tablename = 'api';

2Fix

Use the default operator class if you need the key-exists operators. Keep jsonb_path_ops when you only use containment, because it is usually smaller and more specific.

DROP INDEX idxginp;
CREATE INDEX idxgin ON api USING GIN (jdoc);

3Verify

A key-exists query now uses the index.

EXPLAIN SELECT * FROM api WHERE jdoc ? 'company';

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Store queryable documents as jsonb, never json.
  • Write the index and the query from the same expression, then keep EXPLAIN output in the pull request.
  • Choose jsonb_path_ops only if every query uses @>, @? or @@.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.