只给表子集建索引？用函数结果建索引？PostgreSQL这俩操作凭啥能省空间又加速？

1. 部分索引（Partial Indexes）

1.1 什么是部分索引？

部分索引是建立在表子集上的索引，子集由一个**条件表达式（谓词）**定义。只有满足谓词的表行才会被写入索引。相比全表索引，它的优势是：

• 更小的体积：减少磁盘占用，加快索引查询速度；
• 更低的维护成本：表更新时，只有满足谓词的行需要同步更新索引；
• 更精准的查询匹配：只针对特定场景生效。

1.2 部分索引的典型应用场景

1.2.1 排除常见值，减小索引 size

如果某列的常见值占比极高（比如90%以上），且查询很少涉及这些常见值，部分索引可以过滤掉它们，节省空间。

示例：Web访问日志的外部IP查询
假设你存储了Web服务器的访问日志，大部分访问来自公司内部子网（192.168.100.0/24），而你只关心外部IP的访问记录。

-- 创建访问日志表
CREATE TABLE access_log (
    url varchar(255),       -- 访问的URL
    client_ip inet,         -- 客户端IP
    access_time timestamp   -- 访问时间
);

-- 创建部分索引：排除内部IP（192.168.100.0-192.168.100.255）
CREATE INDEX access_log_client_ip_ix 
ON access_log (client_ip)
WHERE NOT (client_ip >= inet '192.168.100.0' AND client_ip <= inet '192.168.100.255');

-- 可使用索引的查询（外部IP）
SELECT * FROM access_log 
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

-- 无法使用索引的查询（内部IP，索引中无数据）
SELECT * FROM access_log 
WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';

原理：索引只包含外部IP的记录，查询外部IP时直接扫描小索引；查询内部IP时，PostgreSQL自动选择全表扫描（本来就是更高效的方式）。

1.2.2 排除不感兴趣的值，优化高频查询

如果你的查询只关注表中的小部分行（比如“未结算的订单”），部分索引可以只保留这些行，加快查询速度。

示例：未结算订单的快速查询
假设订单表orders中，90%的订单是“已结算”（billed = true），而你经常查询“未结算订单”（billed = false）。

-- 创建订单表
CREATE TABLE orders (
    order_nr serial PRIMARY KEY,  -- 订单号
    amount numeric(10,2),         -- 订单金额
    billed boolean DEFAULT false  -- 是否结算
);

-- 创建部分索引：只包含未结算的订单
CREATE INDEX orders_unbilled_idx 
ON orders (order_nr)
WHERE NOT billed;

-- 可使用索引的查询（未结算且金额>5000）
SELECT * FROM orders 
WHERE NOT billed AND amount > 5000.00;

-- 无法使用索引的查询（不确定订单是否未结算）
SELECT * FROM orders 
WHERE order_nr = 3501;

1.2.3 实现“部分唯一约束”

部分唯一索引可以仅约束满足条件的记录，比全表唯一约束更灵活。

示例：测试结果的成功记录唯一
假设你需要确保“每个受试者+测试目标的成功记录唯一”，但失败记录可以任意添加。

-- 创建测试结果表
CREATE TABLE tests (
    subject text,       -- 受试者
    target text,        -- 测试目标
    success boolean,    -- 是否成功
    score int           -- 分数
);

-- 创建部分唯一索引：success为true时，subject+target组合唯一
CREATE UNIQUE INDEX tests_success_unique 
ON tests (subject, target)
WHERE success;

-- 允许：同一个subject+target的失败记录
INSERT INTO tests VALUES ('Alice', 'Math', false, 60);
INSERT INTO tests VALUES ('Alice', 'Math', false, 70);

-- 禁止：同一个subject+target的成功记录（会报错）
INSERT INTO tests VALUES ('Alice', 'Math', true, 90);
INSERT INTO tests VALUES ('Alice', 'Math', true, 85);

1.2.4 修正错误的查询计划

如果PostgreSQL的查询 planner 错误地选择了索引扫描（比如数据分布异常），部分索引可以“屏蔽”不适合的查询，强制选择更优的方式（此场景很少见，仅作为最后的优化手段）。

1.3 部分索引的“红线”：不要替代分区！

不要用大量非重叠的部分索引替代分区（Partitioning）。比如对category字段的每个值创建部分索引：

-- 错误示例：用部分索引替代分区
CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
-- ... 直到 category = N

这种做法会让查询 planner 花费大量时间选择索引，反而降低性能。正确的做法是创建复合索引（将category作为前缀）：

-- 正确示例：复合索引替代多个部分索引
CREATE INDEX mytable_cat_data ON mytable (category, data);

如果表数据量极大，复合索引仍不够用，应该使用分区表（参考 官方分区文档）。

