mssql - sql group by having




優化GROUP BY查詢以檢索每個用戶的最新記錄 (2)

也許表上的不同索引會有所幫助。 試試這個: user_msg_log(user_id, aggr_date) 。 我並不認為Postgres會以distinct on進行最佳使用。

所以,我會堅持使用該索引並嘗試此版本:

select *
from user_msg_log uml
where not exists (select 1
                  from user_msg_log uml2
                  where uml2.user_id = u.user_id and
                        uml2.aggr_date <= :mydate and
                        uml2.aggr_date > uml.aggr_date
                 );

這應該用索引查找替換排序/分組。 它可能會更快。

我在Postgres 9.2中有下表(簡化形式)

CREATE TABLE user_msg_log (
    aggr_date DATE,
    user_id INTEGER,
    running_total INTEGER
);

它每個用戶和每天最多包含一條記錄。 每天將有大約500,000條記錄,為期300天。 running_total總是為每個用戶增加。

我想在特定日期之前有效地檢索每個用戶的最新記錄。 我的查詢是:

SELECT user_id, max(aggr_date), max(running_total) 
FROM user_msg_log 
WHERE aggr_date <= :mydate 
GROUP BY user_id

這非常慢。 我也嘗試過:

SELECT DISTINCT ON(user_id), aggr_date, running_total
FROM user_msg_log
WHERE aggr_date <= :mydate
ORDER BY user_id, aggr_date DESC;

具有相同的計劃,同樣緩慢。

到目前為止,我在user_msg_log(aggr_date)上有一個索引,但沒有多大幫助。 我應該使用其他任何索引來加快速度,還是以任何其他方式實現我想要的目標?


為獲得最佳讀取性能,您需要一個多列索引

CREATE INDEX user_msg_log_combo_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST)

要使索引僅掃描成為可能,請添加其他不需要的列running_total

CREATE INDEX user_msg_log_combo_covering_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST, running_total)

為什麼DESC NULLS LAST

對於每個user_id或小表的行,簡單的DISTINCT ON是最快和最簡單的解決方案之一:

對於每個user_id許多行, 鬆散的索引掃描將(更高)更有效。 這在Postgres中沒有實現(至少在Postgres 10中實現),但有一些方法可以模仿它:

1.沒有唯一用戶的單獨表格

以下解決方案超出了Postgres Wiki中的內容
使用單獨的users表,下面2中的解決方案通常更簡單,更快捷。

1A。 使用LATERAL連接的遞歸CTE

常用表格表達式要求Postgres 8.4+
LATERAL要求Postgres 9.3+

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT user_id, aggr_date, running_total
   FROM   user_msg_log
   WHERE  aggr_date <= :mydate
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id, u.aggr_date, u.running_total
   FROM   cte c
   ,      LATERAL (
      SELECT user_id, aggr_date, running_total
      FROM   user_msg_log
      WHERE  user_id > c.user_id   -- lateral reference
      AND    aggr_date <= :mydate  -- repeat condition
      ORDER  BY user_id, aggr_date DESC NULLS LAST
      LIMIT  1
      ) u
   )
SELECT user_id, aggr_date, running_total
FROM   cte
ORDER  BY user_id;

這在Postgres的當前版本中是優選的,並且檢索任意列很簡單。 第2a章有更多解釋 下面。

1B。 具有相關子查詢的遞歸CTE

方便檢索單個列整行 。 該示例使用表的整行類型。 其他變體是可能的。

WITH RECURSIVE cte AS (
   (
   SELECT u  -- whole row
   FROM   user_msg_log u
   WHERE  aggr_date <= :mydate
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT u1  -- again, whole row
           FROM   user_msg_log u1
           WHERE  user_id > (c.u).user_id  -- parentheses to access row type
           AND    aggr_date <= :mydate     -- repeat predicate
           ORDER  BY user_id, aggr_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.u).user_id IS NOT NULL        -- any NOT NULL column of the row
   )
SELECT (u).*                               -- finally decompose row
FROM   cte
WHERE  (u).user_id IS NOT NULL             -- any column defined NOT NULL
ORDER  BY (u).user_id;

