需要幫助了解 Postgres 的物化視圖

postgres 9.6.20

需要一些幫助來獲得關於 postgres 物化視圖發生了什麼的準確心智模型。

基於此來自 postgres 文件 ( https://www.postgresql.org/docs/9.6/rules-materializedviews.html ) “關於 PostgreSQL 系統目錄中物化視圖的資訊與表或所以對於解析器來說，物化視圖是一個關係，就像一個表或一個視圖。當一個物化視圖在查詢中被引用時，數據是直接從物化視圖返回的，就像從一個表中一樣；規則是僅用於填充物化視圖。” （強調我的）

我基於上述假設（以及我一直在閱讀的其他內容）：

• 然後製作一個墊子視圖將允許我查詢該新表
• 生成該視圖的查詢僅在 REFRESH 呼叫上執行
• 如果用於生成 mat 視圖的查詢結果返回約 30000 行，那麼這就是物化視圖表的大小

我的假設中的某些東西是錯誤的，我試圖理解是什麼。

我有一個很好的大（對我來說）查詢——一些 CTE、一些連接、一些將資訊聚合到 jsonb 列中作為我的物化視圖。

WITH
durations AS (SELECT DISTINCT ON(song) song, song_source_files.duration
   FROM song_source_files WHERE song_source_files.type = 'Full'
   ORDER BY song, updated_at DESC),
...<truncated more CTEs>
SELECT DISTINCT
   published_song.song,
   songs.bpm,
   ...<truncated>...
   COALESCE(durations.duration, 0) as duration,
   ...<truncated>
FROM
   published_song
   LEFT JOIN songs ON songs.id = published_song.song
   LEFT JOIN durations ON durations.song = published_song.song
   ...<truncated>...
   LEFT JOIN vocals ON vocals.song = published_song.song

CREATE UNIQUE INDEX IF NOT EXISTS "idx_{mv_name}_uuid" ON "{mv_name}" ("uuid");
...<truncated>

當我執行查詢時，大約需要 30 秒，並返回約 30000 行。

在 mat 視圖中，當我使用針對索引列的 WHERE 進行查詢時，它很快，正如預期的那樣。

當我這樣做EXPLAIN (ANALYZE, FORMAT JSON) SELECT count(*) FROM published_song_mv;大約需要 2.5 分鐘時，我會看到"Plan Rows": 12606570和"Actual Rows": 29536.

所以這就是我感到困惑的地方——我的期望是墊子視圖只適用於實際行。不知道為什麼 1200 萬的計劃行甚至進入等式（從某種意義上說，為什麼它會應用於約 30000 行的最終結果集 - 我知道我有一些粗糙的連接來生成視圖）？

因此，我正在尋求幫助，以糾正我腦海中對幕後發生的事情的心理模型。

使用 Postgres 9.6 - 這可能是一個因素 - 我應該考慮盡快升級嗎？

你所有的假設都是正確的。

你需要ANALYZE物化視圖來獲得正確的估計——這應該由 autovacuum 自動完成，所以我不確定那裡可能有什麼問題。

看起來您的物化視圖也非常臃腫。你需要執行VACUUM (FULL)它來解決這個問題。

兩者都指向 autovacuum 無法正常工作的方向。你應該對此進行調查。

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