软删除场景，一条 SQL性能提升 32 倍

digoal2026年04月02日约 7 分钟

软删除场景的一条 SQL，只改一个词，性能就提升了 32 倍

看到一篇关于 postgresql partial index 优化 SQL 的文章, 涉及的场景非常常见, 所以分享给大家: https://postgres.ai/blog/20260311-not-exists-vs-exists-partial-index

在开始之前, 我想问大家一个问题:

大家都知道a=?这样的条件可以走索引, 那a<>? 这样的不等于条件能走索引吗?
你知道partial index吗?

理解了上面2个问题, 这篇文章就不难理解了.

下面这两条查询，谁更快？

-- Query 1  
select pt.*  
from post_tags pt  
where pt.tag_id = any($1)  
andexists (  
select
from posts  
where posts.post_id = pt.post_id  
andnot deleted  
);

-- Query 2  
select pt.*  
from post_tags pt  
where pt.tag_id = any($1)  
andnotexists (  
select
from posts  
where posts.post_id = pt.post_id  
and deleted  
);

如果只看语义，很多人会觉得这两条 SQL 没什么本质区别。
在这个例子里，它们确实是逻辑等价的：posts.post_id 是主键，子查询最多只会命中一行；post_tags.post_id 又有外键约束，不会出现孤儿记录。也就是说，这里 exists (... and not deleted) 和 not exists (... and deleted)，本质上只是从两个方向表达同一件事。

但性能上，它们却可能差出一个数量级。

在作者的测试里，第二条查询比第一条快了 32 倍。而这个优化，并不需要改表结构，也不需要引入更复杂的执行计划，只是把查询从“确认多数派存在”改写成了“排除少数派存在”。

先说结论

一句话概括：

当业务中存在“少数派状态”时，比如 deleted = true 只占很小比例，使用 NOT EXISTS 去检查“是否不存在少数派”，往往会比使用 EXISTS 去确认“多数派存在”更快。

这篇文章给出的实验里，胜出的方案有两个明显特点：

第一，它查的是少数派索引。
第二，它的大部分查找结果都是索引里找不到。

而对 PostgreSQL 来说，这两点组合在一起，恰恰非常有利。因为：

在索引里找不到，通常可以直接结束；在索引里找到了，反而往往还得回表。

这才是 32 倍性能差距背后的根因。

场景其实很常见：软删除

文章讨论的是一个很典型的业务模型：

posts 表保存文章
post_tags 表保存文章和标签的关联关系
posts.deleted 是一个布尔字段，用来表示软删除

数据分布也很典型：绝大多数帖子都处于活跃状态，只有大约 2% 被软删除。

针对这个分布，作者分别建了两个部分索引：

一个索引覆盖 未删除 的帖子，大小约 1050 MiB
一个索引覆盖 已删除 的帖子，大小约 22 MiB

于是问题就来了：

Query 1 会去访问那个很大的“未删除索引”
Query 2 会去访问那个很小的“已删除索引”

两条 SQL 返回的结果一样，但成本完全不一样。

实测有多夸张？

在测试环境里，作者分别用 50、250、1000 个 tag_id 做了三组测试。结果如下：

tag_ids	EXISTS (not deleted)	NOT EXISTS (deleted)	提速
50	5350 ms	384 ms	14x
250	22574 ms	717 ms	31x
1000	63750 ms	1996 ms	32x

Buffer 读取量的差异也非常明显：

tag_ids	Q1 读取	Q2 读取	差距
50	50,900	4,132	12x
250	211,017	7,296	29x
1000	588,083	18,759	31x

也就是说，这并不是“快一点点”，而是从几万毫秒级降到几百或几千毫秒级。它不只是 CPU 更省，更关键的是 I/O 压力被大幅压低了。

真正决定胜负的，不是“索引大小”，而是“找不找得到”

很多人第一反应会说：
“这不就是因为小索引比大索引更容易命中缓存吗？”

这当然是原因之一，但不是最核心的原因。

真正主导差异的是下面这个机制：

1）如果在索引里没找到，Postgres 可以直接结束

这是 Query 2 最大的优势。

Query 2 会去 posts_deleted_id_key 里查：
“这个 post_id 有没有一条 deleted = true 的记录？”

而由于被删除的数据只占 2%，所以绝大多数情况下答案都是：没有。

一旦索引里没找到，执行器就可以直接结束这次探测。
不需要做可见性检查，不需要再访问 heap，也不需要额外随机读。

这点非常关键。因为在 PostgreSQL 里，真正贵的往往不是索引探测本身，而是索引命中之后的回表检查。

2）如果在索引里找到了，往往还得回 heap 验证

这恰恰是 Query 1 的问题。

Query 1 去查的是 posts_not_deleted_id_key。由于绝大部分帖子都没删除，所以它几乎每次都能在索引里找到匹配项。
但“找到了”并不代表事情结束了。

