GIN index for arrays and JSONB in PostgreSQL - Step-by-Step Execution

Concept Flow - GIN index for arrays and JSONB

Start: Create GIN index

↓

PostgreSQL scans array/JSONB

↓

Extract elements/keys

↓

Store elements in GIN index

↓

Query uses GIN index

↓

Fast search for elements/keys

↓

Return matching rows

This flow shows how PostgreSQL creates a GIN index on arrays or JSONB by extracting elements and storing them for fast search.

Execution Sample

PostgreSQL

CREATE TABLE products(id SERIAL PRIMARY KEY, tags TEXT[]);
CREATE INDEX idx_tags_gin ON products USING GIN(tags);

INSERT INTO products(tags) VALUES
('{red,large}'),
('{blue,small}');

SELECT * FROM products WHERE tags @> '{red}';

Create a GIN index on an array column and query rows containing 'red' tag.

Execution Table

Step	Action	Index State	Query Condition	Result Rows
1	Create table with array column	Empty index
2	Create GIN index on tags	Index ready but empty
3	Insert row with tags {"red", "large"}	Index stores 'red', 'large' for row 1
4	Insert row with tags {"blue", "small"}	Index stores 'blue', 'small' for row 2
5	Query WHERE tags @> '{"red"}'	Index used to find rows with 'red'	tags contains 'red'	Row 1
6	Query WHERE tags @> '{"small"}'	Index used to find rows with 'small'	tags contains 'small'	Row 2
7	Query WHERE tags @> '{"green"}'	Index used but no match	tags contains 'green'	No rows

💡 Queries stop after index finds matching rows or none.

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
Index Content	Empty	{'red': [1], 'large': [1]}	{'red': [1], 'large': [1], 'blue': [2], 'small': [2]}	Used to find rows with 'red'	Used to find matching rows or none

Key Moments - 3 Insights

Why does the GIN index store individual elements instead of whole arrays?

Because GIN indexes each element separately, it can quickly find rows containing specific elements without scanning entire arrays, as shown in execution_table rows 3 and 4.

How does the query use the GIN index to speed up searches?

The query condition uses the index to directly find rows containing the searched element, avoiding full table scans, as seen in execution_table rows 5 and 6.

What happens if the searched element is not in any array?

The index quickly shows no matching rows, so the query returns empty results without scanning the table, as in execution_table row 7.

Visual Quiz - 3 Questions

Look at the execution_table, what elements does the index store after inserting the second row?

A 'red', 'large', 'blue', 'small'

B 'red', 'large' only

C 'blue', 'small' only

D Empty index

💡 Hint

Check the 'Index Content' variable after Step 4 in variable_tracker.

At which step does the query find rows containing the 'red' tag?

A Step 3

B Step 5

C Step 6

D Step 7

💡 Hint

Look at the 'Query Condition' and 'Result Rows' columns in execution_table.

If we query for tags containing 'green', what will the result be according to the execution_table?

A Row 1

B Row 2

C No rows

D All rows

💡 Hint

See the last row in execution_table where no match is found.

Concept Snapshot

GIN index stores each element of arrays or JSONB separately.
It allows fast searches for elements inside these complex types.
Create with: CREATE INDEX idx ON table USING GIN(column);
Queries use operators like @> to find elements.
Index avoids full scans by quickly locating matching rows.

Full Transcript

This visual execution trace shows how PostgreSQL creates and uses a GIN index for arrays and JSONB data. First, a table with an array column is created. Then a GIN index is built on that column. When rows are inserted, the index stores each element separately with references to the row. Queries using conditions like @> search the index for matching elements, returning only relevant rows quickly. If no matching element is found, the index quickly returns no results without scanning the whole table. This makes searching inside arrays or JSONB much faster and efficient.