PHP小码农

Appearance

GiST 索引原理 ​

GIN 解决的是「一个列里存多个值」。但还有一类问题它回答不了：「这两个区间有没有重叠」「哪个门店离我最近」「这个多边形内部有没有点」。这类问题里的值不是一个可枚举的集合，而是一个占据空间、可以互相重叠的区域。

GiST（Generalized Search Tree，通用搜索树）就是为这类「区域型」数据准备的索引框架。它和 GIN 的关系有点像「R 树（空间索引）与倒排索引」的关系：GIN 把值拆成键，GiST 把值抽象成一个可以包住它的边界框。本节讲清 GiST 为什么是框架而不是索引、有损索引与回表复检的机制、KNN 这个独有能力、操作符类总览与 btree_gist 的重要性，最后给出 GiST 与 GIN 的选型对照。

前置知识

阅读本节前，建议先了解：fastupdate 与 gin_pending_list_limit

基础概念 ​

GiST 是「框架」，不是「索引」 ​

B-Tree、哈希、GIN 都是具体的索引实现：它们已经知道自己要索引什么、怎么比较、怎么分裂。GiST 不同，它只提供树的结构（平衡、页分裂、并发控制、WAL 日志、搜索流程），至于「两个键怎么算重叠」「怎么选分裂点」「怎么把值压成边界框」，全部交给操作符类去实现。

text
        B-Tree / GIN                         GiST
  ┌──────────────────────┐        ┌──────────────────────┐
  │ 结构 + 语义 全都在内核里 │        │ 结构（平衡树、并发、WAL）│  ← GiST 提供
  └──────────────────────┘        ├──────────────────────┤
                                   │ 语义（比较、合并、分裂）  │  ← 操作符类提供
                                   └──────────────────────┘

这个设计带来一个直接结果：你可以用 SQL 定义自己的 GiST 操作符类，让别人写的 GiST 支持你的数据类型。PostGIS 的几何索引就是基于 GiST 实现的。

官方文档的说法

"GiST stands for Generalized Search Tree. It is a balanced, tree-structured access method, that acts as a base template in which to implement arbitrary indexing schemes." —— 它是模板，B-Tree、R 树都能在它之上实现。

GiST 与 B-Tree 的根本区别：没有全序 ​

B-Tree 的前提是「任意两个键都能比较大小」，因此它能维护一个全局有序的键序列，支持 <、=、> 以及范围扫描。

GiST 放弃了「全序」这个前提。它只要求操作符类实现一个「合并」操作（union）——把一组键抽象成一个能包含它们的父键。树的每个内部节点存的是「子树里所有键的合并结果」，而不是「某个中间值」。

text
B-Tree（全序，内部节点是分割点）        GiST（无全序，内部节点是包围盒）

            [ 50 ]                          ┌───────────────────┐
           /      \                         │  包围整个子树的区域  │
      [ 20 ]      [ 80 ]                   └───────────────────┘
      /    \      /    \                     /               \
  [10]  [30]  [70]  [90]               ┌────────┐        ┌────────┐
                                       │ 区域 A  │        │ 区域 B  │
                                       └────────┘        └────────┘
  查询 x > 60：                         查询「与区域 Q 重叠的项」：
  沿路径判断即可，边界清晰                只需访问与 Q 有交集的子树，可能多个

R 树式索引的核心价值就在最后那一步：如果某个子树的外包框与查询区域没有交集，整棵子树都可以跳过。这是「空间剪枝」。

支持三类 B-Tree 做不到的谓词 ​

谓词类别操作符含义典型场景
重叠&&两个区域有交集时间段冲突检测
包含@> <@一个区域包含 / 被包含另一个点在多边形内
相对位置<< >> &< &> |&> <<|严格在左 / 右 / 不延伸至右 …空间排序
相等（区域意义）~=同一个区域几何去重
最近邻（排序）<->距离，用于 ORDER BY附近的门店、相似向量

&& 与 @> 的语义随类型变化

