PostgreSQL JSON Index

Summary: in this tutorial, you will learn how to create a PostgreSQL JSON index for a JSONB column to improve query performance.

Introduction to PostgreSQL JSON index

JSONB (binary JSON) is a data type that allows you to store JSON data and query it efficiently.

When a JSONB column has a complex JSON structure, utilizing an index can significantly improve query performance.

PostgreSQL uses the GIN index type for indexing a column with JSONB data type. GIN stands for Generalized Inverted Index.

Note that you can utilize the GIN index for tsvector or array columns.

To create a GIN index for a JSONB column, you can use the following CREATE INDEX statement:

CREATE INDEX index_name
ON table_name 
USING GIN(jsonb_column);Code language: SQL (Structured Query Language) (sql)

This statement creates a GIN index on the jsonb_column. This GIN index is suitable for general-purpose queries on JSONB data.

When creating a GIN index on a JSONB column, you can use a specific GIN operator class.

The operator class determines how PostgreSQL builds the index and how it optimizes the queries on the indexed column.

For example, The following CREATE INDEX statement creates a GIN index on the jsonb_coumn with jsonb_path_ops operator class:

CREATE INDEX index_name
ON table_name 
USING GIN(jsonb_column jsonb_path_ops);Code language: SQL (Structured Query Language) (sql)

This index is optimized for the queries that use the @> (contains), ? (exists), and @@ JSONB operators. It can be useful for searches involving keys or values within JSONB documents.

The following table displays the GIN operator classes:

Note that if you don’t explicitly specify a GIN operator class, the statement will use the jsonb_ops operator by default, which is suitable for most cases.

Additionally, PostgreSQL allows you to create a GIN index for a specific field in JSON documents as follows:

CREATE INDEX index_name 
ON table_name 
USING GIN ((data->'field_name') jsonb_path_ops);Code language: SQL (Structured Query Language) (sql)

This index can improve the queries that involve searching values within the field_name of JSON documents stored in the JSONB column (data).

PostgreSQL JSON index examples

We’ll use the tables in the sample database.

1) Setting up a sample table

First, create a new table called customer_json that stores the customer information in JSON format:

CREATE TABLE customer_json(
   id SERIAL PRIMARY KEY,
   data JSONB NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert data from the customer, address, city, and country tables into the customer_json table:

WITH json_cte AS(
  SELECT 
    jsonb_build_object(
      'first_name',  first_name, 
      'last_name',  last_name, 
      'email',  email, 
      'phone',  a.phone, 
      'address', 
      jsonb_build_object(
        'address', a.address, 
        'city', i.city, 
        'postal_code', a.postal_code, 
        'district',  a.district, 
        'country', o.country
      )
    ):: jsonb AS data 
  FROM 
    customer c 
    INNER JOIN address a ON a.address_id = c.address_id 
    INNER JOIN city i ON i.city_id = a.city_id 
    INNER JOIN country o ON o.country_id = i.country_id
) 
INSERT INTO customer_json(data) 
SELECT 
  data 
FROM 
  json_cte;Code language: SQL (Structured Query Language) (sql)

Third, retrieve the email of the customer whose first name is John:

SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}'; Code language: SQL (Structured Query Language) (sql)

Output:

 first_name | last_name  |    phone
------------+------------+-------------
 John       | Farnsworth | 51917807050
(1 row)Code language: SQL (Structured Query Language) (sql)

Finally, explain and analyze the above query:

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}'; Code language: SQL (Structured Query Language) (sql)

Output:

                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on customer_json  (cost=0.00..31.50 rows=1 width=96) (actual time=0.063..0.118 rows=1 loops=1)
   Filter: (data @> '{"first_name": "John"}'::jsonb)
   Rows Removed by Filter: 598
 Planning Time: 1.109 ms
 Execution Time: 0.128 ms
(5 rows)Code language: SQL (Structured Query Language) (sql)

The output indicates that PostgreSQL has to scan the entire customer_json table to search for the customer.

