Hacker News Reader

Views can really bite you performance wise, at least with Postgres. If you add a WHERE against a query on a view, Postgres (edit: often) won't merge in your queries' predicates with the predicates of the view, often leading to large table scans.

View on HN

IIRC Postgres has supported predicate push down on trivial views like this for over a decade now, and possibly even more complex views these days (I haven't kept up with the latest greatest changes).

Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.

I haven’t had any problems with this at all and I’ve been using joins in my views for years.

Are you using CTEs in your views?

Postgres 12 fixed the CTE issue.

Postgres docs on CTEs: https://www.postgresql.org/docs/current/queries-with.html

There is no impact with views in MS SQL. You can also have indexed views and filtered indexes, so you can have even better performance.

Reply on news.ycombinator.com