4 ms·
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 que
by firloop 4y ago
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.
- dafelst 4y agoIIRC 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).
- firloop 4y agoPostgres 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.
- sarchertech 4y agoI 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?
- deleted 4y ago[deleted]
- tomnipotent 4y agoPostgres 12 fixed the CTE issue.
- bavell 4y agoPostgres docs on CTEs: https://www.postgresql.org/docs/current/queries-with.html https://www.postgresql.org/docs/current/queries-with.html
- deleted 4y ago[deleted]
- AdrianB1 4y agoThere is no impact with views in MS SQL. You can also have indexed views and filtered indexes, so you can have even better performance.