sql - 关于PostgreSQL部分索引的疑问？

Question

PostgreSQL 官方文档关于部分索引给了以下一个示例：

Example 11.2. Setting up a Partial Index to Exclude Uninteresting Values

If you have a table that contains both billed and unbilled orders, where the unbilled orders take up a small fraction of the total table and yet those are the most-accessed rows, you can improve performance by creating an index on just the unbilled rows. The command to create the index would look like this:

CREATE INDEX orders_unbilled_index ON orders (order_nr)    WHERE billed is not true;

A possible query to use this index would be:

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

好奇针对未开票的订单，直接在 billed 字段上针对 not true 部分创建索引不是更好吗？

CREATE INDEX orders_unbilled_index ON orders (billed)    WHERE billed is not true;

官方示例却在 order_nr 字段上创建部分索引，这里是有什么讲究吗？求大佬指教 ～

Answer 1

你没明白它这个 Partial Index 特性的意图。

先忽略它的这个具体的例子，你现在先这么想：

首先，你现在有一张大表 R，其中有部分字段是经常会在出现的查询条件里的，这些字段我们叫它 P 吧。于是你想到了给 P 加上索引。

接着，你发现虽然是在查询这张大表 R，但其实只有其中的某些行（我们可以管这部分数据集合叫 R1）才是高频访问的。而前面 P 加的索引，是对全表 R 生效的，这样其实有很多是浪费了的，因为索引本身存储需要空间、插入和更新时也会有额外开销。

那么，自然而然想到，有没有办法只针对 R1 这部分数据的 P 才加索引，而不是对全表 R 的 P 都加索引呢？

这就是 Partial Index。

回到你题目中的例子上，“orders” 就是大表 R，“orders WHERE billed is not true” 大表的子集 R1，“order_nr” 是 P。

---

至于为什么要多一步 R → R1、然后再加 P，而不是把 P 范围扩大化、然后直接给 R 加，前面已经说了，因为索引本身是有开销的。

例子中已经说了，只有未开票订单才是高频访问的，言外之意就是已开票订单有没有索引其实不重要。给想给 billed 设索引，确实有一定效果，但缺点很明显，那就是你这么做已开票订单这个字段同样也有了索引的开销（注意前文加粗部分）。

---

当然了，如果是 MySql 这种没有 Partial Index 特性的数据库，你还真的确实需要给 billed 加个索引。

Answer 2

第一个索引根据order_nr字段，只包括billed为false的行，这个可以快速定位到没开票的订单的行，第二个是可以查所有没开票的订单，但是加上一些过滤条件的话可能性能没第一个好，取决于你的需求