我應該在表之間複製列以加快 SUM 等聚合嗎？

我在 PostgreSQL 10.12 數據庫中有兩個表：

• waste_card（id (PK)、user_id、manufacturer_date、transfer_date、address_id、card_type、..（和其他 card_specific_firelds）
• wastes(user_id, waste_card_id(FK), amount, … (其他 waste_specific_fields)

我需要經常按地址和製造日期或轉移日期列出和計算使用者浪費（2 個單獨的 API 端點）

現在列出我觸發 2 個查詢的項目：1-載入使用者卡，然後 2-載入浪費

SELECT waste_cards.* 
FROM   waste_cards 
WHERE  waste_cards.user_id = $1 
      AND (waste_cards.manufacture_date < '$2') 
      AND (waste_cards.address_id = $3) 
LIMIT $4

SELECT wastes.* 
FROM   wastes 
WHERE  wastes.waste_card_id IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)  

只計算我開火的數量：

SELECT SUM(wastes.amount) 
FROM   waste_cards 
LEFT   OUTER JOIN wastes 
      ON wastes.waste_card_id = waste_cards.id 
WHERE  waste_cards.user_id = $1 
      AND (waste_cards.manufacture_date < '$2') 
      AND (waste_cards.address_id = $3) 

我應該添加

$$ manufacture_date, transfer_date, address_id $$要浪費的欄位以加快查詢速度，如下所示：

SELECT SUM(amount) 
FROM   wastes 
WHERE  user_id = $1 
      AND address_id = $2 
      AND manufacture_date < '$3'

我剛開始使用這個系統。查詢少於 150 毫秒。今天它的大約 1K 使用者每張卡有 100-200 張卡，每張卡上有 2-5 個廢物。還不算多，但我們想在下個月增加其他 10-15K 使用者。所以我想問一下這個數據庫模式是否正確，或者我應該在投入生產之前更改它。

數據庫設計

waste_card和之間似乎是 1:n 的關係wastes。實際的表定義（CREATE TABLE語句）會澄清。

不要重複（重複）多表中的user_id、manufacture_date和列。這會使您的表格膨脹，引入維護問題，提出真正的資訊來源問題，公然無視規範化等。address_id``wastes

查詢

在計算總和時，只需使用普通的[INNER] JOIN. 沒有相關條目的廢紙waste不會改變結果，可以排除：

SELECT SUM(w.amount) AS total_waste
FROM   waste_card wc
JOIN   wastes     w  ON w.waste_card_id = wc.id
WHERE  wc.user_id = $1 
AND    wc.manufacture_date < $2
AND    wc.address_id = $3;

LEFT JOIN有意義…

• 列出每個條目的總和*waste_card*
• 並包括沒有條目的卡片waste

但是通常最好使用**LATERAL**子查詢來計算每張卡的總和。聚合函式保證子查詢中只有一行，所以我們也可以切換到CROSS JOIN LATERAL（就像 Andriy 指出的那樣）：

SELECT wc.*  -- or better just the columns you need
    , w.sum_waste
FROM   waste_card wc
CROSS  JOIN LATERAL (
  SELECT SUM(w.amount) AS sum_waste
  FROM   wastes w
  WHERE  w.waste_card_id = wc.id
  ) w
WHERE  wc.user_id = $1 
AND    wc.manufacture_date < $2
AND    wc.address_id = $3;

看：

• 加入（選擇……） ue ON 1=1？
• 計算有多少日期範圍涵蓋系列中每個日期的最快方法
• 將多對多連接的右側轉換為數組

覆蓋指數

為了加快任一查詢，索引“覆蓋”wastes.amount可能會付費（需要 Postgres 11 或更高版本）：

CREATE INDEX your_idx_name ON wastes(waste_card_id) INCLUDE (amount);

對於 Postgres 10 或更早版本，回退到多列索引：

CREATE INDEX your_idx_name ON wastes(waste_card_id, amount);

看：

• 具有主鍵和外鍵的查詢是否比僅具有主鍵的查詢執行得更快？
• PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？

---

旁白： LIMIT沒有ORDER BY（就像在您的第一個查詢中一樣）會產生任意結果。通常您要添加ORDER BY以確定結果。

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