15 深入专题：索引原理与设计

覆盖 B-tree、GiST、GIN、BRIN 等各类索引访问方法，以及索引选择、覆盖索引与在线重建等设计要点。

B-tree 的 deduplication（去重）和 INCLUDE 列的关系是什么？

B-tree deduplication 是 PG13 引入的，把叶子页中重复的索引 key 压缩成一个 + posting list，减少索引体积。但 B-tree 有非 key 列（INCLUDE 列）时不使用 deduplication——源码和文档都明确这一点。因为 INCLUDE 列是 payload，不同行的 payload 不同，不能简单去重合并。所以 INCLUDE 覆盖索引虽然能减少回表，但会以失去 deduplication 收益为代价，索引可能更大。

BRIN 索引的原理和适用场景是什么？它为什么能瘦身几百倍？

BRIN（Block Range Index）是块级范围索引：为每段连续的 heap 物理块（block range）记录被索引列在该范围内的最小值和最大值摘要。查询时先根据范围条件跳过不匹配的 block range，只访问可能匹配的块。它极其紧凑（每个 range 只有一条摘要），可瘦身几百倍，适合数据按索引列物理有序（如 IoT 时序数据、append-only 按时间写入）的大表。局限：强依赖数据物理顺序，数据随机分布时剪枝效果差；只适合范围/等值过滤（配合 recheck），不适合精确点查排序。PG14 起 BRIN 支持 multi-range min-max 和 bloom filter 两种 opclass，增强对随机数据和大量 distinct 值等值查询的支持。

Bitmap 索引（on-disk）与 Bitmap Scan 的区别是什么？

Bitmap Index（on-disk，如 Greenplum/Oracle 支持）是一种持久化的索引类型：每个 distinct key 一条压缩 bitmap vector，标记哪些行号属于该 key，用 HRL/WAH 等压缩，适合低基数、读多写少、多条件组合的聚合查询。Bitmap Scan 则是执行期的扫描技术：用索引（可以是 B-tree、GIN 等任何支持 amgetbitmap 的 AM）把候选 TID 写进内存 TIDBitmap，再 BitmapAnd/Or 合并、按页顺序回表。二者本质不同：Bitmap Index 是存储结构，Bitmap Scan 是执行方式，B-tree 也能产出执行期 bitmap。PostgreSQL 官方内核没有 on-disk bitmap index AM（曾有 2006-2008 的 patch 后放弃）。

Bloom 索引的原理是什么？它和 BRIN、Hash 的对比如何？

Bloom 索引用 Bloom filter 结构：每个索引行的每个字段经 hash 函数算出多个 bit 置位，多字段的 bit 相交得到该行对应的签名。查询时拿输入字段值算签名，若对应 bit 不全为 1 则一定不存在（无 false negative），全为 1 则可能存在（有 false positive，需回表 recheck）。它是扁平结构，每次查询都要遍历整个索引（用 buffer ring 读，类似顺序扫描）。对比：BRIN 更紧凑、支持范围查询但依赖物理顺序；Bloom 无物理顺序限制但只能等值查询、体积更大（可含多个字段）；Hash 等值精确但体积远大于 Bloom 且不能多列。Bloom 适合对多字段做任意组合等值过滤的宽表。

Bloom 索引的签名长度和置位 bit 数如何选择？

创建 Bloom 索引时指定 signature length（签名总长）和每字段置位 bit 数（col1-col32）。理论公式：给定 false positive 概率 p，最优签名 bit 数 m = -n*log2(p)/ln2（n 为字段数），每字段置位数 k = -log2(p)。签名以 2 字节整数数组存储，m 可向上取整到 16 的倍数。索引大小约等于 (m/8 + 6)*N 字节（N 为行数，6 为 TID 指针）。false positive 率 p 对应单过滤器，表扫描时大约会得到 Np 个误报（需 recheck）。实际结果通常比公式更差，公式只用于选初始值。

GIN 索引创建为什么慢？PG18 如何并行化？

GIN 索引创建慢是因为要把多值列拆成元素、聚合同一 key 的 posting，且写入路径需要维护倒排结构。PG18 让 GIN 索引创建支持并行，把元素抽取和 posting 聚合分给多个 worker 并行处理，大幅缩短 GIN 建索引时间（尤其大表多值列）。此前 GIN 建索引是串行的，成为大批量导入和全文检索表建索引的瓶颈。

GIN 索引的原理和适用场景是什么？它有什么局限？