同样是 @>，在数组上表示「包含这些元素」，在范围类型上表示「包含这个点 / 这个区间」，在几何类型上表示「几何包含」。它支持哪些操作符完全由操作符类决定，EXPLAIN 里的 Index Cond 是唯一的验证方式。

有损索引与回表复检 ​

这是 GiST 最需要理解的一点，也是「GiST 索引建了但查询还是慢」的常见原因。

GiST 内部节点存的是抽象后的键：点会被存成边界框，多边形会被存成外接矩形，长文本会被存成签名（signature）。抽象一定是有损的——一个边界框里可能装着很多根本不在查询区域内的实际值。

text
查询某个小圆区域 Q：

  ┌──────────────────────────────────┐
  │  内部节点的边界框                  │
  │        ┌─────┐                    │
  │        │  Q  │  ← 查询区域         │
  │        └─────┘                    │
  │   •         •                     │
  │        •          •   ← 这些点框在  │
  │   •          •          边界框内，  │
  │        •             但不一定在 Q 内 │
  └──────────────────────────────────┘

  索引只能回答「可能在」，不能回答「一定在」
  → 必须回表（或对索引项本身）复检真实值

这个「可能命中」在 EXPLAIN 里表现为两个标志：

计划节点含义
Recheck Cond: (...)索引只给出候选，条件需要重新求值
Rows Removed by Index Recheck: N复检后被剔除的假阳性行数
text
                          QUERY PLAN
-------------------------------------------------------------
 Index Scan using idx_events_period on events
   Index Cond: (during && '[...)'::tstzrange)
   Rows Removed by Index Recheck: 1243        ← 假阳性
(3 rows)

有损不等于劣质

有损+复检是 GiST 用空间换正确性的方式。Rows Removed by Index Recheck 只要不是「与结果集同数量级」，索引就是有效的。真正需要警觉的是它远大于 actual rows——说明索引的选择性几乎为零，此时应该改用更精确的索引（比如 GIN）或调整查询条件。

KNN：GiST 独有的杀手特性 ​

GiST 的 distance 接口让它可以做有序扫描（ordered scan）。这意味着数据库不需要「先取全部候选、再算距离、再排序」，而是沿着距离递增的顺序返回行，取够 LIMIT 就停。

text
无 KNN（普通索引 / 全表）：
  扫描全部行 → 每行算距离 → 排序 → 取前 10
  代价 ∝ 表大小

有 KNN（GiST + ORDER BY <->）：
  按距离从近到远遍历树 → 取到第 10 行即停
  代价 ∝ 需要的行数

使用方式很直白：

sql
SELECT id, name
FROM stores
ORDER BY location <-> point '(116.4, 39.9)'
LIMIT 10;

ORDER BY 里的 <-> 会被识别成「按距离排序」，配合 GiST 变成有序索引扫描。GIN 不支持这个能力——它是倒排结构，没有「距离」的概念。

操作符类总览 ​

操作符类适用类型索引操作符支持 KNN
box_opsbox&& @> <@ << >> &< &> ~=✅ <->
point_opspoint<< >> <@ ~=✅ <->
poly_opspolygon&& @> <@ << >> &< &> ~=✅ <->
circle_opscircle&& @> <@ << >> &< &> ~=✅ <->
range_ops任意范围类型&& @> <@ << >> &< &> -|- =❌
multirange_ops任意多范围类型同上（多范围版本）❌
inet_opsinet / cidr<< <<= >> >>= = <> < <= > >= &&❌
tsvector_opstsvector@@❌
trgm_opstext 等LIKE ILIKE ~ %❌
btree_gist各种标量类型B-Tree 全部操作符（= < > 等）❌
cube_opscube&& @> <@ << >> …✅
ltree_opsltree@> <@ ~ ?❌

inet_ops 不是默认操作符类

出于历史原因，inet / cidr 类型的默认 GiST 操作符类不是 inet_ops。要用必须显式指定：

sql
CREATE INDEX ON my_table USING gist (my_inet_column inet_ops);

btree_gist 扩展：被低估的关键组件 ​

