liveBook · Manning

Operator class

As explained in Just Use Postgres!

A PostgreSQL operator class defines the operators and comparison semantics that an index access method uses for a data type.

An operator class selects the operators and comparison semantics an index access method uses for a data type. With PostgreSQL GIN indexes on JSON data, different classes organize indexed information differently and support distinct data-access behavior. section 5.6.2

For JSONB GIN indexes, the available choices shown here are:

  • The default class extracts keys, values, and array elements as separate index items. section 5.6.2
  • jsonb_path_ops indexes paths from the document root to values and array elements. section 5.6.2

Choose jsonb_path_ops for a path-oriented GIN index

CREATE INDEX idx_pizza_orders_paths_ops_gin ON pizzeria.order_items USING GIN (pizza jsonb_path_ops); A containment query using @> uses this index through a Bitmap Index Scan and returns 563 rows in the demonstrated plan. section 5.6.2

Listing 5.16 Creating a default GIN index
CREATE INDEX idx_pizza_orders_gin 
ON pizzeria.order_items 
USING GIN(pizza);

Inspect the complete default GIN index definition before selecting an alternative operator class. section 5.6.2

How Just Use Postgres! covers it

How deep it goes
Explains two JSON GIN organization strategies and demonstrates index plans, but stops short of defining operator-class internals.
Examples
Uses pizza-order JSON, CREATE INDEX statements, EXPLAIN plans, and a 112 kB versus 56 kB size comparison.
What it assumes
Assumes familiarity with JSON data and GIN indexes while demonstrating their setup through executable SQL.
What you can do after
Operate/configure GIN indexes by choosing the default or jsonb_path_ops class for JSONB containment searches.
How it connects
Connects operator classes with PostgreSQL, GIN indexes, JSONB, containment operators, and Bitmap Index Scans.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

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.