GIN（Generalized Inverted Index，倒排索引）把多值列（数组、tsvector、jsonb、hstore）拆成元素，建立’元素 -> TID 列表’的倒排结构，适合’哪些行包含这些 key’的包含/匹配查询（@>、@@、? 等）。它支持多值类型按元素检索，一对多数据模型。局限：posting 里主要存 heap TID，不存词位置，短语搜索和 ranking 需要回表 recheck；写入通过 pending list（fastupdate）缓冲批量合并，但 pending list 未合并时查询要额外扫描，会带来性能抖动；GIN 通常不支持 index-only scan（只保存原值片段）。

GiST 的 KNN（最近邻）排序为什么要求 distance 是子树距离下界？

GiST 支持 ORDER BY indexed_column <-> query LIMIT n，靠 opclass 提供 distance support function。关键要求是内部节点的 distance 必须是该子树里任意真实对象到 query 的距离下界，否则优先队列按距离出队时会把更近的真实对象排到后面，结果顺序错误。以 PostGIS 2D GiST 为例，内部节点返回 box 到 query box 的最小可能距离，叶子节点设 recheck=true 让执行器用真实 geometry 距离修正排序。所以 GiST KNN 不是’知道几何距离’，而是 opclass 提供了可用于优先队列的距离下界。

GiST 索引的定位是什么？它和 B-tree 的根本差别是什么？

GiST（Generalized Search Tree）是可扩展搜索树模板，把’搜索树的数据库工程部分’（页格式、锁、WAL、崩溃恢复、分裂、VACUUM）与’数据类型的搜索语义’（opclass 提供的 consistent/union/penalty/picksplit/distance 等函数）分离，适合空间相交、区间重叠、最近邻等非全序谓词。与 B-tree 的根本差别：B-tree 靠全序关系定位范围、路径通常唯一；GiST 内部节点保存覆盖子树的’谓词摘要’（如 R-tree 的 bounding box），靠 consistent() 判断子树是否可能命中，路径可以有多条，内部节点可以重叠，搜索可能下探多条路径。

Hash 索引的特点和演进是什么？

Hash 索引只支持等值查询，不支持范围、排序。历史上 PostgreSQL 的 hash 索引长期不被推荐（崩溃恢复不完整、不写 WAL），直到 PG10 起才完整支持 WAL 和崩溃恢复，变得可靠。相比 B-tree，hash 索引在纯等值点查、key 较长时可能更紧凑高效，但不能多列、不能排序、不能 index-only scan。PG18/19 还在继续优化 hash 索引（如 streaming read 用于 bulk delete、并行行车记录仪式的 IO 预取地基）。

HypoPG 虚拟索引是什么？它的作用和使用限制是什么？

HypoPG 是 PostgreSQL 的假设/虚拟索引扩展，用于在不真正创建索引的情况下，测试某个索引是否会被优化器采用、以及估算索引大小。它通过 hypopg_create_index() 把索引定义存入连接私有内存（不写 catalog、不膨胀表、不影响其他连接），然后用 EXPLAIN（不含 ANALYZE）查看优化器是否会使用该虚拟索引。限制：虚拟索引不存在，只能用于 EXPLAIN 不能用于 ANALYZE 实际执行；支持 btree/brin/bloom 等 AM；默认借用保留 OID 空间，同时最多约 2500 个虚拟索引。

INCLUDE 覆盖索引的语义是什么？与多列索引的 key 列有什么区别？

INCLUDE 列是非 key 的 payload 列，只出现在叶子索引 tuple 中，不参与搜索、排序和唯一性约束。例如 CREATE UNIQUE INDEX ON t(x) INCLUDE (y)，唯一性只约束 x，不约束 (x,y)。INCLUDE 列用于让 index-only scan 能返回更多列、避免回表。代价是宽列会显著扩大索引体积、更新 payload 列也要维护索引（可能破坏 HOT 更新机会）、且 B-tree 有 INCLUDE 列时不使用 deduplication。非 key 列不能作为 index scan 的搜索条件。

Index Only Scan 的触发条件是什么？为什么计划是 Index Only Scan 但 Heap Fetches 仍然很高？

Index Only Scan 需要三个条件：1) 索引访问方法能返回（或重构）查询需要的原始列值（B-tree 总是支持，GiST/SP-GiST 部分 opclass 支持，GIN/BRIN/Hash 通常不支持）；2) 查询需要的所有列都在索引中（含输出列、过滤条件、连接条件）；3) 目标 heap page 的 visibility map all-visible 位命中率高。计划是 Index Only Scan 但 Heap Fetches 高，是因为 MVCC 可见性信息在 heap tuple 上而不在索引条目里：当 VM 无法证明对应 heap page all-visible（刚导入、刚更新、长事务拖住 VACUUM），执行器仍要回表验证可见性。所以要看 EXPLAIN ANALYZE 的 Heap Fetches，而不是只看节点名。