btree_gist 是一个 contrib 扩展，它给 GiST 加上了「B-Tree 语义」——让 integer、text、timestamp、uuid 这类标量类型也能用 GiST 索引，并支持 =、<、>、<=、>=、<>。

安装：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
text
CREATE EXTENSION

它有两个不可替代的用途。

用途一：把标量列和范围列放进同一个 GiST 索引。

GiST 支持多列索引（这是它相对 GIN 的优势之一），但前提是每一列都有 GiST 操作符类。范围列天然有（range_ops），而 room_id integer 这种标量列默认没有——btree_gist 补上了这一环：

sql
-- 按会议室 + 时间段索引，一条索引同时过滤两个条件
CREATE INDEX idx_bookings_room_period
  ON bookings USING gist (room_id, during);

用途二：EXCLUDE 排他约束的必要前提。

EXCLUDE 约束要求「用索引来表达不允许的组合」，而用于排他约束的索引必须是 GiST（不支持 GIN）。要让「同一会议室的时间段不能重叠」这条业务规则由数据库强制保证，就必须同时用上 btree_gist（管 room_id 的等值）和 range_ops（管 during 的重叠）：

sql
ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);
text
ALTER TABLE

没有 btree_gist 会直接报错

sql
ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);
text
ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

写入代价 ​

GiST 的写入比 B-Tree 贵，原因也是「没有全序」：

步骤做什么代价
penalty决定新键插入哪个子树（选「扩大后体积最小」的那个）每个候选子树都要算
picksplit页满时决定怎么把键分成两半组合优化，开销不小
union更新路径上所有父节点的包围盒可能波及多个祖先
compress索引项压缩（若操作符类提供）与值的复杂度相关

相比之下 B-Tree 的「找到位置、插入、必要时分裂」非常直白。所以 GiST 适合读多写少、且范围查询能真正受益的场景。写入极密集的表要谨慎评估。

GiST 的并发与维护

  • 支持 CREATE INDEX CONCURRENTLY 与 REINDEX INDEX CONCURRENTLY。
  • 支持 fastupdate？不支持——那是 GIN 的存储参数。GiST 没有 pending list，写入是同步的。
  • GiST 也会因为更新/删除产生膨胀，需要 VACUUM；严重时 REINDEX INDEX CONCURRENTLY。

GiST 与 GIN 选型对照 ​

这是本系列最实用的一张表。两者都支持 &&、@> 这类操作符，但在实现路线上完全相反：

维度GiSTGIN
结构平衡树 + 包围盒倒排表 + 行号列表
查询精度有损，需要回表复检无损，直接给出精确行号
查询速度中等（多扫一些假阳性）快（尤其多键同时命中）
索引体积小（只存抽象后的键）大（存所有键的倒排）
写入速度快（只更新包围盒路径）慢（要更新多个键的倒排列表）
更新代价低高（fastupdate 缓解但仍有 pending list）
KNN 最近邻支持（ORDER BY <->）不支持
多列索引支持不支持
EXCLUDE 约束支持不支持
典型场景空间数据、范围重叠、KNN、排他约束全文检索、JSONB / 数组、模糊匹配

一句话选型规则：

  • 需要 KNN 排序 / 多列组合 / EXCLUDE 约束 → 只能用 GiST。
  • 需要 精确、高频、复杂的多条件匹配（全文、JSONB） → 用 GIN。
  • 两者都支持且只是简单的 && / @>：写多读少选 GiST，读多写少选 GIN。

经验值

范围重叠查询（时间段、数值区间）上，GiST 索引体积通常只有 GIN 的 1/3 到 1/2，写入快好几倍，而查询因为复检可能慢 2~5 倍。如果查询 QPS 不高但写入量大，GiST 几乎总是更优的选择。

该用 GiST 的典型场景 ​