2. 表达式索引（Indexes on Expressions）

2.1 什么是表达式索引？

普通索引的列是表的原始列，而表达式索引的列是函数或标量表达式的结果。比如对lower(email)（小写转换）或first_name || ' ' || last_name（字符串拼接）创建索引。

它的核心价值是：将常用计算逻辑预存到索引中，避免查询时重复计算，从而加快查询速度。

2.2 表达式索引的实用示例

2.2.1 大小写不敏感的快速查询

如果经常需要执行大小写不敏感的字符串查询（比如WHERE lower(email) = '[email protected]'），表达式索引可以将小写转换的结果预存，避免全表扫描。

示例：用户表的邮箱查询

-- 创建用户表
CREATE TABLE users (
    id serial PRIMARY KEY,
    email varchar(255) NOT NULL  -- 邮箱（大小写混合）
);

-- 创建表达式索引：存储email的小写形式
CREATE INDEX users_lower_email_idx ON users (lower(email));

-- 可使用索引的查询（大小写不敏感）
SELECT * FROM users 
WHERE lower(email) = '[email protected]';

-- 注意：ILIKE无法使用该索引（表达式不同）
SELECT * FROM users 
WHERE email ILIKE '[email protected]';  -- 效率更低

2.2.2 组合字段的精确匹配

如果经常需要查询多个字段的组合结果（比如first_name || ' ' || last_name = 'John Smith'），表达式索引可以预存组合后的字符串，加快查询速度。

示例：联系人表的全称查询

-- 创建联系人表
CREATE TABLE people (
    first_name varchar(50),  -- 名
    last_name varchar(50)    -- 姓
);

-- 创建表达式索引：存储姓名全称（注意括号！）
CREATE INDEX people_fullname_idx 
ON people ((first_name || ' ' || last_name));

-- 可使用索引的查询（全称匹配）
SELECT * FROM people 
WHERE (first_name || ' ' || last_name) = 'John Smith';

2.3 表达式索引的维护成本

表达式索引的插入/更新代价更高：每次插入或修改表行时，PostgreSQL需要重新计算表达式的值，并同步更新索引。因此，它适合查询频率远高于更新频率的场景（比如用户表的邮箱查询，更新频率低，但查询频繁）。

课后 Quiz：巩固你的理解

问题1：如何用部分索引优化“在售商品的价格查询”？

假设你有一张products表，status字段表示商品状态（in_stock：在售，out_of_stock：缺货，discontinued：停产）。你经常查询“在售商品的价格<100”，但很少查询缺货/停产商品。如何用部分索引优化？

答案解析：
创建部分索引，只包含在售商品的price字段：

CREATE INDEX products_in_stock_price_idx 
ON products (price)
WHERE status = 'in_stock';

这样查询在售商品时会扫描小索引，加快速度；更新缺货/停产商品时，不需要修改索引。

问题2：为什么下面的表达式索引会报错？如何修正？

CREATE INDEX people_fullname_idx 
ON people (first_name || ' ' || last_name);

答案解析：
报错原因是复杂表达式缺少括号。PostgreSQL要求：非单一函数调用的表达式必须用括号包裹。修正后的语句：

CREATE INDEX people_fullname_idx 
ON people ((first_name || ' ' || last_name));

常见报错解决方案

报错1：部分索引的WHERE条件引用不存在的列

-- 错误示例：WHERE条件用了非表字段`ip`
CREATE INDEX access_log_client_ip_ix 
ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND ip < inet '192.168.100.255');

报错信息：ERROR: column "ip" does not exist
解决办法：检查WHERE条件中的字段是否属于当前表，修正为client_ip：

CREATE INDEX access_log_client_ip_ix 
ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

报错2：表达式索引缺少括号

-- 错误示例：复杂表达式未用括号
CREATE INDEX people_fullname_idx 
ON people (first_name || ' ' || last_name);

报错信息：ERROR: syntax error at or near "||"
解决办法：给表达式添加括号：

CREATE INDEX people_fullname_idx 
ON people ((first_name || ' ' || last_name));

报错3：部分唯一索引的重复插入

-- 示例：部分唯一索引`tests_success_unique`（success=true时subject+target唯一）
INSERT INTO tests VALUES ('Alice', 'Math', true, 90);
INSERT INTO tests VALUES ('Alice', 'Math', true, 85);  -- 重复插入

报错信息：ERROR: duplicate key value violates unique constraint "tests_success_unique"
解决办法：检查插入的数据是否符合业务规则，或调整索引谓词（比如允许特定情况的重复）。

参考链接

• 部分索引官方文档：www.postgresql.org/docs/17/ind…
• 表达式索引官方文档：www.postgresql.org/docs/17/ind…
• 分区表官方文档：www.postgresql.org/docs/17/ddl…