非常缓慢的postgresql查询与深层次结构

Question

我有一个查询，我想获得我的数据库中的特定用户（所有者表）的所有交易。数据库非常规范化，所以从事务到所有者遍历许多表。是我与有关外键的表如下：

**owners** 
------- 
id 

**store_shops** 
----------- 
id 
owner_id 

**service_shops** 
------------- 
id 
owner_id 

**products** 
------------- 
id 
store_shop_id 

**services** 
------------ 
id 
service_shop_id 

**order_services** 
------------------ 
id 
service_id 
order_id 

**order_products** 
------------------ 
id 
product_id 
order_id 


**orders** 
---------- 
id 
transaction_id 


**transactions** 
---------------- 
id 
refund_transaction_id 
amount 

我有以下查询：

SELECT DISTINCT ON (sales.id) sales.id, sales.amount FROM transactions sales 
LEFT OUTER JOIN transactions refunds ON refunds.id = sales.refund_transaction_id 
LEFT OUTER JOIN orders ON orders.transaction_id = trans.id OR orders.transaction_id = refunds.id 
LEFT OUTER JOIN order_services ON order_services.order_id = orders.id 
LEFT OUTER JOIN order_products ON order_products.order_id = orders.id 
LEFT OUTER JOIN products ON products.id = order_products.product_id 
LEFT OUTER JOIN services ON services.id = order_services.service_id 
LEFT OUTER JOIN service_shops ON service_shops.id = services.service_shop_id 
LEFT OUTER JOIN store_shops ON store_shops.id = products.store_shop_id 
LEFT OUTER JOIN owners service_shop_owners ON service_shop_owners.id = service_shops.owner_id 
LEFT OUTER JOIN owners store_shop_owners ON store_shop_owners.id = store_shops.owner_id 
WHERE (service_shop_owners.id = 26930 OR store_shop_owners.id = 26930) 

这给了我所期望的结果。唯一的麻烦是，在成千上万条记录的数据集上，它变得无法缓慢。

当谈到SQL时，我并不是很先进，但我意识到所有的左外连接都不是非常有效。

有没有更好的方法来处理这个查询？或者我将不得不非常规化数据库并在事务表中存储更多信息？

UPDATE 使用下面Wyzard的回答，我现在有这个疑问：

SELECT trans.id, trans.amount, refunds.id 
FROM 
    service_shops 
    JOIN services ON services.service_shop_id = service_shop.id 
    JOIN order_services ON order_services.service_id = services_id 
    JOIN orders ON orders.id = order_services.order_id 
    JOIN transactions trans ON trans.id = orders.transaction_id 
    LEFT JOIN transactions refunds ON refunds.id = trans.refund_transaction_id 
WHERE service_shops.owner_id = 26930 
UNION 
SELECT trans.id, trans.amount, refunds.id 
FROM 
    store_shops 
    JOIN products ON store_shops.id = products.store_shop_id 
    JOIN order_products ON order_products.product_id = products.id 
    JOIN orders ON orders.id = order_products.order_id 
    JOIN transactions trans ON trans.id = orders.transaction_id 
    LEFT JOIN transactions refunds ON refunds.id = trans.refund_transaction_id 
WHERE store_shops.owner_id = 2693 

这是非常快，一个大的提升。现在唯一的问题是，两个LEFT JOIN transactions refunds ON refunds.id = trans.refund_transaction_id似乎并没有抓取关联退款transactions.我假设这是因为他们没有与他们直接关联order，所以WHERE条款将它们过滤出来。

Answer 1

首先，EXPLAIN是你的朋友：它告诉你关于数据库将用来运行查询的查询计划，所以你可以看到瓶颈在哪里。输出起初可能很难理解，但如果您使用pgAdmin，则其EXPLAIN菜单命令命令将为您提供一个更直观的nice graphical visualization。

---

其次，在你的WHERE子句中使用的值是在外部的长链的末端连接，这是低效的，因为数据库可能有做的所有连接和生产的每候选行只是为了获得所有者ID只能放弃大部分行，因为所有者ID与WHERE条件不匹配。

看起来您已经这样构建查询，因为从销售到所有者有两条独立的路径：通过产品或通过服务。这意味着您基本上一次执行两个不同的查询，强制数据库在实际来自服务的行上处理与产品相关的连接条件，反之亦然。它可能会更有效使用UNION两个单独的查询做其实并开始每一个从您使用的是过滤表：

SELECT col1, col2, etc 
FROM 
    owners 
    JOIN service_shops ON service_shops.owner_id = owners.id 
    JOIN services ON services.service_shop_id = service_shop.id 
    ...etc... 
WHERE owners.id = 26930 
UNION 
SELECT col1, col2, etc 
FROM 
    owners 
    JOIN store_shops ON store_shops.owner_id = owners.id 
    JOIN products ON store_shops.id = products.store_shop_id 
    ...etc... 
WHERE owners.id = 26930 

这应该允许数据库快速查找主人使用索引，然后使用另一个索引快速查找相关商店，依此类推。 （这是假设你有索引你的FK列，像service_shops.owner_id。如果没有，你应该。）

请注意，我已经写JOIN而不是LEFT OUTER JOIN。由于您没有在同一个查询中混合产品数据和服务数据，因此您不会将与产品相关的行无法加入到与服务相关的表中，反之亦然，因此您可能不需要外部连接。

另外，如果除了ID以外，您不需要owners表中的任何属性，则可以将该表保留在查询之外。只要做WHERE store_shops.owner_id = 26930。

---

第三，我发现它有助于结构FROM子句中使用外只有在他们真正需要的连接。假设你已经写了：

FROM 
    foo 
    LEFT JOIN bar ON bar.foo_id = foo.id 
    LEFT JOIN baz ON baz.bar_id = bar.id 

让我们假设你需要获得foo数据，即使它没有相关bar，但你不需要bar的数据，如果它没有相关baz - 或也许你知道永远不会有是 a bar没有关联baz。在这种情况下，你可以重写这样的查询：

FROM 
    foo 
    LEFT JOIN (
    bar 
    JOIN baz ON baz.bar_id = bar.id 
) ON bar.foo_id = foo.id 

根据我的经验，这往往是更有效的PostgreSQL。 （我不知道其他数据库。）

Answer 2

更改此：

WHERE (service_shop_owners.id = 26930 OR store_shop_owners.id = 26930) 

要这样：

WHERE 26930 IN (service_shop_owners.id, store_shop_owners.id) 

使用OR通常意味着该索引将不会被使用，但应与IN一起使用。

---

上述变化应该足以产生很大的差异。为了进一步改进查询，请颠倒表格的顺序，尤其是列表service_shop_owners作为FROM子句中的第一个表格。优化器应该为你做这件事，但通常不会。