PostgreSQL 教程: JSON 索引

六月 3, 2024

摘要：在本教程中，您将学习如何在 PostgreSQL 中为 JSONB 类型的列创建 JSON 索引，以提高查询性能。

PostgreSQL JSON 索引简介

JSONB（二进制 JSON）是一种数据类型，允许您存储 JSON 数据并对其进行高效查询。

当一个 JSONB 列具有复杂的 JSON 结构时，使用索引可以显著提高查询性能。

PostgreSQL 使用GIN索引类型来索引具有 JSONB 数据类型的列。GIN的意思是通用倒排索引。

请注意，您可以将 GIN 索引用于 tsvector 或数组列。

要为 JSONB 列创建GIN索引，可以使用下面的CREATE INDEX语句：

CREATE INDEX index_name
ON table_name 
USING GIN(jsonb_column);

此语句在jsonb_column上创建GIN索引。这种GIN索引适用于对 JSONB 数据的通用查询。

在一个 JSONB 列上创建GIN索引时，可以使用特定的GIN操作符类。

操作符类确定 PostgreSQL 如何构建索引，以及如何优化索引列上的查询。

例如，下面的CREATE INDEX语句，使用jsonb_path_ops操作符类在jsonb_coumn上创建GIN索引：

CREATE INDEX index_name
ON table_name 
USING GIN(jsonb_column jsonb_path_ops);

该索引针对使用 @> (contains)、? (exists) 和 @@ 的 JSONB 运算符的查询做了优化。它对于涉及 JSONB 文档中的键值搜索非常有用。

下表显示了GIN操作符类：

名称	可索引的操作符
`array_ops`	`&& (anyarray,anyarray)`
`@> (anyarray,anyarray)`
`<@ (anyarray,anyarray)`
`= (anyarray,anyarray)`
`jsonb_ops`	`@> (jsonb,jsonb)`
`@? (jsonb,jsonpath)`
`@@ (jsonb,jsonpath)`
`? (jsonb,text)`
`?\| (jsonb,text[])`
`?& (jsonb,text[])`
`jsonb_path_ops`	`@> (jsonb,jsonb)`
`@? (jsonb,jsonpath)`
`@@ (jsonb,jsonpath)`
`tsvector_ops`	`@@ (tsvector,tsquery)`
`@@@ (tsvector,tsquery)`

请注意，如果您没有显式指定GIN操作符类，则该语句将默认使用jsonb_ops操作符，这适用于大多数情况。

此外，PostgreSQL 允许您为 JSON 文档中的特定字段创建GIN索引，如下所示：

CREATE INDEX index_name 
ON table_name 
USING GIN ((data->'field_name') jsonb_path_ops);

如果有一个 JSON 文档存储在 JSONB 列中，当查询涉及在该 JSON 文档数据的字段field_name中搜索值时，该索引可以改进查询性能。

PostgreSQL JSON 索引示例

我们将使用示例数据库中的表。

1) 设置一个示例表

首先，创建一个新表，名为customer_json，以 JSON 格式存储客户信息：

CREATE TABLE customer_json(
   id SERIAL PRIMARY KEY,
   data JSONB NOT NULL
);

第二步，将customer、address、city和country表中的数据插入到customer_json表中：

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;

第三步，检索名为John的客户的电子邮件：

SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}';

输出：

 first_name | last_name  |    phone
------------+------------+-------------
 John       | Farnsworth | 51917807050
(1 row)

最后，对上面的查询进行解释和分析：

EXPLAIN ANALYZE  
SELECT
   data ->> 'first_name' first_name,
   data ->> 'last_name' last_name,
   data ->> 'phone' phone
FROM
   customer_json
WHERE
   data @> '{"first_name": "John"}';

输出：

                                               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)

输出表明 PostgreSQL 必须扫描整个customer_json表来搜索客户。

要提升查询的性能，可以在customer_json表的数据列上创建GIN索引。

2) 在 JSONB 列上创建索引

首先，在customer_json表的data列上创建一个索引：

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN(data);

第二步，执行查询，搜索名为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"}';

输出：

                                                         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)

查询计划表明 PostgreSQL 使用了customer_json_index来提升性能。

这一次，执行时间明显缩短了，0.045ms对比0.128ms，比不使用GIN索引的查询快了 2 - 3 倍。

3) 使用 GIN 操作符类在 JSONB 列上创建索引

首先，删除索引customer_json_index：

DROP INDEX customer_json_index;

第二步，使用GIN操作符类，在customer_json表的 data 列上创建GIN索引：

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN(data jsonb_path_ops);

第三步，解释查询，该查询查找名为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"}';

输出：

                                                         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)

查询计划显示查询确实使用了customer_json_index来提升性能。

最后，解释查询，该查询搜索 data 列中first_name字段中的值为 John 的客户：

EXPLAIN ANALYZE 
SELECT * FROM customer_json
WHERE data->>'first_name' = 'John';

输出：

                                                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)

在此计划中，查询无法充分利用GIN索引customer_json_index。原因是查询没有使用 JSONB 操作符（@、@?、@@），而jsonb_path_ops操作符类是针对这些操作符进行优化的。

4) 在 JSONB 列的特定字段上创建索引

首先，删除索引customer_json_index：

DROP INDEX customer_json_index;

第二步，使用GIN操作符类，在customer_json表的first_name字段上创建GIN索引：

CREATE INDEX customer_json_index 
ON customer_json 
USING GIN((data->'first_name'));

第三步，解释查询，该查询查找 JSONB 列data中的 “first_name“ 字段包含值"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"';

输出：

                                                         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)

输出表明查询使用了customer_json_index索引。

总结

使用GIN索引为表的 JSONB 列创建索引，以提升查询性能。