sql - 如何有效地从 Postgresql 8.1 表中删除行？

Question

我正在处理 PostgreSQL 8.1 SQL 脚本，它需要从表中删除大量行。

假设我需要从中删除的表是 Employees(约 26 万行)。它有一个名为 id 的主键。

我需要从该表中删除的行存储在名为 EmployeesToDelete(约 10K 条记录)的单独临时表中，该表具有对 Employees.id 的外键引用，称为 employee_id。

有没有一种有效的方法来做到这一点？

起初，我想到了以下几点:

DELETE
FROM    Employees
WHERE   id IN
        (
        SELECT  employee_id
        FROM    EmployeesToDelete
        )

但我听说使用“IN”子句和子查询可能效率低下，尤其是对于较大的表。

我看过 PostgreSQL 8.1 文档，其中提到DELETE FROM ... USING 但它没有示例，所以我不确定如何使用它。

我想知道以下是否有效并且效率更高？

DELETE
FROM    Employees
USING   Employees e
INNER JOIN
        EmployeesToDelete ed
ON      e.id = ed.employee_id

非常感谢您的评论。

编辑:我运行了 EXPLAIN ANALYZE，奇怪的是第一个 DELETE 运行得非常快(几秒钟内)，而第二个 DELETE 花了很长时间(超过 20 分钟)我最终取消了它。

向临时表添加索引对性能有很大帮助。

对于任何感兴趣的人，这是第一个 DELETE 的查询计划:

 Hash Join  (cost=184.64..7854.69 rows=256482 width=6) (actual time=54.089..660.788 rows=27295 loops=1)
   Hash Cond: ("outer".id = "inner".employee_id)
   ->  Seq Scan on Employees  (cost=0.00..3822.82 rows=256482 width=10) (actual time=15.218..351.978 rows=256482 loops=1)
   ->  Hash  (cost=184.14..184.14 rows=200 width=4) (actual time=38.807..38.807 rows=10731 loops=1)
         ->  HashAggregate  (cost=182.14..184.14 rows=200 width=4) (actual time=19.801..28.773 rows=10731 loops=1)
               ->  Seq Scan on EmployeesToDelete  (cost=0.00..155.31 rows=10731 width=4) (actual time=0.005..9.062 rows=10731 loops=1)

 Total runtime: 935.316 ms
(7 rows)

在这一点上，除非我能找到更好的写法，否则我会坚持使用第一个 DELETE。

Answer 1

不要猜测，要测量。尝试各种方法，看看哪种方法执行起来最短。另外，使用 EXPLAIN了解 PostgreSQL 将做什么并查看可以优化的地方。很少有 PostgreSQL 用户能够正确地猜出最快的查询...