Postgresql

存在子選擇與內部連接？

  • December 15, 2017

我正在進入我的神秘查詢的下一個級別。看起來存在內部有一個子選擇，但在同一個表上。我認為這可能可以通過INNER JOIN更高層來簡化。

使用 PostgreSQL 9.4.2。

表定義（/d+）：https
://gist.github.com/neezer/879f5d3649ca1903c6f3 基數：

billing_pricequote：1,462,625 行

billing_pricequotestatus：3,331,657 行

billing_lineitem：43,687,855 行

這是原始查詢，不建議對裡面的子查詢進行修改EXISTS：

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                               AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND EXISTS(
 SELECT s1.quote_id
 FROM billing_pricequotestatus s1
 INNER JOIN (
   SELECT DISTINCT ON (quote_id) quote_id, MAX(created_at) AS max_created_at
   FROM billing_pricequotestatus
   WHERE quote_id=i.quote_id
   GROUP BY quote_id, created_at
   ORDER BY quote_id, created_at DESC
 ) AS s2
 ON s1.quote_id = s2.quote_id
 AND s1.created_at = s2.max_created_at
 WHERE s1.name IN ('adjustment','payment','billable')
)
GROUP BY i.quote_id, i.acct_id
;

我注意到看起來很奇怪的部分是SELECTon billing_pricequotestatus，然後是INNER JOIN.

我嘗試通過我的其他 SO 文章的修改來改變它：

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                               AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND EXISTS(
 SELECT quote_id, MAX(created_at) AS max_created_at
 FROM billing_pricequotestatus
 WHERE quote_id=i.quote_id
 AND name IN ('adjustment','payment','billable')
 GROUP BY quote_id
)
GROUP BY i.quote_id, i.acct_id
;

這將我的執行時間減少了一半（約 40 秒到約 20 秒），但產生的結果略有不同（原始查詢返回 28,895 行，但我的新查詢返回 28,917 行）。我不清楚為什麼我的修改沒有產生等效的輸出（它需要）。

EXPLAIN ANALYZE對於這兩個查詢

解釋原始查詢 depesz.com 的分析。

非常感謝任何幫助/指導！

我嘗試用​​ a 更新@ypercubeᵀᴹ 的答案LATERAL JOIN，並且性能似乎大致相同（每個獲勝次數都相同，相差不到一秒）：

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
LEFT JOIN LATERAL
( SELECT name
 FROM billing_pricequotestatus
 WHERE quote_id = i.quote_id
 ORDER BY created_at DESC
 LIMIT 1
) pqs ON true
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                               AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND pqs.name IN ('adjustment', 'payment', 'billable')
GROUP BY i.quote_id, i.acct_id
;

解釋分析。

還有什麼其他建議可以讓這個時間低於 10 秒嗎？

據我了解，您的子查詢的目的：

選擇其中最新相關條目billing_pricequotestatus具有限定符的行name。

第二次查詢不正確

我不清楚為什麼我的修改沒有產生等效的輸出

第一個查詢從中挑選最新行billing_pricequotestatus並檢查是否name符合條件 ( name IN ('adjustment','payment','billable'))。

第二個查詢是向後的：它檢查任何符合條件的行name（不僅僅是最後一個）。EXISTS此外，在半連接中計算聚合也沒有意義。你不想要那個。而且不等價。

因此，您可以從第二個查詢中獲得更多行。

時間範圍不正確

這個謂詞是一團糟。效率低下且可能不正確 - 或者至少是一個定時炸彈：

WHERE pq.date_applied AT TIME ZONE 'PST'
BETWEEN '2016-02-02T00:00:00'::timestamp
AND '2016-03-03T22:27:41.734102-08:00'::timestamptz

該列date_applied的類型為**timestamptz**。該構造AT TIME ZONE 'PST'將其轉換為類型timestamp，並按硬編碼為時區縮寫“PST”的時間偏移量進行移位——這從一開始就是一個糟糕的舉動。它使表達式不可分割。這更昂貴，更重要的是，排除在date_applied.

