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

1

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
source

1 answer

3

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