PostgreSQL六大索引解析PostgreSQL六大索引：B-tree（默认，支持等值、范围、排序）、Hash（仅等

每种索引都针对特定场景设计，合理使用效果显著。

下面我们就来详细解析这六大索引各自的应用场景。

我们先回顾一下什么是索引。

大家想一下，如果现在你的一张表中只有十条数据，你不用索引，从第一条到最后一条扫一下速度很快。

但是如果有10万条，有10亿条，那这个扫描速度就会随着我们的数据量不断增大，就会越来越慢。

这个时候如何科学的为我们数据建立一个快速检索的目录，快速的定位到我们所需要的数据，这就是索引的目的了。

B-Tree 索引 (B-树)

B-Tree 是 PostgreSQL 中最通用、最强大的索引类型，也是 CREATE INDEX 命令不指定索引类型时的默认选项。它非常适合处理大多数数据库应用中常见的等值查询和范围查询。

介绍： B-Tree 索引的核心思想是维护一个平衡的多路搜索树。树的叶子节点包含了指向表中实际数据行（称为 tuple）的指针。由于树是平衡的，从根节点到任何叶子节点的路径长度都大致相同，这保证了在大型数据集上也能有稳定且可预测的查询性能。B-Tree 索引存储了索引列的排序副本，这使得它不仅能高效处理 =、>、< 等比较操作，还能直接服务于 ORDER BY、MIN() 和 MAX() 等操作，避免了额外的排序步骤。

Demo 示例： 假设我们有一个 employees 表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC(10, 2),
    hire_date DATE
);
INSERT INTO employees (name, salary, hire_date) VALUES
('Alice', 70000, '2020-01-15'),
('Bob', 85000, '2019-03-10'),
('Charlie', 60000, '2021-07-22');
-- 在 salary 列上创建 B-Tree 索引
CREATE INDEX idx_employees_salary ON employees(salary);

受益的查询：

-- 1. 等值查询
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary = 70000;

-- 2. 范围查询
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 65000;

-- 3. 排序操作
EXPLAIN ANALYZE SELECT * FROM employees ORDER BY salary DESC;

使用限制：

不适合非排序数据： 对于那些没有自然排序顺序的数据类型（例如，几何图形），B-Tree 索引无法发挥作用。
like模糊查询就很烂了 ：只有当模式匹配的通配符（% 或 _）不在字符串的开头时，B-Tree 索引才能被利用。例如，LIKE 'prefix%' 可以使用索引，但 LIKE '%suffix' 或 LIKE '%keyword%' 则无法使用，会导致全表扫描。
多列索引的顺序很重要： 对于多列 B-Tree 索引 (col1, col2)，只有当查询条件中包含 col1 时，索引才可能被有效利用。

Hash 索引 (哈希)

第二种索引是哈希索引，现在用得比较少了

Hash 索引提供了一种在大型表上进行快速等值查询的方法。它的核心优势在于其 O(1) 的理论查找复杂度。

介绍： Hash 索引内部维护一个哈希表。当创建索引时，PostgreSQL 会对索引列的每个值应用一个哈希函数，生成一个哈希码，这个哈希码指向一个存储桶（bucket），桶中存放着指向表中实际数据行的指针。当执行等值查询时，会用相同的哈希函数计算查询值的哈希码，然后直接定位到对应的存储桶，从而快速找到数据。

重要提示：在 PostgreSQL 10 之前，Hash 索引不是事务安全的且不支持复制，不推荐在生产环境使用。从 PostgreSQL 10 开始，Hash 索引被完全重构，变得持久化、崩溃安全且支持复制，可以安全地用于生产环境。它并非只存在于内存中，而是和 B-Tree 一样是存储在磁盘上的。

Demo 示例： 假设我们有一个存储 URL 的表：

CREATE TABLE web_pages (
id SERIAL PRIMARY KEY,
url TEXT UNIQUE
);

INSERT INTO web_pages (url) VALUES
('example.com/page1'),
('example.com/page2');

-- 在 url 列上创建 Hash 索引
CREATE INDEX idx_web_pages_url ON web_pages USING hash (url);

受益的查询：

-- 只有等值查询能利用 Hash 索引
EXPLAIN ANALYZE SELECT * FROM web_pages WHERE url = 'example.com/page1';

使用限制：

仅支持等值查询 ： Hash 索引只能用于 = 操作符的查询。它不支持范围查询（如 >、<）或排序。

不支持多列索引： 不能在多列上创建单一的 Hash 索引来加速复合条件的查询。通常不是首选： 尽管 Hash 索引已经变得可靠，但在大多数场景下，B-Tree 索引的综合性能和功能性仍然更优。

在这个过程中大家发现了，哈希索引在处理等值判断的时候，效率是非常高的。
它只需要做一次哈希运算，找到具体的槽，然后向后遍历就行了。
但是它的效率上其实也有待商榷，为什么呢？
因为如果我们槽不够多，那假如当前这一个槽的数据可能有几百个。
那你扫的时候从左到右依次扫，是不是也是一件痛苦的事情？
是的，这就是哈希本身的问题。

