PostgreSQL:连接大表的小子集的最佳方式

Question

我在 Windows 机器上使用 PostgreSQL 9.3。假设我有一个包含 400 万条记录的客户表和一个包含 2500 万条记录的订单表。但是，我只对纽约的客户感兴趣。只有 5,000 名纽约客户下了 15,000 个订单，即一个小得多的子集。

检索客户 ID 和纽约客户下过的订单总数的最佳方法是什么？

是像这样的相关子查询

Select c.clientid
, ( select count(orders.clientid) from orders where orders.clientid = c.clientid) as NumOrders

From clients c
WHERE c.city = 'New York'

比类似的连接更快

Select c.clientid
,coalesce(o.NumOrders,0) as NumOrders

From clients c

Left outer join
( select clientid, count(*) as NumOrders from orders group by clientid ) o
on c.clientid = o.clientid

WHERE c.city = 'New York'

因为后者大部分时间都在计算记录，然后由于与纽约客户无关而被丢弃？或者有更好的方法吗？

谢谢!

PS 是的，我知道，我应该查看执行计划，但我是在家里写这篇文章的，我没有包含数百万条记录的数据库来测试它。

Answer 1

正如您提到的，真正知道的唯一方法是比较执行计划。事实上，最好的方法是使用 EXPLAIN ANALYZE，以便它实际执行查询并将结果与​​估计一起插入到输出中，这样您就可以了解查询计划程序与现实情况的对比。

但是，一般来说，在这种情况下我会做的可能是为客户端子集创建一个临时表，然后JOIN到订单表。您可以选择使用 WITH 而不是在一个查询中执行所有操作。

所以，像这样:

CREATE TEMP TABLE tmp_clients AS
SELECT c.clientid
FROM clients c
WHERE c.city = 'New York'
ORDER BY c.clientid;

SELECT *
FROM orders AS o
JOIN tmp_clients AS c ON (o.clientid = c.clientid)
ORDER BY o.clientid;

这样，tmp_clients 仅包含纽约客户 -- 约 5000 行 -- 并且该表将连接到订单表。

为了进一步优化，您还可以在临时表(在 clientid 上)上创建一个索引，然后在执行 JOIN 之前对其进行ANALYZE 以确保 JOIN纯粹是在索引上完成的。您需要检查每种情况下的查询计划以查看相对差异(或者如果 JOIN 没有您希望的那么快，请牢记这一点)。

对来自@poshest 的评论的回应:

这听起来像是临时表堆积起来，这会增加内存占用，而且对于长时间运行的连接，功能似乎是内存泄漏。

不过，在那种情况下，它不会是真正的泄漏，因为临时表 的范围仅限于一个连接。它们会自动消失，但直到连接结束后才会消失。但是，您可以在完成后立即让它们消失。简单地 DROP 表，就像你对其他表一样，一旦你完成了它们，我怀疑你将能够多次调用该函数 - 在同一个连接上 - 而无需相同类型的单调内存占用量增加。