為什麼視圖的查詢中沒有下推 WHERE 子句？

使用 Postgres 9.4，我經常執行以下查詢：

SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

所以我決定創建一個視圖：

CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
ORDER BY recipient, timestamp DESC;

我剛剛意識到，如果我查詢我的視圖，就像SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB我得到一個明顯更差的性能一樣。

在執行EXPLAIN ANALYZE這兩個查詢時，我發現原因是第二種情況下的數據庫會顯示所有記錄，進行左連接，然後應用該WHERE子句。換句話說，該WHERE子句不會下推到視圖的查詢中。我也嘗試ORDER BY從視圖中刪除，但數據庫仍然執行LEFT JOIN完整數據而不是過濾集。

這種行為的原因是什麼？有沒有辦法在使用視圖時獲得相當的性能？

你可以像這樣創建一個函式；

CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp (
 time_a timestamp,
 time_b timestamp
)
RETURNS TABLE (
 recipient varchar,
 "timestamp" timestamp
) AS
$$
BEGIN
 RETURN QUERY
   SELECT DISTINCT ON (m.recipient) 
       m.recipient,
       m."timestamp"
     FROM messages m
     LEFT JOIN identities i ON m.recipient = i.name
     WHERE 
       m."timestamp" BETWEEN time_a AND time_b
     ORDER BY 
       m.recipient,
       m."timestamp" DESC;
END;
$$
LANGUAGE 'plpgsql';

然後您可以像使用表格一樣使用該功能

 SELECT *
        FROM get_messages_by_timestamp('2015-01-01', '2015-01-02')

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