Multi-Index Bitmap Scan 是什么？什么时候比单个索引扫描好？

Multi-Index Bitmap Scan 不是磁盘上的 bitmap index，而是执行期把多个支持 amgetbitmap 的索引（B-tree、GIN、GiST、BRIN 等）扫描结果写进内存 TIDBitmap，再用 BitmapAnd/BitmapOr 合并，最后 BitmapHeapScan 按物理 block 顺序回表。它解决’多个条件各自有索引、但单索引都不够好’的中间地带，优势在中等选择率区域。代价是启动成本高、丢失索引顺序、work_mem 不足时 bitmap lossify 导致更多 recheck。OR 条件要求每一臂都能匹配到索引路径，否则无法整体转成 BitmapOr。

PG 为什么不会自动选择索引类型？DBA 如何选？

PG 不会根据查询自动选择/建议索引类型，需要 DBA 根据查询语义和数据特征手动选择：等值点查用 btree/hash；多值包含/全文检索用 GIN/RUM；空间/区间/最近邻用 GiST/SP-GiST；时序范围用 BRIN；多字段组合等值用 bloom；低基数组合用（外部）bitmap index。选择依据是查询操作符、选择性、数据物理顺序、更新频率和索引体积的权衡。辅助工具如 HypoPG、pg_qualstats 可帮助评估和发现缺失索引。

PG 的 index include 与索引组织表（IOT）有什么区别？

index include 是在索引叶子结点填充其他字段值（非 key payload 列），减少回表，达到类似聚簇/索引组织表的效果。它比传统 IOT 的好处是不限于主键维度：可以在任何索引、任何维度上 include 任意列，达到’任意组织’的效果。代价是 include 列不参与搜索/排序/唯一性，只用于返回，且增加索引体积。IOT（索引组织表）则整表数据按主键有序存储在主键索引的叶子里，表本身即索引。

PG18 把命中同一索引的多个 OR 条件转换为 = ANY(…) 有什么好处？

PG18 优化器把命中同一索引的多个 OR 条件（如 WHERE a=1 OR a=2 OR a=3）转换为 = ANY(’{1,2,3}’) 形式，这样可以用一次数组索引扫描（SAOP，Scalar Array Operation）处理，而不是低效的 BitmapOr（对每个 OR 分支单独扫一次索引再合并 bitmap）。这减少了索引下探次数和 bitmap 构建开销，提升 OR 等值列表查询的效率。

PG18 的 index searches 统计（explain analyze）解决什么问题？

PG18 让 EXPLAIN ANALYZE 支持 index searches 统计，显示一次索引扫描实际执行的 index search 次数。这用于观察 skip scan、IN/ANY 数组条件等会产生多次索引下探的场景：Index Searches 数反映了扫描做了多少次独立定位，帮助 DBA 判断索引路径是否因为重复下探而变得昂贵，而不只是看’是否用了索引’。

PG18 的 index skip scan 优化做了什么？

PG18 为 B-tree 引入了 skip scan 优化：在复合索引缺少前导列等值条件但后续列有强选择条件时，允许优化器评估用 skip array 动态枚举前导列 distinct 值来做多次小范围搜索，替代整索引扫描。优化器会用被跳过列的 ndistinct 估算搜索次数，若搜索次数超过索引页数则回退。它解决了之前 PG 不支持 index skip scan、需要用递归 SQL 模拟的痛点，在低基数前导列 + 高选择后缀列场景收益明显。

PG19 的 Bloom 索引扫描和 GIN 索引 VACUUM 的 streaming read 优化带来什么提升？

PG19 把 Bloom 索引的 bitmap scan（blgetbitmap）和 GIN 索引的 VACUUM 清理从同步循环读改成 streaming read（流式/异步预取读）。原理是把’发出读请求→阻塞等待→再发下一个’的串行 IO 改成异步预取、IO 合并、流水线，充分发挥 NVMe SSD 高并发 IO 能力。实测：Bloom 索引扫描用 io_uring 提升 3-7 倍，Bloom VACUUM 提升 30%，GIN VACUUM 提升 5 倍。索引越大收益越明显。前提是能提前知道要读哪些块且顺序确定。

