How to Use GIN Index on JSONB in PostgreSQL

To use a GIN index on a jsonb column in PostgreSQL, create the index with CREATE INDEX index_name ON table_name USING GIN (jsonb_column); This index speeds up queries that search or filter inside JSONB data efficiently.

Syntax

The basic syntax to create a GIN index on a jsonb column is:

• CREATE INDEX index_name: Names the index.
• ON table_name: Specifies the table.
• USING GIN: Chooses the GIN index type, optimized for jsonb.
• (jsonb_column): The jsonb column to index.

sql

CREATE INDEX index_name ON table_name USING GIN (jsonb_column);

Example

This example shows creating a table with a jsonb column, inserting data, creating a GIN index, and querying using the index for fast search.

sql

CREATE TABLE products (id serial PRIMARY KEY, info jsonb);

INSERT INTO products (info) VALUES
  ('{"name": "apple", "color": "red", "price": 1.2}'),
  ('{"name": "banana", "color": "yellow", "price": 0.8}'),
  ('{"name": "cherry", "color": "red", "price": 2.5}');

CREATE INDEX idx_products_info ON products USING GIN (info);

-- Query to find products with color red
SELECT * FROM products WHERE info @> '{"color": "red"}';

Output

 id |                 info                 
----+------------------------------------
  1 | {"name": "apple", "color": "red", "price": 1.2}
  3 | {"name": "cherry", "color": "red", "price": 2.5}
(2 rows)

Common Pitfalls

Common mistakes when using GIN indexes on jsonb include:

• Not using the @> operator for queries, which can use the index.
• Creating the index on the wrong column or without specifying USING GIN.
• Expecting the index to speed up all JSONB queries; some operators like -> or ->> do not use GIN indexes.

sql

/* Wrong: No GIN index created */
CREATE INDEX idx_wrong ON products (info);

/* Right: Create GIN index */
CREATE INDEX idx_right ON products USING GIN (info);

/* Query that uses index */
SELECT * FROM products WHERE info @> '{"color": "red"}';

Quick Reference

Command	Description
CREATE INDEX idx ON table USING GIN (jsonb_column);	Create a GIN index on a jsonb column
jsonb_column @> '{"key": "value"}'	Query to check if jsonb contains a key-value pair, uses GIN index
jsonb_column -> 'key'	Extract JSON object field, does NOT use GIN index
jsonb_column ->> 'key'	Extract JSON text field, does NOT use GIN index

Key Takeaways

Create a GIN index on a jsonb column using: CREATE INDEX idx ON table USING GIN (jsonb_column);

Use the @> operator in queries to leverage the GIN index for fast JSONB containment searches.

Not all JSONB operators use the GIN index; only containment queries benefit.

Always verify the index is used by checking query plans with EXPLAIN.

Avoid creating regular indexes on jsonb columns without USING GIN for JSON search optimization.