sql - 索引查找外键不存在的记录

Question

table products
id primary_key

table transactions
product_id foreign_key references products

下面的 SQL 查询非常慢:

SELECT products.* 
FROM   products 
       LEFT JOIN transactions 
              ON ( products.id = transactions.product_id ) 
WHERE  transactions.product_id IS NULL; 

在100亿条产品记录中，可能只有100条产品没有对应的交易记录。

此查询非常慢，因为我怀疑它正在执行全表扫描以查找那些空的外键产品记录。

我想像这样创建一个部分索引:

CREATE INDEX products_with_no_transactions_index 
ON (Left JOIN TABLE 
    BETWEEN products AND transactions) 
WHERE transactions.product_id IS NULL;

以上是否可能，我将如何去做？

注意:该数据集的一些特征:

1. 交易永远不会被删除，只会被添加。

2. 产品永远不会被删除，但会以每分钟 100 秒的速度添加(显然这是一个更复杂的实际用例背后的虚构示例)。其中一小部分暂时成为孤儿

3. 我需要经常查询(最多每分钟一次)并且需要始终知道当前的孤立产品集是什么

Answer 1

我能想到的最好的是你在评论中的最后一个想法:a materialized view .

CREATE MATERIALIZED VIEW orphaned_products AS
SELECT *
FROM   products p
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id)

然后您可以使用此表(物化 View 只是一个表)作为大表 products 的替代品，用于处理孤立产品的查询 - 显然对性能有很大影响(a少 100 行而不是 1 亿行)。物化 View 需要 Postgres 9.3，但根据评论，这就是您正在使用的。您可以在早期版本中轻松地手动实现它。

但是，物化 View 是快照，不会动态更新。 (无论如何，这可能会使任何性能优势无效。)要更新，您运行(昂贵的)操作:

REFRESH MATERIALIZED VIEW orphaned_products;

您可以在战略上适当的时间点执行此操作，并使多个后续查询从中受益，具体取决于您的业务模型。

当然，您会在 orphaned_products.id 上有一个索引，但是对于一个只有几百行的小表来说这不是很重要。

如果您的模型是永远不会删除交易的，您可以充分利用它。手动创建一个类似的表:

CREATE TABLE orphaned_products2 AS
SELECT *
FROM   products p
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id);

当然，您可以通过截断和重新填充来刷新“物化 View ”，就像第一个 View 一样。但关键是要避免昂贵的操作。您真正需要的只是:

• 添加新产品到orphaned_products2。
  用 trigger 实现插入产品后。

• 一旦引用行出现在表 transactions 中，就从 orphaned_products2 中删除产品。
  使用触发器 AFTER UPDATE OF product_id ON transactions 实现。 仅如果您的模型允许更新 transations.products_id - 这将是非常规的事情。
  还有一个 AFTER INSERT ON transactions。

所有相对便宜的操作。

• 如果交易也可以被删除，您将需要另一个触发器来添加孤立产品AFTER DELETE ON transactions - 这会更昂贵一些。对于每笔已删除的交易，您需要检查它是否是最后一次引用相关产品，并在这种情况下添加一个孤儿。可能仍然比刷新整个物化 View 便宜很多。

真空

在您提供更多信息后，我还建议 custom settings for aggressive vacuuming orphaned_products2，因为它会产生大量死行。