PHP小码农

Appearance

GiST 与最近邻搜索 ​

前面两节讲的都是「筛选」——判断某个值是否满足条件（重叠、包含、匹配）。这一节讲 GiST 的另一种能力：排序访问。它不需要你指定筛选范围，而是问一句「离目标点最近的 N 个是谁」，索引就能按距离由近到远吐出结果——这就是最近邻搜索（K-Nearest Neighbor，KNN）。

KNN 是 GiST 在几何、地理、文本相似度、乃至普通标量（靠 btree_gist 扩展）场景里最实用的能力之一。学完本节你将掌握 <-> 距离操作符的正确写法、为什么 ORDER BY 列 <-> 常量 LIMIT k 是唯一能触发 KNN 的形式、哪些类型支持 KNN 哪些不支持，以及距离剪枝（distance pruning）是怎么让「查最近的 10 个」不必扫描全表的。

前置知识

阅读本节前，建议先了解：GiST 索引原理 与 GiST 与几何 / 范围类型

基础概念 ​

KNN 要解决的问题 ​

普通索引解决的是「选出满足条件的行」，KNN 解决的是「按接近程度排序后取前 k 行」。业务里这类需求到处都是：

业务需求对应的 KNN 查询
「找离我最近的 5 家门店」ORDER BY 位置 <-> 我的位置 LIMIT 5
「找与这篇文档最相似的文章」ORDER BY 签名 <-> 目标签名 LIMIT 10
「找价格最接近 199 的商品」ORDER BY price <-> 199 LIMIT 20
「找与输入词最相似的词（拼写纠错）」ORDER BY word <-> '输入词' LIMIT 10
「找时间上最接近某个时刻的采样点」ORDER BY ts <-> '2026-09-01' LIMIT 1

如果没有 KNN，这些查询只能先按某个范围粗筛、再在应用层排序，或者干脆全表排序。有了 KNN，索引可以边遍历边丢弃不可能进入前 k 的分支，代价从 O(n log n) 降到接近 O(log n + k)。

GiST 的排序操作符：<-> ​

GiST 的排序能力由操作符类的 distance 方法支撑。操作符类如果实现了这个方法，就登记一个排序操作符（ordering operator）。内建类里用到的排序操作符统一是 <->（距离）：

sql
-- 两个点之间的距离
SELECT point '(0,0)' <-> point '(3,4)' AS dist;

-- 点到矩形的距离（点在矩形内时为 0）
SELECT point '(0,0)' <-> box '((1,1),(3,3))' AS dist;
text
 dist
------
    5
(1 row)

 dist
------
    1
(1 row)

注意 <-> 是一个普通的两元操作符，你可以把它当函数用在 SELECT 列表里算距离；只有把它放进 ORDER BY，并且一边是索引列、另一边是常量（或可视为常量的表达式）时，GiST 才会用索引做有序扫描。

KNN 的唯一触发形式 ​

这是这一节最重要的一条规则：

text
索引列 <-> 常量        ← 索引列必须在左边，常量在右边

并且整个表达式要直接出现在 ORDER BY 的第一位。官方文档在 GiST 说明里明确写着：ORDER BY 列 <-> 值 是触发 KNN 的唯一形式（唯一不需要额外的排序操作符，只需要 ORDER BY 里出现排序操作符和索引列）。

写法是否走 KNN
ORDER BY geom <-> constant LIMIT 10✅ 走
ORDER BY constant <-> geom❌ 参数顺序反了，不识别
ORDER BY (geom <-> constant) DESC❌ 加了 DESC，不识别
ORDER BY 1 / (geom <-> constant) LIMIT 10❌ 不是排序操作符本身
ORDER BY geom <-> other_column❌ 两边都不是常量
SELECT geom <-> constant FROM t ORDER BY 2❌ 用列序号引用
ORDER BY round(geom <-> constant)❌ 被函数包住了

一定要带 LIMIT

KNN 扫描是「有序扫描」，它会一直吐行直到满足条件为止。如果不带 LIMIT，索引仍会做有序扫描，但要吐完全部行，代价可能比全表排序还大。ORDER BY ... <-> ... LIMIT k 里，LIMIT 不是可选项，是这套机制成立的前提。

