Re: Performance of views

Simon,

* Simon Windsor (simon(dot)windsor(at)cornfield(dot)me(dot)uk) wrote:
> Generally, I have avoided using VIEWS within application code and only
> used them for client interfaces, the sole reason being the performance
> of views against tables.

Views really shouldn't have a large impact on overall performance. In
PostgreSQL, that's even more true. In general, I would strongly
recommend moving complex queries from your application into views in the
database. The performance difference really should be minimal, while
the maintainability is improved.

> * Data within a view is not necessary sequential, unlike a table

I've got no idea what you're talking about here, to be honest. Pulling
data out of a table has no guarenteed ordering to it unless you
explicitly ask for one, and you can do that in a view too.

> * Higher overhead mapping to original tables and indexes

This just plain isn't true in PG, at least, and I'd think most other
sensible databases..

> * Danger of linking views and tables and not utilising utilising
> underlying tables properly.

If you push your complicated queries into your views and keep the
application code simpler, identifying and fixing performance or other
issues often becomes easier, and easier to fix..

> Am I right to avoid to VIEWS within application code?

No.

Thanks,

Stephen