PostgreSQL(PostGIS)如何删除与另一表无空间交集的行及慢查询优化

1. SQL执行逻辑不符合预期：你写的NOT IN子查询被PostgreSQL优化器判定为关联子查询，并不会像你预期的那样先执行一次子查询拿到完整的id列表再做匹配。实际执行时，数据库会对base_grid_916453354的每一行都重新执行一次子查询判断id是否符合条件，900万次重复计算直接导致耗时爆炸。你单独执行子查询仅需12秒，但嵌套到DELETE里后执行逻辑发生了本质变化。
2. 大表删除的额外开销：如果需要删除的行占总表比例较高（超过30%），删除过程中需要同步维护表上所有索引、写入事务日志，若存在未释放的行锁还会进一步阻塞执行。

方案1：物化子查询结果，避免重复计算 #

先将需要保留的id一次性存入临时表，再执行删除，确保空间查询仅执行1次：

-- 生成需要保留的网格id临时表，耗时和你单独执行子查询一致
CREATE TEMP TABLE preserved_grid_ids AS
SELECT DISTINCT bg.id
FROM base_grid_916453354 bg, (SELECT * FROM tracks_heatmap_1000 LIMIT 100000) tr
WHERE bg.geom && tr.buffer;

-- 给临时表id建索引加快匹配
CREATE INDEX idx_preserved_grid_id ON preserved_grid_ids(id);

-- 执行删除
DELETE FROM base_grid_916453354
WHERE id NOT IN (SELECT id FROM preserved_grid_ids);

方案2：大比例删除场景下直接重建表 #

如果需要删除的行占总表比例超过50%，直接保留需要的行重建表效率远高于逐行删除：

-- 导出需要保留的网格数据到新表
CREATE TABLE base_grid_916453354_new AS
SELECT DISTINCT bg.*
FROM base_grid_916453354 bg, (SELECT * FROM tracks_heatmap_1000 LIMIT 100000) tr
WHERE bg.geom && tr.buffer;

-- 给新表补充原表的索引、约束、权限配置后，替换原表
-- 替换操作建议在业务低峰期执行
DROP TABLE base_grid_916453354;
ALTER TABLE base_grid_916453354_new RENAME TO base_grid_916453354;

• 执行删除前可先临时删除表上非必要的索引，删除完成后再重建，避免删除过程中频繁维护索引的开销
• 操作前先通过SELECT COUNT(*)判断需要删除的行占比，选择匹配的优化方案
• 执行大操作前确认没有长事务持有该表的锁，避免阻塞

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