Is my JSONB query using the index?

The symptom

crm.customers has 412,000 rows and a column attributes jsonb holding documents like {"plan": "pro", "referrer": "ads"}. It has a GIN index, customers_attributes_idx, created with the default operator class, jsonb_ops. Two queries ask the same question:

SELECT id, email FROM crm.customers
WHERE  attributes @> '{"plan": "pro"}';

SELECT id, email FROM crm.customers
WHERE  attributes->>'plan' = 'pro';

The first comes back at once. The second reads the whole table. Same rows out, same index on disk.

What the Plan tab shows

The Plan tab is Pro. Run the first query and click Plan. The scan on customers is a Bitmap Heap Scan fed by a Bitmap Index Scan on customers_attributes_idx. Select the index scan and the conditions block at the bottom of the inspector reads Index Cond: (attributes @> '{"plan": "pro"}'::jsonb). The table wheel beside the scan draws the index hub and the page bitmap, not the full-scan arrow. Under Indexes · customers, customers_attributes_idx is tagged ✓ used.

Run the second and click Plan. One node, Seq Scan on customers, with Filter: ((attributes ->> 'plan'::text) = 'pro'::text) in the conditions block. About 6% of customers are on the pro plan, so the row flow draws a −N drain under the scan for the other 94%, roughly 387,000 of 412,000 rows. The wheel shows the full-scan arrow. Under Indexes · customers, customers_attributes_idx is tagged not chosen. The Findings panel adds Seq Scan on customers, because the scan read 10,000 rows or more and kept a fifth or less of them.

not chosen is the planner's verdict, not an error. It looked at the index and found nothing in it that could answer the question.

Why the index cannot answer ->>

An index does not index a column. It indexes a column for a set of operators, and that set is the operator class. The default class for jsonb, jsonb_ops, stores every key and every value in the document as separate entries. It can answer @> (contains), ? (has key), ?| and ?& (has any, has all of these keys), and the jsonpath operators @? and @@. That is the whole list. The planner can only use an index for an operator in its class. The JSON indexing section of the manual has the full table.

attributes->>'plan' is not on the list. It produces a text value, and = compares that text. The index holds the key plan and the JSON value "pro". It has never seen the text 'pro', and does not know that ->> is how you get from one to the other. So the planner reads the table.

That is also why the first query works. @> '{"plan": "pro"}' asks "does the document contain this", which is exactly what jsonb_ops indexes.

What to do

Three choices, in the order to try them.

Rewrite to containment when the query asks about a fixed key and value. WHERE attributes @> '{"plan": "pro"}' says what WHERE attributes->>'plan' = 'pro' says, with one difference: containment also matches {"plan": ["pro", "free"]}, because an array contains its elements. If your data never puts an array there, the rewrite is free and the existing index answers it.

Add an expression index when you cannot change the query, or when you need <, >, LIKE or an ORDER BY on the value, which no GIN class provides:

CREATE INDEX customers_plan_idx
  ON crm.customers ((attributes->>'plan'));

A B-tree on the expression. From then on attributes->>'plan' = 'pro' is an ordinary indexed equality, and the Plan tab shows an Index Scan or a Bitmap Index Scan on customers_plan_idx. The double parentheses are required, and the expression has to be written exactly as the query writes it.

Switch to jsonb_path_ops when every query on the column uses @>, @? or @@:

CREATE INDEX customers_attributes_idx
  ON crm.customers USING gin (attributes jsonb_path_ops);

It hashes each path together with its value instead of storing keys and values apart, so the index is smaller and @> lookups are faster. It cannot answer ?, ?| or ?& at all. Choose it when you know that.

Reading the JSON itself

To see what is in attributes before you index it, open crm.customers, select a cell in the column, and press Space. The inspector opens the value in Tree view, with Text for the raw document. Click a node in the tree and Path to selected node offers two copies. SQL path, which for a scalar leaf is attributes->>'plan', is the exact expression the index above wants. JSONPath, $.plan, is for @? and @@.

Caveats

• About 6% of the sample's customers carry "plan": "pro". Your drain will show your share, and the finding only fires on an analyzed plan where the scan read at least 10,000 rows and a fifth or less survived.
• The planner will choose a Seq Scan over a usable index when most rows match. customers is 54 MB of heap, and a filter that keeps half of it is cheaper to scan. not chosen with a small drain is the planner being right.
• There are no JSON-path filters in the grid's Filter rows box, on any engine. Filter on JSON in the SQL editor.
• MySQL, MariaDB and SQLite have JSON columns and no GIN. Of the three fixes, the expression index is the one that travels. See MySQL, MariaDB and SQLite.