PostgreSQL 索引与 鉴权体系 的正确姿势

前阵子接手了一个跑了五六年的老项目，用户天天投诉列表页慢，打开 pg_stat_statements 一看，最狠的一条查询要跑十几秒。顺手查了下账号，发现全公司共用一个 postgres 超级用户直连生产库，密码还是老掉牙的 md5。趁着这次优化，把索引和鉴权一起收拾了，中间踩了不少坑，记下来，省得下次又去翻文档。

索引不是建了就快的

先说结论：别猜，直接上 EXPLAIN ANALYZE。

我一开始想当然地给 status 字段建了索引，跑起来照样全表扫描。看了执行计划才反应过来，表里 90% 的行都是同一个状态值，优化器根本懒得走索引，走了反而更慢。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

老老实实改成复合索引，注意列的顺序，等值条件的列放前面，排序的列放最后：

CREATE INDEX idx_orders_user_status_time
ON orders (user_id, status, created_at DESC);

后面又发现这个查询永远只查 paid 状态，干脆上了部分索引，索引体积直接小了一大半：

CREATE INDEX idx_orders_user_paid
ON orders (user_id, created_at DESC)
WHERE status = 'paid';

还有一个巨常见的坑：LIKE '%关键字%' 这种两边都带百分号的模糊搜索，B-tree 是完全帮不上忙的，实测得请出 pg_trgm：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_orders_remark_trgm
ON orders USING gin (remark gin_trgm_ops);

建完过后，那条查询从 8 秒多降到了 40 毫秒左右，效果立竿见影。另外注意别在条件里对字段做函数运算，比如 WHERE created_at::date = '2024-05-01'，这样索引直接失效，改成范围查询 created_at >= '2024-05-01' AND created_at < '2024-05-02' 就好了。

鉴权：先把 md5 换成 scram-sha-256

PostgreSQL 14 开始默认就是 scram-sha-256 了，但老库一路升级上来的，很多还停留在 md5，先看一眼：

SHOW password_encryption;

不是的话就改：

ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload