PostgreSQL中的索引：B-tree、GIN及使用时机

1. 概述

PostgreSQL支持多种索引类型，但B-tree和GIN是日常工作中最常见的两种。选错索引类型会导致查询变慢或索引占用空间却没有帮助。

本文以足够用的程度解释B-tree和GIN的工作原理、正确的索引创建方式、如何读取EXPLAIN来判断是否使用了索引，以及选择合适索引类型的checklist。阅读完后，你可以自信地为Order、Product表添加索引，不需要盲目猜测。

2. B-tree索引：默认且最常见的

2.1. B-tree如何工作

B-tree（平衡树）以排序的方式存储值，允许查找、比较（<、>、BETWEEN）和排序（ORDER BY）非常快速——时间复杂度为O(log n)。这是不指定方法时运行CREATE INDEX时的默认索引类型。

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

CREATE INDEX这一行在customer_id列上创建B-tree。当查询WHERE customer_id = 123时，PostgreSQL遍历树而不是全表扫描(sequential scan)。

2.2. 检查索引是否被使用

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scan意味着规划器选择了使用索引。如果看到Seq Scan尽管已有索引，通常是因为表太小（规划器认为全表扫描更便宜）或统计信息过期——运行ANALYZE customer_order;更新统计。

2.3. 复合索引和列顺序

B-tree支持多列，但列的顺序决定了效率。

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- 能使用完整的索引
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- 只能使用索引的customer_id部分
SELECT * FROM customer_order WHERE customer_id = 123;

-- 无法使用这个索引（缺少customer_id）
SELECT * FROM customer_order WHERE status = 'PAID';

原理：复合索引就像按(customer_id, status)排序的通讯录——你可以按customer_id查询，但不能只按status直接查询。

注意： 如果多个列都独立用于查询，将选择性高的列（值差异大）放在前面。如果总是一起查询这些列，则将过滤效果最强的列放在前面，以便树快速缩小范围。

3. GIN索引：处理“一行中多个值”的数据

3.1. B-tree何时失效

B-tree对于一行包含多个“元素”需要独立查询的数据类型效率不高：数组（array）、JSON（jsonb）、全文搜索（tsvector）。GIN（广义倒排索引）就是为这种情况而生的——它为每个元素分别建立索引，而不是整个值。

3.2. GIN用于JSONB

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes)在整个JSONB列上创建索引。@>操作符是GIN最优化的——它检查左侧JSON是否包含右侧结构，无需每次都重新解析整个document。

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. GIN用于全文搜索

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

search_vector列是自动生成的，从name生成tsvector。这列上的GIN索引使@@（匹配）在大文本数据上运行迅速，而不是用LIKE '%laptop%'进行全表扫描。

3.4. GIN用于数组

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

类似JSONB，GIN为数组tag_list中的每个元素建立索引，所以查询“包含标签X的文章”比在大表上使用B-tree快很多倍。

4. 比较和成本

标准	B-tree	GIN
适合的数据	标量：数字、字符串、日期	数组、jsonb、tsvector
支持的操作符	=、<、>、BETWEEN、ORDER BY	@>、?、@@、&&
写入速度(INSERT/UPDATE)	快	较慢，特别是大型JSONB的UPDATE
索引大小	较小	较大（为每个元素存储条目）
索引构建速度	快	在大表上较慢

GIN在写入成本上更昂贵，因为每次INSERT/UPDATE都必须更新多个条目（JSON/数组中每个元素一个），而不是B-tree的一个条目。

-- 检查索引的实际大小
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. 验证索引工作（与Spring Boot集成）

当使用Spring Data JPA时，通常想在部署前验证索引是否正常工作。使用Testcontainers在真实的PostgreSQL上进行测试。

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

这个测试执行EXPLAIN（不需要ANALYZE），将结果行聚合成一个字符串，然后检查计划是否提及预期的索引名。这是确认索引实际参与查询计划，而不仅仅是“在schema中存在”的方法。

注意： EXPLAIN不执行真实查询，只询问规划器。要知道实际执行时间，使用EXPLAIN ANALYZE——但记住它确实执行了语句，除非在回滚的事务中，否则避免用于DELETE/UPDATE。

6. 选择索引类型的清单

• 按标量列（数字、字符串、日期）用=、<、>过滤，或需要ORDER BY → B-tree。
• 按多列同时过滤，类型为“AND” → 复合B-tree，选择性高的列或独立使用的列放在前面。
• 在JSONB列上查询，使用@>、?、?&、?| → GIN。
• 全文搜索（@@与tsvector) → GIN。
• 在数组上查询，使用@>、&&、<@ → GIN。
• 表写入多、读取少，JSONB数据经常变化 → 考虑不要在整列上建GIN，只在需要的子表达式上建（表达式索引）。

-- 表达式索引：只在JSONB中的一个特定key上建索引，比整列GIN轻量
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

这个表达式索引实际上是在attributes ->> 'brand'结果（文本值）上的B-tree，适合当你总是按同一个固定的key查询，不需要GIN的灵活性时。

7. 常见错误

• 在低基数列上创建索引（例如在百万行表上status只有3个值）：规划器通常跳过索引，选择Seq Scan因为更便宜。不是有索引就一定会被用。
• 插入大量数据后忘记ANALYZE：规划器基于旧统计，估计行数错误，选择糟糕的计划。
• 对不常改变但写入频繁的列使用GIN：索引更新的成本积累大于查询收益——在决定前用EXPLAIN ANALYZE测试。
• 复合B-tree列顺序错误：把很少使用的列放在前面，导致索引对实际查询几乎无用。
• 在一个表上创建太多索引：每个INSERT/UPDATE都必须更新所有相关索引，减慢写入路径而不是所有索引都被使用。

8. 结论

B-tree是默认选择，适合大多数标量数据的过滤和排序条件。GIN专门用于“多值”数据，如JSONB、数组、全文搜索——B-tree无法有效表示的地方。

添加索引前，总是用EXPLAIN ANALYZE检查规划器是否真的使用它，如果表有高INSERT/UPDATE频率则测量写入成本。索引不是“以防万一而添加”——每个索引都是读取速度和写入速度之间的权衡。