在SQL语句中有效表达多个元组对的WHERE条件的方法

Question

我想执行一个SQL查询，这个查询在逻辑上等同于下面的内容：

DELETE FROM pond_pairs
WHERE
  ((pond1 = 12) AND (pond2 = 233)) OR
  ((pond1 = 12) AND (pond2 = 234)) OR
  ((pond1 = 12) AND (pond2 = 8)) OR
  ((pond1 = 13) AND (pond2 = 6547)) OR
  ((pond1 = 13879) AND (pond2 = 6))

我将会有成千上万的pond1-pond2配对。我在(pond1, pond2)上有一个索引。

由于我对SQL的了解有限，我想到了几种方法：

1. 直接运行整个查询。
2. 把查询分成小块，每块包含n个WHERE条件。
3. 把pond1-pond2的配对保存到一个新表中，然后在WHERE子句中做一个子查询来识别。
4. 把用Python写的逻辑转换成一个存储过程，用来识别需要删除的行。需要注意的是，我对编写存储过程不太熟悉，所以这可能需要我花费很多时间学习。

我在使用Postgres，如果这有关系的话。

Answer 1

如果你有成千上万对数据，直接运行查询是不行的，因为SQL语句会太长了。

如果你已经把这些对放在一个表里，第三种方法就不错。如果没有，你得先把它们插入进去。如果你后面不需要这些数据，倒不如直接运行同样数量的删除语句，而不是插入语句。

那如果在循环中使用预处理语句呢？也许可以批量处理（如果Python支持的话）。

1. 开始一个事务
2. 准备语句 "DELETE FROM pond_pairs WHERE ((pond1 = ?) AND (pond2 = ?))"
3. 在Python中循环你的数据，使用一对数据运行这个语句（或者添加到批量中）
4. 提交事务

这些对数据是从哪里来的？如果你能写一个SELECT语句来找出它们，你就可以把这个条件放到删除语句的WHERE子句里。

DELETE FROM pond_pairs WHERE (pond1, ponds) in (SELECT pond1, pond2 FROM ......  )

Answer 2

我会选择第3种方法（用JOIN而不是子查询），然后测量一下DELETE查询的时间（不需要创建表和插入数据）。这样做是个不错的起点，因为JOIN操作非常常见，而且已经经过优化，所以很难有其他方法能比它更快。然后你可以把这个时间和你现在的方法进行比较。

你还可以尝试以下方法：

1. 按照索引的方式对数据对进行排序。
2. 使用你描述中的第二种方法进行删除（可能在一个事务中完成）。

在删除之前进行排序可以提高索引读取的性能，因为这样更有可能利用到硬盘缓存。

Answer 3

如果有很多 pond1 和 pond2 的配对需要在一次删除操作中删除，我会先创建一个临时表，然后在这个表上进行连接操作。

-- Create the temp table:
CREATE TEMP TABLE foo AS SELECT * FROM (VALUES(1,2), (1,3)) AS sub (pond1, pond2);

-- Delete
DELETE FROM bar 
USING  
  foo -- the joined table
WHERE 
  bar.pond1= foo.pond1 
AND 
  bar.pond2 = foo.pond2;