当 DeepSeek 遇上 ChatGPT，从一个专业的技术领域问题聊起

前言

各位 PGer 大家好，许久不见，春节假期躺平了半个月，都有点荒废了。

今天与各位聊一个十分专业的技术问题——PostgreSQL 中的复合索引和 INCLUDE 索引有何区别，这二者孰优孰劣？什么场景下需要选择复合索引，什么场景 INCLUDE 索引又更为合适？我想很多老鸟或许都未能说得清。同时，最近爆火的当红炸子鸡 DeepSeek 和 ChatGPT 相碰撞，同时面对这个问题又会擦除怎样的火花？

DeepSeek 怎么说

DeepSeek 最近是火出了圈，每天铺天盖地的信息，让我们试试！(同时勾选上深度思考和联网搜索 🔍)

在 PostgreSQL 中，有复合索引，也有 INCLUDE 索引，这二者的区别是什么？各自适用于什么场景？各自的优劣势是什么？

复合索引优势：

•对多列条件的查询和排序有显著优化效果。•减少随机 I/O (通过覆盖查询避免回表)。

复合索引劣势：

•索引键长度较大：所有列的值都存储在索引树的每一层，可能导致索引体积膨胀。•维护成本高：对索引列的更新 (如频繁修改 col1 或 col2) 会触发索引重组。

INCLUDE 索引优势：

•更小的索引键：包含列不参与 B-Tree 结构构建，减少索引层级和体积。•更低的维护成本：对包含列的更新不会触发索引树重组（仅叶子节点数据更新）。•灵活的覆盖查询：可以按需添加需要返回的列，无需将所有列作为键。

INCLUDE 索引劣势：

•无法优化包含列的查询条件或排序。•需要显式指定包含列，可能需权衡索引设计。

ChatGPT 怎么说

作为对比，让我们再看看 GPT-o1 怎么说。

复合索引优势：

•提高对多个列组合查询的性能，尤其是 WHERE 子句中涉及多个条件时。•对排序查询有加速作用 (比如 ORDER BY col1, col2)。

复合索引劣势：

•索引大小相对较大，尤其是当多个列的数据量较大时，索引维护的开销较高。•如果查询条件与索引列的顺序不一致，可能无法有效利用复合索引。

INCLUDE 索引优势：

•减少了回表 (访问主表数据) 次数，提高了查询性能，尤其是对某些列的查询。•索引可以包含额外的列，而不增加索引的搜索代价。

INCLUDE 索引劣势：

•包含列的数量会增加索引的存储空间。•如果没有合理选择包含列，可能会导致索引空间浪费。

分析

可以看到，一大啪啦地都讲了很多，看起来回答了许多，但是从专业角度来说，还是欠了些许火候。这里让我们先抛开两位 AI 界翘楚的回答不谈，先分析一下二者的异同。INCLUDE 索引是 11 版本之后引入的新特性，其目的是为了减少回表，使用 Index-Only-Scan 以提升查询性能，举个栗子：

postgres=# create table test(id int,info text);CREATE TABLEpostgres=# insert into test select i,md5(random()::text) from generate_series(1,100000) as i;INSERT 0 100000postgres=# insert into test select i,'test' from generate_series(1,10) as i;INSERT 0 10postgres=# create index on test(info);CREATE INDEXpostgres=# analyze test;ANALYZEpostgres=# explain select sum(id) from test where info = 'test';                                   QUERY PLAN                                   -------------------------------------------------------------------------------- Aggregate  (cost=8.44..8.45 rows=1 width=8)   ->  Index Scan using test_info_idx on test  (cost=0.42..8.44 rows=1 width=4)         Index Cond: (info = 'test'::text)(3 rows)
复制

由于要查询的 ID 不在索引中，因此需要回表去获取数据

postgres=# create index on test(info) INCLUDE(id);CREATE INDEXpostgres=# explain select sum(id) from test where info = 'test';                                       QUERY PLAN                                       ---------------------------------------------------------------------------------------- Aggregate  (cost=8.44..8.45 rows=1 width=8)   ->  Index Only Scan using test_info_id_idx on test  (cost=0.42..8.44 rows=1 width=4)         Index Cond: (info = 'test'::text)(3 rows)
复制

