liveBook · Manning

Jsonb_ops

As explained in Just Use Postgres!

jsonb_ops is PostgreSQL's default GIN operator class for JSONB values that indexes distinct keys, values, and array elements.

jsonb_ops is PostgreSQL’s default GIN operator class for JSONB. It indexes distinct keys, values, and array elements, allowing searches through nested JSON structures. section 5.6.2

Use a default GIN index when JSONB queries rely on these operators:

  • Support key-exists (?) and containment (@>) queries, plus jsonpath matches (@? and @@). section 5.6.2
  • Expect Bitmap Index Scan followed by Bitmap Heap Scan and condition rechecking because the index stores partial data. section 5.6.2

Query nested pizza data

CREATE INDEX idx_pizza_orders_gin ON pizzeria.order_items USING GIN(pizza);\n\nEXPLAIN (analyze, costs off) SELECT count(*) FROM pizzeria.order_items WHERE pizza @> '{"type": "custom"}';\n\nThe plan uses idx_pizza_orders_gin through a Bitmap Index Scan and returns 563 matching rows. Nested containment can also trigger rechecks: a cheese query examined 728 index candidates and retained 638 rows.

How Just Use Postgres! covers it

How deep it goes
Explains index contents, bitmap execution, and rechecks, but does not develop implementation internals.
Examples
Uses pizza orders, nested toppings, containment predicates, EXPLAIN plans, and index-size comparisons.
What you can do after
Operate/configure PostgreSQL JSONB indexes and verify their use with EXPLAIN.
How it connects
Connects GIN indexes with JSONB, containment operators, key-exists operators, and jsonpath queries.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.