需要在兩個表上建立索引的數據庫設計

使用 PostgreSQL（目前是 9.6，但可以升級），我目前有以下數據庫佈局，客戶可以在其中訂購產品，這些產品本身被分類（產品可能在多個類別中）：

Orders
id -- PRIMARY KEY
customer_id -- FOREIGN KEY (Customer - id)
product_id -> FOREIGN KEY (Product - id)

Products
id -- PRIMARY KEY

Categories
id -- PRIMARY KEY

Product_Categories
product_id -- FOREIGN KEY (Product - id)
category_id -- FOREIGN KEY (Category - id)

數據量

現在，我有相當多的訂單（~30M）和合理數量的類別（~1K）和客戶（~10K）。大約有 30,000 個產品，按類別平均有 3 個產品。產品可能偶爾會從一個類別轉移到另一個類別（假設每月一次洗牌）

查詢傾向

我的問題是我想讓以下類型的查詢快速執行：“獲取產品屬於 C 類的客戶的所有訂單”。那看起來像：

SELECT * FROM Orders 
JOIN Product_Categories ON Orders.product_id = Product_Categories.product_id
WHERE Orders.customer_id = X AND Product_Categories.category_id = Y

索引註意事項

我能想到的最好的索引是customer_idOrders 上的索引，由Product_Categories.product_id. 這導致了以下計劃（不是真正的計劃，因為我上面展示的設計是對實際案例的非常大的簡化）：

- Index Scan on Orders using index on customer_id ---> Returns ~10K Rows
- 10K Joins done by Index Lookup on the product_id index of Product_Categories (MAIN TIME CONSUMER)
- 9990 Rows Filtered Out.
- 10 Rows Returned

我想有一個索引 over (customer_id, category_id)，但我一直無法找到一種方法來做到這一點。我能想到的最佳解決方案是添加一列categories_id INTEGER[]，然後：

1. 使用包含在列表運算符中的categories_id和添加 GIN 索引。customer_id
2. 在 上創建 1000 個部分索引order_id。

在這兩種情況下，我都必須與↔關聯表categories_id中的更新同步，這很不幸。category``product

問題

我的問題是：

• 我是不是想多了？“過濾掉 10k”行是不是一個問題，我能想到的任何解決方案都會使問題變得更糟？
• 我錯過了什麼嗎？我可以在不更改數據庫架構的情況下提高效率嗎？
• 假設我應該更改我的數據庫架構，那麼最好的方法是什麼？

如果您有一個 indexon product_categories (category_id)以及您已經擁有的on orders (customer_id)那個，那麼這種類型的查詢應該非常快。您可以分別對每個表進行高度特定的索引掃描，然後對結果進行雜湊連接。

https://explain.depesz.com/s/JEpZ

如果這對您來說還不夠快，或者即使您有索引也無法使用這樣的計劃，那麼恐怕您將不得不向我們提供更多資訊，例如實際查詢計劃包括時間安排，以及您希望達到的時間。

引用自：https://dba.stackexchange.com/questions/237339