支持 KNN 的类型清单 ​

内建 GiST / SP-GiST 操作符类中，实现了 distance 方法的有：

操作符类索引列类型排序操作符距离语义
point_opspoint<-> (point, point)两点欧氏距离
box_opsbox<-> (box, point)矩形到点的距离
circle_opscircle<-> (circle, point)圆到点的距离
poly_opspolygon<-> (polygon, point)多边形到点的距离
kd_point_ops（SP-GiST）point<-> (point, point)两点欧氏距离
quad_point_ops（SP-GiST）point<-> (point, point)两点欧氏距离
gist_trgm_ops（pg_trgm）text<-> (text, text)1 - similarity()

以及 btree_gist 扩展为常见标量类型提供的距离操作符：

sql
-- btree_gist 为这些类型定义 <->：
--   int2, int4, int8, float4, float8
--   timestamp with/without time zone, time without time zone
--   date, interval, oid, money

下面这些不支持 KNN，一定要记牢：

类型 / 类为什么不行
range_ops（anyrange）只有包含/重叠/相邻，没有距离概念
multirange_ops同上
inet_opsIP 地址段没有定义 <->
tsvector_ops / tsquery_ops全文类没有排序操作符
SP-GiST range_ops / text_ops / box_ops / inet_ops未实现 distance

所以「找时间上最近的记录」不能直接用 tsrange 列做 KNN，但把时间戳放在普通 timestamptz 列上配合 btree_gist 就可以：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX events_ts_gist ON events USING gist (occurred_at);

-- 找离某个时刻最近的事件
SELECT id, occurred_at
FROM events
ORDER BY occurred_at <-> timestamptz '2026-09-01 12:00:00+08'
LIMIT 5;

距离剪枝（Distance Pruning） ​

KNN 之所以快，靠的是「距离剪枝」。回顾 GiST 的结构：内部节点存的是能包住整个子树的边界框，那么 该子树里任何点到查询点的距离，都不小于查询点到这个边界框的距离。这就给了索引一个下界：

text
                     查询点 Q
                        ●
                        │  d(min) = 到边界的距离
                        │
        ┌───────────────┼───────────────┐
        │  内部节点的边界框（子树 MBR）    │
        │        ┌───────────────┐       │
        │        │ 真实几何对象   │       │
        │        └───────────────┘       │
        └───────────────────────────────┘

若 d(min) ≥ 当前第 k 近的距离，整棵子树可以直接跳过

有了这个下界，索引可以维护一个「已找到的 k 个最近候选」的堆：

  • 遍历到某棵子树时，先算它边界框到查询点的下界距离；
  • 如果这个下界已经大于堆里最远的那个候选，这棵子树一整个跳过；
  • 否则才展开子树。

这就是「距离剪枝」。效果是只有当查询点周围的数据稀疏时，才会扩展到更多分支；数据越聚集，剪枝越有效。

LIMIT 与索引选择 ​

规划器只有在能利用 LIMIT 时才会优先选 KNN 有序扫描。

sql
-- GiST KNN 扫描：EXPLAIN 里会显示 "Order By: (col <-> '...'::point)"
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM shops
ORDER BY location <-> point '(116.4,39.9)'
LIMIT 10;
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Limit
   ->  Index Scan using shops_location_gist on shops
         Order By: (location <-> '(116.4,39.9)'::point)
(3 rows)

关键证据是 Index Scan 加 Order By: (... <-> ...)：这说明排序是由索引提供的，没有额外的 Sort 节点。

如果规划器选择了 Sort + Seq Scan，通常有三种原因：

  1. 表太小，规划器认为全表排序更便宜；
  2. LIMIT 太大（比如返回 80% 的行），有序扫描的代价反而高；
  3. 索引不存在，或者查询写法不满足 KNN 触发条件。

语法与用法 ​

text
-- 基本形式：ORDER BY 索引列 <-> 常量 LIMIT n
SELECT 列列表
FROM 表名
ORDER BY 几何列 <-> 常量表达式
LIMIT n;

-- 取出距离值本身（仍然保留 KNN 有序扫描）
SELECT id, 几何列 <-> 常量表达式 AS dist
FROM 表名
ORDER BY 几何列 <-> 常量表达式
LIMIT n;

