Postgres: What is the best use of views or setof or function that returns table?


Out of curiosity to see the performance of the query, since within the view that it is a virtual table it can be generated just like doing a function that returns a SETOF if it were a query of a table, if it were of several inner join then the function would be that it returns a table. Well I the last two and not views

asked by Luis Castillo Elera 24.04.2016 в 07:40

1 answer


In my opinion, in the general case, a query (CREATE VIEW) is better than a function that returns a SETOF. Postgresql (like most modern database engines) optimizes queries to achieve the best execution plan. In addition, they adapt to the current data of the base 1 .

The advantage of the view is that when using it within a query the execution engine can decide to optimize it by combining what is inside the view with the query that uses it (without altering the results, obviously).

When using a function, optimization is unlikely.

1 That's why it's important to run an ANALYZE every once in a while.

answered by 24.04.2016 в 15:12