What this usually means
Slow JSONB queries typically mean PostgreSQL is scanning every row sequentially because no suitable index exists or the query pattern cannot use the existing index. JSONB columns are stored as deconstructed binary, so operators like @> (contains) and ? (exists) require specific index types (GIN). Even with an index, querying on deeply nested paths or using functions on indexed expressions can bypass the index. Another common cause is using a B-tree index on the JSONB column itself, which is useless for JSONB operators.
The first ten minutes — establish facts before touching code.
- 1Run EXPLAIN (ANALYZE, BUFFERS) on the slow query and look for 'Seq Scan on table'
- 2Check existing indexes: \di+ table_name — note the index type (btree vs gin)
- 3Test the query with a GIN index: CREATE INDEX idx_gin ON table USING GIN (jsonb_col);
- 4If the query uses a path like data->'key', create an index on that expression: CREATE INDEX idx_path ON table USING GIN ((jsonb_col -> 'key'));
- 5Check for implicit type conversions in WHERE clauses (e.g., comparing text to integer)
- 6Monitor query execution with pg_stat_statements to identify slow JSONB queries
The specific files, logs, configs, and dashboards that usually own this bug.
- searchpg_stat_user_tables — check seq_tup_read vs idx_tup_fetch
- searchpg_stat_user_indexes — see index usage counts
- searchEXPLAIN (ANALYZE, BUFFERS) output — look for Seq Scan and filter rows
- searchPostgreSQL logs — enable log_min_duration_statement to capture slow queries
- searchIndex definitions: \d table_name — check index type and columns
- searchQuery patterns in application code — look for functions wrapped around JSONB columns
- searchpg_class.reltuples and pg_class.relpages — estimate table size
Practical causes, not theory. These are the things you will actually find.
- warningMissing GIN index on the JSONB column
- warningUsing B-tree index on JSONB column (only supports equality on entire JSONB value)
- warningQuerying on a JSONB path without an expression index
- warningUsing JSONB operator that cannot use GIN index, like ?| or ?& on non-existent paths
- warningCasting indexed JSONB expression to a different type in WHERE clause
- warningOutdated statistics leading to poor query plans (ANALYZE needed)
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd a GIN index on the JSONB column: CREATE INDEX idx_gin ON table USING GIN (jsonb_col);
- buildFor path queries, create an expression index: CREATE INDEX idx_path ON table USING GIN ((jsonb_col -> 'path'));
- buildEnsure queries use the same type as the index expression (e.g., text vs integer)
- buildRun ANALYZE after creating indexes to update statistics
- buildUse jsonb_path_ops operator class for faster @> queries: CREATE INDEX idx_gin_pathops ON table USING GIN (jsonb_col jsonb_path_ops);
- buildPartition large tables if JSONB queries are filtered by other columns
- buildConsider using a dedicated column for frequently queried keys instead of JSONB
A fix you cannot prove is a guess. Close the loop.
- verifiedEXPLAIN (ANALYZE, BUFFERS) now shows 'Bitmap Index Scan' or 'Bitmap Heap Scan' instead of Seq Scan
- verifiedQuery execution time drops from seconds to milliseconds
- verifiedpg_stat_user_indexes shows idx_scan increasing for the new index
- verifiedTest with a sample query that exercised the slow path—confirm index usage
- verifiedCheck that the index size is reasonable (GIN indexes can be large, but not huge)
- verifiedRun a load test to ensure the fix holds under concurrent access
Things that make this bug worse or harder to find.
- warningCreating a GIN index on the entire JSONB column when only a few paths are queried (wasteful)
- warningForgetting to use jsonb_path_ops for @> queries, leading to slower index scans
- warningApplying functions like LOWER() or CAST() on the JSONB column in WHERE, which disables index use
- warningOver-indexing: creating many GIN indexes on the same column can slow writes
- warningIgnoring write performance: GIN indexes on JSONB slow down INSERT/UPDATE significantly
- warningAssuming a B-tree index works for JSONB operators (it doesn't)
E-commerce Product Search Slows to a Crawl
Timeline
- 09:15PagerDuty alert: product search API p95 latency > 5s
- 09:20Checked RDS Performance Insights: top query is SELECT * FROM products WHERE attributes @> '{"color": "red"}'
- 09:25EXPLAIN ANALYZE shows Seq Scan on products (2 million rows)
- 09:30Checked indexes: only primary key and a B-tree on created_at
- 09:35Created GIN index: CREATE INDEX idx_gin_attrs ON products USING GIN (attributes);
- 09:40Re-ran EXPLAIN: still Seq Scan!
- 09:45Realized query uses @> with a JSON string, not a JSONB object: '{"color": "red"}'::text vs ::jsonb
- 09:50Fixed query to use proper JSONB: attributes @> '{"color": "red"}'::jsonb
- 09:52EXPLAIN now shows Bitmap Index Scan on idx_gin_attrs, query runs in 20ms
- 10:00Deployed fix, p95 back to 100ms
At 9:15, the on-call phone buzzed with a PagerDuty alert: product search API p95 latency exceeded 5 seconds. The team had recently launched a new feature allowing customers to filter products by arbitrary attributes stored in a JSONB column. The feature worked fine in staging with 10k products, but production had 2 million.
I jumped into RDS Performance Insights and immediately saw the culprit: SELECT * FROM products WHERE attributes @> '{"color": "red"}'. The query was doing a Sequential Scan on the entire products table. I checked indexes: only a primary key and a B-tree on created_at. No GIN index on the JSONB column. That was the obvious fix.
I created a GIN index: CREATE INDEX idx_gin_attrs ON products USING GIN (attributes). But when I re-ran the query with EXPLAIN, it still showed a Sequential Scan! That was confusing. Then I noticed the query used a string literal, not a JSONB value. The @> operator expects a jsonb operand, but PostgreSQL was implicitly casting the string to jsonb? Actually, the literal '{"color": "red"}' was being treated as text. After explicitly casting to jsonb, the index kicked in. Query time dropped from 5 seconds to 20ms. The lesson: always verify the data types in your queries.
Root cause
Missing GIN index on JSONB column and implicit text-to-jsonb casting in the query, which prevented index usage.
The fix
Created a GIN index on the JSONB column and cast the query parameter to jsonb explicitly.
The lesson
Always use EXPLAIN ANALYZE to verify index usage, and ensure query parameters match the index expression's data type.
PostgreSQL provides two index types for JSONB: GIN (Generalized Inverted Index) and B-tree. B-tree indexes on JSONB columns are essentially useless for most queries because they only support equality checks on the entire JSONB value. GIN indexes, on the other hand, decompose the JSONB document into individual keys, values, and array elements, allowing efficient lookup for operators like @>, ?, ?|, ?&, and jsonb_path_exists.
When you create a GIN index on a JSONB column, PostgreSQL builds an index entry for each key/value pair in the JSONB document. This makes queries like WHERE data @> '{"key": "value"}' fast because the index can directly look up the pair. However, the default GIN operator class (jsonb_ops) indexes all possible paths, which can be large. The jsonb_path_ops operator class indexes only the paths used in @> queries, reducing index size and improving speed for that operator.
One nuance: GIN indexes do not support ordering or range queries. They are designed for existence and containment checks. If you need to sort by a JSONB value, you'll need a different approach, like extracting the value into a separate column with a B-tree index.
Often you only query specific paths within JSONB, like data->'user'->>'name'. A GIN index on the entire JSONB column will still be used, but it may be larger than necessary. You can create an expression index on just that path: CREATE INDEX idx_name ON table USING GIN ((data -> 'user' ->> 'name'));. However, note that the ->> operator returns text, so the index will be of type text, not JSONB. The query must match exactly: WHERE (data -> 'user' ->> 'name') = 'Alice' will use the index, while data @> '{"user": {"name": "Alice"}}' will not use this expression index (it uses the GIN index on the column).
Be careful with expression indexes: they are only used when the WHERE clause contains the exact expression. If you wrap it in a function like LOWER(), the index is skipped. Also, expression indexes need to be refreshed manually after table changes if they depend on IMMUTABLE functions. For JSONB, the -> and ->> operators are not immutable (they depend on the input), so the index will be maintained automatically.
Even with a GIN index, certain query patterns can cause sequential scans. The most common is using the wrong data type. For example, attributes @> '{"color": "red"}' is a text literal, not JSONB. PostgreSQL will attempt an implicit cast, but it may not use the index. Always use explicit casting: attributes @> '{"color": "red"}'::jsonb.
Another pattern is using the ? operator on a path that doesn't exist in the index. For instance, WHERE data ? 'key' works with a GIN index on the column, but WHERE data->'nested' ? 'key' may not because the index is on the column, not on the path. You'd need an expression index on (data->'nested').
Also, avoid using functions like JSONB_TYPEOF or JSONB_EXTRACT_PATH in the WHERE clause unless you have a matching expression index. These functions are not indexed by default.
Once you've created indexes, monitor their usage via pg_stat_user_indexes. If an index is never scanned, it's either not needed or the queries aren't using it. Check for missing casts or expression mismatches. Also, keep statistics up to date: run ANALYZE after creating indexes or after large data changes. Outdated statistics can lead the planner to choose a sequential scan even when an index exists.
GIN indexes can become large and slow to update under heavy write load. Consider the trade-off: if your JSONB column is updated frequently, a GIN index will slow down writes significantly. In such cases, you might extract the most-queryed fields into separate columns with B-tree indexes, or use a partial index on a subset of rows. Also, monitor index bloat with pgstattuple and rebuild if necessary.
Frequently asked questions
Can I use a B-tree index on a JSONB column?
Yes, but it only supports equality and range comparisons on the entire JSONB value. It's not useful for operators like @> or ?. For JSONB queries, use GIN.
Why does EXPLAIN show a Seq Scan even after I created a GIN index?
Possible reasons: the query is not using an operator supported by GIN (like =), the index was just created and statistics are not updated (run ANALYZE), or the query uses a different data type (e.g., text vs jsonb). Use EXPLAIN (ANALYZE, BUFFERS) to see if the index is considered.
What is the difference between jsonb_ops and jsonb_path_ops operator classes?
jsonb_ops is the default; it indexes all keys and values. jsonb_path_ops indexes only paths used in @> queries, resulting in a smaller index and faster lookups for @> but not supporting ? or ?|. Choose jsonb_path_ops if your primary query is @>.
How can I speed up queries that sort by a JSONB value?
Extract the value into a separate column with a B-tree index. For example, ALTER TABLE t ADD COLUMN sort_key text; UPDATE t SET sort_key = data->>'key'; CREATE INDEX ON t(sort_key). Then query with ORDER BY sort_key.
Does indexing a JSONB column affect write performance?
Yes, significantly. GIN indexes are costly to maintain on writes because each JSONB value must be decomposed and indexed. For write-heavy workloads, consider partial indexes or extracting fields to separate columns.