To improve the performance of the query, you can create a GIN index on the data column of the customer_json table.

2) Creating an index on the JSONB column

First, create an index on the data column of the customer_json table:

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN(data);Code language: SQL (Structured Query Language) (sql)

Second, execute the query that searches for the customer whose first name is John:

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}'; Code language: SQL (Structured Query Language) (sql)

Output:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_json  (cost=21.51..25.53 rows=1 width=96) (actual time=0.024..0.024 rows=1 loops=1)
   Recheck Cond: (data @> '{"first_name": "John"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on customer_json_index  (cost=0.00..21.51 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)
         Index Cond: (data @> '{"first_name": "John"}'::jsonb)
 Planning Time: 0.164 ms
 Execution Time: 0.045 ms
(7 rows)Code language: SQL (Structured Query Language) (sql)

The query plan indicates that PostgreSQL uses the customer_json_index to improve the performance.

This time, the execution time is significantly smaller 0.045ms vs. 0.128 ms, about 2 – 3 times faster than a query without using the GIN index.

3) Creating an index on the JSONB column with the GIN operator class

First, drop the customer_json_index index:

DROP INDEX customer_json_index;Code language: SQL (Structured Query Language) (sql)

Second, create a GIN index on the data column of the customer_json table with a GIN operator class:

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN(data jsonb_path_ops);Code language: SQL (Structured Query Language) (sql)

Third, explain the query that finds the customer whose first name is John:

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}'; Code language: SQL (Structured Query Language) (sql)

Output:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_json  (cost=12.82..16.84 rows=1 width=96) (actual time=0.014..0.015 rows=1 loops=1)
   Recheck Cond: (data @> '{"first_name": "John"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on customer_json_index  (cost=0.00..12.82 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
         Index Cond: (data @> '{"first_name": "John"}'::jsonb)
 Planning Time: 0.120 ms
 Execution Time: 0.034 ms
(7 rows)Code language: SQL (Structured Query Language) (sql)

The query plan shows that the query does use the customer_json_index for improved performance.

Finally, explain the query that searches for the customer where the value in the first_name field within the data column is John:

EXPLAIN ANALYZE 
SELECT * FROM customer_json
WHERE data->>'first_name' = 'John';Code language: SQL (Structured Query Language) (sql)

Output:

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on customer_json  (cost=0.00..32.98 rows=3 width=275) (actual time=0.161..0.284 rows=1 loops=1)
   Filter: ((data ->> 'first_name'::text) = 'John'::text)
   Rows Removed by Filter: 598
 Planning Time: 0.085 ms
 Execution Time: 0.298 ms
(5 rows)Code language: SQL (Structured Query Language) (sql)

In this plan, the query cannot fully utilize the GIN index customer_json_index. The reason is that the query does not use the JSONB operator (@, @?, @@) that the jsonb_path_ops operator class is optimized for.

4) Creating an index on a specific field of a JSONB column

First, drop the customer_json_index index:

DROP INDEX customer_json_index;Code language: SQL (Structured Query Language) (sql)

Second, create a GIN index on the first_name field of the customer_json table using the GIN operator class:

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN((data->'first_name'));Code language: SQL (Structured Query Language) (sql)

Third, explain the query that finds the rows where the “first_name” field in the data JSONB column contains the value "John":

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data->'first_name' @> '"John"';Code language: SQL (Structured Query Language) (sql)

Output:

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on customer_json  (cost=8.58..23.72 rows=6 width=96) (actual time=0.031..0.032 rows=1 loops=1)
   Recheck Cond: ((data -> 'first_name'::text) @> '"John"'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on customer_json_index  (cost=0.00..8.58 rows=6 width=0) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: ((data -> 'first_name'::text) @> '"John"'::jsonb)
 Planning Time: 0.167 ms
 Execution Time: 0.133 ms
(7 rows)Code language: SQL (Structured Query Language) (sql)

The output indicates that the query uses the customer_json_index index.

Summary

• Use the GIN index to create an index for a JSONB column of a table to improve query performance.