PG19 的 GIN 索引垃圾回收为什么能狂飙 5 倍？

GIN 索引 VACUUM 需要遍历 GIN 索引所有页、清理指向已死亡 heap 元组的条目。传统实现是同步循环逐页读，串行 IO 无法发挥 NVMe 高并发能力。PG19（commit 6c228755）把 GIN VACUUM 的页面遍历改成 streaming read（异步预取 + IO 合并 + 流水线），在模拟高延迟 + debug_io_direct 测试下运行时提升高达 5 倍，索引页数和元组数越多优化越明显。

PostGIS 的空间索引和常用数据类型有哪些？

PostGIS 提供 geometry、geography、raster 等空间类型，支持点、线、面、栅格。空间索引主要用 GiST（默认）和 SP-GiST，加速空间关系运算（相交 &&、包含 @>、距离 <->、KNN 等）。核心函数如 ST_Intersects、ST_Distance、ST_Contains、ST_Transform、ST_AsGeoJSON、ST_AsMVT 等。PostGIS 3 拆离 raster 为独立扩展，并优化了空间排序（z-order、Hilbert）和 GEOS 性能。

PostgreSQL 12 nbtree v4 做了哪些关键增强？

PG12 的 nbtree 发展到第四版，主要三点：1) 把 heap ctid 加入 index leaf page 的 key value，使重复值的 heap tuples 在 leaf 里完全按物理行顺序存储，大幅提高扫描重复值时的效率（index & heap correlate=1）；2) 由于 leaf 里有 ctid，key value 唯一，插入重复值时选择最右边的 leaf page 分裂，降低空间浪费（旧版随机选页分裂）；3) internal/branch page 会 truncate 冗余 key value（如多列索引中不用于行定位的属性），让索引更小。另外还引入 REINDEX CONCURRENTLY、减少 B-tree 插入锁开销、pg_stat_progress_create_index 视图等。

PostgreSQL 的 Index Access Method（索引访问方法）是什么？它解决什么问题？

Index Access Method（AM）是 PostgreSQL 核心系统使用索引的统一接口边界：核心通过统一目录、能力标志和 C 回调表来使用索引，B-tree、Hash、GiST、SP-GiST、GIN、BRIN 这些具体索引类型在接口后面各自实现完全不同的数据结构和维护算法。它解决的是’数据库核心如何在不写死索引算法的情况下使用多种索引结构’的问题。每种 AM 的能力标志（能否支持排序、能否 index-only scan、能否多列、能否 bitmap scan 等）不同，优化器据此判断某索引能否满足查询。

PostgreSQL 的 global index（全局索引）是什么？为什么分区表需要它？

PG 目前只有本地索引（针对单表数据）、部分索引（partial index，带 where）、表达式索引。分区表若要对非分区键字段实施全局唯一/主键约束，就需要全局索引：因为本地索引只能保证单个分区内的唯一，无法跨分区。全局索引的 leaf 页需要 keyvalue -> tableoid + ctid（知道记录在哪个子表），而普通索引只是 keyvalue -> ctid。实现上可用 partial index + 多棵树构建，或全局分区索引（索引本身也分区）避免单棵索引过大。PG 社区尚未内置全局索引。

REINDEX CONCURRENTLY 解决什么问题？

REINDEX CONCURRENTLY 在线重建索引，不长时间阻塞 DML，用于替换普通 REINDEX（会锁表阻塞写入）。它分多阶段：用新名字建一个临时索引、扫描表填充、再原子切换，最后删除旧索引。适合：pg_upgrade 升级后重建 nbtree v4 索引、修复损坏索引、消除索引膨胀、重建到指定表空间等场景。PG14 起 reindex/reindexdb 支持指定 tablespace。失败可能留下 invalid 索引需清理。

RUM 索引相比 GIN 的核心提升是什么？代价是什么？

RUM 是 GIN 的增强版倒排索引，核心提升是在 posting 里除 TID 外还存储 additional information（addInfo），例如 tsvector 的词位置信息、或 attach 绑定的业务字段（时间戳/数值等）。这让短语搜索、相关度排序（ORDER BY <=> LIMIT）可以在索引内完成，不必回表取 tsvector 位置或排序字段，Top-N 延迟更低。代价是索引条目更大、build/insert 慢于 GIN（无 pending list 缓冲）、使用 generic WAL records 导致 WAL 更大。适合’全文过滤 + 相关度/短语/业务字段排序’场景，不适合只做简单包含过滤的高频写入场景。

