sql - postgresql 中的高效增量插入

Question

我使用数据库来表示文件列表，以及与每个文件关联的一些元数据。我需要定期更新这个文件列表，只添加新文件并删除不再存在的文件(我不必触摸表中的现有行，因为我会丢失相关的元数据)。

当我有大约 10000 个文件时，我当前的查询只需要几秒钟，但如果我当前的 150000 个文件表需要一个小时。

经过网上的一些研究，我得到了以下过程:

1. 用扫描结果填充表“newfiles”
2. DELETE FROM files WHERE path NOT IN (SELECT path FROM newfiles);
3. INSERT INTO files (SELECT * FROM newfiles WHERE path NOT IN (SELECT path FROM files));

我也有索引:

CREATE INDEX "files_path" ON "files" ("path");
CREATE INDEX "files_path_like" ON "files" ("path" varchar_pattern_ops);
CREATE INDEX "files_path" ON "newfiles" ("path");
CREATE INDEX "files_path_like" ON "newfiles" ("path" varchar_pattern_ops);

(我主要使用这些索引在数据库中进行搜索；我的应用程序在文件中有一个搜索引擎。)

当我有 150000 个文件时，这两个查询中的每一个都需要一个多小时。我该如何优化它？

谢谢。

Answer 1

尝试使用 NOT EXISTS 而不是 NOT IN，如:

DELETE FROM files WHERE NOT EXISTS
  (SELECT 1 FROM newfiles WHERE newfiles.path=files.path);

此外，如果 newfiles 每次都从头开始填充，请确保在发出任何使用它的查询之前 ANALYZE newfiles，以便优化器可以使用良好的统计信息.

如果这不能解决问题，请尝试对您的查询使用 EXPLAIN 或 EXPLAIN ANALYZE 以获得执行计划并将其附加到问题中。