对于 Index Only Scan，只有当对应数据页在 visibility map 中被标记为 all-visible 时，Postgres 才能真正只看索引、不回表。
而在活跃更新的表上，这个条件很容易被打破。只要页面不是 all-visible，Postgres 就必须回 heap 做一次可见性确认。

这意味着什么？

意味着 Query 1 看起来走的是索引，实际上却会触发大量随机 heap fetch。
而当 heap 很大、又不能完全驻留内存时，这些随机访问就会迅速把延迟拉高。

Heap Fetch 的差距，才是最致命的

文章给出的数据非常直接：

tag_ids	Q1 Heap Fetches	Q2 Heap Fetches
50	26,966	554
250	132,280	2,680
1000	526,344	10,766

也就是说，在 1000 个 tag_id 的情况下：

Query 1 做了 52.6 万次 heap fetch
Query 2 只做了 1 万次出头

这就很好理解了：

Query 1：几乎每次都“找得到”，所以不断触发回表
Query 2：98% 的时候都“找不到”，所以大部分请求直接在索引层结束

差距不是偶然，而是由数据分布决定的。

大索引 vs 小索引，会进一步把差距放大

在这个案例中，两边的索引大小差得也很夸张：

posts_not_deleted_id_key：覆盖 4900 万活跃行，约 1050 MiB
posts_deleted_id_key：覆盖 100 万已删除行，约 22 MiB

22 MiB 的索引很容易常驻 shared_buffers，在生产环境下基本能一直保持热状态。
1050 MiB 的索引则没这么幸运，尤其在冷启动、内存紧张或者 buffer pool 被别的查询争抢时，更容易被驱逐。

更糟的是：

当索引页是冷的，heap fetch 访问到的页通常也会是冷的。
这两种成本不是相加关系，而是相互叠加。

所以 Query 1 不只是多做了回表，它往往还在更大的索引和更冷的 heap 页面之间来回跳。

就算全都预热，这个差距仍然存在

为了证明问题不只是“冷缓存”造成的，作者还做了一个更严格的实验：用 pg_prewarm 把两个索引和 heap 都提前预热。

结果呢？

Query 1：724 ms
Query 2：161 ms

即便在几乎没有 I/O 压力的情况下，仍然还有 4.5 倍 的差距。
原因还是一样：Query 1 依旧做了 12.2 万次 heap fetch，而 Query 2 只有 2562 次。

这说明：

内存再大，也只能缓解问题，不能从根上消除问题。
真正要解决，还是得改写查询。

为什么优化器不自己选更好的方案？

这也是文章里一个很有意思的问题。

从执行计划形态上看，两条 SQL 都用了 Nested Loop + Index Only Scan，其实都没错。
对于“基于唯一索引做单条查找”这种模式，这本来就是合理的计划。

问题在于，优化器很难准确预测下面这件事：

这些 heap fetch 在运行时到底会有多少命中缓存、多少会变成冷读？

优化器知道统计信息，知道表大概有多少行，知道可见性映射覆盖率，也知道 dead tuple 数量。
但它并不知道实际运行那一刻，buffer pool 里哪些页还在，哪些已经被别的查询挤掉了。

所以，这不是简单调一个 GUC 就能解决的问题。
执行计划形态看起来没错，但查询写法本身让执行器更容易走进“命中索引后大量回表”的坑。

还有一个经常被忽略的副作用：它会制造更多写压

文章里还提到一个很容易被忽视的成本：EXPLAIN 中，Query 1 的 dirtied=112,214，而 Query 2 只有 2,109。

原因是，某些 heap fetch 会顺手更新 tuple 的 hint bits，从而把页面标记为 dirty。
这些脏页最终都得由 bgwriter 或 checkpoint 刷回磁盘。

也就是说，慢查询不只是拖慢自己：

它读得更多
它回表更多
它还会制造更多写入压力

在并发环境下，这种副作用会影响整个系统，而不只是当前会话。

真正值得记住的经验

这篇文章最有价值的地方，不只是讲了一个 SQL 小技巧，而是给了一个可以迁移到很多业务场景的思路：

当某个布尔状态只占很小比例时，不要总想着“确认多数派存在”，
更应该考虑“确认少数派不存在”。

换句话说：

不要优先写 EXISTS (... majority condition ...)
优先考虑 NOT EXISTS (... minority condition ...)

并且，为那个少数派状态建立一个很小的部分索引。

这个模式不只适用于 deleted，也适用于很多类似字段：

is_archived
is_banned
is_draft
is_suspended

只要某个标记值是少数派，这个思路就值得试。

最后总结

把整件事压缩成一句最值得记住的话，就是：

“索引里找不到”很便宜；“索引里找到了”在活跃表上往往很贵。

所以，当 98% 的查找都是针对活跃数据时，最好的策略未必是去一个大索引里不断“找到它”，而是去一个很小的少数派索引里反复“找不到它”。

这就是为什么，只改一个词，把 EXISTS 改成 NOT EXISTS，性能就可能从秒级直接掉到百毫秒级。