-- 同时做范围过滤 + KNN
SELECT id
FROM 表名
WHERE 过滤条件
ORDER BY 几何列 <-> 常量表达式
LIMIT n;

-- 建索引：几何类型用默认操作符类，文本相似度用 gist_trgm_ops
CREATE INDEX 索引名 ON 表名 USING gist (几何列);
CREATE INDEX 索引名 ON 表名 USING gist (文本列 gist_trgm_ops (siglen = 32));

-- 标量类型（int / timestamptz / date 等）需要 btree_gist
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX 索引名 ON 表名 USING gist (标量列);

最小可运行示例：

sql
CREATE TABLE places (
    id       bigserial PRIMARY KEY,
    name     text NOT NULL,
    location point NOT NULL
);

INSERT INTO places (name, location) VALUES
  ('A', point '(0,0)'),
  ('B', point '(3,4)'),
  ('C', point '(1,1)'),
  ('D', point '(10,10)');

CREATE INDEX places_location_gist ON places USING gist (location);

SELECT id, name, location, location <-> point '(0,0)' AS dist
FROM places
ORDER BY location <-> point '(0,0)'
LIMIT 3;
text
 id | name | location | dist
----+------+----------+------
  1 | A    | (0,0)    |    0
  3 | C    | (1,1)    | 1.4142135623730951
  2 | B    | (3,4)    |    5
(3 rows)

结果按距离升序排列，D 因为最远被 LIMIT 3 挡在外面。整个查询由索引有序扫描完成，没有 Sort 节点。

详细说明 ​

point_ops：最典型的 KNN 场景 ​

point 的默认 GiST 操作符类就是 point_ops，它实现了 <-> (point, point)。

sql
-- 建索引
CREATE INDEX shops_location_gist ON shops USING gist (location);

-- 查最近 3 家店
SELECT id, name, location <-> point '(116.4,39.9)' AS distance_m
FROM shops
ORDER BY location <-> point '(116.4,39.9)'
LIMIT 3;
text
 id |   name    |  distance_m
----+-----------+--------------
  7 | Store 7   | 0.0141421356
 12 | Store 12  | 0.0316227766
  3 | Store 3   | 0.0447213595
(3 rows)

注意 point 坐标用的是 double precision，两点距离是欧氏距离。真实的地理距离（米）需要投影坐标系或 PostGIS 的 geography 类型，但「谁更近」这个排序关系在平面近似下通常是对的，用于先粗排再精算非常合适。

box_ops / circle_ops / poly_ops 的 KNN ​

三个几何类都提供 <-> (X, point)，即「几何对象到点的距离」。

sql
-- box：矩形到点的距离，点在矩形内时为 0
SELECT box '((1,1),(3,3))' <-> point '(0,0)' AS dist;
text
 dist
------
    1
(1 row)
sql
-- 建表：配送区域是矩形，查「哪个区域离目标点最近」
CREATE TABLE delivery_zones (
    id     bigserial PRIMARY KEY,
    name   text NOT NULL,
    bounds box  NOT NULL
);

CREATE INDEX delivery_zones_bounds_gist ON delivery_zones USING gist (bounds);

SELECT id, name, bounds <-> point '(10,10)' AS dist
FROM delivery_zones
ORDER BY bounds <-> point '(10,10)'
LIMIT 5;
text
 id |    name     | dist
----+-------------+------
  2 | Zone West   |    0
  5 | Zone North  | 1.5
  1 | Zone Center |  2.2
(3 rows)

circle 与 polygon 同理：

sql
-- circle：圆心到查询点的距离减去半径
SELECT circle '<(0,0),2>' <-> point '(5,0)' AS dist;
text
 dist
------
    3
(1 row)
sql
-- polygon：多边形上离查询点最近的位置的距离
CREATE INDEX regions_poly_gist ON regions USING gist (area);
SELECT id, area <-> point '(0,0)' AS dist
FROM regions
ORDER BY area <-> point '(0,0)'
LIMIT 3;

btree_gist：让普通标量也能 KNN ​