当创建了 INCLUDE 索引之后，执行计划也变为了 Index Only Scan。其目的和复合索引类似，但是还是有几点明显的区别，DeepSeek 和 ChatGPT 都答不上来。

唯一性

首选，INCLUDE 索引不会影响唯一性，假设现在要求 ID 列唯一，如果创建了复合唯一索引，那么 ID 是可以重复的，毕竟其唯一性针对的“复合值”，这种情况下就是 INCLUDE 索引的用武之地了，INCLUDE 并不会影响其唯一性。

postgres=# create table test1(id int,info text);CREATE TABLEpostgres=# create unique index on test1(id,info);CREATE INDEXpostgres=# insert into test1 values(1,'hello');INSERT 0 1postgres=# insert into test1 values(1,'hello1');INSERT 0 1postgres=# drop index test1_id_info_idx ;DROP INDEXpostgres=# create unique index on test1(id) INCLUDE(info);        ---不会破坏唯一性ERROR:  could not create unique index "test1_id_info_idx"DETAIL:  Key (id)=(1) is duplicated.
复制

有序性

其次便是有序性，以 BTREE 为例，复合索引是按照列依次排序，但是 INCLUDE 列本身并不会阻止排序，也就是说，其还是原模原样：

postgres=# create table test2(id int,id2 int);CREATE TABLEpostgres=# create index on test2(id,id2);CREATE INDEXpostgres=# insert into test2 select ceil(random() * 100),ceil(random() * 1000) from generate_series(1,100000);INSERT 0 100000postgres=# explain select * from test2 order by id,id2;                                        QUERY PLAN                                         ------------------------------------------------------------------------------------------- Index Only Scan using test2_id_id2_idx on test2  (cost=0.29..4474.05 rows=100118 width=8)(1 row)
postgres=# drop index test2_id_id2_idx ;DROP INDEXpostgres=# create index on test2(id) INCLUDE(id2);CREATE INDEXpostgres=# explain select * from test2 order by id,id2;                                           QUERY PLAN                                            ------------------------------------------------------------------------------------------------- Incremental Sort  (cost=76.37..8847.31 rows=100000 width=8)   Sort Key: id, id2   Presorted Key: id   ->  Index Only Scan using test2_id_id2_idx on test2  (cost=0.42..2612.42 rows=100000 width=8)(4 rows)
复制

可以看到，这种情况下，优化器会选择增量排序 Incremental Sort，进一步按照 ID2 再进行排序。增量排序相较其他排序算法占用的内存更少，因为它将数据集分成了几个较小的组；此外，它允许执行器在处理完第一个分组后便开始返回结果，而无需等待整个数据集排序完成。在 PostgreSQL 中，实现更加微妙：相对较大的组分别处理，较小的组则被合并在一起并完全排序。这减少了调用排序过程所产生的开销。

因此进一步拓展的话，INCLUDE 列不必维持顺序，因此如果进行更新，则无需重新排序，这可以减少页面拆分或其他额外写入，使操作更高效并减少内部碎片。

潜在性能

在 12 版本中，PostgreSQL 支持了后缀截断，即 suffix truncation，内部索引条目包含索引键，但它们的值仅用于在搜索过程中确定要下降到的子树。在多列索引中，通常只需取第一个键属性 (或几个首属性)。为了在页面中节省空间，可以截断其他属性。当叶子页面被分裂且内部页面需要容纳一个新指针时，便会发生这种 suffix truncation (后缀截断)。在官网上有这么一段话，耐人寻味：

In practice, covering indexes without an INCLUDE clause often avoid storing columns that are effectively payload in the upper levels. However, explicitly defining payload columns as non-key columns reliably keeps the tuples in upper levels small.

即使不使用 INCLUDE，复合索引也可能通过后缀截断自动将某些键列视为非键列 (即不存储在上层节点中)。但这一行为依赖于数据分布等特性，并不可靠。而通过 INCLUDE 显式定义非键列，可以强制确保这些列永远不会出现在上层节点，从而始终保证上层节点的体积最小。尤其是当大基数 (索引层次很高)，潜在的性能收益会更为明显，因为每次查询都需要从根节点开始逐层向下遍历。因此，上层节点越小，查询性能越高。

其次，官网还提到了一种 case：SELECT f(x) FROM tab WHERE f(x) < 1; 对于这种，如果有表达式索引，理论上也可以走 Index Only Scan，但由于目前优化器还无法识别，需要额外包含在内才可以：CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);。