使用cu IS NOT NULL測試行值可能會產生誤導。 如果測試行的每一列都是NOT NULL ,則只返回true如果包含單個NULL值,則會失敗。 (我的答案中有一段時間我有這個錯誤。)相反,在上一次迭代中找到了一個行,測試了一行定義為NOT NULL的行(就像主鍵一樣)。 更多:

2b章中對此查詢的更多解釋 下面。
相關答案:

2.具有單獨的users

只要每個相關的user_id只有一行,表格佈局就不重要了。 例:

CREATE TABLE users (
   user_id  serial PRIMARY KEY
 , username text NOT NULL
);

理想情況下,表是物理排序的。 看到:

或者它足夠小(低基數),這幾乎不重要。
否則,對查詢中的行進行排序有助於進一步優化性能。 見Gang Liang的補充。

2A。 LATERAL加入

SELECT u.user_id, l.aggr_date, l.running_total
FROM   users u
CROSS  JOIN LATERAL (
   SELECT aggr_date, running_total
   FROM   user_msg_log
   WHERE  user_id = u.user_id  -- lateral reference
   AND    aggr_date <= :mydate
   ORDER  BY aggr_date DESC NULLS LAST
   LIMIT  1
   ) l;

JOIN LATERAL允許在同一查詢級別引用前面的FROM項。 每個用戶只能獲得一個索引(-only)查找。

通過在另一個答案中對Gang Liang建議的users表進行排序來考慮可能的改進。 如果users表的物理排序順序恰好與user_msg_log上的索引匹配,則不需要這樣。

即使您在user_msg_log有條目,也不會在users表中找不到users結果。 通常,您將具有強制引用完整性的外鍵約束來規則。

對於user_msg_log沒有匹配條目的任何用戶,您也沒有獲得一行。 這符合你原來的問題。 如果你需要在結果中包含這些行,請使用LEFT JOIN LATERAL ... ON true而不是CROSS JOIN LATERAL

此表單最適合檢索每個用戶的多行 (但不是全部)。 只需使用LIMIT n而不是LIMIT 1

實際上,所有這些都會做同樣的事情:

JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...

但後者的優先級較低。 顯式JOIN在逗號之前綁定。

2B。 相關子查詢

單行檢索單個列的好選擇。 代碼示例:

多列可能相同,但您需要更多智能:

CREATE TEMP TABLE combo (aggr_date date, running_total int);

SELECT user_id, (my_combo).*  -- note the parentheses
FROM (
   SELECT u.user_id
        , (SELECT (aggr_date, running_total)::combo
           FROM   user_msg_log
           WHERE  user_id = u.user_id
           AND    aggr_date <= :mydate
           ORDER  BY aggr_date DESC NULLS LAST
           LIMIT  1) AS my_combo
   FROM   users u
   ) sub;
  • 與上面的LEFT JOIN LATERAL一樣,此變體包括所有用戶,即使沒有user_msg_log條目也是user_msg_logmy_combo會得到NULL ,如果需要,可以使用外部查詢中的WHERE子句輕鬆過濾。
    Nitpick:在外部查詢中,您無法區分子查詢是否未找到行或返回的所有值都是NULL - 相同的結果。 您必須在子查詢中包含NOT NULL列才能確定。

  • 相關子查詢只能返回單個值 。 您可以將多個列包裝為複合類型。 但是為了稍後分解它,Postgres需要一種眾所周知的複合類型。 匿名記錄只能在提供列定義列表的情況下進行分解。

  • 使用已註冊類型(如現有表的行類型)或創建類型。 使用CREATE TYPE顯式(和永久)註冊複合類型,或創建臨時表(在會話結束時自動刪除)以臨時提供行類型。 轉換為該類型: (aggr_date, running_total)::combo

  • 最後,我們不希望在同一查詢級別上分解combo 。 由於查詢規劃器的弱點,這將為每列評估子查詢一次​​(直到Postgres 9.6 - 計劃為Postgres 10進行改進)。 相反,將其作為子查詢並在外部查詢中進行分解。

有關:

使用100k日誌條目和1k用戶演示所有4個查詢:
SQL小提琴 - 第9.6頁
db <> here小提琴 - 第10頁





postgresql-performance