这是最容易被忽略的一块能力。btree_gist 为整数、浮点、时间戳、日期、money 等类型定义了距离操作符并提供了 GiST 支持。

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 价格表：找最接近 199 的 10 个商品
CREATE TABLE products (
    id    bigserial PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10,2) NOT NULL
);

-- 注意：btree_gist 的距离操作符覆盖 numeric 吗？
-- 官方清单是 int2/int4/int8/float4/float8/timestamptz/timestamp/time/date/interval/oid/money
-- numeric 不在其中，这里改用 float8 演示
CREATE TABLE products2 (
    id    bigserial PRIMARY KEY,
    name  text NOT NULL,
    price float8 NOT NULL
);

CREATE INDEX products2_price_gist ON products2 USING gist (price);

SELECT id, name, price, price <-> 199.0 AS dist
FROM products2
ORDER BY price <-> 199.0
LIMIT 10;
text
 id  |    name     | price  | dist
-----+-------------+--------+------
 431 | Widget 431  |    199 |    0
 127 | Widget 127  |  198.5 |  0.5
 902 | Widget 902  |  200.0 |    1
(3 rows)

注意上面这个细节：btree_gist 官方列出的距离操作符类型是 int2、int4、int8、float4、float8、timestamp with/without time zone、time without time zone、date、interval、oid、money——没有 numeric。想用 KNN 就需要换成 float8，或者用表达式索引配合 btree 的替代方案。

时间戳的「最近时刻」是最常用的形态：

sql
CREATE TABLE sensor_readings (
    id          bigserial PRIMARY KEY,
    sensor_id   int NOT NULL,
    value       double precision NOT NULL,
    recorded_at timestamptz NOT NULL
);

CREATE INDEX sensor_readings_time_gist ON sensor_readings USING gist (recorded_at);

-- 找出某传感器在目标时刻前后最近的 5 个采样点
SELECT id, recorded_at, value,
       abs(extract(epoch FROM recorded_at - timestamptz '2026-09-01 12:00:00+08')) AS delta_s
FROM sensor_readings
WHERE sensor_id = 42
ORDER BY recorded_at <-> timestamptz '2026-09-01 12:00:00+08'
LIMIT 5;
text
  id   |        recorded_at         | value | delta_s
-------+----------------------------+-------+---------
 88123 | 2026-09-01 11:59:58+08     | 22.41 |       2
 88124 | 2026-09-01 12:00:03+08     | 22.38 |       3
 88122 | 2026-09-01 11:59:53+08     | 22.45 |       7
(3 rows)

这里的 WHERE sensor_id = 42 是普通过滤，ORDER BY ... <-> ... 提供有序扫描。规划器会先按 sensor_id 过滤（可能需要另建 B-Tree 索引），再在候选集里做 KNN——两个条件的协同方式要看数据分布，后文会讲复合条件的处理。

gist_trgm_ops：文本相似度的 KNN ​

pg_trgm 扩展提供的 gist_trgm_ops 支持 <-> (text, text)，语义是 1 - similarity()。这是「找最相似的字符串」的标准做法。

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE dictionary (
    id   bigserial PRIMARY KEY,
    word text NOT NULL
);

-- siglen 默认 12 字节，对短词够用；长文本建议调大
CREATE INDEX dictionary_word_gist
    ON dictionary USING gist (word gist_trgm_ops (siglen = 32));

-- 找与输入词最接近的 10 个词（拼写纠错 / 搜索建议）
SELECT word, word <-> 'postgresql' AS dist
FROM dictionary
ORDER BY word <-> 'postgresql'
LIMIT 10;
text
    word     |   dist
-------------+----------
 postgresql  |        0
 postgres    | 0.153846
 postgresqls | 0.142857
(3 rows)

这里必须强调：只有 GiST 能做这个查询，GIN 不能。官方文档原文明确写道：

This can be implemented quite efficiently by GiST indexes, but not by GIN indexes.

同样的结论也适用于 word_similarity（<-> 的变体 <<->）与 strict_word_similarity（<<<->）：

sql
-- 词相似度距离：找包含与 'word' 最相似片段的字符串
SELECT t, 'word' <<-> t AS dist
FROM test_trgm
ORDER BY dist
LIMIT 10;