场景索引关键操作符
会议室 / 车辆 / 房间预订冲突gist (room_id, during)&&
「附近的门店 / 用户」排序gist (location)<-> + LIMIT
地理围栏（点是否在某区域）gist (area)<@
价格 / 时间区间重叠统计gist (price_range)&&
排除约束（同一资源不重叠）gist (resource_id, during)EXCLUDE ... WITH =, &&
IP 网段包含查询gist (network inet_ops)<< >>
梯形 / 层级标签路径gist (path)@> <@

语法与用法 ​

text
-- 最简形式
CREATE INDEX 索引名 ON 表名 USING gist (列名);

-- 显式指定操作符类（inet、trgm 等必须这样写）
CREATE INDEX 索引名 ON 表名 USING gist (列名 操作符类名);

-- 多列（GiST 支持，GIN 不支持）
CREATE INDEX 索引名 ON 表名 USING gist (列1, 列2);

-- 表达式索引
CREATE INDEX 索引名 ON 表名 USING gist (lower(列名) gist_trgm_ops);

-- 部分索引
CREATE INDEX 索引名 ON 表名 USING gist (列名) WHERE 条件;

-- 在线建索引（不阻塞读写）
CREATE INDEX CONCURRENTLY 索引名 ON 表名 USING gist (列名);

-- KNN 查询写法
SELECT ... ORDER BY 列名 <-> 查询值 LIMIT N;

-- 排他约束（必须是 GiST）
ALTER TABLE 表名 ADD CONSTRAINT 约束名
  EXCLUDE USING gist (列1 WITH =, 列2 WITH &&);

最小可运行示例：

sql
CREATE TABLE demo_gist (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  during tsrange NOT NULL
);

CREATE INDEX idx_demo_gist_during ON demo_gist USING gist (during);
text
CREATE TABLE
CREATE INDEX

详细说明 ​

观察有损索引的复检行为 ​

下面这个例子能直接在 EXPLAIN 里看到 Rows Removed by Index Recheck。

sql
DROP TABLE IF EXISTS gist_demo;

CREATE TABLE gist_demo (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  during tsrange NOT NULL,
  label  text NOT NULL
);

-- 构造大量互相重叠的区间
INSERT INTO gist_demo (during, label)
SELECT
  tsrange(
    timestamp '2026-01-01' + ((g % 10000) || ' minutes')::interval,
    timestamp '2026-01-01' + ((g % 10000 + 120) || ' minutes')::interval
  ),
  'row-' || g
FROM generate_series(1, 200000) AS g;

CREATE INDEX idx_gist_demo_during ON gist_demo USING gist (during);
ANALYZE gist_demo;
text
DROP TABLE
CREATE TABLE
INSERT 0 200000
CREATE INDEX
ANALYZE

查询一个很窄的区间：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM gist_demo
WHERE during && tsrange(timestamp '2026-01-01 05:00', timestamp '2026-01-01 05:05');
text
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=512
   ->  Bitmap Heap Scan on gist_demo (actual rows=3 loops=1)
         Recheck Cond: (during && '["2026-01-01 05:00:00","2026-01-01 05:05:00")'::tsrange)
         Rows Removed by Index Recheck: 11
         Heap Blocks: exact=14
         Buffers: shared hit=470
         ->  Bitmap Index Scan on idx_gist_demo_during (actual rows=14 loops=1)
               Index Cond: (during && '["2026-01-01 05:00:00","2026-01-01 05:05:00")'::tsrange)
               Buffers: shared hit=42
(9 rows)

三个数字连起来读：

text
Bitmap Index Scan  actual rows = 14   ← 索引给出的候选（有损）
Rows Removed by Index Recheck = 11    ← 复检剔除的假阳性
Bitmap Heap Scan   actual rows = 3    ← 真正命中的行
14 - 11 = 3  ✅ 对得上

这 11 行假阳性的来源就是范围类型的内部表示：GiST 对 tsrange 会把它归约成「下界 → 上界」的边界框，长度/包含关系被抽象掉了，所以相邻但不重叠的区间也会被判定为「可能重叠」。

复检不是 bug

Recheck Cond 是 GiST 的固有机制，不是配置错误。只有当 Rows Removed by Index Recheck 远大于 actual rows（例如 1 万 vs 10）时，才说明有损程度过高，索引收益被复检吃掉了。

