With Postgres 9.4, I'm doing the following query quite often:
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;
So I decided to create a view:
CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name ORDER BY recipient, timestamp DESC;
I just realized if I query my view like
SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB I get a significantly worse performance.
EXPLAIN ANALYZE on both queries, I found out the reason is that the database in the second case brings up all the records, does the left join and then applies the
WHERE clause. In other words, the
WHERE clause is not pushed down into the view's query. I also tried to remove the
ORDER BY from the view, but still the database performs the
LEFT JOIN on full data rather on the filtered set.
What is the reason of this behavior? Is there a way I can get a comparable performance when using view?