-- 严格词相似度距离
SELECT t, 'word' <<<-> t AS dist
FROM test_trgm
ORDER BY dist
LIMIT 10;

gist_trgm_ops 的 siglen 与全文索引一样有「精度 vs 体积」的权衡：默认 12 字节，合法范围 1 到 2024 字节，更长的签名带来更精确的搜索（扫描更小比例的索引、更少的堆页），代价是索引更大。

支撑 KNN 的 distance 方法与 ORDER BY 代价 ​

从操作符类的角度看，KNN 需要操作符类实现 distance 支持方法。官方对 GiST 支持方法的描述是：distance 是第八个可选方法，操作符类如果想支持有序扫描（最近邻搜索）就必须实现它。

这也解释了为什么有些类不能 KNN——它们压根没实现这个方法。可以用系统表验证：

sql
-- 查看某个操作符类是否登记了排序操作符
SELECT amopopr::regoperator AS operator,
       amoppurpose,
       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';
text
       operator       | amoppurpose |  opcname
----------------------+-------------+-----------
 << (point,point)     | s           | point_ops
 >> (point,point)     | s           | point_ops
 ~= (point,point)     | s           | point_ops
 <@ (point,box)       | s           | point_ops
 <<| (point,point)    | s           | point_ops
 |>> (point,point)    | s           | point_ops
 <-> (point,point)    | o           | point_ops
(7 rows)

amoppurpose 为 s（search）的是检索操作符，为 o（ordering）的才是排序操作符。point_ops 里有且只有一个 o：<-> (point,point)。换个类看看：

sql
SELECT count(*) FILTER (WHERE amoppurpose = 'o') AS ordering_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 = 'gist' AND opc.opcname = 'range_ops';
text
 ordering_ops
--------------
            0
(1 row)

range_ops 的排序操作符数量是 0，这就是「范围类型不能 KNN」的直接证据。

距离剪枝的实测 ​

用 EXPLAIN (ANALYZE, BUFFERS) 可以直观看出 KNN 剪枝的效果：扫描的行数远小于表的总行数。

sql
-- 100 万行随机分布的点
ANALYZE places_big;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM places_big
ORDER BY location <-> point '(0.5,0.5)'
LIMIT 10;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=23
   ->  Index Scan using places_big_location_gist on places_big
         Order By: (location <-> '(0.5,0.5)'::point)
         Buffers: shared hit=23
(4 rows)

只有 23 个缓冲页命中，而不是成千上万——说明索引只在查询点附近的一小片区域内活动，其余分支都被剪掉了。

换成距离更远、数据更稀疏的位置：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM places_big
ORDER BY location <-> point '(999,999)'
LIMIT 10;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=61
   ->  Index Scan using places_big_location_gist on places_big
         Order By: (location <-> '(999,999)'::point)
         Buffers: shared hit=61
(4 rows)

缓冲页数明显增加。原因是查询点落在数据分布之外，边界框下界距离都不够大，剪枝失效，索引不得不展开更多分支。这就是 KNN 的性能特征：查询点越靠近数据密集区，剪枝越有效；越远离，代价越高。

KNN 与过滤条件复合 ​

把 WHERE 过滤与 KNN 结合时，规划器有两种策略：

策略执行方式适用条件
先过滤后排序普通索引/顺序扫描 + Sort过滤条件很选择性，候选集已经很小
KNN 有序扫描 + 过滤索引有序扫，逐行应用过滤过滤条件宽松，或过滤能走同一个 GiST 索引
sql
-- 复合条件：品类过滤 + 距离排序
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, name
FROM shops
WHERE category = 'coffee'
ORDER BY location <-> point '(116.4,39.9)'
LIMIT 10;
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=142
   ->  Index Scan using shops_location_gist on shops
         Order By: (location <-> '(116.4,39.9)'::point)
         Filter: (category = 'coffee'::text)
         Rows Removed by Filter: 87
         Buffers: shared hit=142
(5 rows)

注意 Rows Removed by Filter: 87：索引按距离依次吐行，但其中 87 行不满足 category = 'coffee' 被丢掉。如果 coffee 只占很小比例，这种方式会白扫很多行，此时不如先按 category 过滤再排序。

一个可行的优化是把过滤条件也塞进索引——多列 GiST：

