GiST 与几何 / 范围类型
上一节我们从框架层面理解了 GiST：它把「值」抽象成可以互相重叠的区域，用一个平衡树把这些区域组织起来，靠操作符类提供「怎么合并、怎么比较」的语义。这一节落到最典型的两个使用场景：几何类型（点、矩形、多边形、圆）与范围类型（整数区间、时间区间、日期区间）。
学完本节你将能：看懂 &&、@>、<@、-|- 这些操作符在不同类型上的实际语义；写出一个真正能拦住「同一会议室时间重叠」的 EXCLUDE USING gist 约束；并且知道为什么 point 用 <@ 而 box 用 @>——这两个方向的差别正是 GiST 几何查询最常踩的坑。
前置知识
阅读本节前，建议先了解：GiST 索引原理
基础概念
几何类型与范围类型：两种「区域」
GiST 擅长处理的「区域型」数据，在标准 PostgreSQL 里主要来自两个类型族。
| 类型族 | 具体类型 | 「区域」的含义 | 是否有明确边界 |
|---|---|---|---|
| 几何类型 | point、box、polygon、circle | 平面上的形状或点 | 有，但可含/不含边界点 |
| 范围类型 | int4range、numrange、tsrange、tstzrange、daterange | 一维数轴 / 时间轴上的区间 | 有，开闭区间明确 |
| 多范围类型 | int4multirange、tsmultirange | 一组互不重叠的一维区间 | 同上 |
两者表面差异很大：一个是二维平面几何，一个是一维区间。但对 GiST 来说它们是同一类问题——「两个区域有没有交集」「一个区域包不包住另一个」。GiST 的 overlap、contains、same 这些策略在几何和范围上都能落地，所以内建操作符类里既有 box_ops 也有 range_ops，两者用的都是同一套树结构。
text
一维范围（range） 二维几何（box / polygon）
[──A──) [──B──) ┌──────────┐
↑ 重叠 │ A │
─────────┼──────────┼───→ └──┬───────┘
│ │ │ ┌────────┐
A 的下界 B 的上界 └───┤ B │
└────────┘
"A && B" 为 true "A && B" 为 trueGiST 在这里的角色
几何类型与范围类型本身就有索引能力，但 B-Tree 只能回答「大于 / 等于 / 小于」——它无法回答「这两个矩形有没有重叠」。原因在上一节讲过：B-Tree 要求全序，而二维平面上的矩形之间不存在自然全序。
GiST 放弃了全序前提，内部节点存的是「能包住子树里所有值的边界框」（几何）或「能覆盖子树里所有值的合并区间」（范围）。查询时只要某一棵子树的边界框与查询区域不相交，整棵子树就能跳过。
sql
-- B-Tree 能做：范围类型的大小比较（比较下界，再比较上界）
SELECT 'int4range(1,5)'::int4range < 'int4range(2,3)'::int4range;
-- B-Tree 不能做，GiST 能做：区间重叠
SELECT 'int4range(1,5)'::int4range && 'int4range(3,9)'::int4range;text
?column?
----------
t
(1 row)
?column?
----------
t
(1 row)重叠、包含、相邻：三组核心谓词
几何与范围类型的操作符很多，但真正驱动索引的只有三类语义。
| 语义 | 几何操作符 | 范围操作符 | 业务含义 |
|---|---|---|---|
| 重叠 | && | && | 两个区域有公共部分 |
| 包含 | @> / <@ | @> / <@ | 一个区域完全包住另一个 / 被包住 |
| 相对位置 | << >> &< &> <<| |>> | << >> &< &> | 严格在左 / 右 / 上 / 下 |
| 相邻 | 无（用 ~= 判同一区域） | `- | -` |
| 几何去重 | ~= | = | 是否为同一个区域 |
| 距离（排序） | <-> | 不支持 | 最近邻 |
~= 与 = 完全不同
几何类型里 ~= 才是「同一个几何对象」（polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))' 为 t）；而 = 比较的是面积。范围类型则相反，= 就是「两个区间完全相同」。这个差异是几何类型历史遗留，务必区分。
操作符语义对照表（可索引部分）
只有被操作符类登记过的操作符才能用上索引。下面这张表是几何与范围类型上「能走 GiST 索引」的清单，写查询时应尽量落在表内。
| 类型 | 可索引操作符（摘要） |
|---|---|
box | << &< && &> >> ~= @> <@ &<| <<| |>> |&>；排序 <-> (box, point) |
circle | << &< &> >> <@ @> ~= && |>> <<| &<| |&>；排序 <-> (circle, point) |
point | |>> << >> <<| ~= <@ (point, box) <@ (point, polygon) <@ (point, circle)；排序 <-> (point, point) |
polygon | << &< &> >> <@ @> ~= && <<| &<| |&> |>>；排序 <-> (polygon, point) |
anyrange | = && @> <@ << >> &< &> -|- |
anymultirange | = && @> <@ << >> &< &> -|- |
注意 point 没有 &&
&&（重叠）只在 box、polygon、circle 上定义，point 没有 &&。要判断「点是否落在矩形内」，正确写法是 point <@ box，走 point_ops；而不是 box @> point。后者虽然也能求值，但不在 GiST box_ops 的可索引操作符清单里，用不上索引。
边界框与 R-Tree 式组织
GiST 在几何场景下最典型的实现就是 R-Tree（R 树）。它的特点是：内部节点存「最小外接矩形」（MBR, Minimum Bounding Rectangle），叶子节点存真实几何对象。
text
┌─────────────────────────────┐
│ MBR（整棵子树的外接矩形） │
│ ┌───────┐ ┌───────┐ │
│ │ MBR_A │ │ MBR_B │ │
│ └───────┘ └───────┘ │
│ ▲ ▲ │
└───────┼────────────┼─────────┘
│ │
┌───────┴──┐ ┌────┴─────┐
│ 真实对象 │ │ 真实对象 │
└──────────┘ └──────────┘由此产生两个直接后果：
- 重叠的 MBR 会导致多路径搜索。若两个子树的 MBR 都覆盖了查询区域，两条路都得走。这就是 GiST 里
Rows Removed by Index Recheck偏高的根源。 - 插入时的分裂策略影响很大。操作符类会用
penalty选「代价最小的子树」、用picksplit选「最不重叠的分裂方式」。数据本身的分布决定了索引质量。
内建操作符类清单（几何与范围相关）
PostgreSQL 核心自带的 GiST 操作符类中，与本节相关的有：
| 操作符类 | 适用类型 | 是否默认 | 排序操作符 |
|---|---|---|---|
box_ops | box | 是 | <-> (box, point) |
circle_ops | circle | 是 | <-> (circle, point) |
point_ops | point | 是 | <-> (point, point) |
poly_ops | polygon | 是（polygon 的默认） | <-> (polygon, point) |
range_ops | 所有 anyrange | 是 | 无 |
multirange_ops | 所有 anymultirange | 是 | 无 |
inet_ops | inet / cidr | 否，需显式指定 | 无 |
poly_ops 的默认性有历史包袱
polygon 的 GiST 默认操作符类是 poly_ops，但同时 polygon 还存在 B-Tree 的操作符类（比较面积）。建索引时若不写操作符类，USING gist (col) 会选 GiST 下该类型的默认类 poly_ops，这是正确的；但查询里若用了 = 这类比较面积的操作符，不会走 GiST 索引。
EXCLUDE 与 GiST 的配合
EXCLUDE 排他约束是 GiST 最有价值的应用场景之一。它的语义是：对约束里列出的每个操作符组合，任意两行都不允许同时为真。
text
EXCLUDE USING gist (room_id WITH =, during WITH &&)
含义：不存在两行，它们 room_id 相等 且 during 区间重叠
room_id | during
--------+--------------------
1 | [09:00, 10:00)
1 | [10:00, 11:00) ← 允许（首尾相接不算重叠）
1 | [09:30, 10:30) ← 拒绝（与第一行重叠）
2 | [09:30, 10:30) ← 允许（房间不同）room_id WITH = 需要 btree_gist
= 是标量操作符，GiST 本身不认。必须安装 btree_gist 扩展，让标量类型也拥有 GiST 操作符类，EXCLUDE 里才能出现 WITH =。
语法与用法
text
-- 1. 几何 / 范围列的普通 GiST 索引
CREATE INDEX 索引名 ON 表名 USING gist (列名);
-- 2. 显式指定操作符类（inet 等非默认类必须写）
CREATE INDEX 索引名 ON 表名 USING gist (列名 inet_ops);
-- 3. 多列（几何 + 标量，需要 btree_gist 支持标量）
CREATE INDEX 索引名 ON 表名 USING gist (标量列, 区域列);
-- 4. 排他约束（只能建在 GiST / SP-GiST 上）
ALTER TABLE 表名 ADD CONSTRAINT 约束名
EXCLUDE USING gist (标量列 WITH =, 区域列 WITH &&);
-- 5. 带 WHERE 的部分索引
CREATE INDEX 索引名 ON 表名 USING gist (期间列) WHERE 状态列 = 'active';
-- 6. KNN 排序（仅部分操作符类支持）
SELECT * FROM 表名 ORDER BY 几何列 <-> 查询值 LIMIT 10;最小可运行示例：给一张会议室预订表加上「同房间时间不重叠」的约束。
sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id int NOT NULL,
during tsrange NOT NULL,
booker text NOT NULL
);
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);text
CREATE EXTENSION
CREATE TABLE
ALTER TABLE详细说明
几何类型字面量与输出格式
几何类型有一套自己的字面量语法，写法错了会直接报类型错误。先统一认一遍。
sql
SELECT
'(1,2)'::point AS pt,
'((0,0),(3,4))'::box AS bx,
'<(0,0),2>'::circle AS cir,
'((0,0),(1,0),(1,1))'::polygon AS pol;text
pt | bx | cir | pol
---------+-----------+---------+-----------------------
(1,2) | (3,4),(0,0) | <(0,0),2> | ((0,0),(1,0),(1,1))
(1 row)几个容易写错的点：
box的输入是两个对角点，输出时 PostgreSQL 会自动交换成「右上角, 左下角」的顺序（如上面输入((0,0),(3,4))，输出(3,4),(0,0)）。circle用尖括号包住圆心与半径：<(x,y),r>。polygon用双层圆括号，顶点按顺序列出，首尾不自动闭合（多边形本身就是闭合的）。point可以用[0]、[1]当数组访问坐标。
sql
SELECT p[0] AS x, p[1] AS y FROM (SELECT '(1,2)'::point AS p) t;text
x | y
---+---
1 | 2
(1 row)几何类型转换函数
box(polygon)、circle(box)、polygon(circle)、path(polygon)、point(box)、bound_box(box, box) 等转换函数在处理索引列时很实用。比如给 polygon 列建索引前，可以先算它的外接矩形 box(pol) 用于粗过滤。
范围类型的构造与 [) 语义
范围类型的默认构造方式是左闭右开，这是 PostgreSQL 刻意选择的结果。
sql
SELECT
int4range(1, 5) AS r_default,
numrange(1.1, 2.2, '[]') AS r_closed,
daterange('2026-01-01', '2026-02-01') AS r_date,
tsrange('2026-01-01 09:00', '2026-01-01 10:00') AS r_ts;text
r_default | r_closed | r_date | r_ts
-----------+-----------+----------------------+--------------------------------
[1,5) | [1.1,2.2] | [2026-01-01,2026-02-01) | ["2026-01-01 09:00:00","2026-01-01 10:00:00")
(1 row)为什么左闭右开是黄金标准？ 因为它让「首尾相接不重叠」这条业务规则与数据库语义天然一致：
sql
SELECT '[09:00,10:00)'::tsrange && '[10:00,11:00)'::tsrange AS adjacent_overlap;text
adjacent_overlap
------------------
f
(1 row)如果改成闭区间 '[09:00,10:00]' 与 '[10:00,11:00]'，10:00 这个时刻被两个区间同时包含，&& 就会返回 t，与会议室「上一场 10:00 结束、下一场 10:00 开始」的现实约定冲突。
整数范围的自动规范化
int4range 会把 [1,5] 自动规范化成 [1,6)，因为它把整数看成离散点。这会影响 = 的比较：
sql
SELECT '[1,5]'::int4range = '[1,6)'::int4range AS normalized_equal;text
normalized_equal
------------------
t
(1 row)而 numrange、tsrange 是连续类型，不做这种规范化。写范围相等判断时要留意。
重叠检测：&& 的索引行为
&& 是几何与范围类型上最常用的操作符，也是最容易验证索引效果的一个。
sql
DROP TABLE IF EXISTS rooms_geom;
CREATE TABLE rooms_geom (
id int PRIMARY KEY,
area box NOT NULL,
name text NOT NULL
);
INSERT INTO rooms_geom
SELECT g,
box(point((g % 100) * 10, (g / 100) * 10),
point((g % 100) * 10 + 5, (g / 100) * 10 + 5)),
'room_' || g
FROM generate_series(1, 50000) AS g;
CREATE INDEX idx_rooms_geom_area ON rooms_geom USING gist (area);
ANALYZE rooms_geom;text
CREATE TABLE
INSERT 0 50000
CREATE INDEX
ANALYZEsql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name
FROM rooms_geom
WHERE area && box(point(0, 0), point(100, 100));text
QUERY PLAN
-------------------------------------------------------------
Bitmap Heap Scan on rooms_geom
Recheck Cond: (area && '(100,100),(0,0)'::box)
Rows Removed by Index Recheck: 3021
Heap Blocks: exact=43
-> Bitmap Index Scan on idx_rooms_geom_area
Index Cond: (area && '(100,100),(0,0)'::box)
(6 rows)这里能看到 GiST 的两个特征：
Recheck Cond：GiST 只保证「边界框可能相交」，真实box是否相交需要复检。Rows Removed by Index Recheck：被剔除的假阳性。本例中数量远小于 50000，说明索引有效。
&& 在 point 上不存在
对 point 列写 WHERE pt && some_box 会直接报错（找不到 && 操作符）。point 的正确用法是 <@，见下一节。
包含关系：@> 与 <@ 的方向陷阱
包含关系有两个方向，选错方向会导致「索引建了但用不上」。方向规则很简单：「被索引的列放在操作符左边还是右边」决定了它能否走索引。
text
point <@ box ✅ 走 point_ops（point 在左）
box @> point ❌ 不在 box_ops 的可索引清单里
box @> box ✅ 走 box_ops
point <@ polygon ✅ 走 point_ops
point <@ circle ✅ 走 point_ops
polygon @> polygon ✅ 走 poly_opssql
DROP TABLE IF EXISTS pois;
CREATE TABLE pois (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
pt point NOT NULL
);
INSERT INTO pois (name, pt)
SELECT 'poi_' || g, point((g % 200) * 1.0, (g / 200) * 1.0)
FROM generate_series(1, 40000) AS g;
CREATE INDEX idx_pois_pt ON pois USING gist (pt);
ANALYZE pois;text
CREATE TABLE
INSERT 0 40000
CREATE INDEX
ANALYZE正确的方向：
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name
FROM pois
WHERE pt <@ box(point(0, 0), point(20, 20));text
QUERY PLAN
--------------------------------------------------------
Bitmap Heap Scan on pois
Recheck Cond: (pt <@ '(20,20),(0,0)'::box)
Heap Blocks: exact=17
-> Bitmap Index Scan on idx_pois_pt
Index Cond: (pt <@ '(20,20),(0,0)'::box)
(5 rows)反向写会用不上索引
WHERE box(point(0,0), point(20,20)) @> pt 语义等价，但 @> 不在 point_ops 的清单里，EXPLAIN 会退化成 Seq Scan。判断「点是否在区域内」时，永远把 point 列放左边、用 <@。
相邻判断：-|- 与业务上的「首尾相接」
范围类型独有的 -|- 用来判断两个区间是否相邻但不重叠。这个操作符在排班、订房、计价分段里非常有用。
sql
SELECT
'[1,5)'::int4range -|- '[5,9)'::int4range AS adjacent,
'[1,5)'::int4range -|- '[6,9)'::int4range AS gap,
'[1,5)'::int4range -|- '[4,9)'::int4range AS overlap;text
adjacent | gap | overlap
----------+-----+---------
t | f | f
(1 row)-|- 也在 range_ops 的可索引清单里，可以用索引加速：
sql
DROP TABLE IF EXISTS rate_segments;
CREATE TABLE rate_segments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
plan_id int NOT NULL,
span numrange NOT NULL
);
CREATE INDEX idx_rate_segments_span ON rate_segments USING gist (span);text
CREATE TABLE
CREATE INDEX空范围是特例
官方文档明确说明：左/右/相邻操作符遇到空范围一律返回 false——空范围不被认为在任何其他范围之前或之后。而其他场景下空范围被当作加法单位元：任何范围与空范围取并集还是它自己。
用 EXCLUDE USING gist 实现「会议室不重叠」
这是本节最实用的部分。需求：同一个会议室，任意两个预订时间段不能重叠。
sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
DROP TABLE IF EXISTS meeting_rooms;
DROP TABLE IF EXISTS bookings;
CREATE TABLE meeting_rooms (
id int PRIMARY KEY,
name text NOT NULL,
seats int NOT NULL CHECK (seats > 0)
);
CREATE TABLE bookings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id int NOT NULL REFERENCES meeting_rooms (id),
during tsrange NOT NULL,
booker text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE bookings ADD CONSTRAINT bookings_room_no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);text
CREATE TABLE
CREATE TABLE
ALTER TABLE插入几条数据验证：
sql
INSERT INTO meeting_rooms VALUES (1, 'A 会议室', 8), (2, 'B 会议室', 20);
INSERT INTO bookings (room_id, during, booker) VALUES
(1, '[2026-09-01 09:00, 2026-09-01 10:00)', 'alice'),
(2, '[2026-09-01 09:30, 2026-09-01 10:30)', 'bob');text
INSERT 0 2
INSERT 0 2现在试图在同一房间插入重叠时间段：
sql
INSERT INTO bookings (room_id, during, booker)
VALUES (1, '[2026-09-01 09:30, 2026-09-01 10:30)', 'carol');text
ERROR: conflicting key value violates exclusion constraint "bookings_room_no_overlap"
DETAIL: Key (room_id, during)=(1, ["2026-09-01 09:30:00","2026-09-01 10:30:00")) conflicts with existing key (room_id, during)=(1, ["2026-09-01 09:00:00","2026-09-01 10:00:00")).但首尾相接是允许的：
sql
INSERT INTO bookings (room_id, during, booker)
VALUES (1, '[2026-09-01 10:00, 2026-09-01 11:00)', 'dave');text
INSERT 0 1三件必须同时成立的事
btree_gist扩展必须安装，否则room_id WITH =直接报data type integer has no default operator class for access method "gist"。- 约束只能用 GiST / SP-GiST。写成
EXCLUDE USING gin (...)会报access method "gin" does not support exclusion constraints。 - 时间列建议用
tstzrange而不是tsrange。跨时区业务里tsrange存的是不带时区的墙上时间，容易产生歧义。
边界框、R-Tree 式组织与假阳性来源
回到 GiST 的内部结构，理解假阳性从哪来。
text
假设 polygon 列上建了 GiST 索引，内部节点存的是外接矩形（MBR）。
查询：找出与 Q 相交的所有 polygon
┌──────────────────────────────────────┐
│ MBR（内部节点的外接矩形） │
│ ┌────┐ ┌────┐ │
│ │ P1 │ ┌───┐ │ P2 │ │
│ └────┘ │ Q │ └────┘ │
│ └───┘ │
│ P3 的真实形状其实离 Q 很远， │
│ 但它的 MBR 与 Q 相交 │
└──────────────────────────────────────┘
结果：P1、P2、P3 都进候选集，P3 靠复检剔除三种典型假阳性成因：
| 成因 | 说明 | 缓解手段 |
|---|---|---|
| MBR 过粗 | 形状不规则，外接矩形远大于真实形状 | 用 box(pol) 单独索引做粗过滤，或改存更规整的几何 |
| MBR 重叠 | 子树之间边界框互相重叠，导致多路径搜索 | 提高 fillfactor、按空间聚集写入 |
| 数据类型损失 | circle 实际按外接框存储 | 对精度要求高时改用 polygon |
sql
-- 观察假阳性规模
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM rooms_geom WHERE area && box(point(0, 0), point(50, 50));text
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on rooms_geom
Recheck Cond: (area && '(50,50),(0,0)'::box)
Rows Removed by Index Recheck: 1487
Heap Blocks: exact=12
-> Bitmap Index Scan on idx_rooms_geom_area
Index Cond: (area && '(50,50),(0,0)'::box)
(6 rows)假阳性的可接受范围
Rows Removed by Index Recheck 与最终 actual rows 同量级甚至更大时，说明索引选择性几乎为零——此时要么收紧查询条件，要么考虑用更精确的结构（例如把几何按网格分片后再索引）。只要它显著小于表行数，GiST 就是赚的。
内建操作符类逐个核对
「索引建了没用上」最常见的原因就是操作符与操作符类不匹配。下面这段查询可以直接查出当前库里 GiST 支持的所有操作符类。
sql
SELECT opcname, amname
FROM pg_opclass o
JOIN pg_am a ON a.oid = o.opcmethod
WHERE amname = 'gist'
AND opcname IN ('box_ops','circle_ops','point_ops','poly_ops','range_ops','multirange_ops','inet_ops')
ORDER BY opcname;text
opcname | amname
----------------+--------
box_ops | gist
circle_ops | gist
inet_ops | gist
multirange_ops | gist
point_ops | gist
poly_ops | gist
range_ops | gist
(7 rows)想进一步确认「某个操作符能不能走某个操作符类」，可以查 pg_amop：
sql
SELECT amopopr::regoperator AS operator, opc.opcname
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 = 'gist'
AND opc.opcname = 'point_ops'
ORDER BY operator::text;text
operator | opcname
-----------------------+-----------
<-> (point,point) | point_ops
<< (point,point) | point_ops
<<| (point,point) | point_ops
<@ (point,box) | point_ops
<@ (point,circle) | point_ops
<@ (point,polygon) | point_ops
>> (point,point) | point_ops
|>> (point,point) | point_ops
~= (point,point) | point_ops
(9 rows)为什么 @> 不在 point_ops 里
因为「point 包含 box」在语义上没有意义。只有「box 包含 point」，反向写作 point <@ box。操作符类只登记真正有意义的组合，这是设计而不是遗漏。
范围列的更新与膨胀
范围列经常被 UPDATE（例如延长结束时间），而 GiST 是同步写入的索引——没有类似 GIN fastupdate 的延迟合并机制，每次更新都会真实修改索引。频繁更新会导致索引膨胀。
sql
-- 观察索引体积
SELECT pg_size_pretty(pg_relation_size('bookings')) AS heap_size,
pg_size_pretty(pg_relation_size('bookings_room_no_overlap')) AS idx_size;text
heap_size | idx_size
-----------+----------
8192 bytes | 16 kB
(1 row)对于更新密集的范围列，建议配合 fillfactor：
sql
ALTER INDEX bookings_room_no_overlap SET (fillfactor = 80);text
ALTER INDEX约束背后的索引不能随便重建
EXCLUDE 约束依赖一个隐式创建的 GiST 索引。用 REINDEX 重建它是安全的；但不要用 DROP INDEX 删掉它——那会连带删除约束。若 CONCURRENTLY 重建失败留下 indisvalid = false 的索引，约束也可能处于不可信状态，需要用 ALTER TABLE ... VALIDATE CONSTRAINT 复核。
box 与 polygon 的取舍
同样是「面」，选 box 还是 polygon 影响很大。
| 维度 | box | polygon |
|---|---|---|
| 索引精度 | 高（本身就是矩形的精确表示） | 中（内部按外接框组织，需复检） |
| 存储开销 | 小（两个点） | 大（N 个点） |
| 支持的操作符 | && @> <@ ~= << … + KNN | 同左（另有 <<| &<| 等） |
| 适用场景 | 网格、楼层平面矩形区域、IP 段类比 | 行政区划、地块、任意形状围栏 |
如果业务上的区域本来就是矩形（例如机房机柜、停车场车位、地图切片），用 box 比用 polygon 更划算：索引精确、体积小、假阳性几乎为零。
sql
SELECT
box(point(0,0), point(10,10)) && box(point(5,5), point(15,15)) AS boxes_overlap,
'((0,0),(10,0),(10,10),(0,10))'::polygon && '((5,5),(15,5),(15,15),(5,15))'::polygon AS polys_overlap;text
boxes_overlap | polys_overlap
---------------+---------------
t | t
(1 row)实战示例
需求：为一个共享办公空间实现「工位预订系统」，要求：
- 每个工位在同一时间段只能被一个人预订；
- 支持「查某时刻哪些工位空闲」；
- 支持「查与某时间段冲突的所有预订」。
第 1 步：建表与约束
sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
DROP TABLE IF EXISTS desks;
DROP TABLE IF EXISTS desk_bookings;
CREATE TABLE desks (
id int PRIMARY KEY,
code text NOT NULL UNIQUE,
floor int NOT NULL,
seats int NOT NULL CHECK (seats BETWEEN 1 AND 10)
);
CREATE TABLE desk_bookings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
desk_id int NOT NULL REFERENCES desks (id),
during tstzrange NOT NULL,
member_id bigint NOT NULL,
status text NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'cancelled')),
created_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE desk_bookings ADD CONSTRAINT desk_bookings_no_overlap
EXCLUDE USING gist (desk_id WITH =, during WITH &&)
WHERE (status = 'active');
CREATE INDEX idx_desk_bookings_during ON desk_bookings USING gist (during);text
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE INDEX部分排他约束
EXCLUDE ... WHERE (status = 'active') 让约束只作用于未取消的预订。取消的记录不参与冲突检查，这正是业务想要的——否则一旦取消过，那个时间段就永远无法再被预订。
第 2 步：灌入测试数据
sql
INSERT INTO desks
SELECT g, 'D-' || lpad(g::text, 3, '0'), (g % 3) + 1, 1 + (g % 4)
FROM generate_series(1, 200) AS g;
INSERT INTO desk_bookings (desk_id, during, member_id)
SELECT
(g % 200) + 1,
tstzrange(
'2026-09-01 00:00+08'::timestamptz + ((g % 10) * interval '1 hour'),
'2026-09-01 00:00+08'::timestamptz + ((g % 10) * interval '1 hour') + interval '1 hour'
),
(g % 50) + 1
FROM generate_series(1, 2000) AS g
ON CONFLICT DO NOTHING;
ANALYZE desk_bookings;text
INSERT 0 200
INSERT 0 2000
ANALYZE第 3 步：查「某时刻哪些工位空闲」
思路：找出该时刻有活跃预订的工位，再从全集里排除。
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT d.id, d.code
FROM desks d
WHERE NOT EXISTS (
SELECT 1
FROM desk_bookings b
WHERE b.desk_id = d.id
AND b.status = 'active'
AND b.during @> '2026-09-01 03:30+08'::timestamptz
);text
QUERY PLAN
----------------------------------------------------------------------
Hash Anti Join
Hash Cond: (d.id = b.desk_id)
-> Seq Scan on desks d
-> Hash
-> Bitmap Heap Scan on desk_bookings b
Recheck Cond: (during @> '2026-09-01 03:30:00+08'::timestamp with time zone)
Filter: ((status = 'active'::text) AND ...)
-> Bitmap Index Scan on idx_desk_bookings_during
Index Cond: (during @> '2026-09-01 03:30:00+08'::timestamp with time zone)
(8 rows)这里 during @> timestamptz 用的是「范围包含元素」，落在 range_ops 的可索引清单里（@> (anyrange, anyelement)），因此走了 GiST 索引。
第 4 步：查「与某时间段冲突的所有预订」
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, desk_id, during, member_id
FROM desk_bookings
WHERE during && tstzrange('2026-09-01 02:00+08', '2026-09-01 04:00+08')
AND status = 'active';text
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on desk_bookings
Recheck Cond: (during && '["2026-09-01 02:00:00+08","2026-09-01 04:00:00+08")'::tstzrange)
Filter: (status = 'active'::text)
Rows Removed by Index Recheck: 38
Heap Blocks: exact=7
-> Bitmap Index Scan on idx_desk_bookings_during
Index Cond: (during && '[...)'::tstzrange)
(7 rows)第 5 步：验证约束确实生效
sql
-- 试图在同一工位同一时段重复预订
INSERT INTO desk_bookings (desk_id, during, member_id)
SELECT desk_id, during, 999
FROM desk_bookings
LIMIT 1;text
ERROR: conflicting key value violates exclusion constraint "desk_bookings_no_overlap"
DETAIL: Key (desk_id, during)=(3, ["2026-09-01 02:00:00+08","2026-09-01 03:00:00+08")) conflicts with existing key (desk_id, during)=(3, ["2026-09-01 02:00:00+08","2026-09-01 03:00:00+08")).第 6 步：验证「取消后时段可复用」
sql
UPDATE desk_bookings SET status = 'cancelled'
WHERE id = (SELECT id FROM desk_bookings ORDER BY id LIMIT 1);
INSERT INTO desk_bookings (desk_id, during, member_id)
SELECT desk_id, during, 888
FROM desk_bookings
WHERE status = 'cancelled'
LIMIT 1;text
UPDATE 1
INSERT 0 1第二次插入成功，说明部分排他约束按预期工作。
第 7 步：给几何列加一层「空闲工位地图」
如果工位在平面上有坐标，可以用 point + gist 直接做空间查询。
sql
ALTER TABLE desks ADD COLUMN loc point;
UPDATE desks
SET loc = point((id % 20) * 2.5, (id / 20) * 2.5);
CREATE INDEX idx_desks_loc ON desks USING gist (loc);
ANALYZE desks;text
ALTER TABLE
UPDATE 200
CREATE INDEX
ANALYZE查某个矩形范围内的工位：
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, code, floor
FROM desks
WHERE loc <@ box(point(0, 0), point(12, 12));text
QUERY PLAN
--------------------------------------------------------
Bitmap Heap Scan on desks
Recheck Cond: (loc <@ '(12,12),(0,0)'::box)
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_desks_loc
Index Cond: (loc <@ '(12,12),(0,0)'::box)
(5 rows)第 8 步：汇总检查索引健康状况
sql
SELECT indexrelid::regclass AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname IN ('desk_bookings', 'desks')
ORDER BY pg_relation_size(indexrelid) DESC;text
index_name | idx_scan | size
-----------------------------+----------+---------
desk_bookings_no_overlap | 12 | 152 kB
idx_desk_bookings_during | 8 | 120 kB
idx_desks_loc | 2 | 24 kB
desks_pkey | 0 | 16 kB
desks_code_key | 0 | 16 kB
(5 rows)desks_pkey 与 desks_code_key 的 idx_scan 为 0，是本节示例没有产生等值查找所致，真实环境应结合 pg_stat_reset() 后的长期观测判断。
注意事项
EXCLUDE只能建在 GiST / SP-GiST 上。EXCLUDE USING gin (...)会直接报错。用btree_gist让标量列参与排他约束是标准做法。point没有&&。判断「点在区域内」必须写point <@ 区域，把point列放在左边。box @> point不是可索引写法。box_ops的清单里只有@> (box, box)；要查「矩形内的点」，反写成point <@ box。- 几何类型的
=比较面积，不是比较形状。判断两个几何对象是否相同要用~=。 int4range会自动规范化。'[1,5]'::int4range等于'[1,6)'::int4range，写相等判断时别踩这个坑。- 不推荐用
tsrange存跨时区业务的时间区间。改tstzrange，避免夏令时与多时区歧义。 - 不推荐给范围列写没有索引支持的操作符。例如
upper(during) - lower(during) > '2 hours'这类表达式无法走索引，需要建表达式索引或改用during <@ ...的等价条件。 - GiST 不支持唯一索引。想要区域唯一性，只能用
EXCLUDE；CREATE UNIQUE INDEX ... USING gist会报错。 inet_ops必须显式指定。虽然本节聚焦几何与范围，但 IP 段包含查询（<<>>）也是同一套路，且它是非默认操作符类。- 更新密集的范围列要关注膨胀。GiST 同步写入、无延迟合并，建议配合
fillfactor与定期REINDEX。
最佳实践
业务区域是矩形就用
box，不要为了「通用」一律上polygon。索引更精确、体积更小、几乎无假阳性。时间区间一律用左闭右开。
tstzrange('2026-09-01 09:00+08', '2026-09-01 10:00+08')的默认[)语义与「首尾相接不算重叠」天然一致：sqlSELECT tstzrange('2026-09-01 09:00+08', '2026-09-01 10:00+08') && tstzrange('2026-09-01 10:00+08', '2026-09-01 11:00+08') AS overlap;textoverlap --------- f (1 row)排他约束优先用部分约束，把「已取消」「已过期」的脏数据排除在冲突检查之外：
sqlALTER TABLE desk_bookings ADD CONSTRAINT desk_bookings_no_overlap EXCLUDE USING gist (desk_id WITH =, during WITH &&) WHERE (status = 'active');多列 GiST 的列顺序按选择性排列。「等值 + 范围」组合中，等值的高选择性列放前面：
sqlCREATE INDEX idx_bookings_desk_period ON bookings USING gist (desk_id, during);EXPLAIN里同时看Index Cond与Rows Removed by Index Recheck，前者确认索引被使用，后者确认代价可接受。定期核对操作符类。用
pg_opclass+pg_amop查询确认你依赖的操作符真的被登记：sqlSELECT amopopr::regoperator AS operator, opc.opcname 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 = 'gist' AND opc.opcname = 'range_ops' ORDER BY operator::text;textoperator | opcname ----------------------+----------- && (anyrange,anyrange) | range_ops -|- (anyrange,anyrange) | range_ops << (anyrange,anyrange) | range_ops <@ (anyrange,anyrange) | range_ops = (anyrange,anyrange) | range_ops >> (anyrange,anyrange) | range_ops @> (anyrange,anyelement) | range_ops @> (anyrange,anyrange) | range_ops (8 rows)写入密集的场景把
fillfactor调到 70~80：sqlALTER INDEX desk_bookings_no_overlap SET (fillfactor = 80);需要「最近的 N 个」时再考虑 KNN。几何列支持
<->，范围列不支持，下一节会专门讲。
下一节
继续学习：GiST 与全文搜索