SP-GiST 索引的原理和适用场景是什么？

SP-GiST（Space-Partitioned GiST）用空间划分树（如四叉树 quad-tree、kd-tree、radix tree/trie）组织数据，把数据空间递归划分成互不相交的子树，适合有明显空间/前缀划分结构的数据。常见应用：iprange/网络前缀（radix）、二维点（quad-tree/kd-tree）、文本前缀。与 GiST 的区别在于划分方式：GiST 内部节点可以重叠（如 bounding box），SP-GiST 的空间划分通常是互斥的。SP-GiST 适合范围查询、最近邻和前缀匹配等，PG14 起支持 leaf 结点 include 覆盖列，PG14 还支持 sort 接口加速 build。

Skip Index Scan 是什么？它绕过左前缀规则吗？

Skip Index Scan（B-tree skip scan optimization）不是新索引结构，也不是绕过左前缀规则的万能钥匙，而是 B-tree 扫描中的重定位策略：当多列索引缺少前导列等值条件、但后续列有强选择性条件时，把问题改写成多次小范围 index search（动态枚举前导列的 distinct 值：x=1 AND y=7700、x=2 AND y=7700……），替代一次大范围扫描。只有当前导列 distinct 数很少、后续列条件足够精确、统计信息可信时才划算。它不体现在计划节点名里，要看 EXPLAIN ANALYZE 的 Index Searches 次数。

bottom-up index deletion（PG14）解决什么问题？

PG14 增强 nbtree 的 index tuple deletion，引入 bottom-up index deletion（自底向上索引删除）。它针对频繁更新索引列引起的索引分裂和膨胀问题：传统方式下，即便某页的索引项大部分已死，也要等 VACUUM 整页处理；bottom-up 删除可以在普通索引页分裂时，从叶子页自底向上删除已死的索引项，回收空间、减少分裂和索引膨胀，大幅缓解频繁更新索引列导致的索引分裂和膨胀问题。

pg_repack 和 REPACK CONCURRENTLY（PG19）如何在线整理表？

pg_repack 是外部扩展，pg_repack（REPACK）在 PG19 引入 CONCURRENTLY 选项。传统 REPACK/CLUSTER 需要 ACCESS EXCLUSIVE 锁全程阻塞读写。REPACK CONCURRENTLY 的核心原理是利用逻辑解码捕获 repack 期间的业务变更，在最终切换文件前把这些变更应用到新表文件，从而把阻塞窗口缩短到最后的锁升级、第二阶段回放和文件切换这一小段（仍需短暂 ACCESS EXCLUSIVE 锁，不是绝对零停机）。它用于在线回收表膨胀空间、整理物理存储。

pg_trgm GIN 索引如何支持 like ‘%xxx%’ 模糊查询？

pg_trgm 扩展用 trigram（连续三字符）把文本切分成 token 建立 GIN 倒排索引。like ‘%xxx%’ 这种中缀模糊查询会被转成 trigram 匹配：查询串切成 trigram，用 GIN 找出包含这些 trigram 的行，再回表 recheck 确认。它把’无索引的全表扫’变成’倒排候选 + 回表验证’，大幅提升中缀模糊查询效率。代价是 trigram 对短串（<3 字符）效果差，且索引体积大。

为什么 GIN 倒排索引启动和 recheck 代价高？

GIN 倒排索引的启动代价高：查询需要先定位多个 key 的 entry，再取它们的 posting list，当命中 key 很多或 posting 很长时，构建候选 bitmap 的成本高。recheck 代价高：GIN 只存 TID 不存完整值/位置，匹配后要回表 recheck（如 tsvector 短语位置、数组重叠语义、jsonb 包含语义），候选多时回表和 recheck 开销大。另外 fastupdate 的 pending list 未合并时会额外扫描。这些导致 GIN 在高命中、复杂谓词场景下启动慢、recheck 重。

为什么 PostgreSQL 允许在相同字段上创建多个索引？

PostgreSQL 不强制索引唯一性，相同字段可以创建多个索引（如不同 AM、不同 opclass、不同 include 列、不同部分索引条件）。原因是指标选择依赖具体的查询形态和语义：不同 opclass 支持不同操作符（如 text_pattern_ops 支持 LIKE 前缀、默认 ops 不支持），不同 AM 适用不同查询（等值用 btree/hash、包含用 GIN、范围用 BRIN 等）。多个索引让优化器能针对不同查询选择最优路径。但这也是槽点：可以重复创建一模一样的索引，造成存储和写入浪费，DBA 需要自己监控冗余索引。