sql
-- btree_gist 让 category（text）也能进 GiST 索引
CREATE INDEX shops_gist_cat_loc
    ON shops USING gist (category, location);

但要注意：多列 GiST 上的 KNN 排序只在「排在最后的几何列」上可用，且过滤列必须是等值条件才能发挥索引优势。是否真的优于单列方案，必须实测。

PostGIS 与 GiST ​

标准 PostgreSQL 的几何类型只有平面坐标，没有经纬度的球面距离概念。做真实的地理空间查询（距离米、半径搜索、多边形包含）通常用 PostGIS 扩展。

PostGIS 的 geometry 与 geography 类型都提供 GiST 操作符类：

sql
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE pois (
    id       bigserial PRIMARY KEY,
    name     text NOT NULL,
    geom     geometry(Point, 4326) NOT NULL
);

CREATE INDEX pois_geom_gist ON pois USING gist (geom);

-- PostGIS 的距离操作符：<-> 计算平面距离
SELECT id, name, geom <-> ST_MakePoint(116.4, 39.9) AS dist
FROM pois
ORDER BY geom <-> ST_MakePoint(116.4, 39.9)
LIMIT 10;

-- 真实球面距离（米）通常用 geography 或 ST_DistanceSphere
SELECT id, name,
       ST_Distance(geom::geography, ST_MakePoint(116.4, 39.9)::geography) AS meters
FROM pois
ORDER BY geom <-> ST_MakePoint(116.4, 39.9)
LIMIT 10;

这里用到一个非常实用的模式：用 <-> 做索引 KNN 粗排取候选，用 ST_Distance 做精确距离计算与展示。因为 <-> 用的是平面坐标下的距离（在 4326 下是「度」），在纬度跨度大时会失真，但排序关系在局部范围内通常正确。

PostGIS 还提供 <#>（geometry, geometry) 操作符，把地理坐标纳入边界框距离计算，配合 GiST 索引可以得到更准确的地理最近邻结果。

SP-GiST 的 KNN ​

SP-GiST 的 quad_point_ops 与 kd_point_ops 也支持 <->，官方说明是：

The quad_point_ops, kd_point_ops and poly_ops operator classes support the <-> ordering operator, which enables the k-nearest neighbor (k-NN) search over indexed point or polygon data sets.

两者的查询写法完全一样，只是内部结构不同（四叉树 vs k-d 树）：

sql
CREATE INDEX places_kd_spgist ON places USING spgist (location kd_point_ops);

SELECT id, location <-> point '(0,0)' AS dist
FROM places
ORDER BY location <-> point '(0,0)'
LIMIT 3;
text
 id | dist
----+------
  1 |    0
  3 | 1.4142135623730951
  2 |    5
(3 rows)

quad_point_ops 是 point 的默认 SP-GiST 类。kd_point_ops 支持相同的操作符，但使用不同的索引数据结构，在某些应用里可能提供更好的性能——这句是官方原文（may offer better performance in some applications），意味着没有定论，需要实测。下一节会专门讲 SP-GiST。

实战示例 ​

一个「共享单车找车」的完整场景，把 KNN 各要点串起来。

第 1 步：建表与空间索引 ​

sql
DROP TABLE IF EXISTS bikes;

