sql - 连接表时 PostgreSQL ON 与 WHERE？

Question

我有 2 个表 customer 和 coupons，客户可能有也可能没有分配给 reward_id，因此它是一个可以为 null 的列。一个客户可以拥有多张优惠券，优惠券属于一个客户。

+-------------+------------+
|   coupons   | customers  |
+-------------+------------+
| id          | id         |
| customer_id | first_name |
| code        | reward_id  |
+-------------+------------+
customer_id column is indexed

我想在 2 个表之间进行连接。

我的尝试是:

select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust
on c.customer_id = cust.id and cust.reward_id is not null

但是，我认为 reward_id 上没有索引，所以我应该将 cust.reward_id is not null 移动到 where 子句中:

select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust
on c.customer_id = cust.id
where cust.reward_id is not null

我想知道第二次尝试是否会比第一次尝试更有效率。

Answer 1

如果自己看执行计划就更好了。在您的 select 语句之前添加 EXPLAIN ANALYZE 并执行两者以查看差异。

方法如下:

EXPLAIN ANALYZE select ...

它有什么作用？它实际上执行 select 语句并返回查询优化器选择的执行计划。如果没有 ANALYZE 关键字，它只会估计执行计划，而不会在后台实际执行语句。

数据库不会同时使用两个索引，因此在 customer(id) 上建立索引将使其无法在 customer(reward_id) 上使用索引。此条件实际上将被视为过滤条件，这是正确的行为。

您可以试验这样创建的部分索引的性能:customer(id) where reward_id is not null。这将减少索引大小，因为它只会存储分配了 reward_id 的这些客户 ID。

我通常喜欢将关系/连接逻辑与应用的条件分开，我自己将它们放在 WHERE 子句中，因为它在那里更可见，如果有更多更改，将来更容易阅读.

我建议您自己看看可能的性能提升，因为这取决于有多少数据以及 reward_id 可能的低基数。例如，如果大多数行都在该列中填充了一个值，则不会产生太大差异，因为索引大小(正常与部分)几乎相同。