为什么与检索字段类型不一致的输入条件有时不能采用索引？

索引条目的比较依赖操作符的语义和类型。当查询条件里的值类型与索引列类型不一致时，PG 需要做隐式类型转换（cast）。如果转换发生在索引列这一侧（例如把列 cast 成条件值的类型，或运算符没有对应索引列的 operator class），优化器就无法直接使用该列的索引（因为索引里存的是原始类型的排序/结构），只能全表扫描或回表过滤。跨类型的操作符必须有匹配的 opclass，且转换不能在索引列上发生，才能走索引。

为什么优化器不选择索引扫描？常见原因有哪些？

常见原因：1) 统计信息陈旧或缺失，优化器低估/高估了行数和选择性（基数估计错误）；2) 查询选择性太低，回表随机 IO 成本高于顺序扫描，优化器认为 seq scan 更便宜（尤其 random_page_cost 配置偏高）；3) 索引列被表达式/函数包裹，无法下推谓词；4) 类型不一致导致隐式转换破坏索引；5) 查询返回列过多、覆盖不足，index only scan 不可行；6) enable_indexscan/enable_bitmapscan 等开关被关闭；7) 索引列序不匹配查询的前导列，且 skip scan 不划算。诊断要看 EXPLAIN ANALYZE 的实际行数 vs 估算行数、cost 设置和索引可用性。

为什么创建索引会堵塞 DML？如何在线创建索引？

普通 CREATE INDEX 需要对表加 ShareLock，会阻塞对表的 DML（写入）。在线创建索引用 CREATE INDEX CONCURRENTLY：它在不长时间阻塞 DML 的情况下分多阶段建索引，先建一个无效索引、扫描表、再在 pg_index 里标记有效，只在最后的元数据更新瞬间需要短暂锁。代价是更慢、更耗资源，且失败可能留下 invalid index 需要清理。REINDEX CONCURRENTLY 同理用于在线重建索引。

为什么创建索引慢？影响 CREATE INDEX 速度的因素有哪些？

创建索引慢的原因：1) B-tree 构建需要对输入数据排序，若排序数据放不下 maintenance_work_mem 就溢出到磁盘（外部排序），内存越小越慢；2) 表越大、索引列越宽、索引越多越慢；3) 并行创建索引（PG11 起 CREATE INDEX 支持并行）可加速，但受 max_parallel_maintenance_workers 限制；4) CONCURRENTLY 创建比普通创建慢且更耗资源。加速手段：调大 maintenance_work_mem、用并行 CREATE INDEX、批量导入时先删索引后重建、PG14 起 GiST/SP-GiST 支持 sorted build、PG17 支持 BRIN 并行创建。

为什么有的函数不能被用来创建表达式索引？

表达式索引要求索引表达式是 immutable（不可变的），即对相同输入永远返回相同结果、不依赖外部状态。只有 immutable 函数才能建表达式索引，因为索引值在插入时计算并存储，查询时必须能保证用相同表达式算出相同结果来匹配索引。stable/volatile 函数（如 now()、random()、依赖配置的函数）结果会变，无法用于表达式索引，否则查询时算出的值与索引里存储的值对不上。

为什么有的索引不支持字符串前缀匹配（like ‘xxx%’）而有的支持？

字符串 like ‘xxx%’ 前缀匹配能否走索引取决于 operator class 和 collation。默认 text_ops 使用 C 库 locale 比较，非 C collation 下 btree 无法保证前缀有序性，因此不走索引。解决办法：用 text_pattern_ops（按字节比较）、varchar_pattern_ops 这类 opclass 建索引，或用 collate ‘C’。这类 pattern_ops 索引专门支持前缀匹配。PG15 起 starts_with() 提供 planner support，collation 为 C 时可走 btree/sp-gist。

为什么有的索引不支持字符串前置 like / ~ 查询？

字符串 LIKE ‘xxx%’ 或 ~ ‘^xxx’ 前缀匹配能否走索引，取决于索引使用的 operator class（opclass）和排序规则（collation）。默认的 text opclass 基于 C 库的 locale 排序规则，很多非 C collation 下无法证明前缀比较能利用 btree 的有序性，导致不能走索引。要支持前缀匹配，通常需要：1) 使用 text_pattern_ops / varchar_pattern_ops 这类按逐字节比较的 opclass；或 2) collation 为 C。PG15 起为 starts_with() 提供了 planner support function，在 collation 为 C 时字符串前缀匹配可走 btree/sp-gist 索引。