更糟糕的是，時區縮寫'PST'不知道 DST 或任何歷史性的時間變化。如果您的時區有（或過去有）夏令時，並且您的設置跨越不同的 DST 時段，則您目前的表達式很可能是不正確的：

您需要使用適用的時區名稱而不是縮寫來獲得一致的本地時間 - 這更加昂貴。

還有另一個問題：雖然列值移動了硬編碼的時間偏移量（‘PST’），但您的上限'2016-03-03T22:27:41.734102-08:00'::timestamptz是提供的，timestamptz並且會默默地強制匹配數據類型timestamp。由於沒有提供明確的時間偏移量，因此強制轉換預設為目前會話的時區。因此，您可以根據會話的目前時區設置獲得不同的結果。我想不出一個有意義的案例。

不要做任何這些。根本不要將timestamptz列轉換date_applied為本地時間***，***不要像你一樣混合數據類型，不要混合不同的投射方式。而是按原樣使用該列並提供timestamptz參數。

詢問

SELECT i.quote_id, i.acct_id AS account_id, sum(i.delta_amount) AS amt
FROM   billing_pricequote pq
JOIN   LATERAL (
  SELECT name
  FROM   billing_pricequotestatus
  WHERE  quote_id = pq.id
  ORDER  BY created_at DESC
  LIMIT  1
  ) pqs ON pqs.name IN ('adjustment', 'payment', 'billable')
JOIN   billing_lineitem i ON i.quote_id = pq.id
WHERE  pq.date_applied BETWEEN (timestamp '2016-02-02T00:00:00' AT TIME ZONE 'PST')  -- !
                          AND timestamptz '2016-03-03T22:27:41.734102-08:00'
GROUP  BY 1,2;

請注意LATERAL連接，但不要LEFT JOIN立即INNER JOIN實現您的謂詞。

或者使用@ypercube 概述的等效相關子查詢。不確定哪個更快。

另請注意，我LATERAL JOIN在加入billing_pricequote大表之前建立了billing_lineitem基礎。這樣我們可以儘早消除行，這應該更便宜。

指數

目前，您將獲得：

billing_pricequote pq 上的序列掃描

您的 1,5M 行中只有 70k 被選中，大約是 5%。上的索引date_applied可能會有所幫助，但作用不大。但是，如果您可以從中獲取僅**索引掃描**，則此多列索引應該會有所幫助：

CREATE INDEX foo ON billing_pricequotestatus (quote_id, created_at DESC, name);

name_id使用而不是name如下建議的更有效。

統計數據

Postgres 高估了您的時間範圍的選擇性：

（成本=0.00..88,546.50行=7,313寬度=4）（實際時間=2.353..767.408 行=70,623循環=1）

它可能有助於增加僅列的統計目標date_applied。詳情在這裡：

表定義

範例billing_pricequotestatus：

name似乎是幾種可能的類型之一。規範化更多內容並僅使用 4 字節integer引用查找表而不是varchar(20)重複超過 3.3M 行將有助於提高性能。此外，像我展示的那樣對列重新排序（如果可能）會有所幫助：

  Column   |           Type           |              Modifiers
------------+--------------------------+------------------------------------------
id         | integer                  | not null default nextval('...
quote_id   | integer                  | not null
created_at | timestamp with time zone | not null
updated_at | timestamp with time zone | not null
name_id    | integer                  | not null **REFERENCES name_table(name_id)**
notes      | text                     | not null

有關對齊和填充，請參見上面的連結。要測量行大小：

而且“名稱”不是一個好的標識符。我會改用描述性的東西。

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

相關問答

Postgresql

如何加快查詢時間序列中的最後一個值？

  • February 28, 2021
檢視問答
Postgresql

Unix 時間戳與 timestamptz 索引性能

  • February 13, 2021
檢視問答
Postgresql

僅選擇表中的最後一行作為值列表

  • April 28, 2019
檢視問答
Postgresql

子查詢中的慢左連接橫向

  • February 27, 2019
檢視問答
Postgresql

優化大表上的 LATERAL JOIN 查詢

  • August 29, 2017
檢視問答
Postgresql

優化查詢以查找評論文章的前 N 個使用者

  • May 12, 2016
檢視問答