A jsonb query is slow and the GIN index is never used
Seq Scan on a table with a GIN index on the jsonb columnShort 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.
Fix it fast
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"}';2Match what you see, then jump to its fix
- 1
data_type says json in the first queryThe column is json, not jsonbFix - 2
The 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
The index uses jsonb_path_ops and the query uses ?, ?| or ?&The operator class does not support the operator in the queryFix
- 1
What does the error look like?
Seq Scan on a table with a GIN index on the jsonb columnWHERE 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
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix