sql - 具有深层次结构的非常慢的 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 方面不是很熟练，但我意识到所有的 LEFT OUTER JOIN 都不是很有效。

我有更好的方法来处理这个查询吗？还是我必须对数据库进行一些非规范化处理并在事务表中存储更多信息？

更新使用下面 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 交易退款 ON refunds.id = trans.refund_transaction_id 似乎没有获取关联的退款 transactions。 我假设这是因为它们没有直接关联的 order，因此 WHERE 子句将它们过滤掉。

Answer 1

改变这个:

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一起使用。

---

以上变化应该足以产生很大的不同。为了进一步改进 cquery，颠倒表的顺序，特别是将 service_shop_owners 列为 FROM 子句中的第一个表。优化器应该为您做这件事，但通常不会。