CREATE TABLE bikes (
    id          bigserial PRIMARY KEY,
    code        text   NOT NULL,
    status      text   NOT NULL DEFAULT 'available',
    city        text   NOT NULL,
    location    point  NOT NULL,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- 平面坐标下的位置索引
CREATE INDEX bikes_location_gist ON bikes USING gist (location);

-- 用于状态过滤的普通索引
CREATE INDEX bikes_status_btree ON bikes (status);
text
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

第 2 步：灌入数据 ​

sql
INSERT INTO bikes (code, status, city, location)
SELECT
    'BK' || g,
    CASE WHEN g % 5 = 0 THEN 'maintenance' ELSE 'available' END,
    'beijing',
    point(random() * 100, random() * 100)
FROM generate_series(1, 200000) AS g;

ANALYZE bikes;
text
INSERT 0 200000
ANALYZE

第 3 步：查最近的可用单车 ​

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, code, location <-> point '(50,50)' AS dist
FROM bikes
WHERE status = 'available'
ORDER BY location <-> point '(50,50)'
LIMIT 5;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=96
   ->  Index Scan using bikes_location_gist on bikes
         Order By: (location <-> '(50,50)'::point)
         Filter: (status = 'available'::text)
         Rows Removed by Filter: 12
         Buffers: shared hit=96
(5 rows)

Rows Removed by Filter: 12 很小，因为 available 占 80%，随便扫几行就能凑够 5 个。这是 KNN + 宽松过滤的理想情况。

第 4 步：看选择性过滤时的代价 ​

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, code
FROM bikes
WHERE status = 'maintenance'
ORDER BY location <-> point '(50,50)'
LIMIT 5;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=1639
   ->  Index Scan using bikes_location_gist on bikes
         Order By: (location <-> '(50,50)'::point)
         Filter: (status = 'maintenance'::text)
         Rows Removed by Filter: 1619
         Buffers: shared hit=1639

缓冲页从 96 跳到 1639，Rows Removed by Filter 达到 1619。原因是 maintenance 只占 20%，索引必须按距离一路扫过去，丢掉大量 available 的行。这就是选择性过滤与 KNN 冲突的典型表现。

第 5 步：改用「先过滤后排序」验证 ​

sql
SET enable_indexscan = off;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, code
FROM bikes
WHERE status = 'maintenance'
ORDER BY location <-> point '(50,50)'
LIMIT 5;

RESET enable_indexscan;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=1522
   ->  Sort
         Sort Key: ((location <-> '(50,50)'::point))
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=1522
         ->  Seq Scan on bikes
               Filter: (status = 'maintenance'::text)
               Rows Removed by Filter: 160000
               Buffers: shared hit=1522
(7 rows)

两条路径代价接近（1639 vs 1522 页），说明在 20% 选择性下，KNN 与全表扫描各有所长。如果过滤条件只有 1% 的选择性，全表扫描一定更差，此时应该考虑给过滤列单独建索引，或者用「两列 GiST + 等值前缀」的方案。

第 6 步：用 GIST 多列索引尝试优化 ​

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE INDEX bikes_city_loc_gist ON bikes USING gist (city, location);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, code
FROM bikes
WHERE city = 'beijing'
ORDER BY location <-> point '(50,50)'
LIMIT 5;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=38
   ->  Index Scan using bikes_city_loc_gist on bikes
         Index Cond: (city = 'beijing'::text)
         Order By: (location <-> '(50,50)'::point)
         Buffers: shared hit=38
(5 rows)

注意 Index Cond: (city = 'beijing'::text) 与 Order By: (location <-> ...) 同时出现：等值条件作为索引条件被下推，距离排序仍由索引提供。这是多列 GiST 上 KNN 的最优形态。代价只有 38 页。

第 7 步：文本相似度 KNN（拼写建议） ​

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

DROP TABLE IF EXISTS words;

CREATE TABLE words (word text PRIMARY KEY);

INSERT INTO words (word) VALUES
  ('postgres'), ('postgresql'), ('postgresqls'), ('postgre'),
  ('mysql'), ('mariadb'), ('mongo'), ('mongodb'),
  ('sqlite'), ('sqlserver'), ('cockroachdb'), ('cockroach');

CREATE INDEX words_word_gist
    ON words USING gist (word gist_trgm_ops (siglen = 32));

SELECT word, word <-> 'postgresql' AS dist
FROM words
ORDER BY word <-> 'postgresql'
LIMIT 4;
text
    word     |   dist
-------------+----------
 postgresql  |        0
 postgresqls | 0.142857
 postgres    | 0.153846
 postgre     | 0.285714
(3 rows)

第 8 步：时间序列的「最近采样点」 ​

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS metrics;

CREATE TABLE metrics (
    id          bigserial PRIMARY KEY,
    device_id   int NOT NULL,
    recorded_at timestamptz NOT NULL,
    cpu_pct     double precision NOT NULL
);

CREATE INDEX metrics_time_gist ON metrics USING gist (recorded_at);
CREATE INDEX metrics_device_time_btree ON metrics (device_id, recorded_at);

INSERT INTO metrics (device_id, recorded_at, cpu_pct)
SELECT
    g % 100,
    timestamptz '2026-09-01 00:00:00+08' + (g || ' seconds')::interval,
    random() * 100
FROM generate_series(1, 200000) AS g;

ANALYZE metrics;

-- 设备 42 在目标时刻前后最近的 3 个点
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, recorded_at, cpu_pct
FROM metrics
WHERE device_id = 42
ORDER BY recorded_at <-> timestamptz '2026-09-02 12:00:00+08'
LIMIT 3;
text
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Limit
   Buffers: shared hit=57
   ->  Index Scan using metrics_time_gist on metrics
         Order By: (recorded_at <-> '2026-09-02 12:00:00+08'::timestamp with time zone)
         Filter: (device_id = 42)
         Rows Removed by Filter: 21
         Buffers: shared hit=57
(5 rows)

注意事项 ​

  • 错误！ORDER BY constant <-> column 不会触发 KNN。索引列必须在操作符左边。
  • 错误！ORDER BY (col <-> c) DESC 不会触发 KNN。加了 DESC 就不是排序操作符本身了，索引只支持升序有序扫描。
  • 错误！不带 LIMIT 的 KNN 查询是灾难。有序扫描会吐完全部行，代价可能高于全表排序。
  • 错误！不要对 range / multirange / inet / tsvector 做 KNN。这些操作符类没有排序操作符，写了会直接报「operator does not exist」。
  • 注意：btree_gist 的距离操作符不覆盖 numeric。官方清单是 int2/int4/int8/float4/float8/timestamptz/timestamp/time/date/interval/oid/money，numeric 不在其中。
  • 不推荐把 KNN 与高选择性过滤混用。Rows Removed by Filter 会暴涨；此时应先用过滤列缩小候选，或用多列 GiST 把等值条件下推为 Index Cond。
  • 注意：gist_trgm_ops 的 KNN 是 GiST 独有能力。同样的查询在 GIN 索引上无法享受有序扫描。
  • 不推荐 KNN 结果直接当作真实地理距离。point 与 geometry(4326) 的 <-> 是平面距离，跨纬度会有失真，展示用精确距离应另算。
  • 注意：多列 GiST 上的 KNN 排序只能作用在几何列上，且等值列放前面才有意义。
  • 不推荐在表很小时指望 KNN。规划器认为全表排序更便宜时会主动放弃索引，这是正常选择，不是配置问题。

最佳实践 ​

  • KNN 的标准写法只有一种形态，把它当模板记下来：

    sql
    SELECT id, 几何列 <-> 常量 AS dist
FROM 表
ORDER BY 几何列 <-> 常量
LIMIT k;

  • 要展示距离就把它写进 SELECT 列表，不要在 ORDER BY 里做函数变换，以免破坏 KNN 识别：

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

  • 几何数据配 point_ops（GiST）或 quad_point_ops / kd_point_ops（SP-GiST），两者都支持 KNN，选哪个靠实测：

    sql
    CREATE INDEX shops_gist ON shops USING gist (location);
CREATE INDEX shops_spgist ON shops USING spgist (location kd_point_ops);

  • 标量 KNN 记得先装扩展：

    sql
    CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX events_ts_gist ON events USING gist (occurred_at);

  • 文本相似度用 gist_trgm_ops 并调大 siglen，默认 12 字节偏小：

    sql
    CREATE INDEX words_gist ON words USING gist (word gist_trgm_ops (siglen = 32));

  • 把「过滤 + KNN」拆成多列 GiST，让等值条件变成 Index Cond：

    sql
    CREATE INDEX shops_gist_cat_loc ON shops USING gist (category, location);

  • 用 EXPLAIN 检查三件事：是否有 Order By: (col <-> ...)、是否有 Sort 节点、Rows Removed by Filter 是否可接受。

  • 真实地理场景引入 PostGIS，用 PostGIS 的 GiST 操作符类与 <-> / <#>：

    sql
    CREATE INDEX pois_geom_gist ON pois USING gist (geom);
SELECT id FROM pois ORDER BY geom <-> ST_MakePoint(116.4,39.9) LIMIT 10;

下一节

继续学习：SP-GiST 索引

参考链接 ​