GiST 索引 (Generalized Search Tree - 通用搜索树)

GiST 是一种高度可扩展的索引框架，它允许为各种复杂的数据类型构建索引，尤其是那些数据之间存在重叠或包含关系的数据。

介绍： GiST 索引是一种平衡树结构，但与 B-Tree 不同，它的节点不存储单个值，而是存储能够代表其所有子节点数据的“谓词”（predicate）。例如，在二维地理数据中，一个父节点可能存储一个能完全包围其所有子节点几何图形的矩形（Bounding Box）。查询时，通过检查这些谓词，可以快速排除掉那些不可能包含目标数据的子树。通过定义不同的操作符类（operator class），GiST 可以实现对几何数据、范围类型等多种复杂数据的索引。

Demo 示例 (地理空间数据): 此示例需要 PostGIS 扩展。

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
geom GEOMETRY(Point, 4326) -- 存储经纬度点
);

INSERT INTO locations (name, geom) VALUES
('Eiffel Tower', ST_SetSRID(ST_MakePoint(2.2945, 48.8584), 4326));

-- 在几何列上创建 GiST 索引
CREATE INDEX idx_locations_geom ON locations USING gist (geom);

受益的查询：

-- 查询某个矩形区域内的所有地点 (&& 是 "overlaps" 操作符)
EXPLAIN ANALYZE SELECT * FROM locations
WHERE geom && ST_MakeEnvelope(-75, 40, -73, 41, 4326);

使用限制：

有损索引（Lossy）： GiST 索引通常是有损的。这意味着索引扫描可能会返回一些“误报”（false positives）的行，数据库引擎需要访问表中的实际数据行进行二次检查（recheck）。

索引大小和性能： GiST 索引通常比 B-Tree 索引更大，并且构建和更新的速度可能更慢。

SP-GiST 索引 (Space-Partitioned GiST - 空间分区 GiST)

SP-GiST 是 GiST 的一种变体，它支持非平衡的数据结构，通过将搜索空间划分为不相交的分区来组织数据。

丰富介绍 (IP 地址场景):

当用于索引 inet 或 cidr 这类 IP 地址数据类型时，SP-GiST 索引会实现一种称为基数树（Radix Tree / Trie） 的数据结构。基数树是一种专门用于高效存储和检索位序列（如 IP 地址的二进制表示）的树形结构。这种结构使得“前缀匹配”和“包含”查询变得极其高效，例如根据一个具体的 IP 地址快速找到它所属的路由规则。

Demo 示例 (IP 地址路由表):

CREATE TABLE ip_routes (
id SERIAL PRIMARY KEY,
network INET NOT NULL,
gateway INET NOT NULL
);

INSERT INTO ip_routes (network, gateway) VALUES
('192.168.1.0/24', '192.168.1.1'),
('10.0.0.0/8', '10.0.0.1'),
('0.0.0.0/0', '1.2.3.4'); -- 默认路由

-- 在 network 列上创建 SP-GiST 索引
CREATE INDEX idx_ip_routes_network ON ip_routes USING spgist (network);

受益的查询：

-- 查找包含特定 IP 的最具体路由 (>>= 操作符表示“包含或等于”)
EXPLAIN ANALYZE
SELECT * FROM ip_routes
WHERE network >>= '192.168.1.123'
ORDER BY masklen(network) DESC
LIMIT 1;

使用限制：

适用场景有限： SP-GiST 仅对那些可以被递归地划分为不相交子集的数据结构有效。对数据分布敏感： 如果数据分布极不均匀，可能会导致树的深度不平衡，从而影响查询性能。

更新成本： 更新操作可能会导致树节点的分裂或合并，成本相对较高。

GIN 索引 (Generalized Inverted Index - 通用倒排索引)

GIN 索引是为处理“复合值”而设计的，其中一个单独的行可以包含多个键（或元素）。它的工作方式类似于书本末尾的索引（术语表），是全文检索的首选索引类型。

介绍： GIN 的核心是“倒排”思想。传统的索引是从“项”到“位置”，而 GIN 索引则是从“键”到“项列表”。例如，对于一个数组列，GIN 索引会为数组中的每一个唯一元素创建一个条目，这个条目后面跟着一个包含了该元素的所有行的列表（posting list）。当查询数组是否包含某个元素时，GIN 索引可以直接定位到该元素的条目，并立即获取所有匹配的行。这使得它在处理数组、JSONB 和全文搜索等场景时极为高效。

Demo 示例 1 (数组类型):

CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES
('PostgreSQL Indexes', '{"database", "postgres", "performance"}');

-- 在 tags 数组列上创建 GIN 索引
CREATE INDEX idx_articles_tags ON articles USING gin (tags);

-- 查询包含特定标签的文章 (@> 操作符表示“包含”)
EXPLAIN ANALYZE SELECT * FROM articles WHERE tags @> ARRAY['database'];

Demo 示例 2 (全文检索 - 替代 LIKE):

问题场景: 在一个包含大量文本的表中，使用 WHERE content LIKE '%database%' 进行查询会导致全表扫描，性能极差。全文检索就是为了解决这个问题。

