How to Create Index on JSONB in PostgreSQL for Faster Queries

To create an index on a jsonb column in PostgreSQL, use a GIN index with CREATE INDEX index_name ON table_name USING GIN (jsonb_column);. For indexing specific keys inside the jsonb data, use an expression index like CREATE INDEX index_name ON table_name ((jsonb_column->>'key'));.

Syntax

PostgreSQL supports indexing jsonb columns mainly using GIN indexes for efficient containment queries. You can also create expression indexes on specific JSON keys.

• CREATE INDEX index_name ON table_name USING GIN (jsonb_column);: Creates a GIN index on the entire jsonb column.
• CREATE INDEX index_name ON table_name ((jsonb_column->>'key'));: Creates a B-tree index on a specific key's text value inside the jsonb column.

sql

CREATE INDEX index_name ON table_name USING GIN (jsonb_column);

CREATE INDEX index_name ON table_name ((jsonb_column->>'key'));

Example

This example shows how to create a GIN index on a jsonb column and an expression index on a specific key inside the JSON data. It also demonstrates a query that benefits from these indexes.

sql

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

INSERT INTO products (data) VALUES
  ('{"name": "apple", "category": "fruit", "price": 1.2}'),
  ('{"name": "carrot", "category": "vegetable", "price": 0.8}'),
  ('{"name": "banana", "category": "fruit", "price": 1.1}');

-- Create a GIN index on the entire jsonb column
CREATE INDEX idx_products_data ON products USING GIN (data);

-- Create an expression index on the 'category' key inside jsonb
CREATE INDEX idx_products_category ON products ((data->>'category'));

-- Query using the GIN index for containment
EXPLAIN ANALYZE
SELECT * FROM products WHERE data @> '{"category": "fruit"}';

-- Query using the expression index
EXPLAIN ANALYZE
SELECT * FROM products WHERE data->>'category' = 'fruit';

Output

QUERY PLAN
-------------------------------------------------------------
Bitmap Heap Scan on products  (cost=4.29..12.56 rows=2 width=36) (actual time=0.020..0.022 rows=2 loops=1)
  Recheck Cond: (data @> '{"category": "fruit"}'::jsonb)
  Bitmap Index Scan on idx_products_data  (cost=0.00..4.28 rows=2 width=0) (actual time=0.013..0.013 rows=2 loops=1)
Planning Time: 0.123 ms
Execution Time: 0.038 ms

QUERY PLAN
-------------------------------------------------------------
Index Scan using idx_products_category on products  (cost=0.14..8.16 rows=2 width=36) (actual time=0.009..0.010 rows=2 loops=1)
  Index Cond: ((data ->> 'category'::text) = 'fruit'::text)
Planning Time: 0.098 ms
Execution Time: 0.022 ms

Common Pitfalls

Common mistakes when creating indexes on jsonb include:

• Using a B-tree index directly on a jsonb column, which is not supported.
• Not using GIN indexes for containment queries, leading to slow searches.
• Forgetting to create expression indexes when querying specific keys, causing full table scans.

Always choose the right index type based on your query pattern.

sql

/* Wrong: Trying to create B-tree index on jsonb column (will error) */
-- CREATE INDEX idx_wrong ON products (data);

/* Right: Use GIN index for jsonb column */
CREATE INDEX idx_right ON products USING GIN (data);

Quick Reference

Index Type	Usage	Syntax Example
GIN	Index entire jsonb column for containment queries	CREATE INDEX idx ON table USING GIN (jsonb_column);
Expression (B-tree)	Index specific jsonb key for equality queries	CREATE INDEX idx ON table ((jsonb_column->>'key'));

Key Takeaways

Use GIN indexes to speed up containment queries on jsonb columns.

Create expression indexes on specific jsonb keys for fast equality searches.

Do not use B-tree indexes directly on jsonb columns; they are unsupported.

Choose the index type based on your query pattern for best performance.