用 enable_seqscan 验证索引的真实价值 ​

小表上优化器经常忽略索引。要确认 GiST 索引确实有用，可以临时关掉全表扫描对比：

sql
SET enable_seqscan = off;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM gist_demo
WHERE during && tsrange(timestamp '2026-01-01 05:00', timestamp '2026-01-01 05:05');

RESET enable_seqscan;
text
SET
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=512
   ->  Bitmap Heap Scan on gist_demo (actual rows=3 loops=1)
         Recheck Cond: (during && '["2026-01-01 05:00:00","2026-01-01 05:05:00")'::tsrange)
         Rows Removed by Index Recheck: 11
         Heap Blocks: exact=14
         Buffers: shared hit=470
         ->  Bitmap Index Scan on idx_gist_demo_during (actual rows=14 loops=1)
               Index Cond: (during && '["2026-01-01 05:00:00","2026-01-01 05:05:00")'::tsrange)
               Buffers: shared hit=42
(9 rows)

RESET

Buffers: shared hit=512 对 20 万行的表来说非常低（表本身远超 512 页），说明空间剪枝生效了。

btree_gist 的安装与效果 ​

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

SELECT extname, extversion FROM pg_extension WHERE extname = 'btree_gist';
text
CREATE EXTENSION
  extname   | extversion
------------+------------
 btree_gist | 1.8
(1 row)

装上之后，标量列就能参与 GiST 索引：

sql
CREATE TABLE gist_multi (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id integer NOT NULL,
  during  tsrange NOT NULL
);

CREATE INDEX idx_gist_multi ON gist_multi USING gist (room_id, during);

ANALYZE gist_multi;
text
CREATE TABLE
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM gist_multi
WHERE room_id = 7
  AND during && tsrange(timestamp '2026-03-01 09:00', timestamp '2026-03-01 10:00');
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Bitmap Heap Scan on gist_multi (actual rows=0 loops=1)
   Recheck Cond: ((room_id = 7) AND (during && '["2026-03-01 09:00:00","2026-03-01 10:00:00")'::tsrange))
   ->  Bitmap Index Scan on idx_gist_multi (actual rows=0 loops=1)
         Index Cond: ((room_id = 7) AND (during && '["2026-03-01 09:00:00","2026-03-01 10:00:00")'::tsrange))
(4 rows)

注意 Index Cond 里同时出现了两个条件——这是 GiST 多列索引与 btree_gist 组合的直接证据。若用 GIN，这两列必须建两个索引再靠 BitmapAnd 合并。

EXCLUDE 排他约束的完整示例 ​

sql
DROP TABLE IF EXISTS bookings_ex;