1. 准备表和数据我们需要一个专门的 tsvector 列来存储预处理过的文本。

CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
-- tsvector 列，用于存储分词、去停用词、词干提取后的文本
-- 'The quick brown foxes jumped over the lazy dogs' 转换为 TSVECTOR 后，结果可能是：
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazy':8 'quick':2
tsv_content TSVECTOR
);

INSERT INTO documents (title, content) VALUES
('About PostgreSQL', 'PostgreSQL is a powerful, open source object-relational database system.'),
('Learning SQL', 'SQL is a standard language for querying databases.'),
('Web Performance', 'Optimizing web performance is crucial for user experience.');

2. 自动更新 tsvector 列 (使用触发器)

为了让 tsv_content 列在 content 变化时自动更新，我们创建一个触发器。

-- 创建一个函数，用于将 content 转换为 tsvector
CREATE OR REPLACE FUNCTION documents_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
-- 'english' 是配置，用于处理英文的停用词和词干
NEW.tsv_content := to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
RETURN NEW;
END
$-- 创建触发器，在插入或更新时调用上述函数 CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON documents FOR EACH ROW EXECUTE FUNCTION documents_tsvector_update(); -- 手动更新一次现有数据 UPDATE documents SET content = content;$

3. 创建 GIN 索引在 tsvector 列上创建 GIN 索引。

CREATE INDEX idx_documents_tsv_content ON documents USING gin (tsv_content);

4. 高性能查询

现在，我们可以使用全文检索操作符 @@ 来进行高效查询。

-- 慢查询 (无法使用索引，会进行全表扫描)
EXPLAIN ANALYZE SELECT * FROM documents WHERE content LIKE '%database%';

-- 高性能查询 (使用 GIN 索引)
-- to_tsquery 会将查询词也进行处理，以匹配 tsvector
EXPLAIN ANALYZE SELECT * FROM documents WHERE tsv_content @@ to_tsquery('english', 'database');

-- 更复杂的查询：查找同时包含 "database" 和 "system" 的文档
EXPLAIN ANALYZE SELECT * FROM documents WHERE tsv_content @@ to_tsquery('english', 'database & system');

EXPLAIN 的结果会清晰地显示，第二条和第三条查询使用了 Bitmap Index Scan on idx_documents_tsv_content，速度极快。

使用限制：

更新缓慢： GIN 索引的更新成本较高。因为插入或更新一行数据可能会涉及到修改索引中多个键的行列表。

索引体积较大： GIN 索引可能会非常大，特别是当被索引的元素（如单词）数量巨大时。

BRIN 索引 (Block Range Index - 块范围索引)

BRIN 索引是一种革命性的索引类型，专为处理具有物理存储相关性的大型表而设计，它以极小的体积实现了高效的数据过滤。

介绍： BRIN 的核心思想是“摘要化”。它不索引每一行，而是将表在物理上划分成连续的块范围（block ranges），每个范围通常包含多个数据页。对于每个块范围，BRIN 索引只存储一个摘要信息，通常是该范围内所有值的最小值和最大值。当查询时，优化器会用查询条件与这些摘要信息进行比较。如果查询的范围与某个块范围的摘要信息没有交集，那么整个块范围的数据都可以被直接跳过，从而极大地减少了需要扫描的数据量。BRIN索引好比是为一本按时间顺序排列的巨大相册制作的“摘要目录”。它不索引每一张照片，而是为连续的一叠照片（例如100页）创建一个摘要条目，记录下这个范围内“最早和最晚的拍摄日期”。当查找特定日期的照片时，数据库先看这个摘要目录，直接跳过所有日期不符的厚厚几叠照片，仅检查可能包含目标照片的那一小叠。

Demo 示例 (日志表):

CREATE TABLE logs (
id BIGSERIAL PRIMARY KEY,
log_time TIMESTAMP WITH TIME ZONE NOT NULL,
message TEXT
);

-- 假设已经插入了数十亿行按时间顺序排序的数据...

-- 在 log_time 列上创建 BRIN 索引
-- pages_per_range 控制每个摘要条目覆盖的数据页数量，可以调整
CREATE INDEX idx_logs_log_time ON logs USING brin (log_time) WITH (pages_per_range = 64);

受益的查询：

-- 查询特定时间范围内的日志
EXPLAIN ANALYZE SELECT*FROM logs
WHERE log_time >='2025-10-20 00:00:00'AND log_time <'2025-10-21 00:00:00';

使用限制：

依赖数据物理顺序： BRIN 索引的效率与索引列的值和数据在磁盘上的物理存储顺序的“相关性”（correlation）强相关。如果数据是随机插入的，BRIN 索引将几乎无效。

有损索引（Lossy）： BRIN 也是有损的。它只能确定某个块范围可能包含匹配的数据，数据库引擎仍然需要扫描整个匹配的块范围来找到确切的行。

不适合精确查找： 对于需要返回单行或少数几行的精确查找（点查询），B-Tree 索引是更好的选择。