Indexing JSONB with GIN in PostgreSQL - Step-by-Step Execution

Concept Flow - Indexing JSONB with GIN

Create Table with JSONB Column

↓

Insert JSONB Data

↓

Create GIN Index on JSONB Column

↓

Query Using JSONB Operators

↓

PostgreSQL Uses GIN Index to Speed Up Search

↓

Return Results

This flow shows how to create a table with a JSONB column, add a GIN index on it, and then run queries that use the index to quickly find matching JSON data.

Execution Sample

PostgreSQL

CREATE TABLE products (id serial PRIMARY KEY, info JSONB);
INSERT INTO products (info) VALUES
  ('{"name": "apple", "color": "red"}'),
  ('{"name": "banana", "color": "yellow"}');
CREATE INDEX idx_gin_info ON products USING GIN (info);
SELECT * FROM products WHERE info @> '{"color": "red"}';

Create a table with JSONB data, insert two products, create a GIN index on the JSONB column, then query for products where color is red.

Execution Table

Step	Action	Details	Result
1	Create Table	Table 'products' with 'info' JSONB column created	Table ready
2	Insert Row 1	Insert JSONB {'name': 'apple', 'color': 'red'}	Row inserted
3	Insert Row 2	Insert JSONB {'name': 'banana', 'color': 'yellow'}	Row inserted
4	Create GIN Index	Index 'idx_gin_info' created on 'info' column	Index ready
5	Query	Select rows where info contains {'color': 'red'}	Uses GIN index to find matching rows
6	Return Result	Row with 'apple' returned	Query result: 1 row

💡 Query completes after using GIN index to efficiently find matching JSONB data

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
products table	empty	1 row (apple)	2 rows (apple, banana)	2 rows (apple, banana)	2 rows (apple, banana)	2 rows (apple, banana)
GIN index	none	none	none	created on info	used for query	used for query
Query result	none	none	none	none	1 row (apple)	1 row (apple)

Key Moments - 3 Insights

Why do we create a GIN index on the JSONB column?

The GIN index helps PostgreSQL quickly find rows where the JSONB data matches a condition, as shown in step 5 where the query uses the index to find the 'apple' row efficiently.

What does the operator '@>' mean in the query?

The '@>' operator means 'contains'. In the query (step 5), it checks if the JSONB column contains the key-value pair {'color': 'red'}, which matches the 'apple' row.

Does the GIN index store the whole JSON document?

No, the GIN index stores keys and values in a way that allows fast searching for parts of the JSON, not the entire document, which is why it speeds up queries like in step 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of the 'products' table after step 3?

A 2 rows with 'apple' and 'banana'

B 1 row with 'apple'

C Empty

D 3 rows with 'apple', 'banana', and another

💡 Hint

Check the 'products table' row in variable_tracker after step 3

At which step is the GIN index created?

A Step 2

B Step 5

C Step 4

D Step 6

💡 Hint

Look at the 'Action' column in execution_table where index creation is mentioned

If we query for color 'yellow' instead of 'red', what would change in the execution table?

A No rows would be returned

B Step 5 would find the 'banana' row instead of 'apple'

C The GIN index would not be used

D The table would have more rows

💡 Hint

Consider how the query condition affects which rows match in step 5

Concept Snapshot

PostgreSQL JSONB GIN Index:
- Create table with JSONB column
- Insert JSON data
- Create GIN index on JSONB column
- Query using '@>' operator to check containment
- GIN index speeds up JSONB key/value searches
- Use for fast filtering on JSONB fields

Full Transcript

This visual execution trace shows how to use a GIN index on a JSONB column in PostgreSQL. First, a table named 'products' is created with a JSONB column called 'info'. Then two rows are inserted with JSON data representing products 'apple' and 'banana'. Next, a GIN index is created on the 'info' column to speed up queries. When a query searches for products where the JSONB data contains the key-value pair 'color' equal to 'red', PostgreSQL uses the GIN index to quickly find the matching row with 'apple'. The trace shows each step, the state of the table and index, and how the query result is returned efficiently. Key points include understanding the '@>' operator for containment and how the GIN index stores JSON keys and values for fast searching.