CREATE TABLE bookings_ex (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id integer NOT NULL,
  during  tsrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
text
DROP TABLE
CREATE TABLE
sql
INSERT INTO bookings_ex (room_id, during)
VALUES (1, tsrange(timestamp '2026-04-01 09:00', timestamp '2026-04-01 11:00'));
text
INSERT 0 1

插入一个与同一会议室重叠的区间：

sql
INSERT INTO bookings_ex (room_id, during)
VALUES (1, tsrange(timestamp '2026-04-01 10:00', timestamp '2026-04-01 12:00'));
text
ERROR:  conflicting key value violates exclusion constraint "bookings_ex_room_id_during_excl"
DETAIL:  Key (room_id, during)=(1, ["2026-04-01 10:00:00","2026-04-01 12:00:00")) conflicts with existing key (room_id, during)=(1, ["2026-04-01 09:00:00","2026-04-01 11:00")).

换个会议室或换个不重叠的时间段就通过：

sql
INSERT INTO bookings_ex (room_id, during)
VALUES (2, tsrange(timestamp '2026-04-01 10:00', timestamp '2026-04-01 12:00')),
       (1, tsrange(timestamp '2026-04-01 11:00', timestamp '2026-04-01 13:00'));
text
INSERT 0 2

注意第二个值是「首尾相接」（11:00 起，前一个到 11:00 止）。tsrange 默认是左闭右开 [)，所以两者不算重叠，可以插入。如果希望相接也算冲突，必须用 [2026-04-01 09:00, 2026-04-01 11:00] 这样的闭区间写法。

排他约束只能靠 GiST

用 GIN 建 EXCLUDE 约束会直接报错：

sql
ALTER TABLE bookings_ex
  ADD CONSTRAINT no_overlap_gin
  EXCLUDE USING gin (room_id WITH =, during WITH &&);
text
ERROR:  access method "gin" does not support exclusion constraints

KNN 能力的最小验证 ​

KNN 需要 GiST 与 ORDER BY <-> 配合。用一个点类型的小表快速验证：

sql
DROP TABLE IF EXISTS knn_demo;

CREATE TABLE knn_demo (
  id     integer PRIMARY KEY,
  name   text NOT NULL,
  location point NOT NULL
);

INSERT INTO knn_demo (id, name, location)
SELECT g, 'p-' || g, point((g % 100)::float8, (g / 100)::float8)
FROM generate_series(1, 10000) AS g;

CREATE INDEX idx_knn_demo_loc ON knn_demo USING gist (location);
ANALYZE knn_demo;
text
DROP TABLE
CREATE TABLE
INSERT 0 10000
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name FROM knn_demo
ORDER BY location <-> point '(50, 50)'
LIMIT 5;
text
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Buffers: shared hit=12
   ->  Index Scan using idx_knn_demo_loc on knn_demo (actual rows=5 loops=1)
         Order By: (location <-> '(50,50)'::point)
         Buffers: shared hit=12
(5 rows)

关键证据是 Index Scan ... Order By: (location <-> ...) 以及 actual rows=5——只读了 5 行，而不是把 10000 行全部算一遍距离再排序。KNN 的详细实战见 GiST 与最近邻搜索。

GiST 的膨胀与维护 ​

GiST 在大量 UPDATE / DELETE 后同样会膨胀，且没有 pending list 这种「收尾合并」机制，只能靠 VACUUM 清理 dead entries：

sql
SELECT
  c.relname AS index_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
  idx_scan
FROM pg_class c
JOIN pg_stat_user_indexes s ON s.indexrelid = c.oid
WHERE c.relname = 'idx_gist_demo_during';
text
       index_name        | index_size | idx_scan
-------------------------+------------+----------
 idx_gist_demo_during    | 4864 kB    |        3
(1 row)

膨胀严重时重建：

sql
REINDEX INDEX CONCURRENTLY idx_gist_demo_during;
text
REINDEX

重建后同样要校验有效性

sql
SELECT indexrelid::regclass AS invalid_index
FROM pg_index
WHERE NOT indisvalid;
text
 invalid_index
---------------
(0 rows)

GiST 的存储参数 ​

GiST 的存储参数比 GIN 少得多，常用的只有两个：

参数默认作用
fillfactor90页内预留空间，降低后续更新引起的页分裂
bufferingoff离线建索引时用「缓冲构建」加速，代价是建完需要额外一遍清理
sql
-- 更新极频繁的 GiST 索引降低 fillfactor
ALTER INDEX idx_gist_multi SET (fillfactor = 70);

-- 大表离线建索引时开启 buffering
CREATE INDEX idx_gist_big ON gist_multi USING gist (room_id, during) WITH (buffering = on);
text
ALTER INDEX
CREATE INDEX

注意

buffering 只在非并发建索引时有效，CREATE INDEX CONCURRENTLY 中会被忽略。且它会延长建索引时间（多一轮外排序），只在表远大于内存时才有收益。

实战示例 ​

用「门店 + 地理围栏 + 附近搜索」的完整流程串一遍 GiST 的主要能力。

第 1 步：准备扩展与表

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS stores;

CREATE TABLE stores (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  brand      text NOT NULL,
  city       text NOT NULL,
  location   point NOT NULL,
  open_hours tsrange NOT NULL,
  service_area box NOT NULL
);
text
CREATE EXTENSION
DROP TABLE
CREATE TABLE

第 2 步：灌入数据

sql
INSERT INTO stores (brand, city, location, open_hours, service_area)
SELECT
  'brand-' || (g % 20),
  'city-' || (g % 8),
  point((g % 1000)::float8 / 10, (g / 1000)::float8 / 10),
  tsrange(timestamp '2026-01-01 08:00', timestamp '2026-01-01 22:00'),
  box(
    point((g % 1000)::float8 / 10 - 0.5, (g / 1000)::float8 / 10 - 0.5),
    point((g % 1000)::float8 / 10 + 0.5, (g / 1000)::float8 / 10 + 0.5)
  )
FROM generate_series(1, 50000) AS g;
text
INSERT 0 50000

第 3 步：建立三种 GiST 索引

sql
-- KNN 用：单个点列
CREATE INDEX idx_stores_location ON stores USING gist (location);

-- 多列组合：品牌等值 + 服务区域重叠
CREATE INDEX idx_stores_brand_area ON stores USING gist (brand, service_area);

-- 营业时间重叠
CREATE INDEX idx_stores_hours ON stores USING gist (open_hours);

ANALYZE stores;
text
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE

第 4 步：KNN —— 找离用户最近的 5 家门店

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, brand, city
FROM stores
ORDER BY location <-> point '(50, 20)'
LIMIT 5;
text
                                      QUERY PLAN
----------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Buffers: shared hit=13
   ->  Index Scan using idx_stores_location on stores (actual rows=5 loops=1)
         Order By: (location <-> '(50,20)'::point)
         Buffers: shared hit=13
(5 rows)

第 5 步：多列 GiST —— 品牌 + 服务区域包含某点

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, location
FROM stores
WHERE brand = 'brand-7'
  AND service_area @> point '(50, 20)';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on stores (actual rows=3 loops=1)
   Recheck Cond: ((brand = 'brand-7'::text) AND (service_area @> '(50,20)'::point))
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=4
   Buffers: shared hit=28
   ->  Bitmap Index Scan on idx_stores_brand_area (actual rows=4 loops=1)
         Index Cond: ((brand = 'brand-7'::text) AND (service_area @> '(50,20)'::point))
         Buffers: shared hit=24
(8 rows)

第 6 步：营业时间重叠查询

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM stores
WHERE open_hours && tsrange(timestamp '2026-01-01 21:00', timestamp '2026-01-01 23:00');
text
                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1183
   ->  Bitmap Heap Scan on stores (actual rows=50000 loops=1)
         Recheck Cond: (open_hours && '["2026-01-01 21:00:00","2026-01-01 23:00:00")'::tsrange)
         Rows Removed by Index Recheck: 0
         Heap Blocks: exact=1129
         Buffers: shared hit=1169
         ->  Bitmap Index Scan on idx_stores_hours (actual rows=50000 loops=1)
               Index Cond: (open_hours && '["2026-01-01 21:00:00","2026-01-01 23:00:00")'::tsrange)
               Buffers: shared hit=32
(9 rows)

这个例子里所有门店营业时间完全相同，所以索引给出 5 万个候选、复检掉 0 个——索引在这个查询上毫无选择性。这正好说明了判断标准：actual rows 接近表行数时，索引不解决问题，要靠更精确的过滤条件。

第 7 步：查看索引体积与使用情况

sql
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'stores'
ORDER BY pg_relation_size(indexrelid) DESC;
text
        index_name         | index_size | idx_scan
---------------------------+------------+----------
 idx_stores_location       | 3488 kB    |        1
 idx_stores_brand_area     | 4264 kB    |        1
 idx_stores_hours          | 1640 kB    |        1
(3 rows)

对比 GIN：同样 5 万行的 point 数据，GiST 索引通常是「表体积的 30%~60%」，而 GIN 常常接近甚至超过表体积。

注意事项 ​

  • GiST 是有损索引。Recheck Cond 与 Rows Removed by Index Recheck 是正常机制，不是错误。
  • 只有部分操作符类支持 KNN。range_ops、inet_ops、btree_gist 不支持 <->；point_ops、box_ops、poly_ops、circle_ops、cube_ops 支持。用错操作符类时 ORDER BY <-> 会退化成排序而不是索引扫描。
  • ORDER BY 列 <-> 值 是 KNN 的唯一触发形式。把它包进函数（如 ORDER BY distance(location, p)）或写成 ORDER BY (location <-> p) DESC 之后再加别的排序键，KNN 优化都可能失效——必须直接出现在 ORDER BY 的第一位。
  • inet_ops 必须显式指定，它是 inet / cidr 的非默认操作符类。
  • 用 EXCLUDE 必须装 btree_gist（涉及标量列时），且 EXCLUDE 只能建立在 GiST 上。
  • GiST 支持多列，GIN 不支持。需要「等值 + 范围」组合过滤时，GiST 单索引往往优于 GIN 的双索引 BitmapAnd。
  • GiST 没有 fastupdate，写入是同步的。批量导入时不必做「导入后合并」的动作，但也拿不到「追加写」的红利。
  • fillfactor 是 GiST 上最值得调的参数。更新频繁的表降到 70 能显著减少页分裂。
  • buffering = on 与 CONCURRENTLY 互斥，并发建索引时该参数被忽略。
  • 表达式索引必须逐字复刻。CREATE INDEX ... USING gist (lower(name) gist_trgm_ops) 只有在查询里写 lower(name) LIKE ... 时才会命中。

最佳实践 ​

  • 先确认操作符类，再谈优化。用错操作符类的表现是「索引建了但 EXPLAIN 里没有 Index Cond」：

    sql
    SELECT opcname, amname
FROM pg_opclass o
JOIN pg_am a ON a.oid = o.opcmethod
WHERE amname = 'gist'
ORDER BY opcname;
    text
          opcname       | amname
--------------------+--------
 box_ops            | gist
 btree_bool_ops     | gist
 btree_date_ops     | gist
 ...
 point_ops          | gist
 poly_ops           | gist
 range_ops          | gist

  • 需要 KNN 就别犹豫，用 GiST。GIN 无法替代，B-Tree 只能做单维排序。

  • 需要 EXCLUDE 排他约束时必须用 GiST + btree_gist，这是唯一方案。

  • 「等值列 + 范围列」优先用一个多列 GiST 索引，而不是建两个索引让优化器 BitmapAnd：

    sql
    CREATE INDEX idx_bookings_room_period ON bookings USING gist (room_id, during);

    多列顺序有讲究

    与 B-Tree 一样，选择性高的列放前面。gist (room_id, during) 与 gist (during, room_id) 在「先按房间过滤再判重叠」的查询上性能差异明显。

  • 定期检查 Rows Removed by Index Recheck 与结果集的比例。假阳性远大于真实命中时，考虑换 GIN、加部分索引或收紧查询条件。

  • 写入密集的 GiST 索引降低 fillfactor：

    sql
    ALTER INDEX idx_bookings_room_period SET (fillfactor = 70);

  • 范围类型统一用「左闭右开」[) 语义。默认的 tsrange/int4range 就是 [)，能让「首尾相接不算重叠」这条业务规则与数据库语义天然一致；要改成闭区间必须显式写。

  • 大表建 GiST 索引时提高 maintenance_work_mem，并考虑 buffering：

    sql
    SET maintenance_work_mem = '512MB';
CREATE INDEX CONCURRENTLY idx_stores_location ON stores USING gist (location);
RESET maintenance_work_mem;

  • 清理无用 GiST 索引。GiST 的写入代价高于 B-Tree，留着不用的索引是纯亏损：

    sql
    SELECT indexrelid::regclass AS unused_index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'stores' AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
    text
      unused_index   |  size
-----------------+---------
(0 rows)

  • 重建后用 indisvalid 校验，避免 CONCURRENTLY 失败留下的无效索引。

下一节

继续学习：GiST 与几何 / 范围类型

参考链接 ​