PostgreSQL教程FG056-PG高级索引：GiST/GIN/SP-GiST/BRIN索引适用场景
本文档风哥主要介绍PostgreSQL教程056相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
1. 高级索引概述
PostgreSQL提供了多种高级索引类型，适用于不同的数据类型和查询场景。
2. GIN索引
GIN（Generalized Inverted Index）通用倒排索引，适用于包含多个元素的值。
2.1 数组类型GIN索引
CREATE TABLE fgedu_products_gin (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100),
tags TEXT[],
attributes JSONB
);
— 插入测试数据
INSERT INTO fgedu_products_gin (product_name, tags, attributes) VALUES
(‘笔记本电脑’, ARRAY[‘电脑’, ‘办公’, ‘便携’], ‘{“brand”: “ThinkPad”, “cpu”: “i7”, “ram”: 16}’),
(‘无线鼠标’, ARRAY[‘外设’, ‘办公’, ‘无线’], ‘{“brand”: “Logitech”, “dpi”: 12000}’),
(‘机械键盘’, ARRAY[‘外设’, ‘游戏’, ‘RGB’], ‘{“brand”: “Cherry”, “switches”: “红轴”}’),
(‘显示器’, ARRAY[‘显示’, ‘办公’, ‘4K’], ‘{“brand”: “Dell”, “size”: 27, “resolution”: “4K”}’),
(‘耳机’, ARRAY[‘音频’, ‘游戏’, ‘无线’], ‘{“brand”: “Sony”, “type”: “降噪”}’);
— 创建GIN索引
CREATE INDEX idx_fgedu_products_tags_gin ON fgedu_products_gin USING GIN(tags);
CREATE INDEX idx_fgedu_products_attrs_gin ON fgedu_products_gin USING GIN(attributes);
— 数组包含查询
SELECT product_name, tags
FROM fgedu_products_gin
WHERE tags @> ARRAY[‘办公’];
执行结果：
风哥提示：
————–+———————
笔记本电脑 | {电脑,办公,便携}
无线鼠标 | {外设,办公,无线}
显示器 | {显示,办公,4K}
(3 rows)
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT product_name, tags
FROM fgedu_products_gin
WHERE tags @> ARRAY[‘办公’];
执行结果：
————————————————————————————-
Bitmap Heap Scan on fgedu_products_gin (cost=8.00..12.01 rows=1 width=72) (actual time=0.025..0.030 rows=3 loops=1)
Recheck Cond: (tags @> ‘{办公}’::text[])
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_fgedu_products_tags_gin (cost=0.00..8.00 rows=1 width=0) (actual time=0.020..0.020 rows=3 loops=1)
Index Cond: (tags @> ‘{办公}’::text[])
Planning Time: 0.150 ms
Execution Time: 0.050 ms
(7 rows)
2.2 JSONB类型GIN索引
SELECT product_name, attributes
FROM fgedu_products_gin
WHERE attributes @> ‘{“brand”: “Dell”}’;
执行结果：
————–+———————————————–
显示器 | {“brand”: “Dell”, “size”: 27, “resolution”: “4K”}
(1 row)
SELECT product_name, attributes
FROM fgedu_products_gin
WHERE attributes ? ‘cpu’;
执行结果：
from oracle:www.itpux.com
————–+———————————————–
笔记本电脑 | {“brand”: “ThinkPad”, “cpu”: “i7”, “ram”: 16}
(1 row)
3. GiST索引
GiST（Generalized Search Tree）通用搜索树，支持多种数据类型和操作符。
学习交流加群风哥QQ113257174
3.1 几何数据GiST索引
CREATE TABLE fgedu_locations_gist (
location_id SERIAL PRIMARY KEY,
location_name VARCHAR(100),
coordinates POINT,
area BOX
);
— 插入测试数据
INSERT INTO fgedu_locations_gist (location_name, coordinates, area) VALUES
(‘北京总部’, POINT(116.4, 39.9), BOX(POINT(116.0, 39.5), POINT(116.8, 40.3))),
(‘上海分部’, POINT(121.5, 31.2), BOX(POINT(121.0, 30.8), POINT(122.0, 31.6))),
(‘广州办事处’, POINT(113.3, 23.1), BOX(POINT(113.0, 22.8), POINT(113.6, 23.4))),
(‘深圳研发中心’, POINT(114.1, 22.5), BOX(POINT(113.8, 22.2), POINT(114.4, 22.8)));
— 创建GiST索引
CREATE INDEX idx_fgedu_locations_point_gist ON fgedu_locations_gist USING GiST(coordinates);
CREATE INDEX idx_fgedu_locations_box_gist ON fgedu_locations_gist USING GiST(area);
— 范围查询
SELECT location_name, coordinates
FROM fgedu_locations_gist
WHERE coordinates <@ BOX(POINT(115, 39), POINT(117, 41));
执行结果：
更多学习教程公众号风哥教程itpux_com
—————+——————
北京总部 | (116.4,39.9)
(1 row)
SELECT location_name, coordinates,
coordinates <-> POINT(116.4, 39.9) AS distance
FROM fgedu_locations_gist
ORDER BY coordinates <-> POINT(116.4, 39.9)
LIMIT 3;
执行结果：
—————+——————+——————-
北京总部 | (116.4,39.9) | 0
上海分部 | (121.5,31.2) | 8.086409587622975
广州办事处 | (113.3,23.1) | 18.3847763108502
(3 rows)
3.2 范围类型GiST索引
CREATE TABLE fgedu_meetings_gist (
meeting_id SERIAL PRIMARY KEY,
meeting_name VARCHAR(100),
meeting_time TSRANGE
);
— 插入测试数据
INSERT INTO fgedu_meetings_gist (meeting_name, meeting_time) VALUES
(‘项目启动会’, TSRANGE(‘2026-04-04 09:00’, ‘2026-04-04 10:00’)),
(‘技术评审’, TSRANGE(‘2026-04-04 10:30’, ‘2026-04-04 12:00’)),
(‘客户沟通’, TSRANGE(‘2026-04-04 14:00’, ‘2026-04-04 15:30’)),
(‘团队周会’, TSRANGE(‘2026-04-04 16:00’, ‘2026-04-04 17:00’));
— 创建GiST索引
CREATE INDEX idx_fgedu_meetings_time_gist ON fgedu_meetings_gist USING GiST(meeting_time);
— 时间冲突查询
SELECT meeting_name, meeting_time
FROM fgedu_meetings_gist
WHERE meeting_time && TSRANGE(‘2026-04-04 10:00’, ‘2026-04-04 11:00’);
执行结果：
————–+————————————————–
技术评审 | [“2026-04-04 10:30:00″,”2026-04-04 12:00:00”)
(1 row)
4. SP-GiST索引
SP-GiST（Space-Partitioned GiST）空间分区GiST，适用于四叉树等数据结构。
CREATE TABLE fgedu_points_spgist (
point_id SERIAL PRIMARY KEY,
point_name VARCHAR(50),
location POINT
);
— 插入测试数据
INSERT INTO fgedu_points_spgist (point_name, location)
SELECT
‘Point_’ || i,
POINT(random() * 100, random() * 100)
FROM generate_series(1, 10000) i;
— 创建SP-GiST索引
CREATE INDEX idx_fgedu_points_spgist ON fgedu_points_spgist USING SPGiST(location);
— 范围查询
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT point_name, location
FROM fgedu_points_spgist
WHERE location <@ BOX(POINT(0, 0), POINT(25, 25));
执行结果：
————————————————————————————-
Bitmap Heap Scan on fgedu_points_spgist (cost=10.00..150.00 rows=625 width=40) (actual time=0.150..0.500 rows=625 loops=1)
Recheck Cond: (location <@ '(25,25),(0,0)'::box) Heap Blocks: exact=50 -> Bitmap Index Scan on idx_fgedu_points_spgist (cost=0.00..10.00 rows=625 width=0) (actual time=0.100..0.100 rows=625 loops=1)
Index Cond: (location <@ '(25,25),(0,0)'::box) Planning Time: 0.200 ms Execution Time: 0.600 ms (7 rows)
5. BRIN索引
BRIN（Block Range Index）块范围索引，适合大表中物理有序的数据。
更多视频教程www.fgedu.net.cn
CREATE TABLE fgedu_logs_brin (
log_id SERIAL PRIMARY KEY,
log_time TIMESTAMP,
log_level VARCHAR(20),
message TEXT
);
— 插入有序数据
INSERT INTO fgedu_logs_brin (log_time, log_level, message)
SELECT
‘2026-01-01’::TIMESTAMP + (i || ‘ minutes’)::INTERVAL,
CASE i % 4
WHEN 0 THEN ‘INFO’
WHEN 1 THEN ‘DEBUG’
WHEN 2 THEN ‘WARNING’
ELSE ‘ERROR’
END,
‘Log message ‘ || i
FROM generate_series(1, 1000000) i;
— 创建BRIN索引
CREATE INDEX idx_fgedu_logs_time_brin ON fgedu_logs_brin USING BRIN(log_time);
— 查看索引大小
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = ‘fgedu_logs_brin’;
执行结果：
—————————-+————
fgedu_logs_brin_pkey | 21 MB
idx_fgedu_logs_time_brin | 128 kB
(2 rows)
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT COUNT(*) FROM fgedu_logs_brin
WHERE log_time BETWEEN ‘2026-02-01’ AND ‘2026-02-02’;
执行结果：
————————————————————————————-
Aggregate (cost=1500.00..1500.01 rows=1 width=8) (actual time=25.234..25.235 rows=1 loops=1)
-> Bitmap Heap Scan on fgedu_logs_brin (cost=100.00..1400.00 rows=1440 width=0) (actual time=5.234..20.456 rows=1440 loops=1)
Recheck Cond: ((log_time >= ‘2026-02-01 00:00:00’::timestamp without time zone) AND (log_time <= '2026-02-02 00:00:00'::timestamp without time zone)) Rows Removed by Index Recheck: 0 Heap Blocks: lossy=15 -> Bitmap Index Scan on idx_fgedu_logs_time_brin (cost=0.00..100.00 rows=1440 width=0) (actual time=0.100..0.100 rows=150 loops=1)
Index Cond: ((log_time >= ‘2026-02-01 00:00:00’::timestamp without time zone) AND (log_time <= '2026-02-02 00:00:00'::timestamp without time zone)) Planning Time: 0.200 ms Execution Time: 25.300 ms (9 rows)
6. 索引类型对比
CREATE TABLE fgedu_index_compare (
id SERIAL PRIMARY KEY,
content TEXT,
tags TEXT[],
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
— 插入测试数据
INSERT INTO fgedu_index_compare (content, tags)
SELECT
‘Content ‘ || i || ‘ with some text for testing’,
ARRAY[‘tag’ || (i % 10), ‘category’ || (i % 5)]
FROM generate_series(1, 50000) i;
— 创建不同类型索引
CREATE INDEX idx_compare_content_btree ON fgedu_index_compare(content);
CREATE INDEX idx_compare_tags_gin ON fgedu_index_compare USING GIN(tags);
CREATE INDEX idx_compare_created_brin ON fgedu_index_compare USING BRIN(created_at);
— 对比索引大小
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = ‘fgedu_index_compare’
ORDER BY pg_relation_size(indexname::regclass) DESC;
执行结果：
学习交流加群风哥微信: itpux-com
—————————-+————
idx_compare_content_btree | 1536 kB
idx_compare_tags_gin | 1152 kB
idx_compare_created_brin | 48 kB
fgedu_index_compare_pkey | 1072 kB
(4 rows)
7. 清理环境
DROP TABLE IF EXISTS fgedu_products_gin;
DROP TABLE IF EXISTS fgedu_locations_gist;
DROP TABLE IF EXISTS fgedu_meetings_gist;
DROP TABLE IF EXISTS fgedu_points_spgist;
DROP TABLE IF EXISTS fgedu_logs_brin;
DROP TABLE IF EXISTS fgedu_index_compare;
执行结果：
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
- GIN：适合数组、JSONB、全文检索，查询快但写入慢
- GiST：适合几何数据、范围类型，支持多种操作符
- SP-GiST：适合四叉树等非平衡结构
- BRIN：适合大表有序数据，索引极小
选择索引类型时需要考虑数据特征、查询模式和存储空间。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