存储空间

其次便是存储空间了，在 13 中，索引支持了去重 (deduplication)，非唯一索引可以包含许多指向不同堆元组的重复键。由于非唯一键出现不止一次，并因此占用大量空间，所以重复键被折叠收敛成一个单独的索引条目，其中包含键和相应元组 ID 的列表。在某些情况下，这个过程 (称为去重) 可以显著减小索引的大小，但是 INCLUDE 索引是不支持的，因此有些情况下，你会发现 INCLUDE 的索引要大得多 ( INCLUDE 索引不仅存储索引列的键值，还包括 INCLUDE 列的值)。

INCLUDE indexes can never use deduplication.

postgres=# create table test4(id int,id2 int);CREATE TABLEpostgres=# insert into test4 select ceil(random() * 100),ceil(random() * 1000) from generate_series(1,1000000);INSERT 0 1000000postgres=# create index on test4(id) INCLUDE(id2);CREATE INDEXpostgres=# create index on test4(id,id2);CREATE INDEXpostgres=# \di+ test4_id_id2_idx*                                              List of relations Schema |       Name        | Type  |  Owner   | Table | Persistence | Access method |  Size   | Description --------+-------------------+-------+----------+-------+-------------+---------------+---------+------------- public | test4_id_id2_idx  | index | postgres | test4 | permanent   | btree         | 21 MB   |  public | test4_id_id2_idx1 | index | postgres | test4 | permanent   | btree         | 9088 kB | (2 rows)
postgres=# select attname,n_distinct from pg_stats where tablename = 'test4'; attname | n_distinct ---------+------------ id      |        100 id2     |       1000(2 rows)
复制

由于我插入的是固定随机值，因此数据量越大，对于普通复合索引，能够做去重的机会就越多，其大小自然也小得多。

花絮

关于 suffix truncation 这一段内容，笔者最开始也有点云里雾里，丢给 GPT-o1 的时候，其回答就显得"一本正经的胡说八道"

而 DeepSeek 就回答地十分专业，笔者也是从中顿悟

小结

可以看到，复合索引或者 INCLUDE 索引本身二者就有各自的优劣势，并且有很大区别，但是其目的是相同的 —— 使用 Index Only Scan，提升查询性能。

回到最近爆火的 DeepSeek，用下来之后，DeepSeek 雀氏牛逼 ~可以看到，它的回答在这个场景下和上下文中，相较于我每个月要花费 140 RMB 的 GPT 甚至更为准确与完善，尤其是当上下文和提示词 (Prompt) 足够多的时候。

工欲善其事，必先利其器，AI 可以成为我们十分重要的利器，常有人说，随着人工智能的不断发展，我们会不会失业诸如此类的话题，我想说的是，我们不必过于杞人忧天，有风险意识是好的，说明我们在思考，有忧患意识，但是更重要的是，我们毕竟是个体，是人，人的一大优势便是能使用工具，这类 AI 工具便可以成为我们的轮子，极大提升我们的价值，其次，在专业领域，AI 工具至少短期内是无法替代我们的。

我们要学会适应这个快速发展的时代，稍有懈怠，可能就会落下，时代像筛子，筛得每一个人流离失所，筛得少数人出类拔萃，我们在低头拉车的同时也要抬头看看路，定期深度思考一下，规划一下。

参考

https://postgres-internals.cn/^[1]

https://postgres.ai/blog/20211029-how-partial-and-covering-indexes-affect-update-performance-in-postgresql^[2]

https://dba.stackexchange.com/questions/313274/advantage-of-using-INCLUDE-as-against-adding-the-column-in-index-for-covering-in^[3]

https://www.postgresql.org/docs/16/btree-implementation.html^[4]

https://www.tubring.cn/articles/INCLUDE-columns-in-btree-indexes^[5]

References

[1]: https://postgres-internals.cn/
[2]: https://postgres.ai/blog/20211029-how-partial-and-covering-indexes-affect-update-performance-in-postgresql
[3]: https://dba.stackexchange.com/questions/313274/advantage-of-using-INCLUDE-as-against-adding-the-column-in-index-for-covering-in
[4]: https://www.postgresql.org/docs/16/btree-implementation.html
[5]: https://www.tubring.cn/articles/INCLUDE-columns-in-btree-indexes