PostgreSQL中ON DELETE CASCADE外键触发器性能差异问题

问题场景 #

我在PostgreSQL中遇到了ON DELETE CASCADE的性能问题，为定位原因简化了数据库架构，具体如下：

数据库架构 #

CREATE TABLE IF NOT EXISTS public.items
(
    id uuid NOT NULL,
    name text COLLATE pg_catalog."default",
    CONSTRAINT items_pk PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.links
(
    parent uuid,
    child uuid,
    CONSTRAINT links_parent_fk FOREIGN KEY (parent)
        REFERENCES public.items (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT links_child_fk FOREIGN KEY (child)
        REFERENCES public.items (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS parent_idx
    ON public.links USING btree
    (parent ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS child_idx
    ON public.links USING btree
    (child ASC NULLS LAST);
    
CREATE EXTENSION "uuid-ossp";

数据生成 #

INSERT INTO public.items
   SELECT uuid_generate_v4 (), 'item_' || i
   FROM generate_series(1, 134001) AS i;
   
INSERT INTO links
   SELECT (SELECT id FROM public.items WHERE name='item_1'), id FROM public.items;

测试操作 #

执行删除父项item_1的所有子项：

BEGIN;
EXPLAIN ANALYZE DELETE FROM public.items where id in (SELECT child FROM public.links WHERE parent = (SELECT id FROM public.items WHERE name='item_1'));
ROLLBACK;

执行计划结果 #

Trigger for constraint links_parent_fk: time=10451.471 calls=134001
Trigger for constraint links_child_fk: time=2962.035 calls=134001

交换links表列数据（parent为各item id，child为item_1的id）后，耗时反转：links_child_fk触发器耗时约10秒，links_parent_fk耗时约3秒。

疑问：为何两个级联删除触发器的执行耗时存在显著差异？

PostgreSQL版本：12.4、13.9

原因分析 #

这种性能差异的核心在于触发器执行的DELETE操作是否能在引用表中找到匹配行，结合索引特性和缓存行为共同导致：

1. 无匹配行的触发器开销更高
   当删除items中的子项时：

   • links_parent_fk触发器每次执行DELETE FROM links WHERE parent = $1，但所有links的parent都是item_1的id，因此该查询始终返回0行。尽管没有实际删除操作，PostgreSQL仍需完成完整的查询流程：遍历BTREE索引确定无匹配行、检查锁与约束等，这些步骤的累加开销远高于有实际删除的场景。
   • links_child_fk触发器每次执行DELETE FROM links WHERE child = $1，能找到1条匹配行并删除。删除操作的执行路径虽包含行删除和索引更新，但整体开销低于无匹配行的索引遍历+空结果判断。

2. 索引特性与缓存命中率加剧差异

   • parent_idx是高重复率索引（所有键为同一个uuid），待查找的$1是随机生成的其他uuid。由于uuid的随机性，每次查找都需要遍历到BTREE索引的不同位置才能确定无匹配，且这些索引页很难被缓存复用，频繁触发磁盘IO，累加后总耗时剧增。
   • child_idx是低重复率索引（每个键为唯一uuid），每次查找能准确定位到目标行，即使uuid随机，查找后的索引页会被缓存，后续操作的命中率相对更高，降低了单次调用的平均耗时。

3. 数据交换后的反转验证
   交换links表列数据后，links_child_fk触发器变为执行无匹配行的DELETE操作，重复了之前links_parent_fk的高耗时场景；而links_parent_fk触发器变为执行有匹配行的删除操作，耗时随之降低，完全符合上述逻辑。

优化建议 #

• 提前清理关联数据：在删除items记录前，先主动删除links中对应的关联行，避免触发无意义的级联删除触发器。
• 调整外键策略：如果业务上不需要对某一侧的外键进行级联删除，可移除ON DELETE CASCADE约束，减少不必要的触发器调用。
• 升级PostgreSQL版本：更高版本的PostgreSQL对BTREE索引的查找逻辑有性能优化，可能缓解高重复率索引的查找开销。

内容的提问来源于stack exchange，提问作者maly216