Are PostgreSQL VIEWS created newly each time they are queried against?

Question

I am creating a web app which has some complex underlying associations. In order to solve several issues I was having I created a UNION View. There are probably a lot of other ways this could be solved.

But I am now considering the efficiency of my design, and I wanted to know if a VIEW is newly created each time it is queried, or is it only created once, and kept updated.

To elaborate, if I have table_a (100 records) and table_b (100 records) and make a UNION View, then I have created a view with 200 records.

Does this whole process occur each time I do a select against the View?

Again, obviously each time I update the underlying table records the view is updated, but does the view update this one record or does it recreate the whole view from scratch?

Dale

Answer 1

Use EXPLAIN to see how a VIEW is executed, you'll see the same results as a normal query.

EXPLAIN
SELECT * FROM name_of_your_view WHERE foo = 23;

PostgreSQL will try to optimize the inner query, even when you join views, have views using other views, etc. Try to avoid situations where a VIEW has to be executed before the optimizer can do it's (great) job. Aggregates, ORDER BY and LIMIT are examples of potential problems when using inside nested views. Just use EXPLAIN to see what is going on.