SP-GiST 索引
SP-GiST（Space-Partitioned GiST，空间分区通用搜索树）是 PostgreSQL 内置的六种索引访问方法之一。名字里带 GiST，但它和 GiST 的关系仅止于「都是可扩展的树框架」——GiST 是平衡树（R 树、B 树那一系），SP-GiST 则刻意放弃平衡性，用非平衡的空间分区来组织数据，直接支持四叉树（quad-tree）、k-d 树（k-d tree）和基数树（radix tree / trie）。
这一节要回答三个问题：什么样的数据在 SP-GiST 上比 GiST 更快？非平衡结构在磁盘上为什么不会退化成链表？以及为什么 SP-GiST 不支持多列索引。学完你将能判断该给 point 选 quad_point_ops 还是 kd_point_ops、IP 归属查询为什么适合 SP-GiST，以及它的几条硬性限制分别是什么。
前置知识
阅读本节前，建议先了解：GiST 索引原理 与 GiST 与最近邻搜索
基础概念
从「平衡」到「分区」
GiST 的核心约束是平衡：所有叶子节点到根的距离相同，靠 union 维护「能包住子树内所有值」的边界框。这个模型对互相重叠的区域（矩形、时间区间）非常自然，但它有一个代价：同一层的区域会互相重叠，一次查询可能不得不走进多条分支。
SP-GiST 换了一条路。它不要求平衡，也不要求各分区大小相等，而是反复把搜索空间切分成分区：
GiST（平衡、区域可重叠） SP-GiST（非平衡、分区互不相交）
┌─────────────────┐ ┌─────────┬─────────┐
│ MBR（可重叠） │ │ NW │ NE │
│ ┌────┐ ┌────┐ │ ├─────────┼─────────┤
│ │ A │ │ B │ │ │ SW │ SE │
│ └────┘ └────┘ │ └─────────┴─────────┘
└─────────────────┘ 四个象限互不重叠，一次只进一个官方对 SP-GiST 的定义是：
SP-GiST is an abbreviation for space-partitioned GiST. SP-GiST supports partitioned search trees, which facilitate development of a wide range of different non-balanced data structures, such as quad-trees, k-d trees, and radix trees (tries). The common feature of these structures is that they repeatedly divide the search space into partitions that need not be of equal size. Searches that are well matched to the partitioning rule can be very fast.
最后半句是关键：只有「与分区规则匹配得好」的查询才非常快。这是 SP-GiST 的性能特征，也是它的选型判据。
非平衡结构怎么落到磁盘上
四叉树、k-d 树这些结构原本是为内存设计的：节点用指针动态连接。直接搬到磁盘上会出问题——指针链太长，一次查询要读太多磁盘页。
SP-GiST 要解决的核心挑战是：把搜索树节点映射到磁盘页上，使得即使要遍历很多节点，一次搜索也只需要访问少数几个磁盘页（官方原文：a search need access only a few disk pages, even if it traverses many nodes）。它的做法是：
| 机制 | 作用 |
|---|---|
| 高扇出（high fanout） | 一个内部元组里放下多个节点（比如四叉树的 4 个象限） |
| 节点下链（downlink） | 一个节点可以直接指向同一索引页上的一小组叶子元组 |
| 前缀（prefix） | 内部元组存一个「所有成员共享的值」，减少重复存储 |
| 节点标签（label） | 描述该节点的分区依据，例如基数树里的下一个字符 |
这些机制组合起来，让「非平衡」在磁盘上仍然是高效的，不会退化成链表。
内建操作符类清单
PostgreSQL 18 内置的 SP-GiST 操作符类一共 7 个（官方 Table 65.2）：
| 操作符类 | 索引列类型 | 支撑的数据结构 | 排序操作符（KNN） |
|---|---|---|---|
quad_point_ops | point | 四叉树 | <-> (point, point) |
kd_point_ops | point | k-d 树 | <-> (point, point) |
box_ops | box | 分区索引 | 无 |
poly_ops | polygon | 分区索引 | <-> (polygon, point) |
range_ops | anyrange | 分区索引 | 无 |
inet_ops | inet | 基数树 | 无 |
text_ops | text | 基数树（trie） | 无 |
两个要点必须记牢：
point的默认 SP-GiST 操作符类是quad_point_ops（官方原文：Of the two operator classes for typepoint,quad_point_opsis the default）。kd_point_ops支持完全相同的操作符，但内部数据结构不同。- 只有
quad_point_ops、kd_point_ops、poly_ops支持<->排序操作符（官方原文：Thequad_point_ops,kd_point_opsandpoly_opsoperator classes support the<->ordering operator, which enables the k-nearest neighbor (k-NN) search over indexed point or polygon data sets）。
SP-GiST 没有全文操作符类
SP-GiST 的操作符类里没有 tsvector_ops 或 tsquery_ops。全文检索只能靠 GIN 或 GiST。text_ops 虽然名字像全文检索，但它提供的是 =、<、<=、>、>= 以及 ~<~ 系列和 ^@（前缀匹配），与全文无关。
text_ops 的特殊操作符：^@
SP-GiST 的 text_ops 提供了一组基于排序规则的比较操作符（~<~、~<=~、~>=~、~>~），以及一个非常有用的前缀操作符 ^@：
^@ (text, text) → boolean
starts_with(string, prefix) 的等价写法：
string ^@ prefix这是 SP-GiST 相对 B-Tree 的一个优势：LIKE 'abc%' 在 B-Tree 上要靠 text_pattern_ops 才能走索引，而 SP-GiST 的 ^@ 有一个专门的操作符，语义更直白。但请注意两条：
^@只在 SP-GiSTtext_ops里被登记，GiST 没有；- 它能处理前缀，但不支持
%abc%这种中间匹配（那需要pg_trgm）。
SP-GiST 与 GiST 的能力对照
选型前先把两者的边界画清楚：
| 维度 | GiST | SP-GiST |
|---|---|---|
| 树结构 | 平衡（B 树 / R 树系） | 非平衡（四叉树 / k-d 树 / 基数树） |
| 分区是否等大 | 不要求 | 不要求 |
| 分区是否重叠 | 允许重叠 | 倾向互不相交 |
| 多列索引 | 支持 | 不支持 |
| 唯一索引 | 不支持 | 不支持 |
INCLUDE 覆盖列 | 支持 | 支持 |
EXCLUDE 排他约束 | 支持 | 支持 |
| KNN 排序 | 支持（几何、btree_gist 标量、gist_trgm_ops） | 支持（仅 quad_point_ops、kd_point_ops、poly_ops） |
| 全文搜索 | 支持（tsvector_ops） | 不支持 |
| 文本前缀查询 | 不支持 ^@ | 支持 ^@ |
| 存储参数 | fillfactor、buffering | fillfactor |
| 建索引加速 | 缓冲构建 / 排序构建 | 无对应的 buffering 参数 |
最醒目的差异是多列。官方多列索引文档明确写着：B-tree、GiST、GIN 与 BRIN 支持多列索引，SP-GiST 与 Hash 不支持。也就是 CREATE INDEX ... USING spgist (a, b) 会报错。
为什么 SP-GiST 不支持多列
这个限制不是疏忽，而是结构使然。SP-GiST 的分区规则是「按某个空间维度反复切分」——四叉树切的是二维平面，k-d 树轮流切不同维度，基数树切的是字符串的字符位置。这些规则都只描述一个值如何被分割。
如果硬要塞进第二列，就必须定义「两个值的联合空间如何分区」，这会把分区规则从「按一个维度切」变成「按一个组合维度切」，与 SP-GiST 的设计目标背离。相比之下 GiST 的 union 方法天然支持「合并任意多个值的区域」，所以多列对 GiST 是自然的。
实践含义：需要多列过滤时，要么改用 GiST（配 btree_gist），要么用两个单列 SP-GiST 索引让规划器做位图 AND。
查询与分区规则的匹配度
SP-GiST 的性能不是「普遍更快」，而是「匹配得快」。判断标准很具体：
| 查询形态 | 与分区的匹配度 | 说明 |
|---|---|---|
| 点查询（等值） | 高 | 每次分区都能排除大部分空间，一次只进一个分支 |
前缀查询（^@） | 高 | 基数树每层按一个字符分区，路径唯一 |
| 范围查询（范围包含某个点） | 中 | 分区能剪枝，但可能命中多个分支 |
最近邻（<->） | 高 | 分区距离下界剪枝非常有效 |
前缀通配（%abc%） | 低 | 与分区规则不匹配，基本退化为全索引扫描 |
| 多列组合条件 | 不可用 | 结构不支持 |
官方文档的措辞是「searches that are well matched to the partitioning rule can be very fast」——这个「well matched」就是上表在说的东西。
语法与用法
-- 基本形式：使用默认操作符类
CREATE INDEX 索引名 ON 表名 USING spgist (列名);
-- 显式指定操作符类（point 想用 k-d 树必须显式写）
CREATE INDEX 索引名 ON 表名 USING spgist (列名 操作符类名);
-- 指定存储参数（SP-GiST 只接受 fillfactor）
CREATE INDEX 索引名 ON 表名 USING spgist (列名) WITH (fillfactor = 90);
-- 覆盖列（SP-GiST 支持 INCLUDE）
CREATE INDEX 索引名 ON 表名 USING spgist (列名) INCLUDE (其他列);
-- 部分索引
CREATE INDEX 索引名 ON 表名 USING spgist (列名) WHERE 条件;
-- 并发创建
CREATE INDEX CONCURRENTLY 索引名 ON 表名 USING spgist (列名);最小可运行示例——点数据的四叉树索引：
CREATE TABLE sites (
id bigserial PRIMARY KEY,
name text NOT NULL,
location point NOT NULL
);
-- 默认操作符类就是 quad_point_ops
CREATE INDEX sites_location_spgist ON sites USING spgist (location);
INSERT INTO sites (name, location) VALUES
('A', point '(1,1)'),
('B', point '(-5,3)'),
('C', point '(100,100)'),
('D', point '(-1,-1)');
-- 点是否落在矩形内：point <@ box（SP-GiST 与 GiST 都登记了这个操作符）
SELECT id, name
FROM sites
WHERE location <@ box '((-10,-10),(10,10))'; id | name
----+------
1 | A
2 | B
4 | D
(3 rows)详细说明
非平衡空间分区：核心思想
用一个具体例子理解分区过程。假设有 8 个点分布在平面上，四叉树第一次以中心点把平面切成四个象限：
第一步：以中心 C 切成四块 第二步：对 NW 象限继续切
┌──────────┬──────────┐ ┌──────────┬──────────┐
│ NW │ NE │ │ ┌──┬──┐ │ NE │
│ │ │ │ │b │a │ │ │
├──────────┼──────────┤ │ ├──┼──┤ │ │
│ │ │ │ │c │d │ │ │
│ SW │ SE │ │ └──┴──┘ │ │
└──────────┴──────────┘ └──────────┴──────────┘
↑ 每个象限是一个「节点」 ↑ 递归切分这就是 SP-GiST 内部元组的形式：一个内部元组含有一个 prefix（这里是中心点 C）和一组 nodes（这里是四个象限），每个节点有一个 label（NW/NE/SW/SE）和一条 downlink（指向下级内部元组，或直接指向同一索引页上的叶子元组列表）。
-- 观察 SP-GiST 索引的物理统计
SELECT c.relname,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
s.idx_scan,
s.idx_tup_read
FROM pg_class c
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = c.oid
WHERE c.relname = 'sites_location_spgist'; relname | index_size | idx_scan | idx_tup_read
-----------------------+------------+----------+--------------
sites_location_spgist | 8192 bytes | 1 | 4
(1 row)quad_point_ops 与 kd_point_ops 的差异
point 有两个 SP-GiST 操作符类，二者支持完全相同的操作符（<<、>>、<<|、|>>、~=、<@ (point, box)），只是树结构不同。
| 维度 | quad_point_ops | kd_point_ops |
|---|---|---|
| 数据结构 | 四叉树 | k-d 树 |
| 分区方式 | 每次在 x、y 两个方向同时切，产生 4 个节点 | 每次只在一个维度上切，产生 2 个节点 |
| 每层节点数 | 4（空间开销略高） | 2（树更深） |
是否为 point 默认 | 是 | 否，必须显式指定 |
| KNN | 支持 <-> | 支持 <-> |
| 性能 | 均匀分布数据通常很好 | 某些应用里可能更好 |
官方对 kd_point_ops 的说明是「supports the same operators but uses a different index data structure that may offer better performance in some applications」——「可能更好」意味着没有普适结论，必须实测。
-- 显式使用 k-d 树
CREATE INDEX sites_location_kd
ON sites USING spgist (location kd_point_ops);
-- 与四叉树版本对比查询计划
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM sites
WHERE location <@ box '((-10,-10),(10,10))'; QUERY PLAN
--------------------------------------------------------------------
Bitmap Heap Scan on sites
Recheck Cond: (location <@ '((-10,-10),(10,10))'::box)
Buffers: shared hit=2
-> Bitmap Index Scan on sites_location_kd
Index Cond: (location <@ '((-10,-10),(10,10))'::box)
Buffers: shared hit=1
(5 rows)inet_ops 与 IP 归属查询
inet_ops 是 SP-GiST 最实用的操作符类之一，用来做 IP 段归属判断。它支持完整的比较操作符与网络包含操作符。
CREATE TABLE ip_blocks (
id bigserial PRIMARY KEY,
label text NOT NULL,
network inet NOT NULL
);
-- SP-GiST 的 inet_ops 是 inet 的默认操作符类吗？
-- 不是：inet 的默认是 B-Tree。用 SP-GiST 需要显式指定 USING spgist
CREATE INDEX ip_blocks_network_spgist ON ip_blocks USING spgist (network);
INSERT INTO ip_blocks (label, network) VALUES
('office', '192.168.1.0/24'),
('vpn', '10.8.0.0/16'),
('datacenter','172.16.0.0/12'),
('public', '0.0.0.0/0');
-- 查询：某个具体 IP 落在哪些网段里？
SELECT label, network
FROM ip_blocks
WHERE network >>= inet '192.168.1.77'; label | network
---------+-----------------
office | 192.168.1.0/24
public | 0.0.0.0/0
(2 rows)>>= 的语义是「第一个网段包含或等于第二个」（is the first subnet equal to or a supernet of the second）。反过来判断「某网段是否在另一个网段内」用 <<=。
-- 反方向：给定一个网段，判断它属于哪条已知记录
SELECT label
FROM ip_blocks
WHERE network >>= inet '192.168.1.0/25'
ORDER BY masklen(network) DESC
LIMIT 1; label
--------
office
(1 row)注意这里必须排序取最精确的匹配：0.0.0.0/0 也满足 >>= 条件，masklen 排序能保证拿到掩码最长的那个。这是 IP 归属判断的标准写法。
text_ops 与 ^@ 前缀查询
text_ops 提供了 ^@ (text, text) 前缀操作符，用基数树（radix tree / trie）结构加速。
CREATE TABLE domains (
id bigserial PRIMARY KEY,
fqdn text NOT NULL UNIQUE
);
CREATE INDEX domains_fqdn_spgist ON domains USING spgist (fqdn);
INSERT INTO domains (fqdn) VALUES
('api.example.com'),
('api.internal.example.com'),
('app.example.com'),
('mail.example.com'),
('example.org');
-- 前缀查询：所有以 api 开头的域名
SELECT fqdn
FROM domains
WHERE fqdn ^@ 'api'; fqdn
--------------------------
api.example.com
api.internal.example.com
(2 rows)^@ 与 LIKE 'api%' 语义接近，但走索引的机制不同。看执行计划：
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT fqdn FROM domains WHERE fqdn ^@ 'api'; QUERY PLAN
-------------------------------------------------------------------------------------
Index Only Scan using domains_fqdn_spgist on domains
Index Cond: (fqdn ^@ 'api'::text)
Heap Fetches: 0
Buffers: shared hit=3
(4 rows)Index Only Scan + Index Cond 说明它走的是索引条件，而且因为索引覆盖了唯一被查询的列，连堆表都不用回。
^@ 不能做中间匹配
^@ 只能判断前缀。WHERE fqdn ^@ 'example' 匹配不到 api.example.com——它是前缀操作符，不是子串操作符。要做 %example% 这类中间匹配，请用 pg_trgm 的 GIN / GiST 索引。
range_ops：不含 KNN 的范围索引
SP-GiST 也提供 range_ops，覆盖与 GiST range_ops 大部分相同的操作符：
CREATE TABLE reservations (
id bigserial PRIMARY KEY,
room_id int NOT NULL,
during tsrange NOT NULL
);
CREATE INDEX reservations_during_spgist ON reservations USING spgist (during);
-- 重叠查询
SELECT id, during
FROM reservations
WHERE during && tsrange('2026-09-01 09:00', '2026-09-01 12:00'); id | during
----+-----------------------------------------------
1 | ["2026-09-01 10:00:00","2026-09-01 11:00:00")
(1 row)和 GiST 的 range_ops 相比有两点差异：
| 差异 | GiST range_ops | SP-GiST range_ops |
|---|---|---|
| 操作符覆盖 | 含 =、&&、@>、<@、<<、>>、&<、&>、`- | -；还有 anyrange与anymultirange` 的混合形式 |
| 多列 | 支持 | 不支持 |
EXCLUDE 排他约束 | 支持 | 支持 |
要在「会议室时段不重叠」这类排他约束上选 SP-GiST 还是 GiST，取决于数据分布和实测结果。但注意：如果还需要 room_id 参与约束（room_id WITH =, during WITH &&），SP-GiST 用不了，因为它是多列场景，必须用 GiST + btree_gist。
EXCLUDE 约束与 SP-GiST
SP-GiST 支持排他约束。单列场景下可以直接用：
CREATE TABLE single_room_bookings (
id bigserial PRIMARY KEY,
during tsrange NOT NULL,
CONSTRAINT no_overlap_single_room
EXCLUDE USING spgist (during WITH &&)
);
INSERT INTO single_room_bookings (during)
VALUES (tsrange('2026-09-01 09:00', '2026-09-01 10:00'));CREATE TABLE
INSERT 0 1再插一条重叠的会立刻被拒：
INSERT INTO single_room_bookings (during)
VALUES (tsrange('2026-09-01 09:30', '2026-09-01 10:30'));ERROR: conflicting key value violates exclusion constraint "no_overlap_single_room"
DETAIL: Key (during)=(["2026-09-01 09:30:00","2026-09-01 10:30:00")) conflicts with existing key (during)=(["2026-09-01 09:00:00","2026-09-01 10:00:00")).从系统表核对操作符类
判断某个操作符类是否真的存在、支持哪些操作符，最可靠的方式是查 pg_opclass 与 pg_amop。
-- 列出所有 SP-GiST 操作符类
SELECT opc.opcname,
opc.opcintype::regtype AS input_type,
am.amname
FROM pg_opclass opc
JOIN pg_am am ON am.oid = opc.opcmethod
WHERE am.amname = 'spgist'
ORDER BY opc.opcname; opcname | input_type | amname
-----------------+------------+--------
box_ops | box | spgist
inet_ops | inet | spgist
kd_point_ops | point | spgist
poly_ops | polygon | spgist
quad_point_ops | point | spgist
range_ops | anyrange | spgist
text_ops | text | spgist
(7 rows)-- 确认「谁支持 KNN」：数一数每个类的排序操作符
SELECT opc.opcname,
count(*) FILTER (WHERE amop.amoppurpose = 'o') AS knn_ops,
count(*) FILTER (WHERE amop.amoppurpose = 's') AS search_ops
FROM pg_amop amop
JOIN pg_opclass opc ON opc.oid = amop.amopfamily
JOIN pg_am am ON am.oid = opc.opcmethod
WHERE am.amname = 'spgist'
GROUP BY opc.opcname
ORDER BY knn_ops DESC, opc.opcname; opcname | knn_ops | search_ops
-----------------+---------+------------
kd_point_ops | 1 | 6
poly_ops | 1 | 12
quad_point_ops | 1 | 6
box_ops | 0 | 12
inet_ops | 0 | 11
range_ops | 0 | 10
text_ops | 0 | 10
(7 rows)这张表就是「哪些类能 KNN」的权威答案：只有 kd_point_ops、poly_ops、quad_point_ops 的 knn_ops 是 1。
非平衡树的深度控制
非平衡结构有一个理论风险：如果数据分布极端倾斜（比如 99.9% 的点都挤在同一个象限），四叉树会一直往那个象限切，导致树越来越深。
SP-GiST 用 all-the-same 机制应对：当内部元组的多个节点完全等价时，把它标记为 allTheSame，避免无意义地创建大量等价节点。官方在实现说明里专门有一节讲这个（SPGIST-ALL-THE-SAME）。
实际影响：
-- 极端倾斜数据：几乎所有的点都在同一坐标
INSERT INTO sites (name, location)
SELECT 'dup' || g, point '(0,0)'
FROM generate_series(1, 50000) AS g;
ANALYZE sites;
SELECT pg_size_pretty(pg_relation_size('sites_location_spgist')) AS idx_size; idx_size
----------
3352 kB索引体积增长是线性的（每行一个叶子元组），没有出现指数级膨胀。这就是 all-the-same 机制的效果。
SP-GiST 的 fillfactor
SP-GiST 与 B-tree、hash、GiST 一样接受 fillfactor 参数。
-- 写密集的表可以留更多空闲空间
CREATE INDEX sites_location_spgist ON sites USING spgist (location)
WITH (fillfactor = 80);
-- 运行期调整
ALTER INDEX sites_location_spgist SET (fillfactor = 70);注意 SP-GiST 没有 GiST 的 buffering 参数。GiST 的缓冲构建是针对平衡树结构优化的；SP-GiST 的非平衡结构用不上这套机制。
-- 验证：试图给 SP-GiST 设置 buffering 会报错
CREATE INDEX bad_idx ON sites USING spgist (location) WITH (buffering = on);ERROR: unrecognized parameter "buffering"实战示例
一个「日志来源 IP 归属 + 快速前缀查询」的综合场景。
第 1 步：建表与索引
DROP TABLE IF EXISTS access_logs;
CREATE TABLE access_logs (
id bigserial PRIMARY KEY,
client_ip inet NOT NULL,
request_uri text NOT NULL,
status int NOT NULL,
logged_at timestamptz NOT NULL DEFAULT now()
);
-- IP 列：SP-GiST inet_ops
CREATE INDEX access_logs_ip_spgist ON access_logs USING spgist (client_ip);
-- URI 列：SP-GiST text_ops，支持 ^@ 前缀查询
CREATE INDEX access_logs_uri_spgist ON access_logs USING spgist (request_uri);
-- 时间列：BRIN 或 B-Tree 更合适，这里给个 B-Tree 做对照
CREATE INDEX access_logs_time_btree ON access_logs (logged_at);DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX第 2 步：灌入数据
INSERT INTO access_logs (client_ip, request_uri, status, logged_at)
SELECT
('10.' || (g % 256) || '.' || ((g / 256) % 256) || '.' || (g % 254 + 1))::inet,
(ARRAY['/api/v1/users', '/api/v1/orders', '/api/v2/search',
'/static/logo.png', '/healthz'])[1 + (g % 5)],
(ARRAY[200, 200, 200, 404, 500])[1 + (g % 5)],
timestamptz '2026-09-01 00:00:00+08' + (g || ' seconds')::interval
FROM generate_series(1, 500000) AS g;
ANALYZE access_logs;INSERT 0 500000
ANALYZE第 3 步：IP 网段归属查询
CREATE TABLE ip_networks (
id bigserial PRIMARY KEY,
label text NOT NULL,
network inet NOT NULL
);
CREATE INDEX ip_networks_spgist ON ip_networks USING spgist (network);
INSERT INTO ip_networks (label, network) VALUES
('internal-10', '10.0.0.0/8'),
('internal-172', '172.16.0.0/12'),
('internal-192', '192.168.0.0/16'),
('public', '0.0.0.0/0');
-- 统计内部网段的访问量
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*)
FROM access_logs a
JOIN ip_networks n ON n.network >>= a.client_ip
WHERE n.label = 'internal-10'; QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate
Buffers: shared hit=2418
-> Nested Loop
Join Filter: (n.network >>= a.client_ip)
Buffers: shared hit=2418
-> Index Scan using ip_networks_spgist on ip_networks n
Index Cond: (network >>= '10.0.0.0/8'::inet)
Filter: (label = 'internal-10'::text)
Buffers: shared hit=2
-> Seq Scan on access_logs a
Buffers: shared hit=2416
(9 rows)注意这里的执行方式：ip_networks 只用了 2 个缓冲页（SP-GiST 高效定位），但 access_logs 走了顺序扫描。原因在方向——连接条件 n.network >>= a.client_ip 中，索引列 n.network 是「包含方」，而我们要找的是「哪个网段包含某个 IP」。这种情况下索引只能加速网段的定位，不能加速 IP 的匹配。
要让索引真正发挥作用，应该反过来组织查询：
-- 方式一：先取具体 IP，再查它属于哪个网段
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT label
FROM ip_networks
WHERE network >>= inet '10.5.5.5'
ORDER BY masklen(network) DESC
LIMIT 1; QUERY PLAN
------------------------------------------------------------------------------------------
Limit
Buffers: shared hit=4
-> Sort
Sort Key: (masklen(network)) DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=4
-> Index Only Scan using ip_networks_spgist on ip_networks
Index Cond: (network >>= '10.5.5.5'::inet)
Heap Fetches: 0
Buffers: shared hit=4
(8 rows)Index Only Scan + Index Cond：SP-GiST 直接把「包含该 IP 的网段」定位出来了。
第 4 步：URI 前缀统计
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT request_uri, count(*)
FROM access_logs
WHERE request_uri ^@ '/api/v2'
GROUP BY request_uri; QUERY PLAN
---------------------------------------------------------------------------------------------
HashAggregate
Buffers: shared hit=2092
-> Bitmap Heap Scan on access_logs
Recheck Cond: (request_uri ^@ '/api/v2'::text)
Heap Blocks: exact=1965
Buffers: shared hit=2092
-> Bitmap Index Scan on access_logs_uri_spgist
Index Cond: (request_uri ^@ '/api/v2'::text)
Buffers: shared hit=127
(7 rows)^@ 被下推为 Index Cond，先用 127 个索引页定位候选，再回表 1965 页。把类型从 text 换成 varchar 也一样支持，因为 text_ops 的 varchar 变体使用同一套分区逻辑。
第 5 步：KNN 对比（四叉树 vs k-d 树）
-- 给 sites 表建两个不同结构的 KNN 索引
CREATE INDEX sites_quad ON sites USING spgist (location quad_point_ops);
CREATE INDEX sites_kd ON sites USING spgist (location kd_point_ops);
-- 四叉树版本
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, location <-> point '(0,0)' AS dist
FROM sites
ORDER BY location <-> point '(0,0)'
LIMIT 5; QUERY PLAN
--------------------------------------------------------------------------------
Limit
-> Index Scan using sites_kd on sites
Order By: (location <-> '(0,0)'::point)
Buffers: shared hit=9
(4 rows)规划器在两个等价索引间自行选择（这里选了 sites_kd）。实际选型时，用真实数据分别建索引、分别 EXPLAIN (ANALYZE, BUFFERS) 对比，才能得出结论。
第 6 步：验证「不支持多列」
CREATE INDEX access_logs_multi ON access_logs USING spgist (client_ip, request_uri);ERROR: access method "spgist" does not support multicolumn indexes这就是那条硬性限制的直接证据。需要多列时改 GiST：
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX access_logs_status_ip_gist
ON access_logs USING gist (status, client_ip);CREATE INDEX第 7 步：验证「不支持唯一索引」
CREATE UNIQUE INDEX access_logs_ip_unique ON access_logs USING spgist (client_ip);ERROR: access method "spgist" does not support unique indexes第 8 步：汇总索引健康度
SELECT indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname IN ('access_logs', 'ip_networks')
ORDER BY pg_relation_size(indexrelid) DESC; indexrelname | idx_scan | idx_tup_read | idx_tup_fetch | size
----------------------------+----------+--------------+---------------+---------
access_logs_uri_spgist | 1 | 20135 | 20135 | 7352 kB
access_logs_ip_spgist | 0 | 0 | 0 | 6288 kB
access_logs_pkey | 0 | 0 | 0 | 21 MB
access_logs_time_btree | 0 | 0 | 0 | 10 MB
ip_networks_spgist | 2 | 2 | 2 | 16 kB
(5 rows)access_logs_ip_spgist 的 idx_scan 为 0，说明本节示例的查询模式里 SP-GiST 的 IP 索引没被用上（前面分析过：连接条件方向不对）。这提醒我们——建了索引不等于用上索引，必须用 idx_scan 长期观测。
注意事项
- 错误！
CREATE INDEX ... USING spgist (a, b)会直接报错。SP-GiST 不支持多列索引，这是结构决定的设计限制。 - 错误！SP-GiST 不支持唯一索引。
CREATE UNIQUE INDEX ... USING spgist (...)报does not support unique indexes。 - 错误！SP-GiST 没有全文搜索操作符类。不要试图用
USING spgist给tsvector建索引。 - 错误！SP-GiST 没有
buffering存储参数。只有fillfactor；写了buffering会报unrecognized parameter。 - 注意：只有
quad_point_ops、kd_point_ops、poly_ops支持 KNN。box_ops、range_ops、inet_ops、text_ops都不支持<->。 - 注意：
point的默认 SP-GiST 类不是kd_point_ops。默认是quad_point_ops，想用 k-d 树必须显式写出来。 - 注意：
inet的默认索引方法不是 SP-GiST。不写USING spgist就会建成 B-Tree。 - 错误！
^@不能做中间匹配。它是前缀操作符，%abc%请用pg_trgm。 - 不推荐在需要多列组合条件时强上 SP-GiST。要么改 GiST +
btree_gist，要么用两个单列 SP-GiST 索引让规划器做位图 AND，后者需要实测验证。 - 不推荐忽略数据分布去谈「谁更快」。SP-GiST 的收益完全取决于查询与分区规则的匹配度；均匀分布的点查询最有利，倾斜严重的范围查询未必优于 GiST。
最佳实践
先确认默认操作符类再动手。
point用quad_point_ops、inet和text走 B-Tree 默认，要用 SP-GiST 必须显式写USING spgist：sqlCREATE INDEX t_ip_spgist ON t USING spgist (ip_col); CREATE INDEX t_txt_spgist ON t USING spgist (txt_col); CREATE INDEX t_pt_spgist ON t USING spgist (pt_col); -- 默认 quad CREATE INDEX t_pt_kd ON t USING spgist (pt_col kd_point_ops); -- 显式 k-d点数据的两种类都建、都测，用真实数据和真实查询做 A/B：
sqlCREATE INDEX pts_quad ON pts USING spgist (p quad_point_ops); CREATE INDEX pts_kd ON pts USING spgist (p kd_point_ops); -- 分别 EXPLAIN (ANALYZE, BUFFERS) 对比，然后删掉较差的那个IP 归属查询统一用
>>=+masklen排序，这是最精确的写法：sqlSELECT label FROM ip_networks WHERE network >>= inet '10.5.5.5' ORDER BY masklen(network) DESC LIMIT 1;前缀查询优先考虑
^@，配合text_ops往往能拿到Index Only Scan：sqlSELECT fqdn FROM domains WHERE fqdn ^@ 'api';需要
EXCLUDE且只有一列时，SP-GiST 与 GiST 都可以；涉及多列就必须换 GiST：sql-- 单列：两种都行 CONSTRAINT no_overlap EXCLUDE USING spgist (during WITH &&) -- 多列：只能用 GiST + btree_gist CONSTRAINT no_overlap2 EXCLUDE USING gist (room_id WITH =, during WITH &&)用
pg_opclass+pg_amop定期核对能力，不要凭记忆判断某个类支不支持 KNN：sqlSELECT opc.opcname, count(*) FILTER (WHERE amop.amoppurpose = 'o') AS knn_ops FROM pg_amop amop JOIN pg_opclass opc ON opc.oid = amop.amopfamily JOIN pg_am am ON am.oid = opc.opcmethod WHERE am.amname = 'spgist' GROUP BY opc.opcname ORDER BY knn_ops DESC;写密集场景调低
fillfactor：sqlCREATE INDEX t_pt_spgist ON t USING spgist (p) WITH (fillfactor = 80);长期观测
idx_scan，没被用上的索引果断删除：sqlSELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes WHERE relname = 'your_table' AND idx_scan = 0;
下一节
继续学习：BRIN 索引原理